SQL Alchemy、pymssql、Pandas 0.24.2 to_sql 尝试在表已存在时创建表

我正在尝试使用 Pandas 和 Sql Alchemy。这基本上就是我想做的事情。如果我删除该表，它会创建它，但我希望它追加而不需要进行表重命名。我尝试过更新和更改所有库的版本。我很茫然。如果我一开始没有表，它会创建它，然后我再次运行代码，它就会崩溃。错误消息只是说该表已经存在，我知道，这就是为什么我告诉它追加。另外，在加载之前，我正在使用 PYMSSQL 读取数据，并且它可以很好地读取数据帧

Python命令

def writeDFtoSSDatabase(tgtDefiniton,df):
try:
    if int(tgtDefiniton.loadBatchSize) > 0:
        batchSize = int(tgtDefiniton.loadBatchSize)
    else:
        batchSize = 1000
    #Domain error using SQL Alchemy
    logging.debug("Writting Dataframe to SQL Server database")
    #hardcoded type beccause that is only type for now
    with createDBConnection(tgtDefiniton.tgtDatabaseServer
                                ,tgtDefiniton.tgtDatabaseDatabase
                                ,tgtDefiniton.tgtDatabaseUser
                                ,tgtDefiniton.tgtDatabasePassword,tgtDefiniton.tgtDataType).connect().execution_options(schema_translate_map={
                                                                                                                        None: tgtDefiniton.tgtDatabaseSchema}) as conn:
        logging.debug("Writting DF to Database table {0}".format(tgtDefiniton.tgtDatabaseTable))
        logging.debug("ifTableExists: {0}.".format(tgtDefiniton.ifTableExists))
        
        if tgtDefiniton.ifTableExists == "append":
            logging.debug('Appending Data')
            df.to_sql(tgtDefiniton.tgtDatabaseTable,con=conn,if_exists='append',chunksize = batchSize,index=False)
        elif tgtDefiniton.ifTableExists == "replace":
            logging.debug('Replacing Table and Data')
            df.to_sql(tgtDefiniton.tgtDatabaseTable,con=conn,if_exists='replace',chunksize = batchSize,index=False)
        else:
            df.to_sql(tgtDefiniton.tgtDatabaseTable,con=conn,if_exists='fail',index=False)
        logging.debug("Data wrote to database")
except Exception as e:
    logging.error(e)
    raise

Error

(Background on this error at: http://sqlalche.me/e/e3q8)
2021-08-30 13:31:42 ERROR    (pymssql.OperationalError) (2714, b"There is already an object 
named 'test' in the database.DB-Lib error message 20018, severity 16:\nGeneral SQL Server 
error: Check messages from the SQL Server\n")

编辑： 日志条目

  2021-08-30 13:31:36 DEBUG    Writting Dataframe to SQL Server database
  2021-08-30 13:31:36 DEBUG    create_engine(mssql+pymssql://REST OF             CONNECTION INFO
  2021-08-30 13:31:36 DEBUG    DB Engine Created
  2021-08-30 13:31:36 DEBUG    Writting DF to Database table test
  2021-08-30 13:31:36 DEBUG    ifTableExists: append.
  2021-08-30 13:31:36 DEBUG    Appending Data
  2021-08-30 13:31:42 ERROR    (pymssql.OperationalError) (2714, b"There is already an object named 'test' in the database.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

[SQL:

我遇到了同样的问题，我找到了两种方法来解决它，尽管我不知道为什么会解决它：

1. 要么在创建连接时在url中传递数据库名称
2. 或将数据库名称作为模式传递pd.to_sql.

两者都做并没有坏处。

```
#create connection to MySQL DB via sqlalchemy & pymysql
user = credentials['user']
password = credentials['password']
port = credentials['port']
host = credentials['hostname']
dialect = 'mysql'
driver = 'pymysql'
db_name = 'test_db'

# setup SQLAlchemy   
from sqlalchemy import create_engine 
cnx = f'{dialect}+{driver}://{user}:{password}@{host}:{port}/' 
engine = create_engine(cnx) 

# create database
with engine.begin() as con:
    con.execute(f"CREATE DATABASE {db_name}")

############################################################
# either pass the db_name  vvvv - HERE- vvvv after creating a database
cnx = f'{dialect}+{driver}://{user}:{password}@{host}:{port}/{db_name}'      
############################################################
engine = create_engine(cnx) 

table = 'test_table'
col = 'test_col'
with engine.begin() as con:
    # this would work here instead of creating a new engine with a new link
    # con.execute(f"USE {db_name}")
    con.execute(f"CREATE TABLE {table} ({col} CHAR(1));")

# insert into database
import pandas as pd
df = pd.DataFrame({col : ['a','b','c']})

with engine.begin() as con:
    # this has no effect here
    # con.execute(f"USE {db_name}")
    df.to_sql(
        name= table,
        if_exists='append',
        con=con, 
############################################################
# or pass it as a schema vvvv - HERE - vvvv
        #schema=db_name,
############################################################
        index=False
    )```

使用python版本测试3.8.13和 sqlalchemy1.4.32。 可能出现了同样的问题here https://stackoverflow.com/questions/26765863/python-pandas-to-sql-append and here https://stackoverflow.com/questions/69906698/pandas-to-sql-gives-table-already-exists-error-with-if-exists-append.