SQL 是结构化查询语言 Structured Query Language 的简称, 是一种数据库查询和程序设计语言, 用于存取数据以及查询, 更新和管理关系数据库系统.
在正式讲解代码之前, 先来科普一下数据库相关的知识.
数据库是以一定方式储存在一起, 能与多个用户共享, 具有尽可能小的冗余度, 与应用程序彼此独立的数据集合. 数据库系统具有如下特点:
1 数据结构化
实现整体数据的结构化, 这里所说的 "整体" 结构化, 是指在数据库中的数据不再仅针对某个应用, 而是面向全组织; 不仅数据内部是结构化, 而且整体式结构化, 数据之间有联系.
2 数据共享性高
多个用户可以同时存取数据库中的数据, 甚至可以同时存取数据库中的同一个数据.
3 数据冗余度低
减少重复数据的存储, 节约存储空间.
4 数据独立性高
用户的应用程序与数据库的物理存储结构和逻辑结构是相互独立的.
数据库可以分为两类, 关系型数据库和非关系型数据库 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 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:password@localhost: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: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_engine
- import pandas as pd
- engine = 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_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 <'20190510'OR 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 的组合能够大大提升数据分析的效率和质量, 希望大家可以好好学习和利用起来!
来源: http://developer.51cto.com/art/201906/597428.htm