Search code examples
pythondataframesqlalchemysybasetemp-tables

How to prevent pandas dataframe from adding double quotes around #tmp when using sqlalchemy and sybase?

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

Code :

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)

Error:

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.

Solution

  • 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