即将开播：6月19日，互联网银行架构师魏生谈互联网开放银行实施路径的探索与思考

本文转载自公众号“读芯术”(ID：AI_Discovery)

几乎每个人都在使用SQL和Python，Python是用于数据分析、机器学习和网页开发的全明星优秀语言，而SQL是数据库的实际标准。如果将两者结合会发生什么呢?

实际上，两者要结合在一起并不难。我们可以快速利用Python的动态特性，控制和构建SQL查询。设置完成后，我们无需执行任何操作。

这两种工具结合之后可谓是最强搭档，自动化和效率都达到了新高度。

pyodbc

连接两种技术的桥梁是pyodbc，该库可以轻松访问ODBC数据库。

ODBC(开放数据库连接的简称)是一种用于访问数据库的标准化应用程序编程接口(API)，由90年代初的SQLAccess组开发。兼容的数据库管理系统(DBMS)包括：

• IBM Db2
• MySQL
• Oracle
• MS Access
• MS SQL服务器

本文将使用MS SQL服务器。在多数情况下，该服务器可以直接转移，与任何符合ODBC的数据库都可一起使用。唯一需要更改的是连接设置。

连接

首先，要创建与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执行SQL查询。

执行查询

SQL 服务器上运行的每个查询都包含游标初始化和查询执行。如果要在服务器内部进行任何更改，还需要将这些更改提交到服务器。

先来初始化游标：

 
 

  
  
cursor = cnxn.cursor() 
 
 

现在，每当要执行查询时，都要使用此游标对象。

从名为“customers”表中选择前1000行：

 
 

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

执行该操作，但这发生在服务器内部，实际上什么也没有返回到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中的数据，需要在原始的初始化连接后添加另一步，执行查询过程。在SQL中执行查询时，这些变更将保存在临时存在的空格中，而不是直接对数据进行更改。

为了让变更永久生效，必须提交变更。连接firstName和lastName列，创建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() 
 
 

下一步

一旦执行了需要执行的任何操作任务，就可以把数据提取到Python中，也可以将数据提取到Python中，在Python中进行操作。

无论采用哪种方法，一旦Python中有了数据，就可以做很多以前无法做到的事情。

也许需要执行一些日常报告，通常使用这些报告查询SQL 服务器中的最新数据，计算基本统计信息，然后通过电子邮件发送结果。如何自动化这一过程呢?

 
 

  
  
# 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的集成来快速建立更高效、自动化的工作流程。不仅仅可以用来做本例中的事，它还有很多用途等你开发。

Python开辟了新路线，完成了以前仅使用SQL无法完成的操作。这对最强官配，实现了1+1大于2的效果。