I'm having some trouble using pyodbc against a local mysql database. Here's some version information:
➜ ~ python3 --version
Python 3.6.2
➜ ~ pip3 show pyodbc
Name: pyodbc
Version: 4.0.21
Summary: DB API Module for ODBC
Home-page: https://github.com/mkleehammer/pyodbc
Author: Michael Kleehammer
Author-email: michael@kleehammer.com
License: MIT
Location: /usr/local/lib/python3.6/site-packages
Requires:
➜ ~ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/Cellar/unixodbc/2.3.4/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/Cellar/unixodbc/2.3.4/etc/ODBCDataSources
USER DATA SOURCES..: /Users/cognite/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
➜ ~ cat /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini
[ODBC Drivers]
MySQL ODBC 5.3 Unicode Driver = Installed
MySQL ODBC 5.3 ANSI Driver = Installed
MySQL ODBC 5.3 Driver = Installed
[ODBC Connection Pooling]
PerfMon = 0
Retry Wait =
[MySQL ODBC 5.3 Unicode Driver]
Driver = /usr/local/mysql-connector-odbc-5.3.9-macos10.12-x86-64bit/lib/libmyodbc5w.so
[MySQL ODBC 5.3 ANSI Driver]
Driver = /usr/local/mysql-connector-odbc-5.3.9-macos10.12-x86-64bit/lib/libmyodbc5a.so
[MySQL ODBC 5.3 Driver]
Driver = /usr/local/lib/libmyodbc5w.so
➜ ~ cat /usr/local/Cellar/unixodbc/2.3.4/etc/odbc.ini
[ODBC Data Sources]
myodbc = MySQL ODBC 5.3 Unicode Driver
myodbca = MySQL ODBC 5.3 ANSI Driver
[ODBC]
Trace = 0
TraceAutoStop = 0
TraceFile =
TraceLibrary =
[myodbc]
Driver = MySQL ODBC 5.3 Unicode Driver
SERVER = localhost
PORT = 3306
[myodbca]
Driver = MySQL ODBC 5.3 ANSI Driver
SERVER = localhost
PORT = 3306
I can clearly see the test database when I login to mysql (using mysql -u root -p and then entering the password). show databases gives:
...
| mysql |
| performance_schema |
| sys |
| test |
+------------------------------------------------------+
189 rows in set (0.00 sec)
So I'm not sure why my connection attempt with pyodbc are failing. Here's the python code I'm trying to connect with and the output I'm seeing:
import pyodbc
pyodbc.drivers()
# ['ODBC Drivers', 'ODBC Connection Pooling', 'MySQL ODBC 5.3 Unicode Driver', 'MySQL ODBC 5.3 ANSI Driver', 'MySQL ODBC 5.3 Driver']
CONNECTION_STRING = "driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=root;CHARSET=UTF8"
conn = pyodbc.connect(CONNECTION_STRING)
# Traceback (most recent call last):
# File "<input>", line 1, in <module>
# pyodbc.Error: ('H000', '[H000] [unixODBC][ (0) (SQLDriverConnect)')
What does the error mean? Any help with figuring how I can make the connection to my local database would be appreciated.
Update: I had to use the Ansi driver, and then it worked. But my question still stands: What does that error message mean? How can I get more information about the error? I would have liked it to say something along the lines of "unable to connect to the database with this driver".