1.Python语言高级特性
1).深入浅出Python生成器
1).生成器函数:与普通函数定义类似,使用yield语句而不是return语句返回结果。yield语句一次返回一个结果,在每个结果中间挂起函数的状态,以便下次从它离开的地方继续执行2).生成器表达式:类似于列表推导,但是,生成器返回按需产生结果的一个对象,而不是一次构建一个结果列表
使用生成器的例子,使用生成器返回自然数的平方:defgensquares(N):for i inrange(N):yield i ** 2
defmain():for item in gensquares(234):print(item)if __name__ == '__main__':
main()
使用普通函数实现defgensquares(N):
res=[]for i inrange(N):
res.append(i*i)returnresdefmain():for item in gensquares(234):print(item)if __name__ == '__main__':
main()
2).深入浅出Python装饰器
Python中函数可以赋值给另外一个变量名,函数可以嵌套,以及函数对象可以作为另外一个函数的参数等1、函数对象2、嵌套函数3、装饰器原型defbread(f):def wrapper(*args, **kwargs):print("begin")
f()print("end")returnwrapper
@breaddefsay_hi():print("Hi")defmain():
say_hi()if __name__ == '__main__':
main()
3).Python上下文管理器
1、with语句形式化定义2、上下文管理器的应用场景3、上下文管理器协议
2.MySQL数据库
1).Python连接数据库
importosif os.getenv('DB', 'MySQL') == 'MySQL':importpymysql as dbelse:importsqlite3 as dbdef 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'))defexecute_sql(conn, sql):
with conn as cur:
cur.execute(sql)defcreate_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)definsert_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)defmain():
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 inrows:print(row)finally:ifconn:
conn.close()if __name__ == '__main__':
main()
2).使用上下文管理器对数据库连接进行管理
importosfrom contextlib importcontextmanagerif os.getenv('DB', 'MySQL') == 'MySQL':importpymysql as dbelse:importsqlite3 as db
@contextmanagerdef 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:yieldconnfinally:ifconn:
conn.close()defexecute_sql(conn, sql):
with conn as cur:
cur.execute(sql)defcreate_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)definsert_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)defmain():
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 inrows:print(row)if __name__ == '__main__':
main()
3).案例:从csv文件导入数据到MySQL
importosimportcsvfrom collections importnamedtuplefrom contextlib importcontextmanagerif os.getenv('DB', 'MySQL') == 'MySQL':importpymysql as dbelse:importsqlite3 as db
@contextmanagerdef 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:yieldconnfinally:ifconn:
conn.close()defexecute_sql(conn, sql):
with conn as cur:
cur.execute(sql)defget_data(file_name):
with open(file_name) as f:
f_csv=csv.reader(f)
headings=next(f_csv)
Row= namedtuple('Row', headings)for r inf_csv:yield Row(*r)defmain():
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
使用互斥锁例子:importthreading
lock=threading.Lock()
num=0defincre(count):globalnumwhile count >0:
with lock:
num+= 1count-= 1
defmain():
threads=[]for i in range(10):
thread= threading.Thread(target=incre, args=(100000,))
thread.start()
threads.append(thread)for thread inthreads:
thread.join()print("expected value is", 10 * 100000, ", real value is", num)if __name__ == '__main__':
main()
3).线程安全队列Queue
队列是线程间最常用的交换数据的形式,Queue模块实现了线程安全的队列,尤其适合多线程编程
简单例子:importQueue
q=Queue.Queue()for i in range(3):
q.put(i)while notq.empty():print(q.get())
Python官方给出的多线程模型:defworker():whileTrue:
item=q.get()
do_work(item)
q.task_done()
q=Queue()for i inrange(num_worker_threads):
t= Thread(target=worker)
t.daemon=True
t.start()for item insource():
q.put(item)
q.join()#block until all tasks are done
4).案例:使用Python打造一个MySQL压测工具
importstringimportargparseimportrandomimportthreadingimporttimeimportdatetimeimportpymysqlfrom contextlib importcontextmanager
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')returnparser.parse_args()
@contextmanagerdef get_conn(**kwargs):
conn= pymysql.connect(**kwargs)try:yieldconnfinally:
conn.close()defcreate_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.digitsreturn "".join(random.sample(s, length))defadd_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)definsert_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 inrange(row_size):
add_row(c)
conn.commit()defmain():
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 inrange(parser.thread_size):
t= threading.Thread(target=insert_data, args=(conn_args, parser.row_size))
threads.append(t)
t.start()for t inthreads:
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