[220]python從SQL型資料庫讀寫dataframe型資料

Python的pandas包對表格化的資料處理能力很強，而SQL資料庫的資料就是以表格的形式儲存，是以經常将sql資料庫裡的資料直接讀取為dataframe，分析操作以後再将dataframe存到sql資料庫中。而pandas中的

read_sql

和

to_sql

函數就可以很友善得從sql資料庫中讀寫資料。

read_sql

參見

pandas.read_sql

的文檔，

read_sql

主要有如下幾個參數：

sql: SQL指令字元串
con：連接配接sql資料庫的engine，一般可以用SQLalchemy或者pymysql之類的包建立
index_col: 選擇某一列作為index
coerce_float: 非常有用，将數字形式的字元串直接以float型讀入
parse_dates: 将某一列日期型字元串轉換為datetime型資料，與 pd.to_datetime 函數功能類似。可以直接提供需要轉換的列名以預設的日期形式轉換，也可以用字典的格式提供列名和轉換的日期格式，比如{column_name: format string}（format string："%Y:%m:%H:%M:%S"）。
columns: 要選取的列。一般沒啥用，因為在sql指令裡面一般就指定要選擇的列了
chunksize：如果提供了一個整數值，那麼就會傳回一個generator，每次輸出的行數就是提供的值的大小。
params：其他的一些執行參數，沒用過不太清楚。。。

以連結常見的mysql資料庫為例：

import pandas as pd
import pymysql
import sqlalchemy
from sqlalchemy import create_engine


# 1\. 用sqlalchemy建構資料庫連結engine
connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)  #1
engine = create_engine(connect_info)
# sql 指令
sql_cmd = "SELECT * FROM table"
df = pd.read_sql(sql=sql_cmd, con=engine)

# 2\. 用DBAPI建構資料庫連結engine
con = pymysql.connect(host=localhost, user=username, password=password, database=dbname, charset='utf8', use_unicode=True)
df = pd.read_sql(sql_cmd, con)

複制

解釋一下

這個是sqlalchemy中連結資料庫的URL格式：

dialect[+driver]://user:password@host/dbname[?key=value..]

dialect代表書庫局類型，比如mysql, oracle, postgresql。

driver代表DBAPI的名字，比如：psycopg2,pymysql等。

具體說明可以參考這裡。此外由于資料裡面有中文的時候就需要将charset設為utf8。

to_sql

參見pandas.to_sql函數，主要有以下幾個參數：

name: 輸出的表名
con: 與read_sql中相同
if_exits：三個模式：fail，若表存在，則不輸出；replace：若表存在，覆寫原來表裡的資料；append：若表存在，将資料寫到原表的後面。預設為fail
index：是否将df的index單獨寫到一列中
index_label:指定列作為df的index輸出，此時index為True
chunksize：同read_sql
dtype: 指定列的輸出到資料庫中的資料類型。字典形式儲存：{column_name: sql_dtype}。常見的資料類型有sqlalchemy.types.INTEGER(), sqlalchemy.types.NVARCHAR(),sqlalchemy.Datetime()等，具體資料類型可以參考這裡

還是以寫到mysql資料庫為例：

df.to_sql(name='table', 
          con=con, 
          if_exists='append', 
          index=False,
          dtype={'col1':sqlalchemy.types.INTEGER(),
                 'col2':sqlalchemy.types.NVARCHAR(length=255),
                 'col_time':sqlalchemy.DateTime(),
                 'col_bool':sqlalchemy.types.Boolean
          })

複制

注：如果不提供

dtype

，

to_sql

會自動根據df列的dtype選擇預設的資料類型輸出，比如字元型會以

sqlalchemy.types.TEXT

類型輸出，相比NVARCHAR，TEXT類型的資料所占的空間更大，是以一般會指定輸出為NVARCHAR；而如果df的列的類型為

np.int64

時，将會導緻無法識别并轉換成INTEGER型，需要事先轉換成int類型（用map，apply函數可以友善的轉換）。