I am trying to connect to a MySQL db using sqlalchemy and pyodbc. Connecting using ODBC is a requirement, so I cannot use any other methods for creating engine and connection. Here the code I am using to create engine and connection.
params = quote_plus("DRIVER={MySQL ODBC 8.0 Unicode Driver};"
f"SERVER={host}:{port};"
f"DATABASE={db};"
f"UID={username};"
f"PWD={password}")
# Creating dbengine and connection
db_engine = create_engine(f"mysql+pyodbc:///?odbc_connect={params}")
print('Type of dbengine', type(db_engine))
connection = db_engine.connect()
I am giving the last 3 lines of the stack trace I am getting from connection = db_engine.connect().
File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
cursor.execute(statement, parameters)
TypeError: The first argument to execute must be a string or unicode query.
There is no query I am passing or as per the connect() doc, connect takes only 1 kwarg and no query.
I don't understand why I am getting this error. Can someone explain what and why this error?
Also, seems like pyodbc gives a lot of errors sometimes(as per other resources I read). Is there a good alternative for sqlalchemy+pyodbc. I finally want to pass the connection to pd.to_sql and `pd.read_sql' and using odbc is mandatory.
This was an issue with SQLAlchemy 1.3.x and mysql+pyodbc:// that has been fixed in SQLAlchemy 1.4.