Pandas sqlalchemy create_engine connection with SQL server windows authentication

Question

I am trying to upload a Pandas DataFrame to SQL server table. From reading, the sqlalchemy to_sql method seems like a great option. However, I am not able to get the create_engine to make the connection.

I am able to connect to the database to retrieve data with Windows authentication. Here is the connection string I am using:

cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server={server_name};"
                      "Database={database_name};"
                      "Trusted_Connection=yes;")

I have tried several different ways to use my login information to connect, here is the most recent version:

engine = create_engine(
        "mssql+pyodbc://{network_user_name}:{network_pw}@{server_name}//{database_name}"
    )

engine.connect()

Here is the error I am getting:

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)

it looks like your connection string is 1 wrong, also the traceback is complaining that no driver was specified try using this from the docs engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0") — Umar.H
– Umar.H, Commented Jun 16, 2020 at 14:32
This definitely helped. I am now getting a Login failed error. I am trying to use my Windows credentials. — chc5286
– chc5286, Commented Jun 16, 2020 at 15:06

Gord Thompson · Accepted Answer · 2020-06-16 15:05:39Z

6

If you are going to use Windows authentication then you simply omit the username/password part of the connection URI. This works fine for me:

connection_uri = (
    "mssql+pyodbc://@192.168.0.179:49242/mydb?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = sa.create_engine(connection_uri)

answered Jun 16, 2020 at 15:05

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

Pandas sqlalchemy create_engine connection with SQL server windows authentication

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related