Python – Connect to MySQL on Ubuntu 20.04 by pyodbc No.98

How to connect to MySQL by pyodbc and run select statement using Python is shown in this blog.

▼1. Connecting to MySQL by pyodbc Python

In the past blogs, how to connect to MySQL using Java is provided as below. in this blog, the connection to MySQL by pyodbc using Python is written.

(2022/12 時点)
Java –Save records for MySQL into Azure Blob Storage No.86 
Java -Save the results of select for MySQL into a file No.85
Java – Connect to MySQL on Ubuntu and run select query No.80

▼2. Prerequires

2-1. Installing MySQL on Linux

Ref: Java – Connect to MySQL on Ubuntu and run select query No.80　”2-6. installing mysql java connector”

2-2. Installing MySQL の Connector/ODBC 8.0.31

Ref: ODBC installation and configuration

2-2-1.  Downloading 2 types of files from MySQL site

According to your Ubuntu version, files should be downloaded. in this case, files for Ubuntu 20.04 are downloaded.

1. Compressed TAR Archive　(mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit.tar.gz)
2. Ubuntu Linux 20.04 (x86, 64-bit), DEB Package (mysql-connector-odbc-setup_8.0.31-1ubuntu20.04_amd64.deb)

— Steps of download

A. Downloading Compressed TAR Archive　(mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit.tar.gz) from MySQL Download site.

B．Downloading Ubuntu Linux 20.04 (x86, 64-bit), DEB Package (mysql-connector-odbc-setup_8.0.31-1ubuntu20.04_amd64.deb) from MySQL Download site.

2-2-2.  Extracting the tar.gz files that were downloaded

Copy files under /bin/ and /lib/ into /usr/local/bin/ and /usr/local/lib/ after extract.

tar xzvf ./mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit.tar.gz
cd /home/xxx/mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit/
sudo cp bin/* /usr/local/bin
sudo cp lib/* /usr/local/lib

2-2-3.  Registering ODBC Driver using myodbc-installer

sudo myodbc-installer -a -d -n "MySQL ODBC 8.0 Driver" -t "Driver=/usr/local/lib/libmyodbc8w.so"

sudo myodbc-installer -a -d -n "MySQL ODBC 8.0" -t "Driver=/usr/local/lib/libmyodbc8a.so"

myodbc-installer -d -l

2-2-4.  Confirming the registered ODBC Driver

cat /etc/odbcinst.ini

(結果)
[MySQL ODBC 8.0 Driver]
Driver=/usr/local/lib/libmyodbc8w.so
UsageCount=2

[MySQL ODBC 8.0]
Driver=/usr/local/lib/libmyodbc8a.so
UsageCount=2

2-2-5.  Setting up myodbc-connector-odbc

Installing downloaded deb file in 2-2-1 and 2 to generate necessary files under /usr/lib/x86_64-linux-gnu/odbc folder.

sudo dpkg -i ./mysql-connector-odbc-setup_8.0.31-1ubuntu20.04_amd64.deb

2-2-6.  Adding below in /etc/odbc.init

vi /etc/odbc.init

[MySQL]
Description = ODBC for MySQL
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
FileUsage=1

2-2-7.  Updating config file

sudo odbcinst -i -d -f /etc/odbcinst.ini 

(結果)
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 2. 
    Target directory is /etc

▼3. Python code to connect to MySQL and execute select query

3-1. Create Python code

“runmysqlquery.py” is created here  according to your environment, please change parameters.

import pyodbc

driver = '{MYSQL ODBC 8.0 Driver}'
server = 'localhost'
dbname = 'testdb'
user = 'hiveuser'
pw = 'hivepassword'
query = "select * from testtbl"

try:
    conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+dbname+';UID='+user+';PWD='+pw+';')
    cursor = conn.cursor()
    cursor.execute(query)
    row = cursor.fetchone()
    while row:
        print(str(row[0]) + " " + str(row[1]))
        row = cursor.fetchone()
except pyodbc.Error as ex:
    sqlstate = ex.args[1]
    print(sqlstate)

3-２.  Results of this code

The results of select statement are below/

python3 runmysqlquery.py

(Output)
1 1
2 2
3 3

▼4. Reference

1. Installing MySQL Java – Connect to MySQL on Ubuntu and run select query No.80 ”2-6. installing mysql java connector”
2. ODBC installation and configuration