自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

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

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

 

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)用程序彼此獨(dú)立的數(shù)據(jù)集合。數(shù)據(jù)庫系統(tǒng)具有如下特點(diǎn)：

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

實(shí)現(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ù)獨(dú)立性高

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

數(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。

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

【工具】

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)行安裝。

  1. $ 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語句。

  1. import psycopg2 
  2. con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")  
  3. print("Database opened successfully") 
  4. 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庫連接，代碼如下：

  1. from sqlalchemy import create_engine 
  2. 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)對象用于實(shí)際執(zhí)行命令。

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

這里我們創(chuàng)建兩個表，“滬深300指數(shù)日線行情”和“滬深股票qfq日線行情”。

  1. import psycopg2 
  2. con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432") 
  3. print("Database opened successfully") 
  4. cur = con.cursor() 
  5. cur.execute("""CREATE TABLE 滬深300指數(shù)日線行情 
  6.  (ts_code VARCHAR(10) NOT NULL, 
  7.  trade_date DATE NOT NULL, 
  8.  open_p NUMERIC DEFAULT 0, 
  9.  high_p NUMERIC DEFAULT 0, 
  10.  low_p NUMERIC DEFAULT 0, 
  11.  close_p NUMERIC DEFAULT 0, 
  12.  pre_close NUMERIC DEFAULT 0, 
  13.  pct_chg NUMERIC DEFAULT 0, 
  14.  PRIMARY KEY (ts_code, trade_date) 
  15.  ) ; """) 
  16. print("Table created successfully") 
  17. con.commit() 
  18. 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日線行情”。

  1. Database opened successfully  
  2. Table created successfully  

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

既然表已經(jīng)創(chuàng)建成功，我們就可以開始插入數(shù)據(jù)了，先從tushare.pro上面獲取滬深300指數(shù)日線行情數(shù)據(jù)，用INSERT INTO這個SQL語句插入。

  1. import psycopg2 
  2. import pandas as pd 
  3. import tushare as ts 
  4. con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432") 
  5. print("Database opened successfully") 
  6. cur = con.cursor() 
  7. pro = ts.pro_api() 
  8. df = pro.index_daily(ts_code='399300.SZ', start_date='20190501', end_date='20190531') # 單位：漲跌幅（%）, 成交量（手）、成交額（千元） 
  9. ts_code = df['ts_code'].tolist() 
  10. trade_date = df['trade_date'].tolist() 
  11. open_p = df['open'].tolist() 
  12. high_p = df['high'].tolist() 
  13. low_p = df['low'].tolist() 
  14. close_p = df['close'].tolist() 
  15. pre_close = df['pre_close'].tolist() 
  16. pct_chg = df['pct_chg'].tolist() 
  17. count = 0 
  18. for i in range(len(ts_code)): 
  19.  cur.execute(""" 
  20.  INSERT INTO 滬深300指數(shù)日線行情 (ts_code, trade_date, open_p, high_p, low_p, close_p, pre_close, pct_chg) 
  21.  VALUES( %s, %s, %s, %s, %s, %s, %s, %s);""", 
  22.  (ts_code[i], 
  23.  trade_date[i], 
  24.  open_p[i], 
  25.  high_p[i], 
  26.  low_p[i], 
  27.  close_p[i], 
  28.  pre_close[i], 
  29.  pct_chg[i])) 
  30.  con.commit() 
  31.  print("已插入{0}行，共有{1}行".format(count, len(ts_code))) 
  32.  count += 1  

同理，將tushare.pro里面的滬深股票前復(fù)權(quán)通用行情數(shù)據(jù)插入表“滬深股票qfq日線行情”，示例中只插入兩只股票，平安銀行'000001.SZ' 和萬科A'000002.SZ'。

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

  1. from sqlalchemy import create_engine 
  2. import pandas as pd 
  3. import tushare as ts 
  4. ts.set_token('your token') 
  5. engine = create_engine('postgresql://postgres:password@localhost:5432/postgres') 
  6. print('Database opened successfully') 
  7. pro = ts.pro_api() 
  8. code_list = ['000001.SZ', '000002.SZ'] 
  9. for i in code_list: 
  10.  print(i) 
  11.  df = ts.pro_bar(ts_code=i, adj='qfq', start_date='20190501', end_date='20190531') 
  12.  df.to_sql(name='滬深股票qfq日線行情', con=engine, index=False, if_exists='append') 

值得注意的一點(diǎn)是，這種方法在數(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)大的，下面介紹常用的一些篩選條件。

選取某張表的特定幾列：

  1. from sqlalchemy import create_engine 
  2. import pandas as pd 
  3. engine = create_engine('postgresql://postgres:password@localhost:5432/postgres') 
  4. df_index = pd.read_sql("SELECT ts_code, trade_date, close_p FROM 滬深300指數(shù)日線行情;", con=engine) 
  5. print(df_index.head()) 
  6.  ts_code trade_date close_p 
  7. 0 399300.SZ 2019-05-31 3629.7893 
  8. 1 399300.SZ 2019-05-30 3641.1833 
  9. 2 399300.SZ 2019-05-29 3663.9090 
  10. 3 399300.SZ 2019-05-28 3672.2605 
  11. 4 399300.SZ 2019-05-27 3637.1971 

用DISTINCT選取唯一值：

  1. df = pd.read_sql("SELECT DISTINCT ts_code FROM 滬深股票qfq日線行情;", con=engine) 
  2. print(df) 
  3.  ts_code 
  4. 0 000001.SZ 
  5. 1 000002.SZ 

用COUNT計數(shù)：

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

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

  1. df = pd.read_sql("SELECT * FROM 滬深股票qfq日線行情 WHERE trade_date = '20190528';", con=engine) 
  2. print(df) 
  3.  ts_code trade_date open_p ... close_p pre_close pct_chg 
  4. 0 000001.SZ 2019-05-28 12.31 ... 12.49 12.37 0.97 
  5. 1 000002.SZ 2019-05-28 27.00 ... 27.62 27.00 2.30 

WHERE語句搭配AND和OR一起使用：

  1. 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) 
  2. print(df) 
  3.  ts_code trade_date 
  4. 0 000001.SZ 2019-05-21 
  5. 1 000002.SZ 2019-05-28 
  6. 2 000002.SZ 2019-05-07 

和WHERE語句類似，BETWEEN也可以搭配AND和OR一起使用：

  1. df = pd.read_sql("SELECT ts_code, trade_date FROM 滬深股票qfq日線行情 WHERE trade_date BETWEEN '20190510' AND '20190520' AND pct_chg > 1;", con=engine) 
  2. print(df) 
  3.  ts_code trade_date 
  4. 0 000001.SZ 2019-05-15 
  5. 1 000001.SZ 2019-05-14 
  6. 2 000001.SZ 2019-05-10 
  7. 3 000002.SZ 2019-05-15 
  8. 4 000002.SZ 2019-05-10 

WHERE和IN的組合，可以簡化WHERE結(jié)合多個OR進(jìn)行篩選的代碼：

  1. df = pd.read_sql("SELECT ts_code, trade_date FROM 滬深股票qfq日線行情 WHERE trade_date IN ('20190510', '20190520', '20190527');", con=engine) 
  2. print(df) 
  3.  ts_code trade_date 
  4. 0 000001.SZ 2019-05-27 
  5. 1 000001.SZ 2019-05-20 
  6. 2 000001.SZ 2019-05-10 
  7. 3 000002.SZ 2019-05-27 
  8. 4 000002.SZ 2019-05-20 
  9. 5 000002.SZ 2019-05-10 

NULL的意思是空值，IS NULL代表是空值，IS NOT NULL代表不是空值：

  1. df = pd.read_sql("SELECT COUNT(*) FROM 滬深股票qfq日線行情 WHERE close_p IS NULL ;", con=engine) 
  2. print(df) 
  3.  count 
  4. 0 0 

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

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

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

  1. df = pd.read_sql("SELECT AVG(close_p) FROM 滬深300指數(shù)日線行情 WHERE trade_date > '20190515';", con=engine) 
  2. print(df) 
  3.  avg 
  4. 0 3645.740858 

用AS為新列命名：

  1. df = pd.read_sql("""SELECT MAX(close_p) AS max_close_p, 
  2.  MAX(open_p) AS max_open_p FROM 滬深300指數(shù)日線行情;""", con=engine) 
  3. print(df) 
  4.  max_close_p max_open_p 
  5. 0 3743.9635 3775.0765 

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

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

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

  1. df = pd.read_sql("""SELECT trade_date, ts_code FROM 滬深股票qfq日線行情 ORDER BY trade_date, ts_code;""", con=engine) 
  2. print(df) 
  3.  trade_date ts_code 
  4. 0 2019-05-06 000001.SZ 
  5. 1 2019-05-06 000002.SZ 
  6. 2 2019-05-07 000001.SZ 
  7. 3 2019-05-07 000002.SZ 
  8. 4 2019-05-08 000001.SZ 

GROUP BY進(jìn)行分組，并結(jié)合聚合函數(shù)分組計算數(shù)據(jù)：

  1. df = pd.read_sql("""SELECT ts_code, COUNT(*) FROM 滬深股票qfq日線行情 GROUP BY ts_code;""", con=engine) 
  2. print(df) 
  3.  ts_code count 
  4. 0 000001.SZ 20 
  5. 1 000002.SZ 20 

如果要在分組GROUP BY的基礎(chǔ)上再增加聚合函數(shù)篩選條件，可用HAVING：

  1. df = pd.read_sql("""SELECT ts_code FROM 滬深股票qfq日線行情 GROUP BY ts_code HAVING COUNT(*) > 15 ;""", con=engine) 
  2. print(df) 
  3.  ts_code 
  4. 0 000001.SZ 
  5. 1 000002.SZ 

LIMIT限制取出的行數(shù)：

  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、總結(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é)任編輯：龐桂玉 來源： 挖地兔
PythonSQL數(shù)據(jù)庫
相關(guān)推薦

2021-01-21 09:45:16

Python字符串代碼

2020-12-14 13:32:40

Python進(jìn)度條參數(shù)

2021-09-06 10:22:47

匿名對象編程

2020-02-03 12:25:35

Python工具服務(wù)器

2020-10-22 19:37:01

SASESD-WAN網(wǎng)絡(luò)技術(shù)

2022-05-17 20:37:41

MyPick泛型對象類型

2020-06-15 14:43:16

Python開發(fā)工具

2023-12-29 08:17:26

Python代碼分析Profile

2021-02-03 10:18:46

加密算法攻擊加密

2023-11-15 16:35:31

SQL數(shù)據(jù)庫

2021-03-18 07:52:42

代碼性能技巧開發(fā)

2022-06-28 13:43:48

瀏覽器Chrome

2013-06-27 14:57:58

Eclipse超酷插件移動開發(fā)

2010-07-15 09:14:32

SQL server組

2010-05-28 18:17:59

2018-06-28 13:59:52

云計算

2018-06-22 15:52:50

云桌面

2023-10-10 19:32:43

強(qiáng)靜態(tài)類型制表符

2023-03-01 07:57:38

PythonAI編程語言

2022-05-04 09:02:41

TypeScript類型工具
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號