原标题:超实用干货|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
fromsqlalchemy importcreate_engine
engine = create_engine( 'postgresql://postgres:password@localhost:5432/postgres')
02
SQL数据库操作
建表
我们用SQL语句CREATE TABLE在Python中创建Postgres表,先用上面提到的方法建立数据库连接,再调用属于连接对象的cursor
然后调用cursor对象的executecon.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(""" CREATETABLE沪深 300指数日线行情
(ts_code VARCHAR( 10) NOTNULL,
trade_date DATENOTNULL,
open_p NUMERICDEFAULT0,
high_p NUMERICDEFAULT0,
low_p NUMERICDEFAULT0,
close_p NUMERICDEFAULT0,
pre_close NUMERICDEFAULT0,
pct_chg NUMERICDEFAULT0,
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日线行情”。
Databaseopened successfully
Table created successfully
插入数据
既然表已经创建成功,我们就可以开始插入数据了,先从tushare.pro上面获取沪深300指数日线行情数据,用INSERT INTO这个SQL语句插入。
importpsycopg2
importpandas aspd
importtushare asts
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
fori inrange(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一次性插入到数据库中,比上面介绍的先建表,再一行行插入的方法要简洁很多。
fromsqlalchemy importcreate_engine
importpandas aspd
importtushare asts
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']
fori incode_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的查询功能是很强大的,下面介绍常用的一些筛选条件。
fromsqlalchemy importcreate_engine
importpandas aspd
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
0399300.SZ 2019-05-313629.7893
1399300.SZ 2019-05-303641.1833
2399300.SZ 2019-05-293663.9090
3399300.SZ 2019-05-283672.2605
4399300.SZ 2019-05-273637.1971
用DISTINCT选取唯一值:
df = pd.read_sql(" SELECTDISTINCTts_code FROM沪深股票qfq日线行情;", con=engine)
print(df)
ts_code
0 000001.SZ
1 000002.SZ
用COUNT计数:
# 查看某列有多少唯一值
df = pd.read_sql(" SELECTCOUNT( DISTINCTts_code) FROM沪深股票qfq日线行情;", con=engine)
print(df)
count
0 2
用WHERE语句筛选数值:
df = pd.read_sql(" SELECT* FROM沪深股票qfq日线行情 WHEREtrade_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
WHEREAND和OR
df = pd.read_sql(" SELECTts_code, trade_date FROM沪深股票qfq日线行情 WHERE(trade_date < '20190510'ORtrade_date > '20190520') ANDpct_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(" SELECTts_code, trade_date FROM沪深股票qfq日线行情 WHEREtrade_date BETWEEN'20190510'AND'20190520'ANDpct_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(" SELECTts_code, trade_date FROM沪深股票qfq日线行情 WHEREtrade_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(" SELECTCOUNT(*) FROM沪深股票qfq日线行情 WHEREclose_p ISNULL;", con=engine)
print(df)
count
0 0
可以用聚合函数对数据做一些计算,如平均值AVG,最大值MAX,求和SUM:
df = pd.read_sql(" SELECTAVG(close_p) FROM沪深 300指数日线行情;", con=engine)
print(df)
avg
0 3659.63762
聚合函数也可以和WHERE语句结合进行筛选:
df = pd.read_sql(" SELECTAVG(close_p) FROM沪深 300指数日线行情 WHEREtrade_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
03743.96353775.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
02019-05-06000001.SZ
12019-05-06000002.SZ
22019-05-07000001.SZ
32019-05-07000002.SZ
42019-05-08000001.SZ
GROUP BY进行分组,并结合聚合函数分组计算数据:
df = pd.read_sql( """SELECT ts_code, COUNT(*) FROM 沪深股票qfq日线行情 GROUP BY ts_code;""", con=engine)
print(df)
ts_code count
0000001.SZ 20
1000002.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
0000001.SZ
1000002.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
0000001.SZ 2019-05-3112.16... 12.1812.22-0.33
1000001.SZ 2019-05-3012.32... 12.2212.40-1.45
2000001.SZ 2019-05-2912.36... 12.4012.49-0.72
03
总结
本文介绍了数据库系统的优势,如何用Python连接数据库并用SQL进行后续的查询操作。SQL是非常强大的查询语言,在使用Python对数据进行分析之前,可以通过筛选精准地获取想要的数据。
Python和SQL的组合能够大大提升数据分析的效率和质量,希望大家可以好好学习和利用起来!
618来了,说三个小福利历史最低特价还剩1天
仅限6/17-6/19
目前已经破1000人
责任编辑: