优秀官配:世界上人们很喜欢的数据库+很喜欢的语言
即将开播: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的效果。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
5款优秀的数据库,哪款是你的菜?
即将开播:6月19日,互联网银行架构师魏生谈互联网开放银行实施路径的探索与思考 即将开播:4月29日,民生银行郭庆谈商业银行金融科技赋能的探索与实践--> 在现今互联网企业坐拥着爆炸式数据的信息时代,数据库也层出不穷,来适应不同的业务场景,。那什么数据库比较好用呢?今天,快快小编就来给大家盘点5款优秀的数据库,来看看有没有你的爱用吧! 1、MySQL MySQL历史悠久,用户使用活跃,而且体积小,安装方便,易于维护。它是一款免费软件,是一个真正的多用户、多线程SQL数据库服务器。 它是以客户机/服务器结构实现的,由一个服务器守护程序以及很多不同的客户程序和库组成,能够快捷、有效和安全地处理大量的数据。相对于Oracle等数据库来说,MySQL的使用非常简单,它的主要目标是快速、便捷和易用,功能也是相当的强大,用到的语言都是标准的结构化查询语言SQL。 2、Oracle Oracle数据库在市场占比算是比较高的商业数据库了,功能很强大、但复杂的不得了,Oracle可以说是最难的数据库了,不过,通过第三方软件来访问它也是很方便的,这里面第三方软件是比较容易学的,关于Oracle数据库这里面...
- 下一篇
docker-mcr 助您全速下载 dotnet 镜像
2018 年五月之后,微软将后续发布的所有 docker image 都推送到了 MCR (Miscrosoft Container Registry),但在中国大陆,它的速度实在是令人发指,本文将介绍一种方法来解决这个问题。 如何使用 下载方式 存在至少三种方法进行加速: 使用 docker-mcr (推荐) 拉取国内服务器上的镜像 使用 DockerHub 加速器 注意,无论采用什么方式,请先确保本地的 docker 已经正常可用。 使用 docker-mcr docker-mcr 是一个 dotnet core global tool,简单几步,便可以进行安装和使用。 进入dotnet页面,下载并安装 netcore 3.1 SDK。 安装完毕后打开控制台运行以下命令: dotnet tool install newbe.mcrmirror -g 现在,假如需要拉取 mcr.microsoft.com/dotnet/core/aspnet:3.1-buster-slim ,则运行以下命令: docker-mcr -i mcr.microsoft.com/dotnet/core...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2全家桶,快速入门学习开发网站教程
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- CentOS7安装Docker,走上虚拟化容器引擎之路
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- Linux系统CentOS6、CentOS7手动修改IP地址
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- CentOS8编译安装MySQL8.0.19
- Docker安装Oracle12C,快速搭建Oracle学习环境
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题