一、下载模块pymysql
pip3 install pymysql -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com
二、操作
select
# -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang') #创建游标 cursor = conn.cursor() #执行sql，并返回影响行数 effect_row = cursor.execute("select * from student") #获取第一行数据 #row_1 = cursor.fetchone() #获取前n行数据 #row_2 = cursor.fetmany(4) #获取所有数据 row_3 = cursor.fetchall() #打印 print(row_3) #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
关于fetch出来的数据默认是元组类型的，类似下面的结果：
((1, 'liyang', 22, datetime.date(2018, 3, 1)), (2, 'xiaoming', 22, datetime.date(2018, 3, 1)), (3, 'liyang3', 42, datetime.date(2018, 12, 1)))
如何想要结果变成字典呢，如下：
# -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang')#创建游标，创建游标为字典 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #执行sql，并返回影响行数 effect_row = cursor.execute("select * from student") row_3 = cursor.fetchmany(3) print(row_3) #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
结果：
[{'stu_id': 1, 'name': 'liyang', 'age': 22, 'register_date': datetime.date(2018, 3, 1)}, {'stu_id': 2, 'name': 'xiaoming', 'age': 22, 'register_date': datetime.date(2018, 3, 1)}, {'stu_id': 3, 'name': 'liyang3', 'age': 42, 'register_date': datetime.date(2018, 12, 1)}]
update
# -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang') #创建游标 cursor = conn.cursor() #执行sql，并返回影响行数 effect_row = cursor.execute("update student set name = 'xiaoming' where stu_id = %s", (2,)) #打印 print(effect_row) #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
insert
# -*- coding:utf-8 -*- # Author:Brownyangyang # -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang') #创建游标 cursor = conn.cursor() data = [ ("xiaohong","20","2017-04-14"), ("xiaogang","30","2016-07-08"), ("xiaozhu","40","2018-09-09"), ] #执行sql，并返回影响行数 #effect_row = cursor.execute("update student set name = 'xiaoming' where stu_id = %s", (2,)) #对数据进行批量操作,executemany比execute效率高,execute是循环执行，executemany是一次提交全部 effect_row = cursor.executemany("insert into student (name,age,register_date) values (%s,%s,%s)",data) #打印 print(effect_row) #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
delete
# -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang') #创建游标 cursor = conn.cursor() #对数据进行删除，返回受影响的行 effect_row = cursor.execute("delete from student where name=%s",('xiaogang',)) #打印 print(effect_row) #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
create
# -*- coding:utf-8 -*- # Author:Brownyangyang import pymysql #创建连接 conn = pymysql.connect(host='10.211.55.15',port=3306,user='root',passwd='mysql',db='liyang') #创建游标 cursor = conn.cursor() #执行sql，并返回影响行数 effect_row = cursor.execute("create table liyang_test3 (a int,b int)") #提交修改 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()