Python的pandas包对表格化的数据处理能力很强,而SQL数据库的数据就是以表格的形式储存,因此经常将sql数据库里的数据直接读取为dataframe,分析操作以后再将dataframe存到sql数据库中。而pandas中的read_sql
和to_sql
函数就可以很方便得从sql数据库中读写数据。
参见pandas.read_sql
的文档,read_sql
主要有如下几个参数:
pd.to_datetime
函数功能类似。可以直接提供需要转换的列名以默认的日期形式转换,也可以用字典的格式提供列名和转换的日期格式,比如{column_name: format string}(format string:"%Y:%m:%H:%M:%S")。以链接常见的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。
参见pandas.to_sql函数,主要有以下几个参数:
还是以写到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函数可以方便的转换)。