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.

Gord Thompson · Accepted Answer · 2020-08-06 14:17:12Z

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.

answered Aug 6, 2020 at 14:17

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Divyaansh Bajpai Over a year ago

Thanks a lot for your help.

Divyaansh Bajpai Over a year ago

Hi Gord I am expercing some inconsistent behaviour 1) Query is not pulling the complete data Eg: Query on db returns 30k records however here it sometimes returns 1k , 3k or max 10k. 2) Create table statement that getting generated is different all the times even after specifying dtype specifically. Can you guide towards right direction in this regard.

Gord Thompson Over a year ago

@Divyaansh - Please open new questions or GitHub issues if you need further assistance. Notes: Please limit questions/issues to a single topic and include a minimal reproducible example.

Collectives™ on Stack Overflow

pandas to_sql syntax error with SQLAlchemy and Sybase

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related