MySQL and Python3

I’m using Python more and more so it’s not surprising that recently the need arose to connect to and insert into a MySQL database. I was using (and usually do) Python3, and I ran into a couple of issues in the begining. This post will document those issues and the solutions I found to get MySQL and Python3 to work together.

Package Installation

Support for MySQL doesn’t ship with the Python installer, therefore we need to install a package that adds this functionality. I usually use pip3 for this task, and I tried the following.

pip3 install MySQLdb

I found code examples online showing import MySQLdb statements, so I figured that was the name of the package. I was wrong.

Could not find a version that satisfies the requirement MySQLdb (from versions: )
No matching distribution found for MySQLdb

So I thought, let’s try just pip3 install mysql

Collecting mysql
  Using cached https://files.pythonhosted.org/packages/06/ef/c4efbf2a51fb46aba9be03a973638d9539c9ca10a5259b2cbb1a66133b2e/mysql-0.0.1.tar.gz
Collecting MySQL-python (from mysql)
  Using cached https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip
Installing collected packages: MySQL-python, mysql
  Running setup.py install for MySQL-python ... error
    Complete output from command "c:\program files\python36\python.exe" -u -c "import setuptools, tokenize;__file__='C:\\Users\\admin\\AppData\\Local\\Temp\\pip-install-88jce1si\\MySQL-python\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\admin\AppData\Local\Temp\pip-record-f7l848e_\install-record.txt --single-version-externally-managed --compile:
    running install
    running build
    running build_py
    creating build
[omitted output]
    building '_mysql' extension
    creating build\temp.win-amd64-3.6
    creating build\temp.win-amd64-3.6\Release
    C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.12.25827\bin\HostX64\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MT -Dversion_info=(1,2,5,'final',1) -D__version__=1.2.5 "-IC:\Program Files (x86)\MySQL\MySQL Connector C 6.0.2\include" "-Ic:\program files\python36\include" "-Ic:\program files\python36\include" "-IC:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.12.25827\Include" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\shared" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\um" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\winrt" "-IC:\Program Files (x86)\Windows Kits\10\include\10.0.16299.0\ucrt" "-IC:\Program Files (x86)\Windows Kits\NETFXSDK\4.6.1\include\um" /Tc_mysql.c /Fobuild\temp.win-amd64-3.6\Release\_mysql.obj /Zl
    _mysql.c
    _mysql.c(42): fatal error C1083: Cannot open include file: 'config-win.h': No such file or directory
    error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Community\\VC\\Tools\\MSVC\\14.12.25827\\bin\\HostX64\\x64\\cl.exe' failed with exit status 2

Okay this is more like it, but now I’m receiving an error instead of installing a package.

The Python MySQL cl.exe Distraction

Searching the error error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Community\\VC\\Tools\\MSVC\\14.12.25827\\bin\\HostX64\\x64\\cl.exe' failed with exit status 2 and I learned that cl.exe is the Visual C++ compiler.

Apparently, the installation of the compiler is done via Visual Studio. I have Visual Studio installed so I loaded up the Visual Studio Installer – the program used to add functionality to VS. Sure enough, I didn’t have the Visual C++ components installed. I started installing it. It was taking awhile because the installation size was over 1 GB. While that was going on I continued to research MySQL and Python3. Before the process finished, I learned that the MySQLdb was not designed for Python3; the functionality was replaced by the mysqlclient package.

The Right Package (mysqlclient)

Now knowing that MySQLdb was out and mysqlclient was in, I went ahead and installed that package.

pip3 install mysqlclient

Collecting mysqlclient
  Using cached https://files.pythonhosted.org/packages/32/4b/a675941221b6e796efbb48c80a746b7e6fdf7a51757e8051a0bf32114471/mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.12

Example CRUD Operations

Now that the right MySQL package is installed, let’s do some basic Create, Read, Update, and Delete (CRUD) operations.

Create

The following example opens a CSV file containing records of gold PRICE on a given day (TIMESTAMP) from a given SOURCE.

import MySQLdb as mdb

PRICE     = 0
TIMESTAMP = 1
SOURCE    = 2

conn = mdb.connect('hostname', 'username', 'p@s$w0rd', 'database')
cursor = conn.cursor()

with open('gold_price.csv', 'r') as gold_prices:
    lines = gold_prices.read().splitlines()

    for i in range(1, len(lines)):
        _tuple = lines[i].split(',')
        cursor.execute("INSERT INTO gold_price (price, time, source) VALUES (%s, '%s', '%s');" %
        (_tuple[PRICE], _tuple[TIMESTAMP], _tuple[SOURCE]))

cursor.close()
conn.commit()
conn.close()

Retrieve

This bit fetches all the rows in a table (i.e. gold_price) and prints out each row – as a tuple.

import MySQLdb as mdb

conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ')
cursor = conn.cursor()

cursor.execute('SELECT * FROM gold_price;')

for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

# Output
(1319.03, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 12, 19, 30, 2))
(1313.33, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 14, 21, 0, 3))
(1290.68, 'https://www.xe.com/currencyconverter/convert/?Amount=1&From=XAU&To=USD', datetime.datetime(2018, 5, 15, 21, 0, 3))
[output omited]

Update

Here I’ll update the gold price on a give date to $1300.

import MySQLdb as mdb

conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ')
cursor = conn.cursor()

cursor.execute("UPDATE gold_price SET price = %s WHERE time = %s", ("1300", "2018.05.15-21:00:03"))
cursor.close()
conn.commit()
conn.close()

Delete

Now we can delete that altered entry.

import MySQLdb as mdb

conn = mdb.connect('www.jasonfavrod.com', 'econ', 'i8peanut$', 'econ')
cursor = conn.cursor()


cursor.execute("DELETE from gold_price WHERE time = '%s'" % "2018-05-23 02:56:07")
cursor.close()
conn.commit()
conn.close()

For more on the mysqlclient for Python3, see their Github project.

Jason Favrod

Adventures in Information