使用sqlalchemy和sybase时，如何防止pandas数据框在#tmp周围添加双引号?

问题描述

在使用 sqlalchemy 作为池框架处理 sybase 时，我已将问题简化为 pandas to_sql 在 #tmp 周围添加双引号.

代码:

def get_data_with_tmp():engine = get_connection("sybase")使用 engine.connect() 作为连接:df = pd.DataFrame({'alias_id': ['345402KP5', '3454014R1']})df.to_sql(name='#tmp', con=conn, schema=None, if_exists='append', index=False)df = pd.read_sql_query("SELECT alias_id from #tmp", con=conn)

错误:

statement = '\nCREATE TABLE "#tmp" (\n\talias_id TEXT NULL\n)\n\n' E pyodbc.ProgrammingError: ('42000', "[42000] [SAP][ASEODBC 驱动程序][Adaptive Server Enterprise]'('.\n (102) (SQLExecDirectW)") 附近的语法不正确

当我删除 #tmp 周围的双引号时，它在 sybase 中工作正常.此外，同样的代码无需任何修改即可在 SqlServer 中正常运行.

知道如何解决这个问题吗?我也欢迎其他建议.请注意，我确实有一个遍历数据框并逐行插入的解决方法，但我真的更喜欢使用数据框好的解决方案，例如批处理、批量插入等.

解决方案

此错误已在外部 sybase 方言中修复

https://github.com/gordthompson/sqlalchemy-sybase

特别

https://github.com/gordthompson/sqlalchemy-sybase/releases/tag/1.0.1

I have reduced the issue to pandas to_sql adding double quotes around #tmp when dealing with sybase using sqlalchemy as the pooling framework.

def get_data_with_tmp():
    engine = get_connection("sybase")
    with engine.connect() as conn:
        df = pd.DataFrame({'alias_id': ['345402KP5', '3454014R1']})
        df.to_sql(name='#tmp', con=conn, schema=None, if_exists='append', index=False)
        df = pd.read_sql_query("SELECT alias_id from #tmp", con=conn)

statement = '\nCREATE TABLE "#tmp" (\n\talias_id TEXT NULL\n)\n\n' E   pyodbc.ProgrammingError: ('42000', "[42000] [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near '('.\n (102) (SQLExecDirectW)")

When I remove the double quotes around #tmp it works fine in sybase.Also, this same code works fine in SqlServer without any modifications.

Any idea how to solve this issue?I would welcome alternative suggestions as well.Note that I do have a workaround of looping through the dataframe and inserting row by row, but would really prefer a solution that makes use of dataframe good stuff like batching, batch insert etc.

解决方案

This bug has been fixed in the external sybase dialect

https://github.com/gordthompson/sqlalchemy-sybase

specifically

https://github.com/gordthompson/sqlalchemy-sybase/releases/tag/1.0.1

这篇关于使用sqlalchemy和sybase时，如何防止pandas数据框在#tmp周围添加双引号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！