Can't establish connection SQL Server using sqlalchemy

Question

I'm trying to use Python to upload from a Pandas dataframe to a SQL Server table, but I can't successfully create a connection using sqlalchemy. I understand I first need to create an engine object using create_engine(), and create a connection object using engine.connect(), but no string I enter in create_engine() seems to work. I've tried the following:

engine = create_engine('mssql+pyodbc://myServer/myDB')
conn = engine.connect()

and:

engine = create_engine('mssql+pyodbc://Server=myServer;Database=myDB;')
conn = engine.connect()

and:

engine = create_engine('mssql+pyodbc://Driver={SQL Server};Server=myServer;Database=myDB;Trusted_Connection=yes;')
conn = engine.connect()

but all result in the following error:

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I've also tried:

engine = create_engine('mssql+pyodbc://Driver={SQL Server Native Client 11.0};Server=myServer;Database=myDB;Trusted_Connection=yes;')
conn = engine.connect()

which results in the following error:

DBAPIError: (pyodbc.Error) ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

While I can successfully connect using pyodbc like this:

conn = pyodbc.connect('DRIVER={SQL Server};Server=myServer;Database=myDB;Trusted_Connection=yes;')

I can't seem to make this work for sqlalchemy.

Any help would be appreciated.

Lurch · Accepted Answer · 2019-03-19 13:47:40Z

3

The solution:

engine = create_engine('mssql+pyodbc://ERRSTSDBP2/ActPri?driver=SQL+Server+Native+Client+11.0')

Thanks to norbeq for getting me most of the way there!

answered Mar 19, 2019 at 13:47

Lurch

731 gold badge1 silver badge7 bronze badges

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

Comments

alper · Accepted Answer · 2023-12-15 18:36:24Z

1

You can try connection like this:

engine = create_engine('mssql+pyodbc://user:password@host:port/myDB')
conn = engine.connect()

edited Dec 15, 2023 at 18:36

alper

3,52513 gold badges64 silver badges127 bronze badges

answered Mar 19, 2019 at 0:05

norbeq

3,1061 gold badge19 silver badges22 bronze badges

4 Comments

Lurch Over a year ago

I don't have a password, I connect with Windows authentication. What does 'host:port' mean? I tried the following, it didn't work: engine = create_engine('mssql+pyodbc://login@myServer/myDB') conn = engine.connect()

norbeq Over a year ago

Ok, so engine should be : engine = create_engine('mssql+pyodbc://@myServer/myDB?trusted_connection=yes&driver=SQLServer+Native+Client+11.0')

Lurch Over a year ago

Still getting this error: InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Lurch Over a year ago

I got it! The solution: engine = create_engine('mssql+pyodbc://myServer/myDB?driver=SQL+Server+Native+Client+11.0')

Collectives™ on Stack Overflow

Can't establish connection SQL Server using sqlalchemy

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related