Python3 MySQL 数据库连接 - PyMySQL 驱动

PyMySQL 连接数据库，实现增删改查

什么是 PyMySQL？

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

安装PyMySQL

$ pip install PyMySQL

1、创建数据库连接

在操作mysql之前，首先要与mysql建立连接

conn=pymysql.connect(host="mysql域名/ip",user="用户名",password="密码",db="库名",port=端口号3306，charset=‘utf-8’)

2、创建游标对象

当游标建立之时，就自动开始了一个隐形的数据库事务

#使用 cursor() 方法创建一个游标对象 cursor

cursor = conn.cursor()

3、执行sql语句

sql=“select * from user”

cursor.execute(sql) 或cursor.execute(“select * from user”)

4、提交

mit()

5、回滚

conn.rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务

5、关闭游标

cursor.close()

6、关闭数据库连接

conn.close()

创建数据库连接

import pymysql#创建数据库连接db=pymysql.connect(host="数据库域名/ip",user="账号",password="密码",db="库名",port=3306)print(db)#使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()print(cursor)#使用 execute() 方法执行 SQL 查询cursor.execute("select * from user where mobile")# 使用 fetchone() 方法.results1= cursor.fetchone()#获取单条（第1条）数据results2=cursor.fetchmany(3)#获取3条（2、3、4）数据results3=cursor.fetchall() #获取全部（5-全部）数据print(results3)#关闭游标，又从起始位置开始cursor.close()# 关闭数据库连接db.close()C:\Users\wangli\PycharmProjects\AutoMation\venv\Scripts\python.exe C:/Users/wangli/PycharmProjects/AutoMation/case/test.py<pymysql.connections.Connection object at 0x00000161DDC90E80><pymysql.cursors.Cursor object at 0x00000161DF8E44E0>数据省略Process finished with exit code 0

创建数据库表

#!/usr/bin/python3import pymysql# 打开数据库连接db = pymysql.connect("localhost","testuser","test123","TESTDB" )# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()# 使用 execute() 方法执行 SQL，如果表存在则删除cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")# 使用预处理语句创建表sql = """CREATE TABLE EMPLOYEE (FIRST_NAME CHAR(20) NOT NULL,LAST_NAME CHAR(20),AGE INT, SEX CHAR(1),INCOME FLOAT )"""cursor.execute(sql)# 关闭数据库连接db.close()

数据库插入操作

import pymysql#创建数据库连接db=pymysql.connect(host="",user="",password="",db="ck",port=3306)print(db)#使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()print(cursor)sql="INSERT INTO j.store (id,company_id,title,address,district,lon,lat,remark,updated_at,created_at,deleted_at) VALUES ('195', '61', '虹桥路店', '虹桥路1027号', '', '0.000000', '0.000000', NULL, '-03-15 14:54:29', '-03-15 14:54:29', NULL)"#写法2#cursor.execute('insert into stu(id,name,age) values(%s,%s,%s)',('11001','xiaoqian',20))#插入多条数据，具体数据用列表来保存，列表元素是元组#cur.executemany('insert into stu(id,name,age) values(%s,%s,%s)'，[('11001','xiaoqian',20),('11002','smile',21),('11003','wood',23)])try:#执行插入sqlcursor.execute(sql)# 提交到数据库执行mit()except:# 如果发生错误则回滚db.rollback()# 关闭数据库连接db.close()C:\Users\wangli\PycharmProjects\AutoMation\venv\Scripts\python.exe C:/Users/wangli/PycharmProjects/AutoMation/case/test.py<pymysql.connections.Connection object at 0x00000295DEA90EB8><pymysql.cursors.Cursor object at 0x00000295E06B44E0>Process finished with exit code 0

数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone():该方法获取下一个查询结果集。结果集是一个对象fetchall():接收全部的返回结果行.rowcount:这是一个只读属性，并返回执行execute()方法后影响的行

1、单条sql语句查询

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > %s" % (1000)

2、多条sql语句查询

import pymysql#创建数据库连接db=pymysql.connect(host="",user="",password="",db="",port=3306)print(db)#使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()print(cursor)sql="select * from c.user limit 10"try:#执行sql语句cursor.execute(sql)#查询所有记录results=cursor.fetchall()print(results)#打印10条数据for row in results:print(row)#循环打印每条数据user_id=row[0]#第1列字段mobile=row[1]#第2列字段print(user_id,mobile)#循环打印第1、2列字段except:print('查询失败')#关闭数据库连接db.close()C:\Users\wangli\PycharmProjects\AutoMation\venv\Scripts\python.exe C:/Users/wangli/PycharmProjects/AutoMation/case/test.py<pymysql.connections.Connection object at 0x0000018E8BEDB748><pymysql.cursors.Cursor object at 0x0000018E8DB34550>((2, '13888888888', '新', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554291602, 0, 1, 0, datetime.datetime(, 4, 3, 19, 37, 21), datetime.datetime(, 4, 3, 19, 49, 19), None), (75, '13888888888', '小', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554291602, 0, 1, 0, datetime.datetime(, 4, 3, 19, 37, 21), datetime.datetime(, 4, 3, 19, 49, 19), None), (139, '13888888888', '林', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554088503, 0, 2, 0, datetime.datetime(, 4, 1, 11, 11, 37), datetime.datetime(, 4, 1, 11, 15, 3), None), (190, '13888888888', '大', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 28), datetime.datetime(, 4, 1, 10, 45, 2), None), (192, '13888888888', '磊', '33010019900530156x', 0, '', '', '', 1, 1, '', '', '', '', '', 30, 13888888888, 0, 2, 0, datetime.datetime(, 3, 29, 17, 26, 56), datetime.datetime(, 4, 3, 19, 49, 19), None), (199, '13888888888', '一', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 2, 0, datetime.datetime(, 4, 1, 10, 40, 28), datetime.datetime(, 4, 1, 16, 8, 3), None), (202, '13888888888', '三', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 29), datetime.datetime(, 4, 1, 10, 45, 2), None), (203, '13888888888', '四', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 29), datetime.datetime(, 4, 1, 10, 45, 2), None), (230, '13888888888', '大', '320382199303242813', 0, '', '', '', 1, 1, '', '', '', '', '', 30, 1554291602, 0, 2, 0, datetime.datetime(, 4, 3, 19, 37, 20), datetime.datetime(, 4, 3, 20, 11, 18), None), (278, '13700000003', '三', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554171902, 0, 2, 0, datetime.datetime(, 4, 2, 10, 24, 35), datetime.datetime(, 4, 2, 10, 25, 8), None))(2, '13888888888', '林', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554291602, 0, 1, 0, datetime.datetime(, 4, 3, 19, 37, 21), datetime.datetime(, 4, 3, 19, 49, 19), None)2 13888888888(75, '13888888888', '马', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554291602, 0, 1, 0, datetime.datetime(, 4, 3, 19, 37, 21), datetime.datetime(, 4, 3, 19, 49, 19), None)75 18817893609(139, '13888888888', '林', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554088503, 0, 2, 0, datetime.datetime(, 4, 1, 11, 11, 37), datetime.datetime(, 4, 1, 11, 15, 3), None)139 13888888888(190, '13888888888', '徐', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 28), datetime.datetime(, 4, 1, 10, 45, 2), None)190 13888888888(192, '13888888888', '王', '330', 0, '', '', '', 1, 1, '', '', '', '', '', 30, 1553851802, 0, 2, 0, datetime.datetime(, 3, 29, 17, 26, 56), datetime.datetime(, 4, 3, 19, 49, 19), None)192 13888888888(199, '13888888888', '李', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 2, 0, datetime.datetime(, 4, 1, 10, 40, 28), datetime.datetime(, 4, 1, 16, 8, 3), None)199 13888888888(202, '13888888888', '三', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 29), datetime.datetime(, 4, 1, 10, 45, 2), None)202 13888888888(203, '13888888888', '四', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554086702, 0, 1, 0, datetime.datetime(, 4, 1, 10, 40, 29), datetime.datetime(, 4, 1, 10, 45, 2), None)203 13888888888(230, '13888888888', '小', '320', 0, '', '', '', 1, 1, '', '', '', '', '', 30, 1554291602, 0, 2, 0, datetime.datetime(, 4, 3, 19, 37, 20), datetime.datetime(, 4, 3, 20, 11, 18), None)230 13888888888(278, '13700000003', '王', '', 0, '', '', '', 0, 1, '', '', '', '', '', 30, 1554171902, 0, 2, 0, datetime.datetime(, 4, 2, 10, 24, 35), datetime.datetime(, 4, 2, 10, 25, 8), None)278 13700000003Process finished with exit code 0

数据库更新操作

sql语句

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

sql="update c.user set sex=2 where mobile=18221124104"

cur.execute('update stu set age = %s where id = %s',(25,'11006'))

import pymysql#创建数据库连接conn=pymysql.connect(host="",user="qa",password="Qa",db="c",port=3306)print(conn)#使用 cursor() 方法创建一个游标对象 cursorcursor = conn.cursor()print(cursor)sql="update c.user set sex=2 where mobile=18221124104"try:cursor.execute(sql)mit()except:conn.rollback()conn.close()C:\Users\wangli\PycharmProjects\AutoMation\venv\Scripts\python.exe C:/Users/wangli/PycharmProjects/AutoMation/case/test.py<pymysql.connections.Connection object at 0x000002C281555A20><pymysql.cursors.Cursor object at 0x000002C2831944A8>Process finished with exit code 0

删除操作

#!/usr/bin/python3import pymysql# 打开数据库连接db = pymysql.connect("localhost","testuser","test123","TESTDB" )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 删除语句sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try:# 执行SQL语句cursor.execute(sql)# 提交修改mit()except:# 发生错误时回滚db.rollback()# 关闭连接db.close()