pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

Question

I am attempting to write a Python script that can take Excel sheets and import them into my SQL Server Express (with Windows Authentication) database as tables. To do this, I am using pandas to read the Excel files into a pandas DataFrame, I then hope to use pandas.to_sql() to import the data into my database. To use this function, however, I need to use sqlalchemy.create_engine().

I am able to connect to my database using pyodbc alone, and run test queries. This conection is done with the followng code:

def create_connection(server_name, database_name):
    config = dict(server=server_name, database= database_name)

    conn_str = ('SERVER={server};DATABASE={database};TRUSTED_CONNECTION=yes')

    return pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};' + conn_str.format(**config))

...

server = '<MY_SERVER_NAME>\SQLEXPRESS'
db = '<MY_DATABASE_NAME>

connection = create_connection(server, db)
cursor = connection.cursor()
cursor.execute('CREATE VIEW test_view AS SELECT * FROM existing_table')
cursor.commit()

However, this isn't much use as I can't use pandas.to_sql() - to do so I need an engine from sqlalchemy.create_engine(), but I am struggling to figure out how to use my same details in my create_connection() function above to successfully create an engine and connect to the database.

I have tried many, many combinations along the lines of:

engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?driver={ODBC Driver 13 for SQL Server}?trusted_connection=yes")
conn = engine.connect().connection

or

engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?trusted_connection=yes")   
conn = engine.connect().connection

Gord Thompson · Accepted Answer · 2022-11-16 22:56:01Z

26

A Pass through exact Pyodbc string works for me:

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

connection_string = (
    r"Driver=ODBC Driver 17 for SQL Server;"
    r"Server=(local)\SQLEXPRESS;"
    r"Database=myDb;"
    r"Trusted_Connection=yes;"
)
connection_url = URL.create(
    "mssql+pyodbc", 
    query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)

df = pd.DataFrame([(1, "foo")], columns=["id", "txt"])
pd.to_sql("test_table", engine, if_exists="replace", index=False)

edited Nov 16, 2022 at 22:56

answered Jun 26, 2017 at 16:59

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

4 Comments

alwaysaskingquestions Over a year ago

why are we using the quote_plus()?

Gord Thompson Over a year ago

Answer updated to use sqlalchemy.engine.URL

smartexpert Over a year ago

thanks you for this. Any idea why constructing the connection this way works as opposed to the one used in the question?

Gord Thompson Over a year ago

URL.create() takes care of escaping characters that can cause problems in a plain string URL. The most common is '@' in a password.

Collectives™ on Stack Overflow

pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related