Can't establish connection SQL Server using sqlalchemy

2
votes

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.

2
pythonsql-serversqlalchemy

2 Answers

2
votes

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!

0
votes

You can try connection like this:

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