Python學習筆記-SQLSERVER
- 2020 年 1 月 19 日
- 筆記
環境 : python3.6 / win10 / vs2017 / sqlserver2017
一、需要安裝的包pymssql
pip install pymssql
二、pymssql模塊的介紹
pymssql 包 有modules:
所以我更加推薦使用_mssql，而不是網上案列里比較多的pymssql
三、對_mssql模塊的封裝
1、簡單的執行
class C_SQLServer(object): def __init__(self, Server,user,password,database): self.Server=Server self.user=user self.password=password self.database=database #執行無返回操作，適用與(insert,update,delete) def execute_non_query(self,SQLStr): conn = _mssql.connect(server=self.Server, user=self.user, password=self.password, database=self.database,charset='utf8') conn.execute_non_query(SQLStr) #執行返回迭代器的操作，迭代器中的行以字典方式展示，適用於(select) def execute_query(self,SQLStr): conn = _mssql.connect(server=self.Server, user=self.user, password=self.password, database=self.database,charset='utf8') conn.execute_query(SQLStr) return conn #執行返回單行的字典 def execute_row(self,SQLStr): conn = _mssql.connect(server=self.Server, user=self.user, password=self.password, database=self.database,charset='utf8') row=conn.execute_row(SQLStr) return row #執行返回單值的操作，適用於返回行計數等 def execute_scalar(self,SQLStr): conn = _mssql.connect(server=self.Server, user=self.user, password=self.password, database=self.database,charset='utf8') scalar=conn.execute_scalar(SQLStr) return scalar #獲取標題，以及標題類型字典 def MSSQL_GetTitleDict(self,conn): titleDict={} for rows in conn.get_header(): titleDict[rows[0]]=rows[1] #如果調用conn完成後千萬記得，要吧連接關閉。 conn.close() return titleDict
四、對於大批量Insert的操作
pymssql的模塊提供了executemany來執行大批量的導入。
導入列表裡的元素行為Tuple，類似 DataList=[(1,2),(2,3)]
cursor.executemany( "INSERT INTO persons VALUES (%d, %s, %s)", [(1, 'John Smith', 'John Doe'), (2, 'Jane Doe', 'Joe Dog'), (3, 'Mike T.', 'Sarah H.')]) # you must call commit() to persist your data if you don't set autocommit to True conn.commit()
_mssql模塊沒有提供批量導入的功能。
但是我們可以用拼接字符串 Insert ————Select————UNION ALL————SELECT 去實現。
測經過測試，同樣插入10W的數據，_mssql模塊寫拼接比pymssql的executemany快了近3倍多。
代碼如下：
def GetTableTitle(self,tableName): SQLStr=f"select * from {tableName}" conn=self.execute_query(f"select * from {tableName}") titleDict=self.MSSQL_GetTitleDict(conn) return titleDict
#拼接字符串 Insert ————Select————UNION ALL————SELECT類型插入 def InsertByRow(self,tableName,TitleList,DataList): #獲取列頭的字典包含列名以及數據類型 titleDict=self.GetTableTitle(tableName) #定義Insert語句的頭部 insertTitleStr=f"Insert into {tableName} (" + ','.join(TitleList)+")n" #批導入變量，執行行號 i=0 #按行執行 for row_dict in DataList: insertRowStrList=[] #循環列 for columnName in TitleList: columnType=titleDict[columnName] if columnType in [1,4]: isChar=1 else: isChar=0 columValue= row_dict[columnName] #SqlParameter_AddQuotes函數用以增給值增加單引號 columValue=SqlParameter_AddQuotes(isChar,columValue) queryStr=columnName+"="+columValue insertRowStrList.append(queryStr) #行的SelectStr insertRowStr=','.join(insertRowStrList) if i==0: insertStr="Select "+insertRowStr else: insertStr+="n union all nSelect "+insertRowStr i+=1 #定義批量插入的大小，這裡是300行為一批Insert if i%300==0: self.execute_non_query(insertTitleStr+'n'+insertStr) i=0 #剩餘數據Insert self.execute_non_query(insertTitleStr+'n'+insertStr)
性能剛剛的！日常的操作基本也就都封裝好了！
但是記得打開conn後，千萬必須要關閉該連接。
