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

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:

  • Editing a large dataset for SQLBulkCopy into a SQL Server database
  • Use SQL queries virtual console to do any thing on a SQL Server database
  • Error:create function must be the only statement in the batch
  • How can I speed up this SQL query
  • TSQL Help with simple query
  • Looping RecordSet Destination in SSIS Script Task
  • 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))
    db = '<MY_DATABASE_NAME>
    connection = create_connection(server, db)
    cursor = connection.cursor()
    cursor.execute('CREATE VIEW test_view AS SELECT * FROM existing_table')

    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


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

  • How to create sql alchemy connection for pandas read_sql with sqlalchemy+pyodbc and multiple databases in MS SQL Server?
  • Database engine fails to connect to a sql-server instance while trying to insert using to_sql function
  • Parse Json data and update in sql table via python
  • Error when trying to create new database table in SQL Server 2016 from csv file while using python 3.5 with pandas and sqlalchemy
  • Python dataframe to SQL numbers are getting rounded
  • Loop through records and update sql table if exists using Pyodbc or pandas
  • One Solution collect form web for “pyodbc.connect() works, but not sqlalchemy.create_engine().connect()”

    A Pass through exact Pyodbc string works for me from Python 3.6 on Windows:

    from sqlalchemy import create_engine
    import urllib
    conn_str = (
        r'Driver=ODBC Driver 11 for SQL Server;'
    quoted_conn_str = urllib.parse.quote_plus(conn_str)
    engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
    cnxn = engine.connect()
    rows = cnxn.execute("SELECT name FROM sys.tables").fetchall()
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.