【3】数据分析--10--科学计算--Pandas--9--与数据库交互

与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

Sam avatar
About Sam
专注生物信息 专注转化医学