我们可以用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: