I would like write this post after my personal exploration of making mysql + pyodbc connection successful in MAC OS.
To kick start with :
- I don't have mysql odbc drivers in my system. so we have to download them.
- We need to use unixODBC on the Mac because the default driver manager is not compatible with ODBC drivers for some databases. so we need to do this at first brew install unixodbc
- Download the mysql odbc connecter from this link https://dev.mysql.com/downloads/connector/odbc/
- Once we have them installed via dmg pacakge . we have them sit in your system.
- Now we need to register these drivers . so that our system can connect.
- Run the command odbcinst -j in the terminal . you will see list of variables here which shows the data sources path detail files.
- so, plz edit and save odbcinst.ini and odbci.int with below content.
[MySQL ODBC 8.0 ANSI Driver]
Driver=/<pathin your system> /mysql-connector-odbc-8.0.19-macos10.15-x86-64bit/lib/libmyodbc8a.so
UsageCount=1
Connecting to DB using the odbc driver:
connection_string = (
'DRIVER=MySQL ODBC 8.0 ANSI Driver;' 'SERVER=localhost;' 'PORT=3306;' 'DATABASE= dbname;' 'UID=root;' 'PWD=<your pwd>;' 'charset=utf8;')
pyodbc.connect(connection_string);sqlalchemy error:
- suppose if we are using sqlalchemy to connect to db using mysql:pyodbc then it throws the below error
cursor.execute(statement, parameters) TypeError: The first argument to execute must be a string or unicode query.
Fix for above error :
- Check your site-packages and go to this path sqlalchemy/lib/sqlalchemy/dialects/mysql/pyodbc.py and set the below variable flag from False to True
supports_unicode_statements = True
https://github.com/sqlalchemy/sqlalchemy/issues/4869 : Removing the __init__ function helped me to get rid of the above error
