Python+SQL無敵組合，值得你Pick

作者：佚名 2019-06-04 09:30:30

這里我們重點介紹一下關(guān)系型數(shù)據(jù)庫，常用的有Oracle、MySQL、Microsoft SQL Server和PostgreSQL等，下面會用PostgreSQL作為實例，講解如何用Python連接數(shù)據(jù)庫并用SQL進(jìn)行后續(xù)操作。

SQL是結(jié)構(gòu)化查詢語言Structured Query Language的簡稱，是一種數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)。

在正式講解代碼之前，先來科普一下數(shù)據(jù)庫相關(guān)的知識。

數(shù)據(jù)庫是以一定方式儲存在一起、能與多個用戶共享、具有盡可能小的冗余度、與應(yīng)用程序彼此獨立的數(shù)據(jù)集合。數(shù)據(jù)庫系統(tǒng)具有如下特點：

① 數(shù)據(jù)結(jié)構(gòu)化

實現(xiàn)整體數(shù)據(jù)的結(jié)構(gòu)化，這里所說的“整體”結(jié)構(gòu)化，是指在數(shù)據(jù)庫中的數(shù)據(jù)不再僅針對某個應(yīng)用，而是面向全組織；不僅數(shù)據(jù)內(nèi)部是結(jié)構(gòu)化，而且整體式結(jié)構(gòu)化，數(shù)據(jù)之間有聯(lián)系。

② 數(shù)據(jù)共享性高

多個用戶可以同時存取數(shù)據(jù)庫中的數(shù)據(jù)，甚至可以同時存取數(shù)據(jù)庫中的同一個數(shù)據(jù)。

③ 數(shù)據(jù)冗余度低

減少重復(fù)數(shù)據(jù)的存儲，節(jié)約存儲空間。

④ 數(shù)據(jù)獨立性高

用戶的應(yīng)用程序與數(shù)據(jù)庫的物理存儲結(jié)構(gòu)和邏輯結(jié)構(gòu)是相互獨立的。

數(shù)據(jù)庫可以分為兩類，關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫NoSQL（Not Only SQL）。

關(guān)系型數(shù)據(jù)庫是由多張能互相聯(lián)接的二維行列表格組成的數(shù)據(jù)庫。

非關(guān)系型數(shù)據(jù)庫NoSQL主要是指非關(guān)系型、分布式、不提供ACID的數(shù)據(jù)庫設(shè)計模式。其中，ACID是指數(shù)據(jù)庫事務(wù)處理的四個基本要素，分別代表原子性Atomicity、一致性Consistency、隔離性Isolation、持久性Durability。

【工具】

Python 3

PostgreSQL 10

Tushare

【注】

本文假設(shè)你已安裝好PostgreSQL數(shù)據(jù)庫，可直接到官網(wǎng)進(jìn)行下載安裝。文中代碼部分注重的是方法講解，希望大家能夠根據(jù)自身需求靈活運(yùn)用。

01、用Python連接數(shù)據(jù)庫PostgreSQL

PostgreSQL是最先進(jìn)并且應(yīng)用最廣泛的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。它非常受歡迎的原因有很多，其中包括它是開源的、它的可擴(kuò)展性以及它處理許多不同類型的應(yīng)用程序和不同負(fù)載的能力。

用Python可以輕松地建立到PostgreSQL數(shù)據(jù)庫的連接。PostgreSQL有很多Python驅(qū)動程序，其中“psycopg”是最流行的一個，它的當(dāng)前版本是psycopg2。

我們可以用psycopg2模塊將Postgres與Python連在一起。psycopg2是一個用于Python的Postgres數(shù)據(jù)庫適配器。首先，需要用pip命令進(jìn)行安裝。

$ pip3 install psycopg2

【注】這里用的版本是Python 3.5，因此用的是pip3而不是pip進(jìn)行安裝。

安裝好之后，我們就可以用它進(jìn)行數(shù)據(jù)庫連接操作。首先，應(yīng)該創(chuàng)建一個表示數(shù)據(jù)庫的連接對象con。接著，創(chuàng)建一個游標(biāo)對象cur來執(zhí)行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：要連接的數(shù)據(jù)庫名稱。

user：用于身份驗證的用戶名，默認(rèn)為"postgres"。

password：用戶的數(shù)據(jù)庫密碼，自己設(shè)置的。

host：數(shù)據(jù)庫服務(wù)器的地址，如域名、“localhost”或IP地址。

port：端口，默認(rèn)值為5432。

我們也可以用sqlalchemy庫連接，代碼如下：

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

02、SQL數(shù)據(jù)庫操作

建表

我們用SQL語句CREATE TABLE在Python中創(chuàng)建Postgres表，先用上面提到的方法建立數(shù)據(jù)庫連接，再調(diào)用屬于連接對象的cursor()方法來創(chuàng)建游標(biāo)對象，該游標(biāo)對象用于實際執(zhí)行命令。

然后調(diào)用cursor對象的execute()方法來幫助創(chuàng)建表。最后，我們需要提交con.commit()并關(guān)閉連接con.close()。“提交”連接告訴驅(qū)動程序?qū)⒚畎l(fā)送到數(shù)據(jù)庫，這一步很重要。

這里我們創(chuàng)建兩個表，“滬深300指數(shù)日線行情”和“滬深股票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指數(shù)日線行情 
 (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代表的是數(shù)據(jù)類型，NOT NULL代表非空約束，DEFAULT 0表示將默認(rèn)值設(shè)置為0，PRIMARY KEY代表主鍵，用于唯一標(biāo)識數(shù)據(jù)庫表中的一行數(shù)據(jù)。

看到如下輸出，就表示表已創(chuàng)建成功。同理，可創(chuàng)建另一個表“滬深股票qfq日線行情”。

Database opened successfully  
Table created successfully

插入數(shù)據(jù)

既然表已經(jīng)創(chuàng)建成功，我們就可以開始插入數(shù)據(jù)了，先從tushare.pro上面獲取滬深300指數(shù)日線行情數(shù)據(jù)，用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指數(shù)日線行情 (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里面的滬深股票前復(fù)權(quán)通用行情數(shù)據(jù)插入表“滬深股票qfq日線行情”，示例中只插入兩只股票，平安銀行'000001.SZ' 和萬科A'000002.SZ'。

這里我們介紹另一種存儲數(shù)據(jù)的方法，直接用Pandas自帶的df.to_sql()，將獲取的DataFrame一次性插入到數(shù)據(jù)庫中，比上面介紹的先建表，再一行行插入的方法要簡潔很多。

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')

值得注意的一點是，這種方法在數(shù)據(jù)量小的時候一般不會出問題，但當(dāng)數(shù)據(jù)量很大時，可能會因服務(wù)器無法響應(yīng)而報錯。這時，需要設(shè)置參數(shù)值chunksize，限制每次插入的行數(shù)。更多有關(guān)參數(shù)的說明，可到官方文檔查看【1】。

有了數(shù)據(jù)，我們就可以用SQL對數(shù)據(jù)庫進(jìn)行一系列的操作了。

獲取數(shù)據(jù)

我們可以用Pandas自帶的.read_sql()方法獲取數(shù)據(jù)，直接返回的是DataFrame格式，非常方便，詳細(xì)的參數(shù)解析請查看官方文檔【2】。SQL的查詢功能是很強(qiáng)大的，下面介紹常用的一些篩選條件。

選取某張表的特定幾列：

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指數(shù)日線行情;", 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計數(shù)：

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

用WHERE語句篩選數(shù)值：

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結(jié)合多個OR進(jìn)行篩選的代碼：

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

可以用聚合函數(shù)對數(shù)據(jù)做一些計算，如平均值AVG()，最大值MAX()，求和SUM()：

df = pd.read_sql("SELECT AVG(close_p) FROM 滬深300指數(shù)日線行情;", con=engine) 
print(df) 
 avg 
0 3659.63762

聚合函數(shù)也可以和WHERE語句結(jié)合進(jìn)行篩選：

df = pd.read_sql("SELECT AVG(close_p) FROM 滬深300指數(shù)日線行情 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指數(shù)日線行情;""", con=engine) 
print(df) 
 max_close_p max_open_p 
0 3743.9635 3775.0765

ORDER BY排序，默認(rèn)為升序，降序需要在末尾加上DESC：

# 升序： 
df = pd.read_sql("""SELECT ts_code, trade_date FROM 滬深300指數(shù)日線行情 ORDER BY trade_date;""", con=engine) 
print(df) 
# 降序： 
df = pd.read_sql("""SELECT ts_code, trade_date FROM 滬深300指數(shù)日線行情 ORDER BY trade_date DESC;""", con=engine) 
print(df)

ORDER BY也可以根據(jù)多個列進(jìn)行排序：

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進(jìn)行分組，并結(jié)合聚合函數(shù)分組計算數(shù)據(jù)：

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的基礎(chǔ)上再增加聚合函數(shù)篩選條件，可用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限制取出的行數(shù)：

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、總結(jié)

本文介紹了數(shù)據(jù)庫系統(tǒng)的優(yōu)勢，如何用Python連接數(shù)據(jù)庫并用SQL進(jìn)行后續(xù)的查詢操作。

SQL是非常強(qiáng)大的查詢語言，在使用Python對數(shù)據(jù)進(jìn)行分析之前，可以通過篩選精準(zhǔn)地獲取想要的數(shù)據(jù)。

Python和SQL的組合能夠大大提升數(shù)據(jù)分析的效率和質(zhì)量，希望大家可以好好學(xué)習(xí)和利用起來！

責(zé)任編輯：龐桂玉來源：挖地兔