SQL是結構化查詢語言Structured Query Language的簡稱，是一種數據庫查詢和程序設計語言，用于存取數據以及查詢、更新和管理關系數據庫系統。

在正式講解代碼之前，先來科普一下數據庫相關的知識。

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

① 數據結構化

實現整體數據的結構化，這里所說的“整體”結構化，是指在數據庫中的數據不再僅針對某個應用，而是面向全組織；不僅數據內部是結構化，而且整體式結構化，數據之間有聯系。

② 數據共享性高

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

③ 數據冗余度低

減少重復數據的存儲，節約存儲空間。

④ 數據獨立性高

用戶的應用程序與數據庫的物理存儲結構和邏輯結構是相互獨立的。

數據庫可以分為兩類，關系型數據庫和非關系型數據庫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限制取出的行數：

1. df = pd.read_sql("""SELECT * FROM 滬深股票qfq日線行情 LIMIT 3;""", con=engine) 
2. print(df) 
3.  ts_code trade_date open_p ... close_p pre_close pct_chg 
4. 0 000001.SZ 2019-05-31 12.16 ... 12.18 12.22 -0.33 
5. 1 000001.SZ 2019-05-30 12.32 ... 12.22 12.40 -1.45 
6. 2 000001.SZ 2019-05-29 12.36 ... 12.40 12.49 -0.72 

03、總結

本文介紹了數據庫系統的優勢，如何用Python連接數據庫并用SQL進行后續的查詢操作。

SQL是非常強大的查詢語言，在使用Python對數據進行分析之前，可以通過篩選精準地獲取想要的數據。

Python和SQL的組合能夠大大提升數據分析的效率和質量，希望大家可以好好學習和利用起來！