使用pymysql对mysql进行增删查改操作:
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
通过pymysql对mysql进行CRUD操作
与通过pymssql对sql server操作类似
"""
import pymysql
import pandas as pd
# ===========================
# Essential:创建连接对象
# ===========================
db_params = {
'host': '127.0.0.1',
'user': 'root',
'password': 'XXXXXX',
'database': 'CARDB'
}
conn = pymysql.connect(**db_params) # 默认autocommit=False
cursor = conn.cursor() # 创建游标,增删改查都在游标上进行
# ===========================
# create 创建表/表中插入新数据
# 1. 创建表
# 2. 表中插入新行insert
# ===========================
# 1. 创建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS car_details (
id INT NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
PRIMARY KEY(id)
) CHARSET=utf8mb4
""")
# conn.commit() #与pymssql不同,此处不需cnn.commit()
# 2. 表中插入新行insert
cursor.executemany(
"""
INSERT INTO car_details
VALUES (%s, %s, %s)
""",
[(1243, '思域 2016款 尊耀版', 'http://.com/details/8.html'),
(1245, '奥迪A4LTFSI quattro运动版', 'http://.com/details/3-2.html'),
(1246, '奥迪A6L 2017款技术版', 'http://pingce/details/82.html')])
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit() # 如果 update/delete/insert 记得要 conn.commit() ,否则数据库事务无法提交
# ===========================
# retrieve 查询
# 注意:游标执行多次查询只会保留最后一次执行结果
# ===========================
# fetchall 所有结果
cursor.execute("""
select * from car_details
""")
data = cursor.fetchall() # 与pymssql不同,data为tuple形式
col_names = ['id', 'name', 'url']
df_car_detail = pd.DataFrame(list(data), columns=col_names) # 输出结果存储为DataFrame
# fetchone 单个结果
cursor.execute("""
select * from car_details
""")
row = cursor.fetchone() # row为tuple类型
# 可以通过while或for循环来遍历查询结果
while row:
print(row)
row = cursor.fetchone()
# or
for row in cursor:
print(row)
# ===========================
# update 更新
# ===========================
cursor.execute(
"""
UPDATE car_details
SET name = %s
WHERE id = %s
""", ('奥迪A8L 2017款技术版更新', 1246))
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit() # 如果 update/delete/insert 记得要 conn.commit() ,否则数据库事务无法提交
# ===========================
# delete 删除
# ===========================
id = 1245
cursor.execute("""
DELETE FROM car_details
WHERE id=%s
""", id)
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit()
# 关闭连接
conn.close()
pymssql操作见:https://blog.csdn.net/weixin_43254908/article/details/87351176