如何通過SQL和Python的集成來快速建立工作流程

本篇內容主要講解“如何通過SQL和Python的集成來快速建立工作流程”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“如何通過SQL和Python的集成來快速建立工作流程”吧!

幾乎每個人都在使用SQL和Python，Python是用于數據分析、機器學習和網頁開發(fā)的全明星優(yōu)秀語言，而SQL是數據庫的實際標準。如果將兩者結合會發(fā)生什么呢?

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

這兩種工具結合之后可謂是最強搭檔，自動化和效率都達到了新高度。

pyodbc

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

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

• IBM Db2

• MySQL

• Oracle

• MS Access

• MS SQL服務器

在多數情況下，該服務器可以直接轉移，與任何符合ODBC的數據庫都可一起使用。唯一需要更改的是連接設置。

連接

首先，要創(chuàng)建與SQL  服務器的連接，可以通過pyodbc.connect實現。在此函數中，還須傳遞連接字符串。此連接字符串必須指定DBMS驅動程序、服務器、要連接的特定數據庫以及連接設置。

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

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

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

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

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

執(zhí)行查詢

SQL 服務器上運行的每個查詢都包含游標初始化和查詢執(zhí)行。如果要在服務器內部進行任何更改，還需要將這些更改提交到服務器。

先來初始化游標：

cursor = cnxn.cursor()

現在，每當要執(zhí)行查詢時，都要使用此游標對象。

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

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

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

提取數據

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

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

這會返回到包含“customers”表中前1000行的數據框。

在SQL中變更數據

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

為了讓變更永久生效，必須提交變更。連接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")

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

cnxn.commit()

下一步

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

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

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

# 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()

到此，相信大家對“如何通過SQL和Python的集成來快速建立工作流程”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！