当使用Python/ target=_blank class=infotextkey>Python与SQL Server进行交互时,可以使用不同的库和模块。以下是25个示例代码,用于演示如何使用Python与SQL Server进行连接、查询、插入、更新和删除等操作:
使用pyodbc库连接到SQL Server:
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=server_name;'
'Database=database_name;'
'UID=username;'
'PWD=password')
cursor = conn.cursor()
查询数据库中的所有表:
cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
tables = cursor.fetchall()
for table in tables:
print(table.TABLE_NAME)
查询表中的所有列:
cursor.execute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table_name'")
columns = cursor.fetchall()
for column in columns:
print(column.COLUMN_NAME)
执行SELECT查询并获取结果:
cursor.execute("SELECT * FROM table_name")
rows = cursor.fetchall()
for row in rows:
print(row)
执行带有参数的SELECT查询:
param = 'example'
cursor.execute("SELECT * FROM table_name WHERE column_name=?", param)
rows = cursor.fetchall()
for row in rows:
print(row)
插入新记录:
cursor.execute("INSERT INTO table_name (column1, column2) VALUES (?, ?)", value1, value2)
conn.commit()
更新记录:
cursor.execute("UPDATE table_name SET column1=? WHERE column2=?", new_value, condition_value)
conn.commit()
删除记录:
cursor.execute("DELETE FROM table_name WHERE column=? AND column2=?", value1, value2)
conn.commit()
使用事务进行批量插入:
data = [('value1', 'value2'), ('value3', 'value4')]
cursor.execute("BEGIN TRANSACTION")
try:
for row in data:
cursor.execute("INSERT INTO table_name (column1, column2) VALUES (?, ?)", row)
conn.commit()
print("插入成功")
except:
conn.rollback()
print("插入失败")
创建新表:
cursor.execute("CREATE TABLE table_name (column1 datatype, column2 datatype)")
conn.commit()
删除表:
cursor.execute("DROP TABLE table_name")
conn.commit()
使用事务进行多个操作:
cursor.execute("BEGIN TRANSACTION")
try:
# 执行多个SQL语句
# ...
conn.commit()
print("操作成功")
except:
conn.rollback()
print("操作失败")
执行存储过程:
cursor.execute("{CALL stored_procedure_name}")
rows = cursor.fetchall()
for row in rows:
print(row)
获取查询结果的列名:
columns = [column[0] for column in cursor.description]
print(columns)
使用pandas库将查询结果转换为DataFrame:
import pandas as pd
df = pd.read_sql_query("SELECT * FROM table_name", conn)
print(df)
使用WHERE子句进行条件查询:
param = 'example'
cursor.execute("SELECT * FROM table_name WHERE column_name=?", param)
rows = cursor.fetchall()
for row in rows:
print(row)
使用ORDER BY对结果进行排序:
cursor.execute("SELECT * FROM table_name ORDER BY column_name ASC")
rows = cursor.fetchall()
for row in rows:
print(row)
使用LIMIT限制查询结果的数量:
cursor.execute("SELECT * FROM table_name LIMIT 10")
rows = cursor.fetchall()
for row in rows:
print(row)
使用JOIN进行表的连接查询:
cursor.execute("SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column")
rows = cursor.fetchall()
for row in rows:
print(row)
使用GROUP BY进行分组查询:
cursor.execute("SELECT column, COUNT(*) FROM table_name GROUP BY column")
rows = cursor.fetchall()
for row in rows:
print(row)
使用HAVING进行分组后的条件筛选:
cursor.execute("SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 10")
rows = cursor.fetchall()
for row in rows:
print(row)
使用SUM、AVG、MIN、MAX等聚合函数:
cursor.execute("SELECT SUM(column), AVG(column), MIN(column), MAX(column) FROM table_name")
rows = cursor.fetchall()
for row in rows:
print(row)
执行事务中的ROLLBACK:
conn.rollback()
关闭游标和数据库连接:
cursor.close()
conn.close()Python
处理异常错误:
try:
# 执行SQL语句
# ...
except Exception as e:
print("发生错误:", e)
这些示例代码展示了如何使用Python与SQL Server进行交互的一些常见操作。您可以根据自己的需求和具体情况进行修改和扩展。