當(dāng)使用Python/ target=_blank class=infotextkey>Python與SQL Server進(jìn)行交互時(shí)，可以使用不同的庫(kù)和模塊。以下是25個(gè)示例代碼，用于演示如何使用Python與SQL Server進(jìn)行連接、查詢、插入、更新和刪除等操作：
使用pyodbc庫(kù)連接到SQL Server：
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=server_name;'
'Database=database_name;'
'UID=username;'
'PWD=password')
cursor = conn.cursor()
查詢數(shù)據(jù)庫(kù)中的所有表：
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)
執(zhí)行SELECT查詢并獲取結(jié)果：
cursor.execute("SELECT * FROM table_name")
rows = cursor.fetchall()
for row in rows:
print(row)
執(zhí)行帶有參數(shù)的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()
使用事務(wù)進(jìn)行批量插入：
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("插入失敗")
創(chuàng)建新表：
cursor.execute("CREATE TABLE table_name (column1 datatype, column2 datatype)")
conn.commit()
刪除表：
cursor.execute("DROP TABLE table_name")
conn.commit()
使用事務(wù)進(jìn)行多個(gè)操作：
cursor.execute("BEGIN TRANSACTION")
try:
# 執(zhí)行多個(gè)SQL語(yǔ)句
# ...
conn.commit()
print("操作成功")
except:
conn.rollback()
print("操作失敗")
執(zhí)行存儲(chǔ)過(guò)程：
cursor.execute("{CALL stored_procedure_name}")
rows = cursor.fetchall()
for row in rows:
print(row)
獲取查詢結(jié)果的列名：
columns = [column[0] for column in cursor.description]
print(columns)
使用pandas庫(kù)將查詢結(jié)果轉(zhuǎn)換為DataFrame：
import pandas as pd
df = pd.read_sql_query("SELECT * FROM table_name", conn)
print(df)
使用WHERE子句進(jìn)行條件查詢：
param = 'example'
cursor.execute("SELECT * FROM table_name WHERE column_name=?", param)
rows = cursor.fetchall()
for row in rows:
print(row)
使用ORDER BY對(duì)結(jié)果進(jìn)行排序：
cursor.execute("SELECT * FROM table_name ORDER BY column_name ASC")
rows = cursor.fetchall()
for row in rows:
print(row)
使用LIMIT限制查詢結(jié)果的數(shù)量：
cursor.execute("SELECT * FROM table_name LIMIT 10")
rows = cursor.fetchall()
for row in rows:
print(row)
使用JOIN進(jìn)行表的連接查詢：
cursor.execute("SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column")
rows = cursor.fetchall()
for row in rows:
print(row)
使用GROUP BY進(jìn)行分組查詢：
cursor.execute("SELECT column, COUNT(*) FROM table_name GROUP BY column")
rows = cursor.fetchall()
for row in rows:
print(row)
使用HAVING進(jìn)行分組后的條件篩選：
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等聚合函數(shù)：
cursor.execute("SELECT SUM(column), AVG(column), MIN(column), MAX(column) FROM table_name")
rows = cursor.fetchall()
for row in rows:
print(row)
執(zhí)行事務(wù)中的ROLLBACK：
conn.rollback()
關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接：
cursor.close()
conn.close()Python
處理異常錯(cuò)誤：
try:
# 執(zhí)行SQL語(yǔ)句
# ...
except Exception as e:
print("發(fā)生錯(cuò)誤：", e)
這些示例代碼展示了如何使用Python與SQL Server進(jìn)行交互的一些常見操作。您可以根據(jù)自己的需求和具體情況進(jìn)行修改和擴(kuò)展。
