第11章：使用Python打造MySQL专家系统
1.Python语言高级特性
1).深入浅出Python生成器
1).生成器函数：与普通函数定义类似，使用yield语句而不是return语句返回结果。yield语句一次返回一个结果，在每个结果中间挂起函数的状态，以便下次从它离开的地方继续执行 2).生成器表达式：类似于列表推导，但是，生成器返回按需产生结果的一个对象，而不是一次构建一个结果列表 使用生成器的例子，使用生成器返回自然数的平方： def gensquares(N): for i in range(N): yield i ** 2 def main(): for item in gensquares(234): print(item) if __name__ == '__main__': main() 使用普通函数实现 def gensquares(N): res = [] for i in range(N): res.append(i*i) return res def main(): for item in gensquares(234): print(item) if __name__ == '__main__': main()
2).深入浅出Python装饰器
Python中函数可以赋值给另外一个变量名，函数可以嵌套，以及函数对象可以作为另外一个函数的参数等 1、函数对象 2、嵌套函数 3、装饰器原型 def bread(f): def wrapper(*args, **kwargs): print("begin") f() print("end") return wrapper @bread def say_hi(): print("Hi") def main(): say_hi() if __name__ == '__main__': main()
3).Python上下文管理器
1、with语句形式化定义 2、上下文管理器的应用场景 3、上下文管理器协议
2.MySQL数据库
1).Python连接数据库
import os if os.getenv('DB', 'MySQL') == 'MySQL': import pymysql as db else: import sqlite3 as db def get_conn(**kwargs): if os.getenv('DB', 'MySQL') == 'MySQL': return db.connect(host=kwargs.get('host', 'localhost'), user=kwargs.get('user'), passwd=kwargs.get('passwd'), port=kwargs.get('port', 3306), db=kwargs.get('db')) else: return db.connect(database=kwargs.get('db')) def execute_sql(conn, sql): with conn as cur: cur.execute(sql) def create_table(conn): sql_drop_table = "DROP TABLE IF EXISTS student" sql_create_table = """create table student (sno int(11) not null,sname varchar(20) default null,sage int(11) default null,primary key (sno)) engine=InnoDB default charset=utf8""" for sql in [sql_drop_table, sql_create_table]: execute_sql(conn, sql) def insert_data(conn, sno, sname, sage): insert_format = "insert into student(sno, sname, sage) values ({0}, '{1}', {2})" sql = insert_format.format(sno, sname, sage) execute_sql(conn, sql) def main(): conn = get_conn(host='127.0.0.1', user='root', passwd='msds007', port=3306, db='test') try: create_table(conn) insert_data(conn, 1, 'zhangsan', 20) insert_data(conn, 2, 'lisi', 21) with conn as cur: cur.execute("select * from student") rows = cur.fetchall() for row in rows: print(row) finally: if conn: conn.close() if __name__ == '__main__': main()
2).使用上下文管理器对数据库连接进行管理
import os from contextlib import contextmanager if os.getenv('DB', 'MySQL') == 'MySQL': import pymysql as db else: import sqlite3 as db @contextmanager def get_conn(**kwargs): if os.getenv('DB', 'MySQL') == 'MySQL': conn = db.connect(host=kwargs.get('host', 'localhost'), user=kwargs.get('user'), passwd=kwargs.get('passwd'), port=kwargs.get('port', 3306), db=kwargs.get('db')) try: yield conn finally: if conn: conn.close() def execute_sql(conn, sql): with conn as cur: cur.execute(sql) def create_table(conn): sql_drop_table = "DROP TABLE IF EXISTS student" sql_create_table = """create table student (sno int(11) not null,sname varchar(20) default null,sage int(11) default null,primary key (sno)) engine=InnoDB default charset=utf8""" for sql in [sql_drop_table, sql_create_table]: execute_sql(conn, sql) def insert_data(conn, sno, sname, sage): insert_format = "insert into student(sno, sname, sage) values ({0}, '{1}', {2})" sql = insert_format.format(sno, sname, sage) execute_sql(conn, sql) def main(): conn_args = dict(host='127.0.0.1',user='root',passwd='msds007',port=3306,db='test') with get_conn(**conn_args) as conn: create_table(conn) insert_data(conn, 1, 'zhangsan', 20) insert_data(conn, 2, 'lisi', 21) with conn as cur: cur.execute("select * from student") rows = cur.fetchall() for row in rows: print(row) if __name__ == '__main__': main()
3).案例：从csv文件导入数据到MySQL
import os import csv from collections import namedtuple from contextlib import contextmanager if os.getenv('DB', 'MySQL') == 'MySQL': import pymysql as db else: import sqlite3 as db @contextmanager def get_conn(**kwargs): if os.getenv('DB', 'MySQL') == 'MySQL': conn = db.connect(host=kwargs.get('host', 'localhost'), user=kwargs.get('user'), passwd=kwargs.get('passwd'), port=kwargs.get('port', 3306), db=kwargs.get('db')) try: yield conn finally: if conn: conn.close() def execute_sql(conn, sql): with conn as cur: cur.execute(sql) def get_data(file_name): with open(file_name) as f: f_csv = csv.reader(f) headings = next(f_csv) Row = namedtuple('Row', headings) for r in f_csv: yield Row(*r) def main(): conn_args = dict(host='127.0.0.1',user='root',passwd='msds007',port=3306,db='test') with get_conn(**conn_args) as conn: SQL_FORMAT = """insert into student(sno,sname,sage) values({0},'{1}',{2})""" for t in get_data('data.csv'): sql = SQL_FORMAT.format(t.sno, t.sname, t.sage) execute_sql(conn, sql) if __name__ == '__main__': main()
3.Python并发编程
1).Python中的多线程
Python默认的解释器，由于全局解释器锁的存在，确实在任意时刻都只有一个线程在执行Python代码，致使多线程不能充分利用机器多核的特性 Python由于GIL(Global Interpreter Lock)锁的原因，并没有真正的并发 Python标准库提供了两个与线程相关的模块，分别是thread和threading thread是低级模块，threading是高级模块，threading对thread进行了封装 1、创建线程 2、如何给线程传递参数 3、线程的常用方法 4、通过继承创建线程
2).线程同步与互斥锁
在Python标准库的threading模块中有一个名为Lock的工厂函数，会返回一个thread.LockType对象 该对象的acquire方法用来获取锁，release方法用来释放锁 try: lock.acquire() #do something finally: lock.release() 使用上下文管理器： with lock: #do something 使用互斥锁例子： import threading lock = threading.Lock() num = 0 def incre(count): global num while count > 0: with lock: num += 1 count -= 1 def main(): threads = [] for i in range(10): thread = threading.Thread(target=incre, args=(100000,)) thread.start() threads.append(thread) for thread in threads: thread.join() print("expected value is ", 10 * 100000, ", real value is ", num) if __name__ == '__main__': main()
3).线程安全队列Queue
队列是线程间最常用的交换数据的形式，Queue模块实现了线程安全的队列，尤其适合多线程编程 简单例子： import Queue q = Queue.Queue() for i in range(3): q.put(i) while not q.empty(): print(q.get()) Python官方给出的多线程模型： def worker(): while True: item = q.get() do_work(item) q.task_done() q = Queue() for i in range(num_worker_threads): t = Thread(target=worker) t.daemon =True t.start() for item in source(): q.put(item) q.join() # block until all tasks are done
4).案例：使用Python打造一个MySQL压测工具
import string import argparse import random import threading import time import datetime import pymysql from contextlib import contextmanager DB_NAME = 'test_insert_data_db' TABLE_NAME = 'test_insert_data_table' CREATE_TABLE_STATEMENT = """create table {0} (id int(11) not null auto_increment, name varchar(255) not null, birthday datetime not null, primary key (id))""".format(TABLE_NAME) #_argparse的唯一作用就是使用标准库的argparse模块解析民工行参数并生成帮助信息 def _argparse(): parser = argparse.ArgumentParser(description='benchmark tool for MySQL database') parser.add_argument('--host', action='store', dest='host', required=True, help='connect to host') parser.add_argument('--user', action='store', dest='user', required=True, help='user for login') parser.add_argument('--password', action='store', dest='password', required=True, help='password to use when connecting to server') parser.add_argument('--port', action='store', dest='port', default=3306, type=int, help='port number to use for connection or 3306 for default') parser.add_argument('--thread_size', action='store', dest='thread_size', default=5, type=int, help='how much connection for database usage') parser.add_argument('--row_size', action='store', dest='row_size', default=5000, type=int, help='how mucch rows') parser.add_argument('-v', '--version', action='version', version='%(prog)s 0.1') return parser.parse_args() @contextmanager def get_conn(**kwargs): conn = pymysql.connect(**kwargs) try: yield conn finally: conn.close() def create_db_and_table(conn): with conn as cur: for sql in ["drop database if exists {0}".format(DB_NAME), "create database {0}".format(DB_NAME), "use {0}".format(DB_NAME), CREATE_TABLE_STATEMENT]: print(sql) cur.execute(sql) def random_string(length=10): s = string.letters + string.digits return "".join(random.sample(s, length)) def add_row(cursor): SQL_FORMAT = "INSERT INTO {0}(name, birthday) values ('{1}','{2}')" sql = SQL_FORMAT.format(TABLE_NAME, random_string(), datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) cursor.execute(sql) def insert_data(conn_args, row_size): with get_conn(**conn_args) as conn: with conn as c: c.execute('use {0}'.format(DB_NAME)) with conn as c: for i in range(row_size): add_row(c) conn.commit() def main(): parser = _argparse() conn_args = dict(host=parser.host, user=parser.user, password=parser.password, port=parser.port) with get_conn(**conn_args) as conn: create_db_and_table(conn) threads = [] for i in range(parser.thread_size): t = threading.Thread(target=insert_data, args=(conn_args, parser.row_size)) threads.append(t) t.start() for t in threads: t.join() if __name__ == '__main__': main()
4.专家系统设计
专家系统检查内容
1)服务器相关：包括cpu,io,内存,磁盘,网络等方面的检查
2)数据库相关：包括数据库的参数配置，主从复制性能等
3)业务相关：表结构,索引和SQL语句
索引检查：
主键索引检查，无效索引检查，冗余索引检查，索引区分度检查
容量规划：
cpu利用率检查，io能力检查，网络带宽检查，存储空间检查，内存占用检查
用户访问：
死锁统计，慢日志统计
安全检查：
弱密码检查，网络检查，权限检查
参数检查：
内存参数检查，重做日志配置检查，二进制日志检查，连接数配置检查
主从复制：
复制性能检查，数据安全检查
5.MySQL专家系统整体架构
1).作为平台服务的MySQL数据库健康检查系统
2).作为数据库工具的MySQL数据库健康检查系统
专家系统文件组织
# tree health_checker
health_checker
├── client
│ ├── action
│ │ ├── check_binary_logs.py
│ │ ├── check_connections.py
│ │ ├── check_redo_log.py
│ │ ├── check_safe_replication.py
│ │ └── __init__.py
│ ├── client.py
│ ├── database
│ │ ├── connection_pool.py
│ │ ├── __init__.py
│ │ └── mysql.py
│ ├── env.py
│ ├── handler.py
│ ├── __init__.py
│ ├── response.py
│ └── util.py
├── __init__.py
├── main.py
├── server
│ ├── health_checker_server.py
│ ├── __init__.py
│ ├── util.py
│ └── worker
│ ├── advise.py
│ ├── check_binary_logs.py
│ ├── check_connections.py
│ ├── check_redo_log.py
│ ├── check_safe_replication.py
│ ├── generic_worker.py
│ ├── health_checker_item.py
│ └── __init__.py
└── test.py
