python使用read_sql与to_sql读写数据库

详细说明

使用pandas读写数据库的方法（以Mysql为例）如下：

• 首先是打包一个工具函数：

  import pandas as pd
  import numpy as np
  from sqlalchemy import create_engine, textdef get_sql_engine():# 数据库mysql_config = {"db": "test_db","host": "127.0.0.1","user": "test_user","password": "test_pass","port": 3306,}engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(mysql_config['user'], mysql_config['password'], mysql_config['host'],mysql_config['port'], mysql_config['db']))return engine
  

• 读取数据库.read_sql()的方法：

  engine = get_sql_engine()# ======== 写入 ===========
  data_df = pd.DataFrame(np.random.random(size=(100, 5)))
  data_df.to_sql('test_data_df', con=engine, if_exists='replace', index=False)
  

• 读取数据库read_sql()的方法：

   data_df = pd.read_sql_query(text('select * from test_data_df'), con=engine.connect())
  

  在读取的时候容易报错，有几个要点：
  1. 首先导入from sqlalchemy import text，然后以text(sql语句)的形式传入第一个参数
  2. 在第二个参数需要使用engine.connect()

示例程序

import pandas as pd
import numpy as np
from sqlalchemy import create_engine, textdef get_sql_engine():# 数据库mysql_config = {"db": "test_db","host": "127.0.0.1","user": "test_user","password": "test_pass","port": 3306,}engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(mysql_config['user'], mysql_config['password'], mysql_config['host'],mysql_config['port'], mysql_config['db']))return enginedef main():engine = get_sql_engine()# ======== 写入 ===========data_df = pd.DataFrame(np.random.random(size=(100, 5)))data_df.to_sql('test_data_df', con=engine, if_exists='replace', index=False)# ======== 读取 ===========data_df = pd.read_sql_query(text('select * from test_data_df'), con=engine.connect())if __name__ == '__main__':main()