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:

  • SQL Server 2005 Currency Format with comma's and period
  • SQL Server CSV Exports using SQL Server Agent & Powershell?
  • Entity Framework 6.1 : The given key was not present in the dictionary
  • Connection timeout for SQL server
  • Efficient way to string split using CTE
  • Grant execute permission for a user on all stored procedures in database?
  • 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
    

  • Database engine fails to connect to a sql-server instance while trying to insert using to_sql function
  • How to convert the data frame values and its headers into a list with python?
  • pandas + pyodbc ODBC SQL type -150 is not yet supported
  • Pandas IO SQL and stored procedure with multiple result sets
  • Pandas SQL chunksize
  • How to write dataframe into mssql using pymssql?
  • 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;'
        r'Server=(local)\SQLEXPRESS;'
        r'Database=myDb;'
        r'Trusted_Connection=yes;'
    )
    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()
    print(rows)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.