环境: Win10 x64 + Python 3.7 + PyMySQL 1.0.2 + MySQL 8.0.27
1 安装
pip install pymysql
2 地址
https://pypi.org/project/pymysql/
3.1 数据库版本查询 (search_version.py)
import pymysql
# 打开数据库连接
try:
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306)
print('连接成功!')
except:
print('something wrong!')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.fetchone只返回一维元组
data = cursor.fetchone()
print(type(data))
print(data)
print("Database version : %s " % data)
# 关闭数据库连接
db.close()
'''
连接成功!
<class 'tuple'>
('8.0.27',)
Database version : 8.0.27
'''
3.2 创建表 (create_table.py)
import pymysql
# 打开数据库连接
try:
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter')
print('连接成功!')
except:
print('something wrong!')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
ID INT NOT NULL AUTO_INCREMENT,
FIRST_NAME CHAR(20) NOT NULL COMMENT '名字',
LAST_NAME CHAR(20),
AGE INT COMMENT '年龄',
SEX CHAR(1) COMMENT '性别',
INCOME FLOAT NOT NULL COMMENT '收入',
PRIMARY KEY (ID)
)"""
cursor.execute(sql)
print('建表成功!')
# 关闭数据库连接
db.close()
'''
连接成功!
建表成功!
'''
3.3 插入数据 (insert_update_delete_table.py)
import pymysql
# 打开数据库连接
try:
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter')
print('连接成功!')
except:
print('something wrong!')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
# sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Qin', 'DeXu', 20, 'M', 2000)"""
# sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Li', 'Xiaofei', 20, 'F', 3000)"""
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Wang', 'HanYu', 20, 'M', 5000)"""
# SQL 更新语句
# sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
# SQL 删除语句
# sql = "DELETE FROM EMPLOYEE WHERE AGE > %d" % (20)
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
print('数据插入成功!')
except:
# 如果发生错误则回滚
db.rollback()
print('数据插入错误!')
# 关闭数据库连接
db.close()
'''
连接成功!
数据插入成功!
'''
3.4 批量插入数据 (insert_table_many.py)
import pymysql
# 打开数据库连接
try:
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter')
print('连接成功!')
except:
print('something wrong!')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
data = [
('DongFang', 'YuXiao', 25, 'M', 2000.30),
('OuYang', 'HeHe', 22, 'F', 2000),
('GongSun', 'Sheng', 23, 'M', 2000)
]
stmt = "INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)"
try:
# 执行sql语句
cursor.executemany(stmt, data)
# 提交到数据库执行
db.commit()
print('数据插入成功!')
except:
# 如果发生错误则回滚
db.rollback()
print('数据插入错误!')
# 关闭数据库连接
db.close()
'''
连接成功!
数据插入成功!
'''
'''
说明:
占位符 %s 改成 %d ,报错。
'''
3.5 查询数据 (search_table.py)
import pymysql
# 打开数据库连接
try:
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter')
print('连接成功!')
except:
print('something wrong!')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE WHERE INCOME >= %s" % (3000)
try:
# 执行SQL语句
cursor.execute(sql)
# rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数。
row_count = cursor.rowcount
print(type(row_count))
print(row_count)
# fetchall(): 接收全部的返回结果行, 获取所有记录列表 fetchall返回二维元组(元组中含有元组)
results = cursor.fetchall()
print(type(results))
print(results)
for row in results:
id = row[0]
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
# 打印结果
print('数据查询成功!')
print("id=%s, fname=%s, lname=%s, age=%s, sex=%s, income=%s" % (id, fname, lname, age, sex, income))
except:
print("Error: unable to fetch data")
# 关闭数据库连接
db.close()
'''
连接成功!
<class 'int'>
2
<class 'tuple'>
((2, 'Li', 'Xiaofei', 20, 'F', 3000.0), (3, 'Wang', 'HanYu', 20, 'M', 5000.0))
数据查询成功!
id=2, fname=Li, lname=Xiaofei, age=20, sex=F, income=3000.0
数据查询成功!
id=3, fname=Wang, lname=HanYu, age=20, sex=M, income=5000.0
'''
其他情况:
executemany()方法要求传入的参数是元组嵌套的列表,列表中每个元组代表一组数据,而元组中的每个值则代表一个数据的字段值。
####################################
批量插入1:
data = [
('Jane', date(2005, 2, 12)),
('Joe', date(2006, 5, 23)),
('John', date(2010, 10, 3)),
]stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
cursor.executemany(stmt, data)INSERT INTO employees (first_name, hire_date)
VALUES ('Jane', '2005-02-12'), ('Joe', '2006-05-23'), ('John', '2010-10-03')
####################################
批量插入2:
# 定义 SQL 语句
insert_sql = "INSERT INTO person VALUES (%s, %s, %s)"# 定义数据
data = [
('1', '张三', 18),
('2', '李四', 19),
('3', '王五', 20)
]
# 批量插入数据
cursor.executemany(insert_sql, data)批量删除3:
# 定义 SQL 语句
delete_sql = "DELETE FROM WHERE id = %s"
# 定义数据
ids = [(1,), (2,), (3,)]
# 批量删除数据
cursor.executemany(delete_sql, ids)
参考链接:
https://zhuanlan.zhihu.com/p/397765212
http://www.xoxxoo.com/index/index/article/id/288
https://blog.csdn.net/m0_48300767/article/details/131063781
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html