pandas 链接数据库

直接执行sql
from pandas.io import sql
sql.execute(‘SELECT * FROM table_name’, engine)
sql.execute(‘INSERT INTO table_name VALUES(?, ?, ?)’, engine,
params=[(‘id’, 1, 12.2, True)])

创建链接

from sqlalchemy import create_engine

engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')

engine = create_engine('mssql+pyodbc://mydsn')

# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine('sqlite:///foo.db')

# or absolute, starting with a slash:
engine = create_engine('sqlite:absolute/path/to/foo.db')

创建引擎

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
with engine.connect() as conn:
    data = pd.read_sql_table('data', conn)

chunksize设置每次写入的数量，有些数据库会设置限制。

data.to_sql(‘data_chunked’, conn, chunksize=1000)

指定相应的数据类型

from sqlalchemy.types import String
data.to_sql(‘data_dtype’, engine, dtype={‘Col_1’: String})

指定索引列
pd.read_sql_table(‘data’, engine, index_col=‘id’)

指定时间类型
pd.read_sql_table(‘data’, engine, parse_dates=[‘Date’])

读取sql语句选择的数据
pd.read_sql_query(‘SELECT * FROM data’, engine)

列子：
通过sqlalchemy转换指定的数据库格式

from sqlalchemy import create_engine
from sqlalchemy import Date
import pandas as pd

a=[{"time":"2018-09-19","age":17,"salary":199.2,"name":"wang"}]

df=pd.DataFrame(a)

engine = create_engine(
    "postgresql://user:psword@localhost:5432/postgres",
    pool_size=20, max_overflow=0,
    )
with engine.connect() as conn:
    df.to_sql("test", conn, if_exists='replace', index=False,dtype={'time': Date})

参考文献：
http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql
http://docs.sqlalchemy.org/en/latest/core/type_basics.html