使用pymssql对sql server进行增删查改操作:
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
通过pymssql对sql server进行CRUD操作
"""
import pymssql
import pandas as pd
# ===========================
# Essential:创建连接对象
# ===========================
db_params = {
'host': '127.0.0.1',
'user': 'alex.deng',
'password': 'XXXXXX',
'database': 'CarDataCenter'
}
conn = pymssql.connect(**db_params) # 默认autocommit=False
cursor = conn.cursor() # 创建游标,增删改查都在游标上进行
# ===========================
# create 创建表/表中插入数据
# 1. 创建新表
# 2. 表中插入数据insert
# ===========================
# 1. 创建新表
cursor.execute("""
IF OBJECT_ID('dbo.car_details', 'U') IS NOT NULL
DROP TABLE dbo.car_details;
CREATE TABLE dbo.car_details (
id INT NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
PRIMARY KEY(id)
)
""")
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit() # 如果 update/delete/insert 记得要 conn.commit() ,否则数据库事务无法提交
# 2. 表中插入数据insert
cursor.executemany(
"""
INSERT INTO dbo.car_details
VALUES (%d, %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()
# ===========================
# retrieve 查询
# 注意:游标执行多次查询只会保留最后一次执行结果
# ===========================
# fetchall 所有结果
cursor.execute("""
select * from [dbo].[car_details]
""")
data = cursor.fetchall()
col_names = ['id', 'name', 'url']
df_car_detail = pd.DataFrame(data, columns=col_names) # 输出结果存储为DataFrame
# fetchone 单个结果
cursor.execute("""
select * from [dbo].[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 dbo.car_details
SET name = %s
WHERE id = %s
""", ('奥迪A6L 2017款技术版更新', 1246))
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit()
# ===========================
# delete 删除
# ===========================
id = 1243
cursor.execute("""
DELETE FROM dbo.car_details
WHERE id=%d
""", id)
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit()
# 关闭连接
conn.close()
pymysql介绍见https://blog.csdn.net/weixin_43254908/article/details/87375382