Python中怎么實(shí)現(xiàn)SQL自動化

這篇文章給大家介紹Python中怎么實(shí)現(xiàn)SQL自動化，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

從基礎(chǔ)開始

import pyodbc from datetime import datetime classSql:     def__init__(self,  database, server="XXVIR00012,55000"):         # here we are  telling python what to connect to (our SQL Server)         self.cnxn = pyodbc.connect("Driver={SQL  Server Native Client 11.0};"                                    "Server="+server+";"                                    "Database="+database+";"                                    "Trusted_Connection=yes;")         # initialise  query attribute         self.query ="--  {}\n\n-- Made in Python".format(datetime.now()                                                           .strftime("%d/%m/%Y"))

這個代碼就是操作MS SQL服務(wù)器的基礎(chǔ)。只要編寫好這個代碼，通過Python 連接到SQL 僅需:

sql = Sql('database123')

很簡單對么?同時發(fā)生了幾件事，下面將對此代碼進(jìn)行剖析。class Sql:

首先要注意，這個代碼包含在一個類中。筆者發(fā)現(xiàn)這是合乎邏輯的，因?yàn)樵诖烁袷街?，已?jīng)對此特定數(shù)據(jù)庫進(jìn)行了增添或移除進(jìn)程。若見其工作過程，思路便能更加清晰。

初始化類:

def __init__(self, database,server="XXVIR00012,55000"):

因?yàn)楣P者和同事幾乎總是連接到相同的服務(wù)器，所以筆者將這個通用瀏覽器的名稱設(shè)為默認(rèn)參數(shù)server。

在“Connect to Server”對話框或者M(jìn)S SQL Server Management Studio的視窗頂端可以找到服務(wù)器的名稱：

下一步，連接SQL:

self.cnxn =pyodbc.connect("Driver={SQL Server Native Client 11.0};"                           "Server="+self.server+";"                           "Database="+self.database+";"                           "Trusted_Connection=yes;")

pyodbc 模塊，使得這一步驟異常簡單。只需將連接字符串過渡到 pyodbc.connect(...) 函數(shù)即可，點(diǎn)擊以了解詳情here。

最后，筆者通常會在 Sql 類中編寫一個查詢字符串，sql類會隨每個傳遞給類的查詢而更新:

self.query = "-- {}\n\n--Made in Python".format(datetime.now()                                              .strftime("%d/%m/%Y"))

這樣便于記錄代碼，同時也使輸出更為可讀，讓他人讀起來更舒服。

請注意在下列的代碼片段中，筆者將不再更新代碼中的self.query 部分。

組塊

一些重要函數(shù)非常有用，筆者幾乎每天都會使用。這些函數(shù)都側(cè)重于將數(shù)據(jù)從數(shù)據(jù)庫中傳入或傳出。

以下圖文件目錄為始:

對于當(dāng)前此項(xiàng)目，需要:

• 將文件導(dǎo)入SQL

• 將其合并到單一表格內(nèi)

• 根據(jù)列中類別靈活創(chuàng)建多個表格

SQL類不斷被充實(shí)后，后續(xù)會容易很多:

import sys sys.path.insert(0, r'C:\\User\medium\pysqlplus\lib') import os from data importSql sql =Sql('database123')  # initialise the Sql object directory =r'C:\\User\medium\data\\'  # this is where our generic data is  stored file_list = os.listdir(directory)  # get a list of all files for file in  file_list:  # loop to import  files to sql     df = pd.read_csv(directory+file)  # read file to dataframe     sql.push_dataframe(df, file[:-4]) # now we  convert our file_list names into the table names we have imported to SQL table_names = [x[:-4] for x in file_list] sql.union(table_names, 'generic_jan')  # union our files into one new table  called 'generic_jan' sql.drop(table_names)  # drop our original tables as we now  have full table # get list of  categories in colX, eg ['hr', 'finance', 'tech', 'c_suite'] sets =list(sql.manual("SELECT  colX AS 'category' FROM generic_jan GROUP BY colX", response=True)['category']) for category in sets:     sql.manual("SELECT *  INTO generic_jan_"+category+" FROM  generic_jan WHERE colX = '"+category+"'")

從頭開始。

入棧數(shù)據(jù)結(jié)構(gòu)

defpush_dataframe(self, data,  table="raw_data", batchsize=500):     # create execution cursor     cursor = self.cnxn.cursor()     # activate fast execute     cursor.fast_executemany =True     # create create table statement     query ="CREATE  TABLE ["+ table +"] (\n"     # iterate through each column to be  included in create table statement     for i inrange(len(list(data))):         query +="\t[{}]  varchar(255)".format(list(data)[i])  # add column (everything is varchar  for now)         # append correct  connection/end statement code         if i !=len(list(data))-1:             query +=",\n"         else:             query +="\n);"     cursor.execute(query)  # execute the create table statement     self.cnxn.commit()  # commit changes     # append query to our SQL code logger     self.query += ("\n\n--  create table\n"+ query)     # insert the data in batches     query = ("INSERT  INTO [{}] ({})\n".format(table,                                                '['+'], ['  # get columns                                                .join(list(data)) +']') +              "VALUES\n(?{})".format(",  ?"*(len(list(data))-1)))     # insert data into target table in  batches of 'batchsize'     for i inrange(0, len(data), batchsize):         if i+batchsize >len(data):             batch = data[i: len(data)].values.tolist()         else:             batch = data[i: i+batchsize].values.tolist()         # execute batch  insert         cursor.executemany(query, batch)         # commit insert  to SQL Server         self.cnxn.commit()

此函數(shù)包含在SQL類中，能輕松將Pandas dataframe插入SQL數(shù)據(jù)庫。

其在需要上傳大量文件時非常有用。然而，Python能將數(shù)據(jù)插入到SQL的真正原因在于其靈活性。

要橫跨一打Excel工作簿才能在SQL中插入特定標(biāo)簽真的很糟心。但有Python在，小菜一碟。如今已經(jīng)構(gòu)建起了一個可以使用Python讀取標(biāo)簽的函數(shù)，還能將標(biāo)簽插入到SQL中。

Manual(函數(shù))

defmanual(self, query,  response=False):     cursor = self.cnxn.cursor()  # create execution cursor     if response:         returnread_sql(query,  self.cnxn)  # get sql query  output to dataframe     try:         cursor.execute(query)  # execute     except pyodbc.ProgrammingErroras error:         print("Warning:\n{}".format(error))  # print error as a warning     self.cnxn.commit()  # commit query to SQL Server     return"Query  complete."

此函數(shù)實(shí)際上應(yīng)用在union 和 drop 函數(shù)中。僅能使處理SQL代碼變得盡可能簡單。

response參數(shù)能將查詢輸出解壓到DataFrame。generic_jan 表中的colX ，可供摘錄所有獨(dú)特值，操作如下:

sets =list(sql.manual("SELECT colX AS 'category' FROM generic_jan GROUP BYcolX", response=True)['category'])

Union(函數(shù))

構(gòu)建 了manual 函數(shù),創(chuàng)建 union 函數(shù)就簡單了:

defunion(self,  table_list, name="union", join="UNION"):     # initialise the query     query ="SELECT *  INTO ["+name+"] FROM (\n"     # build the SQL query     query +=f'\n{join}\n'.join(                         [f'SELECT [{x}].* FROM [{x}]'for x in table_list]                         )     query +=")  x"  # add end of  query     self.manual(query, fast=True)  # fast execute

創(chuàng)建 union 函數(shù)只不過是在循環(huán)參考 table_list提出的表名，從而為給定的表名構(gòu)建  UNION函數(shù)查詢。然后用self.manual(query)處理。

Drop(函數(shù))

上傳大量表到SQL服務(wù)器是可行的。雖然可行，但會使數(shù)據(jù)庫迅速過載。 為解決這一問題，需要創(chuàng)建一個drop函數(shù):

defdrop(self,  tables):     # check if single or list     ifisinstance(tables, str):         # if single  string, convert to single item in list for for-loop         tables = [tables]     for table in tables:         # check for  pre-existing table and delete if present         query = ("IF  OBJECT_ID ('["+table+"]', 'U')  IS NOT NULL "                  "DROP TABLE  ["+table+"]")         self.manual(query)  # execute

關(guān)于Python中怎么實(shí)現(xiàn)SQL自動化就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。