Python dataframe to SQL Server writing problem

Question

I can connect to the sql-server database and read data from python using,

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=testdb;UID=Another_Domain\\username;PWD=pass; Trusted connection=YES')

But when I try to write data from python to sql-server using,

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

I get an error,

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

How can I connect?

Unrelated, specifically, to the question you're asking, however, if you're using a Trusted connection in the first statement, why are you passing a Username and password? It's one or the other. — Thom A
– Thom A ♦, Commented Mar 11, 2020 at 13:04
The second block seems to come from sqlalchemy, and its connection syntax is very different from pyodbc. You have to assign the driver string explicitly. You may refer to the following MWE (minimum working example) and see if it works. stackoverflow.com/a/60636359/3218693 — Bill Huang
– Bill Huang, Commented Mar 11, 2020 at 13:09

Gennady Morozov · Accepted Answer · 2020-03-11 13:05:22Z

0

Maybe the way you can connect is:

create_engine('mssql+pymssql://{domain}\{username}:{password}@{hostip}:{port}/{db}')

answered Mar 11, 2020 at 13:05

Gennady Morozov

363 bronze badges

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

Collectives™ on Stack Overflow

Python dataframe to SQL Server writing problem

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related