【6.2.1】Pandas--与数据库交互

April 03, 2018 pandas 阅读量：次

与SQLAlchemy通过create_engine() 函数来创建engine对象

import pandas as pd
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')

dataframe另存为sql数据库

df = pd.read_excel(os.path.join(input_dir, selected_sample), sheet_name='analysis')

df.to_sql('table_name' , con=engine, if_exists='replace', index_label='id', chunksize=1000)

读取sql数据库为datafame

df = pd.read_sql_table('table_name', engine, index_col='id')

参考资料

药企，独角兽，苏州。团队长期招人，感兴趣的都可以发邮件聊聊：tiehan@sina.cn

个人公众号，比较懒，很少更新，可以在上面提问题，如果回复不及时，可发邮件给我： tiehan@sina.cn