python pandas df.to_sal 关系 “sqlite_master“ 不存在，解决方式

报错

Traceback (most recent call last):
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 2020, in execute
    cur.execute(*args, **kwargs)
psycopg2.errors.UndefinedTable: 错误:  关系 "sqlite_master" 不存在
LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...
                         ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:/code/IdeaProjects/StudyPython/study/地代码到eimos.py", line 51, in <module>
    df.to_sql(name='code_temp', schema='public', con=conn_2,
  File "D:\Program Files\Python38\lib\site-packages\pandas\core\generic.py", line 2963, in to_sql
    return sql.to_sql(
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 2189, in to_sql
    table.create()
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 831, in create
    if self.exists():
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 815, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 2197, in has_table
    return len(self.execute(query, [name]).fetchall()) > 0
  File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 2032, in execute
    raise ex from exc
pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': 错误:  关系 "sqlite_master" 不存在
LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...
                         ^

报错原因：pandas 使用的数据库连接引擎是sqlalchemy，但如果使用psycopg2 连接代码可以运行，但会报出下边的警告，此时代码可以运行，

    conn = psycopg2.connect(host='xxx.xxx.xxx.xxx', user='postgres', password='xxxx', database='test')
    sql3 = '''select * from table '''.format(moduleId)
    df = pd.read_sql(sql3, con=conn)
D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

当使用以上方式运行df.to_sql时报‘关系 “sqlite_master” 不存在’ 错误

df.to_sql(name='code_temp', schema='public', con=conn, if_exists='append', index=False)

解决方式

from urllib import parse
import pandas as pd
from sqlalchemy import create_engine

    host = 'xx.xx.xx.xx'
    user = 'postgres'
    password = parse.quote_plus('xxxx@xxxx')
    database = 'xxxx'
    post = '5432'
    conn = create_engine(f"postgresql+psycopg2://{user}:{password}@{host}:{post}/{database}")
    
    sql3 = '''select * from xxxx limit 200'''

    df = pd.read_sql(sql3, con=conn)

    df.to_sql(name='code_temp', schema='public', con=conn, if_exists='append', index=False)