超实用干货｜Python+SQL无敌组合 菜鸟必读
SQL是结构化查询语言Structured Query Language的简称，是一种数据库查询和程式设计语言，用于存取资料以及查询、更新和管理关系数据库系统。
在正式讲解程式码之前，先来科普一下数据库相关的知识。
数据库是以一定方式储存在一起、能与多个使用者共享、具有尽可能小的冗余度、与应用程序彼此独立的资料集合。数据库系统具有如下特点：
① 资料结构化
实现整体资料的结构化，这里所说的“整体”结构化，是指在数据库中的资料不再仅针对某个应用，而是面向全组织；不仅资料内部是结构化，而且整体式结构化，资料之间有联络。
② 资料共享性高
多个使用者可以同时存取数据库中的资料，甚至可以同时存取数据库中的同一个资料。
③ 资料冗余度低
减少重复资料的储存，节约储存空间。
④ 资料独立性高
使用者的应用程序与数据库的物理储存结构和逻辑结构是相互独立的。
数据库可以分为两类，关系型数据库和非关系型数据库NoSQL（Not Only SQL）。
关系型数据库是由多张能互相联接的二维行列表格组成的数据库。
非关系型数据库NoSQL主要是指非关系型、分散式、不提供ACID的数据库设计模式。其中，ACID是指数据库事务处理的四个基本要素，分别代表原子性Atomicity、一致性Consistency、隔离性Isolation、永续性Durability。
这里我们重点介绍一下关系型数据库，常用的有Oracle、MySQL、Microsoft SQL Server和PostgreSQL等，下面会用PostgreSQL作为例项，讲解如何用Python连线数据库并用SQL进行后续操作。
01
用Python连线数据库PostgreSQL
PostgreSQL是最先进并且应用最广泛的关系型数据库管理系统之一。它非常受欢迎的原因有很多，其中包括它是开源的、它的可扩充套件性以及它处理许多不同型别的应用程序和不同负载的能力。
用Python可以轻松地建立到PostgreSQL数据库的连线。PostgreSQL有很多Python驱动程式，其中“psycopg”是最流行的一个，它的当前版本是psycopg2。
我们可以用psycopg2模组将Postgres与Python连在一起。psycopg2是一个用于Python的Postgres数据库界面卡。首先，需要用pip命令进行安装。
$ pip3 install psycopg2
安装好之后，我们就可以用它进行数据库连线操作。首先，应该建立一个表示数据库的连线物件con。接着，建立一个游标物件cur来执行SQL语句。
import psycopg2
con = 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_engine
engine = create_engine('postgresql://postgres:[email protected]:5432/postgres')
02
SQL数据库操作
建表
我们用SQL语句CREATE TABLE在Python中建立Postgres表，先用上面提到的方法建立数据库连线，再呼叫属于连线物件的cursor()方法来建立游标物件，该游标物件用于实际执行命令。
然后呼叫cursor物件的execute()方法来帮助建立表。最后，我们需要提交con.commit()并关闭连线con.close()。“提交”连线告诉驱动程式将命令传送到数据库，这一步很重要。
这里我们建立两个表，“沪深300指数日线行情”和“沪深股票qfq日线行情”。
import psycopg2
con = 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 psycopg2
import pandas as pd
import tushare as ts
con = 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 = 0
for 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_engine
import pandas as pd
import tushare as ts
ts.set_token('your token')
engine = create_engine('postgresql://postgres:[email protected]: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_engine
import pandas as pd
engine = create_engine('postgresql://postgres:[email protected]: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_p
0 399300.SZ 2019-05-31 3629.7893
1 399300.SZ 2019-05-30 3641.1833
2 399300.SZ 2019-05-29 3663.9090
3 399300.SZ 2019-05-28 3672.2605
4 399300.SZ 2019-05-27 3637.1971
用DISTINCT选取唯一值：
df = pd.read_sql("SELECT DISTINCT ts_code FROM 沪深股票qfq日线行情;", con=engine)
print(df)
ts_code
0 000001.SZ
1 000002.SZ
用COUNT计数：
# 检视某列有多少唯一值
df = pd.read_sql("SELECT COUNT(DISTINCT ts_code) FROM 沪深股票qfq日线行情;", con=engine)
print(df)
count
0 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_chg
0 000001.SZ 2019-05-28 12.31 ... 12.49 12.37 0.97
1 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 '20190520') AND pct_chg > 1;", con=engine)
print(df)
ts_code trade_date
0 000001.SZ 2019-05-21
1 000002.SZ 2019-05-28
2 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_date
0 000001.SZ 2019-05-15
1 000001.SZ 2019-05-14
2 000001.SZ 2019-05-10
3 000002.SZ 2019-05-15
4 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_date
0 000001.SZ 2019-05-27
1 000001.SZ 2019-05-20
2 000001.SZ 2019-05-10
3 000002.SZ 2019-05-27
4 000002.SZ 2019-05-20
5 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)
count
0 0
可以用聚合函式对资料做一些计算，如平均值AVG()，最大值MAX()，求和SUM()：
df = pd.read_sql("SELECT AVG(close_p) FROM 沪深300指数日线行情;", con=engine)
print(df)
avg
0 3659.63762
聚合函式也可以和WHERE语句结合进行筛选：
df = pd.read_sql("SELECT AVG(close_p) FROM 沪深300指数日线行情 WHERE trade_date > '20190515';", con=engine)
print(df)
avg
0 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_p
0 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_code
0 2019-05-06 000001.SZ
1 2019-05-06 000002.SZ
2 2019-05-07 000001.SZ
3 2019-05-07 000002.SZ
4 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 count
0 000001.SZ 20
1 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_code
0 000001.SZ
1 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_chg
0 000001.SZ 2019-05-31 12.16 ... 12.18 12.22 -0.33
1 000001.SZ 2019-05-30 12.32 ... 12.22 12.40 -1.45
2 000001.SZ 2019-05-29 12.36 ... 12.40 12.49 -0.72
03
总结
本文介绍了数据库系统的优势，如何用Python连线数据库并用SQL进行后续的查询操作。SQL是非常强大的查询语言，在使用Python对资料进行分析之前，可以通过筛选精准地获取想要的资料。
Python和SQL的组合能够大大提升资料分析的效率和质量，希望大家可以好好学习和利用起来！
