Python實(shí)現(xiàn)從SQL型數(shù)據(jù)庫讀寫dataframe型數(shù)據(jù)的方法【基于pandas】
本文實(shí)例講述了Python實(shí)現(xiàn)從SQL型數(shù)據(jù)庫讀寫dataframe型數(shù)據(jù)的方法。分享給大家供大家參考,具體如下:
Python的pandas包對(duì)表格化的數(shù)據(jù)處理能力很強(qiáng),而SQL數(shù)據(jù)庫的數(shù)據(jù)就是以表格的形式儲(chǔ)存,因此經(jīng)常將sql數(shù)據(jù)庫里的數(shù)據(jù)直接讀取為dataframe,分析操作以后再將dataframe存到sql數(shù)據(jù)庫中。而pandas中的read_sql和to_sql函數(shù)就可以很方便得從sql數(shù)據(jù)庫中讀寫數(shù)據(jù)。
參見pandas.read_sql的文檔,read_sql主要有如下幾個(gè)參數(shù):
- sql:SQL命令字符串
- con:連接sql數(shù)據(jù)庫的engine,一般可以用SQLalchemy或者pymysql之類的包建立
- index_col: 選擇某一列作為index
- coerce_float:非常有用,將數(shù)字形式的字符串直接以float型讀入
- parse_dates:將某一列日期型字符串轉(zhuǎn)換為datetime型數(shù)據(jù),與pd.to_datetime函數(shù)功能類似。可以直接提供需要轉(zhuǎn)換的列名以默認(rèn)的日期形式轉(zhuǎn)換,也可以用字典的格式提供列名和轉(zhuǎn)換的日期格式,比如{column_name: format string}(format string:"%Y:%m:%H:%M:%S")。
- columns:要選取的列。一般沒啥用,因?yàn)樵趕ql命令里面一般就指定要選擇的列了
- chunksize:如果提供了一個(gè)整數(shù)值,那么就會(huì)返回一個(gè)generator,每次輸出的行數(shù)就是提供的值的大小。
- params:其他的一些執(zhí)行參數(shù),沒用過不太清楚。。。
以鏈接常見的mysql數(shù)據(jù)庫為例:
import pandas as pd import pymysql import sqlalchemy from sqlalchemy import create_engine # 1. 用sqlalchemy構(gòu)建數(shù)據(jù)庫鏈接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構(gòu)建數(shù)據(jù)庫鏈接engine con = pymysql.connect(host=localhost, user=username, password=password, database=dbname, charset='utf8', use_unicode=True) df = pd.read_sql(sql_cmd, con)
解釋一下 #1: 這個(gè)是sqlalchemy中鏈接數(shù)據(jù)庫的URL格式:dialect[+driver]://user:password@host/dbname[?key=value..]。dialect代表書庫局類型,比如mysql, oracle, postgresql。driver代表DBAPI的名字,比如psycopg2,pymysql等。具體說明可以參考這里。此外由于數(shù)據(jù)里面有中文的時(shí)候就需要將charset設(shè)為utf8。
參見pandas.to_sql函數(shù),主要有以下幾個(gè)參數(shù):
- name: 輸出的表名
- con: 與read_sql中相同
- if_exits: 三個(gè)模式:fail,若表存在,則不輸出;replace:若表存在,覆蓋原來表里的數(shù)據(jù);append:若表存在,將數(shù)據(jù)寫到原表的后面。默認(rèn)為fail
- index:是否將df的index單獨(dú)寫到一列中
- index_label:指定列作為df的index輸出,此時(shí)index為True
- chunksize: 同read_sql
- dtype: 指定列的輸出到數(shù)據(jù)庫中的數(shù)據(jù)類型。字典形式儲(chǔ)存:{column_name: sql_dtype}。常見的數(shù)據(jù)類型有
sqlalchemy.types.INTEGER()
,sqlalchemy.types.NVARCHAR()
,sqlalchemy.Datetime()
等,具體數(shù)據(jù)類型可以參考這里
還是以寫到mysql數(shù)據(jù)庫為例:
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會(huì)自動(dòng)根據(jù)df列的dtype選擇默認(rèn)的數(shù)據(jù)類型輸出,比如字符型會(huì)以sqlalchemy.types.TEXT
類型輸出,相比NVARCHAR,TEXT類型的數(shù)據(jù)所占的空間更大,所以一般會(huì)指定輸出為NVARCHAR;而如果df的列的類型為np.int64時(shí),將會(huì)導(dǎo)致無法識(shí)別并轉(zhuǎn)換成INTEGER型,需要事先轉(zhuǎn)換成int類型(用map,apply函數(shù)可以方便的轉(zhuǎn)換)。
參考:
http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sql-standard-and-multiple-vendor-types
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html
http://docs.sqlalchemy.org/en/latest/core/engines.html
http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sql-standard-and-multiple-vendor-types
http://stackoverflow.com/questions/30631325/writing-to-mysql-database-with-pandas-using-sqlalchemy-to-sql
http://stackoverflow.com/questions/5687718/how-can-i-insert-data-into-a-mysql-database
http://stackoverflow.com/questions/32235696/pandas-to-sql-gives-unicode-decode-error
http://stackoverflow.com/questions/34383000/pandas-to-sql-all-columns-as-nvarchar
更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)學(xué)運(yùn)算技巧總結(jié)》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進(jìn)階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》
希望本文所述對(duì)大家Python程序設(shè)計(jì)有所幫助。
相關(guān)文章
在Ubuntu系統(tǒng)下安裝使用Python的GUI工具wxPython
這篇文章主要介紹了在Ubuntu系統(tǒng)下安裝使用Python的GUI工具wxPython的方法,wxPython可以為Python提供強(qiáng)大的圖形化界面開發(fā)支持,需要的朋友可以參考下2016-02-02通過python繪制華強(qiáng)買瓜的字符畫視頻的步驟詳解
要把華強(qiáng)賣瓜做成字符視頻大概分為三步,通過讀取視頻,把每一幀轉(zhuǎn)為字符畫,接著把字符畫表現(xiàn)出來,本文通過實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友參考下吧2021-11-11pytorch關(guān)于Tensor的數(shù)據(jù)類型說明
這篇文章主要介紹了pytorch關(guān)于Tensor的數(shù)據(jù)類型說明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-07-07