python連接sql server資料庫實現增刪改查

簡述

python連接微軟的sql server資料庫用的第三方模塊叫做pymssql（document：http://www.pymssql.org/en/stable/index.html）。在官方文檔可以看到，pymssql是基於_mssql模塊做的封裝，是為了遵守python的DBAPI規範介面. 兩者之間的關係如下圖：

1.使用pymssql連接sql server資料庫並實現資料庫基本操作(官方api http://www.pymssql.org/en/stable/ref/pymssql.html )

1）基本語法

import pymssql

server = "187.32.43.13" # 連接伺服器地址
user = "root"　　　　　　　　 # 連接帳號
password = "1234"　　　　　　# 連接密碼

conn = pymssql.connect(server, user, password, "連接默認資料庫名稱") #獲取連接
cursor = conn.cursor # 獲取游標

# 創建表 cursor.execute(""" IF OBJECT_ID("persons", "U") IS NOT NULL DROP TABLE persons CREATE TABLE persons ( id INT NOT NULL, name VARCHAR(100), salesrep VARCHAR(100), PRIMARY KEY(id) ) """)

# 插入多行數據 cursor.executemany( "INSERT INTO persons VALUES (%d, %s, %s)", [(1, "John Smith", "John Doe"), (2, "Jane Doe", "Joe Dog"), (3, "Mike T.", "Sarah H.")]) # 你必須調用 commit 來保持你數據的提交如果你沒有將自動提交設置為true conn.commit
# 查詢數據 cursor.execute("SELECT * FROM persons WHERE salesrep=%s", "John Doe")

# 遍曆數據（存放到元組中） 方式1 row = cursor.fetchone while row: print("ID=%d, Name=%s" % (row[0], row[1])) row = cursor.fetchone

# 遍曆數據（存放到元組中） 方式2
for row in cursor:
print("row = %r" % (row,))

# 遍曆數據（存放到字典中）

# cursor = conn.cursor(as_dict=True)
#
# cursor.execute("SELECT * FROM persons WHERE salesrep=%s", "John Doe")
# for row in cursor:
# print("ID=%d, Name=%s" % (row["id"], row["name"]))
#
# conn.close

# 關閉連接
conn.close

# 註：在任何時候，在一個連接下，一次正在執行的資料庫操作只會出現一個cursor對象

2）同時，如果你可以使用另一種語法：with 來避免手動關閉cursors和connection連接

import pymssql

server = "187.32.43.13" # 連接伺服器地址
user = "root"　　　　　　　　 # 連接帳號
password = "1234"　　　　　　# 連接密碼

with pymssql.connect(server, user, password, "你的連接默認資料庫名稱") as conn:
with conn.cursor(as_dict=True) as cursor: # 數據存放到字典中
cursor.execute("SELECT * FROM persons WHERE salesrep=%s", "John Doe")
for row in cursor:
print("ID=%d, Name=%s" % (row["id"], row["name"]))

3）調用存儲過程：

with pymssql.connect(server, user, password, "tempdb") as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute("""
CREATE PROCEDURE FindPerson
@name VARCHAR(100)
AS BEGIN
SELECT * FROM persons WHERE name = @name
END
""")
cursor.callproc("FindPerson", ("Jane Doe",))
for row in cursor:
print("ID=%d, Name=%s" % (row["id"], row["name"]))

2.使用_mssql連接sql server資料庫並實現操作（官方api http://www.pymssql.org/en/stable/ref/_mssql.html）

1）基本語法：

import _mssql
# 創建連接
conn = _mssql.connect(server="SQL01", user="user", password="password",
database="mydatabase")
print(conn.timeout)
print(conn.login_timeout) # 創建table conn.execute_non_query("CREATE TABLE persons(id INT, name VARCHAR(100))") # insert數據 conn.execute_non_query("INSERT INTO persons VALUES(1, "John Doe")") conn.execute_non_query("INSERT INTO persons VALUES(2, "Jane Doe")") # 查詢操作 conn.execute_query("SELECT * FROM persons WHERE salesrep=%s", "John Doe") for row in conn: print "ID=%d, Name=%s" % (row["id"], row["name"]) #查詢數量count numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees") # 查詢一條數據 employeedata = conn.execute_row("SELECT * FROM employees WHERE id=%d", 13) # 帶參數查詢的幾個例子： conn.execute_query("SELECT * FROM empl WHERE id=%d", 13) conn.execute_query("SELECT * FROM empl WHERE name=%s", "John Doe") conn.execute_query("SELECT * FROM empl WHERE id IN (%s)", ((5, 6),)) conn.execute_query("SELECT * FROM empl WHERE name LIKE %s", "J%") conn.execute_query("SELECT * FROM empl WHERE name=%(name)s AND city=%(city)s", { "name": "John Doe", "city": "Nowhere" } ) conn.execute_query("SELECT * FROM cust WHERE salesrep=%s AND id IN (%s)", ("John Doe", (1, 2, 3))) conn.execute_query("SELECT * FROM empl WHERE id IN (%s)", (tuple(xrange(4)),)) conn.execute_query("SELECT * FROM empl WHERE id IN (%s)", (tuple([3, 5, 7, 11]),)) #關閉連接 conn.close

pymssql託管在Github上：https://github.com/pymssql

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！