pandas to_sql syntax error with SQLAlchemy and Sybase

Question

I am currently struggling with to_sql function in pandas while creating and inserting data into a new table.

Here is the code :

import pandas as pd
import sqlalchemy
import urllib

params=urllib.parse.quote_plus("Driver=Adaptive Server Enterprise;SERVER=xxx.div.com;DATABASE=MYDB;USER=DIV;Pass=XXX;PORT=12345)
table_name="tab2"
engine = create_engine("sybase+pyodbc:///?odbc_connect=%s" %params,echo=True)

query = "Select Col1,Col2,Col3 from tabA"

df=pd.real_sql_query(query,con=engine)

df.to_sql(table_name,engine,if_exists='replace',schema=dbo)

The following generates a create statement like: Create table dbo."tabB" ( "col1" BIGINT NULL, "col2" BIGINT NULL, "col3" BIGINT NULL )

and then errors out with sqlalchemy.exc.Programmingerror : (pyodbc.Programmingerror) Incorrect syntax near '('\n

The default '"'(double generated) is causing the issue. Since the SQL statement is throwing same error when executing this on ASEISql.

Any workaround for this would be helpful.

Thanks for your help in advance.

Note Engine creation is successful.

Answer 1

I am able to reproduce your issue using the internal "sybase" dialect in SQLAlchemy 1.3.18. That internal dialect is

• unsupported,
• soon to be officially deprecated, and
• will be removed from a future release.

I also confirmed that the external SAP ASE (Sybase) dialect renders the DDL correctly.