Python+SQL无敌组合，值得你Pick！

TUSHARE 金融与技术学习兴趣小组

翻译整理 | Little monster

本期编辑 | Little monster

译者简介：北京第二外国语学院国际商务专业研一在读，目前在学习Python编程和量化投资相关知识。

作者：Nicholas Samuel × DataCamp

SQL是结构化查询语言Structured Query Language的简称，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

在正式讲解代码之前，先来科普一下数据库相关的知识。

数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。数据库系统具有如下特点：

① 数据结构化
实现整体数据的结构化，这里所说的“整体”结构化，是指在数据库中的数据不再仅针对某个应用，而是面向全组织；不仅数据内部是结构化，而且整体式结构化，数据之间有联系。

② 数据共享性高
多个用户可以同时存取数据库中的数据，甚至可以同时存取数据库中的同一个数据。

③ 数据冗余度低
减少重复数据的存储，节约存储空间。

④ 数据独立性高

用户的应用程序与数据库的物理存储结构和逻辑结构是相互独立的。

数据库可以分为两类，关系型数据库和非关系型数据库NoSQL（Not Only SQL）。

关系型数据库是由多张能互相联接的二维行列表格组成的数据库。

非关系型数据库NoSQL主要是指非关系型、分布式、不提供ACID的数据库设计模式。其中，ACID是指数据库事务处理的四个基本要素，分别代表原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。

这里我们重点介绍一下关系型数据库，常用的有Oracle、MySQL、Microsoft SQL Server和PostgreSQL等，下面会用PostgreSQL作为实例，讲解如何用Python连接数据库并用SQL进行后续操作。

【工具】
Python 3
PostgreSQL 10
Tushare

【注】
本文假设你已安装好PostgreSQL数据库，可直接到官网进行下载安装。文中代码部分注重的是方法讲解，希望大家能够根据自身需求灵活运用。

01

用Python连接数据库PostgreSQL

PostgreSQL是最先进并且应用最广泛的关系型数据库管理系统之一。它非常受欢迎的原因有很多，其中包括它是开源的、它的可扩展性以及它处理许多不同类型的应用程序和不同负载的能力。

用Python可以轻松地建立到PostgreSQL数据库的连接。PostgreSQL有很多Python驱动程序，其中“psycopg”是最流行的一个，它的当前版本是psycopg2。

我们可以用psycopg2模块将Postgres与Python连在一起。psycopg2是一个用于Python的Postgres数据库适配器。首先，需要用pip命令进行安装。

$ pip3 install psycopg2

复制代码

【注】这里用的版本是Python 3.5，因此用的是pip3而不是pip进行安装。

安装好之后，我们就可以用它进行数据库连接操作。首先，应该创建一个表示数据库的连接对象con。接着，创建一个游标对象cur来执行SQL语句。

import psycopg2con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432") print("Database opened successfully")cur = con.cursor()

复制代码

database：要连接的数据库名称。
user：用于身份验证的用户名，默认为"postgres"。
password：用户的数据库密码，自己设置的。
host：数据库服务器的地址，如域名、“localhost”或IP地址。
port：端口，默认值为5432。

我们也可以用sqlalchemy库连接，代码如下：

from sqlalchemy import create_engineengine = create_engine('postgresql://postgres:password@localhost:5432/postgres')

复制代码

02

SQL数据库操作

建表
我们用SQL语句CREATE TABLE在Python中创建Postgres表，先用上面提到的方法建立数据库连接，再调用属于连接对象的cursor()方法来创建游标对象，该游标对象用于实际执行命令。

然后调用cursor对象的execute()方法来帮助创建表。最后，我们需要提交con.commit()并关闭连接con.close()。“提交”连接告诉驱动程序将命令发送到数据库，这一步很重要。

这里我们创建两个表，“沪深300指数日线行情”和“沪深股票qfq日线行情”。

import psycopg2con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")print("Database opened successfully")cur = con.cursor()cur.execute("""CREATE TABLE 沪深300指数日线行情 (ts_code VARCHAR(10) NOT NULL, trade_date DATE NOT NULL, open_p NUMERIC DEFAULT 0, high_p NUMERIC DEFAULT 0, low_p NUMERIC DEFAULT 0, close_p NUMERIC DEFAULT 0, pre_close NUMERIC DEFAULT 0, pct_chg NUMERIC DEFAULT 0, PRIMARY KEY (ts_code, trade_date) ) ; """)print("Table created successfully")con.commit()con.close()

复制代码

简单说明一下，VARCHAR(10)、DATE、NUMERIC代表的是数据类型，NOT NULL代表非空约束，DEFAULT 0表示将默认值设置为0，PRIMARY KEY代表主键，用于唯一标识数据库表中的一行数据。看到如下输出，就表示表已创建成功。同理，可创建另一个表“沪深股票qfq日线行情”。

Database opened successfully Table created successfully

复制代码

插入数据
既然表已经创建成功，我们就可以开始插入数据了，先从tushare.pro上面获取沪深300指数日线行情数据，用INSERT INTO这个SQL语句插入。

import psycopg2import pandas as pdimport tushare as tscon = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")print("Database opened successfully")cur = con.cursor()pro = ts.pro_api()df = pro.index_daily(ts_code='399300.SZ', start_date='20190501', end_date='20190531') # 单位：涨跌幅（%）, 成交量（手）、成交额（千元）ts_code = df['ts_code'].tolist()trade_date = df['trade_date'].tolist()open_p = df['open'].tolist()high_p = df['high'].tolist()low_p = df['low'].tolist()close_p = df['close'].tolist()pre_close = df['pre_close'].tolist()pct_chg = df['pct_chg'].tolist()count = 0for i in range(len(ts_code)): cur.execute(""" INSERT INTO 沪深300指数日线行情 (ts_code, trade_date, open_p, high_p, low_p, close_p, pre_close, pct_chg) VALUES( %s, %s, %s, %s, %s, %s, %s, %s);""", (ts_code[i], trade_date[i], open_p[i], high_p[i], low_p[i], close_p[i], pre_close[i], pct_chg[i])) con.commit() print("已插入{0}行，共有{1}行".format(count, len(ts_code))) count += 1

复制代码

同理，将tushare.pro里面的沪深股票前复权通用行情数据插入表“沪深股票qfq日线行情”，示例中只插入两只股票，平安银行'000001.SZ' 和万科A'000002.SZ'。

这里我们介绍另一种存储数据的方法，直接用Pandas自带的df.to_sql()，将获取的DataFrame一次性插入到数据库中，比上面介绍的先建表，再一行行插入的方法要简洁很多。

from sqlalchemy import create_engineimport pandas as pdimport tushare as tsts.set_token('your token')engine = create_engine('postgresql://postgres:password@localhost:5432/postgres')print('Database opened successfully')pro = ts.pro_api()code_list = ['000001.SZ', '000002.SZ']for i in code_list: print(i) df = ts.pro_bar(ts_code=i, adj='qfq', start_date='20190501', end_date='20190531') df.to_sql(name='沪深股票qfq日线行情', con=engine, index=False, if_exists='append')

复制代码

值得注意的一点是，这种方法在数据量小的时候一般不会出问题，但当数据量很大时，可能会因服务器无法响应而报错。这时，需要设置参数值chunksize，限制每次插入的行数。更多有关参数的说明，可到官方文档查看【1】。

有了数据，我们就可以用SQL对数据库进行一系列的操作了。

获取数据

我们可以用Pandas自带的.read_sql()方法获取数据，直接返回的是DataFrame格式，非常方便，详细的参数解析请查看官方文档【2】。SQL的查询功能是很强大的，下面介绍常用的一些筛选条件。

选取某张表的特定几列：

from sqlalchemy import create_engineimport pandas as pdengine = create_engine('postgresql://postgres:password@localhost:5432/postgres')df_index = pd.read_sql("SELECT ts_code, trade_date, close_p FROM 沪深300指数日线行情;", con=engine)print(df_index.head()) ts_code trade_date close_p0 399300.SZ 2019-05-31 3629.78931 399300.SZ 2019-05-30 3641.18332 399300.SZ 2019-05-29 3663.90903 399300.SZ 2019-05-28 3672.26054 399300.SZ 2019-05-27 3637.1971

复制代码

用DISTINCT选取唯一值：

df = pd.read_sql("SELECT DISTINCT ts_code FROM 沪深股票qfq日线行情;", con=engine)print(df) ts_code0 000001.SZ1 000002.SZ

复制代码

用COUNT计数：

# 查看某列有多少唯一值df = pd.read_sql("SELECT COUNT(DISTINCT ts_code) FROM 沪深股票qfq日线行情;", con=engine)print(df) count0 2

复制代码

用WHERE语句筛选数值：

df = pd.read_sql("SELECT * FROM 沪深股票qfq日线行情 WHERE trade_date = '20190528';", con=engine)print(df) ts_code trade_date open_p ... close_p pre_close pct_chg0 000001.SZ 2019-05-28 12.31 ... 12.49 12.37 0.971 000002.SZ 2019-05-28 27.00 ... 27.62 27.00 2.30

复制代码

WHERE语句搭配AND和OR一起使用：

df = pd.read_sql("SELECT ts_code, trade_date FROM 沪深股票qfq日线行情 WHERE (trade_date < '20190510' OR trade_date > '20190520') AND pct_chg > 1;", con=engine)print(df) ts_code trade_date0 000001.SZ 2019-05-211 000002.SZ 2019-05-282 000002.SZ 2019-05-07

复制代码

和WHERE语句类似，BETWEEN也可以搭配AND和OR一起使用：

df = pd.read_sql("SELECT ts_code, trade_date FROM 沪深股票qfq日线行情 WHERE trade_date BETWEEN '20190510' AND '20190520' AND pct_chg > 1;", con=engine)print(df) ts_code trade_date0 000001.SZ 2019-05-151 000001.SZ 2019-05-142 000001.SZ 2019-05-103 000002.SZ 2019-05-154 000002.SZ 2019-05-10

复制代码

WHERE和IN的组合，可以简化WHERE结合多个OR进行筛选的代码：

df = pd.read_sql("SELECT ts_code, trade_date FROM 沪深股票qfq日线行情 WHERE trade_date IN ('20190510', '20190520', '20190527');", con=engine)print(df) ts_code trade_date0 000001.SZ 2019-05-271 000001.SZ 2019-05-202 000001.SZ 2019-05-103 000002.SZ 2019-05-274 000002.SZ 2019-05-205 000002.SZ 2019-05-10

复制代码

NULL的意思是空值，IS NULL代表是空值，IS NOT NULL代表不是空值：

df = pd.read_sql("SELECT COUNT(*) FROM 沪深股票qfq日线行情 WHERE close_p IS NULL ;", con=engine)print(df) count0 0

复制代码

可以用聚合函数对数据做一些计算，如平均值AVG()，最大值MAX()，求和SUM()：

df = pd.read_sql("SELECT AVG(close_p) FROM 沪深300指数日线行情;", con=engine)print(df) avg0 3659.63762

复制代码

聚合函数也可以和WHERE语句结合进行筛选：

df = pd.read_sql("SELECT AVG(close_p) FROM 沪深300指数日线行情 WHERE trade_date > '20190515';", con=engine)print(df) avg0 3645.740858

复制代码

用AS为新列命名：

df = pd.read_sql("""SELECT MAX(close_p) AS max_close_p, MAX(open_p) AS max_open_p FROM 沪深300指数日线行情;""", con=engine)print(df) max_close_p max_open_p0 3743.9635 3775.0765

复制代码

ORDER BY排序，默认为升序，降序需要在末尾加上DESC：

# 升序：df = pd.read_sql("""SELECT ts_code, trade_date FROM 沪深300指数日线行情 ORDER BY trade_date;""", con=engine)print(df)# 降序：df = pd.read_sql("""SELECT ts_code, trade_date FROM 沪深300指数日线行情 ORDER BY trade_date DESC;""", con=engine)print(df)

复制代码

ORDER BY也可以根据多个列进行排序：

df = pd.read_sql("""SELECT trade_date, ts_code FROM 沪深股票qfq日线行情 ORDER BY trade_date, ts_code;""", con=engine)print(df) trade_date ts_code0 2019-05-06 000001.SZ1 2019-05-06 000002.SZ2 2019-05-07 000001.SZ3 2019-05-07 000002.SZ4 2019-05-08 000001.SZ

复制代码

GROUP BY进行分组，并结合聚合函数分组计算数据：

df = pd.read_sql("""SELECT ts_code, COUNT(*) FROM 沪深股票qfq日线行情 GROUP BY ts_code;""", con=engine)print(df) ts_code count0 000001.SZ 201 000002.SZ 20

复制代码

如果要在分组GROUP BY的基础上再增加聚合函数筛选条件，可用HAVING：

df = pd.read_sql("""SELECT ts_code FROM 沪深股票qfq日线行情 GROUP BY ts_code HAVING COUNT(*) > 15 ;""", con=engine)print(df) ts_code0 000001.SZ1 000002.SZ

复制代码

LIMIT限制取出的行数：

df = pd.read_sql("""SELECT * FROM 沪深股票qfq日线行情 LIMIT 3;""", con=engine)print(df) ts_code trade_date open_p ... close_p pre_close pct_chg0 000001.SZ 2019-05-31 12.16 ... 12.18 12.22 -0.331 000001.SZ 2019-05-30 12.32 ... 12.22 12.40 -1.452 000001.SZ 2019-05-29 12.36 ... 12.40 12.49 -0.72

复制代码

03

总结

本文介绍了数据库系统的优势，如何用Python连接数据库并用SQL进行后续的查询操作。

SQL是非常强大的查询语言，在使用Python对数据进行分析之前，可以通过筛选精准地获取想要的数据。

Python和SQL的组合能够大大提升数据分析的效率和质量，希望大家可以好好学习和利用起来！

END

更多内容请关注“挖地兔”公众号。

【参考链接】http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html【1】http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html#pandas.read_sql【2】
https://www.datacamp.com/courses/intro-to-sql-for-data-science【Datacamp】
https://stackabuse.com/working-with-postgresql-in-python/【Working with PostgreSQL in Python】

【扩展阅读】利用Python创建不同资产组合的基本框架
利用Python玩转PDF，简单实用
利用python进行蒙特卡罗模拟
利用Python实现摘要自动提取，完美瘦身只需一行代码
利用Python搞定期货数据，获得免费API
基于Python的交互式可视化工具