本文轉(zhuǎn)載自公眾號(hào)“讀芯術(shù)”(ID：AI_Discovery)

 幾乎每個(gè)人都在使用SQL和Python，Python是用于數(shù)據(jù)分析、機(jī)器學(xué)習(xí)和網(wǎng)頁(yè)開發(fā)的全明星優(yōu)秀語(yǔ)言，而SQL是數(shù)據(jù)庫(kù)的實(shí)際標(biāo)準(zhǔn)。如果將兩者結(jié)合會(huì)發(fā)生什么呢?

[[330373]]

實(shí)際上，兩者要結(jié)合在一起并不難。我們可以快速利用Python的動(dòng)態(tài)特性，控制和構(gòu)建SQL查詢。設(shè)置完成后，我們無需執(zhí)行任何操作。

這兩種工具結(jié)合之后可謂是最強(qiáng)搭檔，自動(dòng)化和效率都達(dá)到了新高度。

pyodbc

連接兩種技術(shù)的橋梁是pyodbc，該庫(kù)可以輕松訪問ODBC數(shù)據(jù)庫(kù)。

ODBC(開放數(shù)據(jù)庫(kù)連接的簡(jiǎn)稱)是一種用于訪問數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)化應(yīng)用程序編程接口(API)，由90年代初的SQLAccess組開發(fā)。兼容的數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)包括：

• IBM Db2
• MySQL
• Oracle
• MS Access
• MS SQL服務(wù)器

本文將使用MS SQL服務(wù)器。在多數(shù)情況下，該服務(wù)器可以直接轉(zhuǎn)移，與任何符合ODBC的數(shù)據(jù)庫(kù)都可一起使用。唯一需要更改的是連接設(shè)置。

連接

首先，要?jiǎng)?chuàng)建與SQL 服務(wù)器的連接，可以通過pyodbc.connect實(shí)現(xiàn)。在此函數(shù)中，還須傳遞連接字符串。此連接字符串必須指定DBMS驅(qū)動(dòng)程序、服務(wù)器、要連接的特定數(shù)據(jù)庫(kù)以及連接設(shè)置。

因此，假設(shè)要連接到服務(wù)器UKXXX00123,45600和數(shù)據(jù)庫(kù)DB01，需要使用SQL Server Native Client 11.0。從內(nèi)部連接使得連接被信任，無需輸入用戶名和密碼。

cnxn_str = ("Driver={SQLServer Native Client 11.0};" 
"Server=UKXXX00123,45600;" 
"Database=DB01;" 
"Trusted_Connection=yes;") 
現(xiàn)在，連接已初始化為： 
cnxn = pyodbc.connect（cnxn_str） 

如果不通過受信任的連接訪問數(shù)據(jù)庫(kù)，則需要輸入通常用于通過SQLServer Management Studio(SSMS)訪問服務(wù)器的用戶名和密碼。例如，如果用戶名是JoeBloggs，而密碼是Password123，則應(yīng)立即更改密碼。更改密碼之前，可以按照如下進(jìn)行連接：

cnxn_str = ("Driver={SQLServer Native Client 11.0};" 
"Server=UKXXX00123,45600;" 
"Database=DB01;" 
"UID=JoeBloggs;" 
"PWD=Password123;")cnxn = pyodbc.connect(cnxn_str) 

現(xiàn)在我們已連接到數(shù)據(jù)庫(kù)，可以開始通過Python執(zhí)行SQL查詢。

執(zhí)行查詢

SQL 服務(wù)器上運(yùn)行的每個(gè)查詢都包含游標(biāo)初始化和查詢執(zhí)行。如果要在服務(wù)器內(nèi)部進(jìn)行任何更改，還需要將這些更改提交到服務(wù)器。

先來初始化游標(biāo)：

cursor = cnxn.cursor() 

現(xiàn)在，每當(dāng)要執(zhí)行查詢時(shí)，都要使用此游標(biāo)對(duì)象。

從名為“customers”表中選擇前1000行：

cursor.execute("SELECTTOP(1000) * FROM customers") 

執(zhí)行該操作，但這發(fā)生在服務(wù)器內(nèi)部，實(shí)際上什么也沒有返回到Python。讓我們一起看看從SQL中提取的這些數(shù)據(jù)。

提取數(shù)據(jù)

要從SQL中提取數(shù)據(jù)到Python中，需要使用pandas。Pandas提供了一個(gè)非常方便的函數(shù)read_sql，該函數(shù)可以從SQL讀取數(shù)據(jù)。read_sql需要查詢和連接實(shí)例cnxn，如下所示：

data =pd.read_sql("SELECT TOP(1000) * FROM customers", cnxn) 

這會(huì)返回到包含“customers”表中前1000行的數(shù)據(jù)框。

在SQL中變更數(shù)據(jù)

現(xiàn)在，如果要變更SQL中的數(shù)據(jù)，需要在原始的初始化連接后添加另一步，執(zhí)行查詢過程。在SQL中執(zhí)行查詢時(shí)，這些變更將保存在臨時(shí)存在的空格中，而不是直接對(duì)數(shù)據(jù)進(jìn)行更改。

為了讓變更永久生效，必須提交變更。連接firstName和lastName列，創(chuàng)建fullName列。

cursor = cnxn.cursor()# firstalter the table, adding a column 
cursor.execute("ALTER TABLE customer " +  
          "ADD fullNameVARCHAR(20)")# now update that column to contain firstName 
 + lastNamecursor.execute("UPDATEcustomer " +         
"SET fullName = firstName + " " + lastName") 

此時(shí)，fullName并不存在于數(shù)據(jù)庫(kù)中。必須提交這些變更，讓變更永久生效：

cnxn.commit() 

下一步

一旦執(zhí)行了需要執(zhí)行的任何操作任務(wù)，就可以把數(shù)據(jù)提取到Python中，也可以將數(shù)據(jù)提取到Python中，在Python中進(jìn)行操作。

無論采用哪種方法，一旦Python中有了數(shù)據(jù)，就可以做很多以前無法做到的事情。

也許需要執(zhí)行一些日常報(bào)告，通常使用這些報(bào)告查詢SQL 服務(wù)器中的最新數(shù)據(jù)，計(jì)算基本統(tǒng)計(jì)信息，然后通過電子郵件發(fā)送結(jié)果。如何自動(dòng)化這一過程呢?

# imports for SQL data part 
       import pyodbc 
       from datetime import datetime,timedelta 
       import pandas as pd 
             # imports forsending email 
       from email.mime.text importMIMEText 
       fromemail.mime.multipart importMIMEMultipart 
       import smtplib 
             date = datetime.today() -timedelta(days=7)  # get the date 7 days ago 
             date = date.strftime("%Y-%m-%d")  # convert to format yyyy-mm-dd 
             cnxn = pyodbc.connect(cnxn_str)  # initialise connection (assume we havealready defined cnxn_str) 
             # build up ourquery string 
       query = ("SELECT *FROM customers " 
               f"WHERE joinDate > '{date}'") 
             # execute thequery and read to a dataframe in Python 
       data = pd.read_sql(query, cnxn) 
             del cnxn  # close the connection 
             # make a fewcalculations 
       mean_payment = data['payment'].mean() 
       std_payment = data['payment'].std() 
             # get maxpayment and product details 
       max_vals = data[['product', 'payment']].sort_values(by=['payment'], ascending=False).iloc[0] 
             # write an emailmessage 
       txt = (f"Customerreporting for period {date} - {datetime.today().strftime('%Y-%m-%d')}.\n\n" 
             f"Mean payment amounts received: {mean_payment}\n" 
             f"Standard deviation of payment amounts: {std_payments}\n" 
             f"Highest payment amount of {max_vals['payment']} " 
             f"received from {max_vals['product']} product.") 
             # we will built themessage using the email library and send using smtplib 
       msg =MIMEMultipart() 
       msg['Subject'] ="Automatedcustomer report"  # set emailsubject 
       msg.attach(MIMEText(txt))  # add text contents 
             # we will sendvia outlook, first we initialise connection to mail server 
       smtp = smtplib.SMTP('smtp-mail.outlook.com', '587') 
       smtp.ehlo()  # say hello to the server 
       smtp.starttls()  # we will communicate using TLSencryption 
                # login to outlookserver, using generic email and password 
       smtp.login('joebloggs@outlook.com', 'Password123') 
                # send email to ourboss 
       smtp.sendmail('joebloggs@outlook.com', 'joebloggsboss@outlook.com', msg.as_string()) 
                # finally,disconnect from the mail server 
       smtp.quit() 

至此，任務(wù)結(jié)束!運(yùn)行此代碼快速提取前一周的數(shù)據(jù)，計(jì)算關(guān)鍵指標(biāo)，并把摘要發(fā)送給老板。

通過簡(jiǎn)單的步驟，我們了解了如何通過使用SQL和Python的集成來快速建立更高效、自動(dòng)化的工作流程。不僅僅可以用來做本例中的事，它還有很多用途等你開發(fā)。

Python開辟了新路線，完成了以前僅使用SQL無法完成的操作。這對(duì)最強(qiáng)官配，實(shí)現(xiàn)了1+1大于2的效果。