Python操作mysql

一、下载模块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()