Touch MySQL from Python 3

When I investigated how to operate MySQL from Python 3, many information on Python 2 and old information were hit and it was difficult to understand, so I will summarize it.

The environment uses Python 3.6.

package

There is also a package called mysqlclient, but the official MySQL mysql-connector-python-rf -connector-python-rf) There is a package, so I will use this one this time.

** [2018-12-03 postscript] ** Please note that the update of mysql-connector-python-rf has stopped, and there seems to be a package called mysql-connector-python, which is also the official MySQL. See the comments section for details.

I don't know much about the merit of the two packages, but I personally find mysql-connector-python-rf easier to use because I couldn't find a way to treat the query results as a dictionary with mysqlclient.

You can install it from pip normally.

$ pip install mysql-connector-python-rf

Connect

The PostgreSQL connector (psycopg2) was able to connect by just throwing a URL string, but it seems that mysql-connector-python-rf cannot. There is no help for it, so parse it with urllib.

from urllib.parse import urlparse
import mysql.connector

url = urlparse('mysql://user:pass@localhost:3306/dbname')

conn = mysql.connector.connect(
    host = url.hostname or 'localhost',
    port = url.port or 3306,
    user = url.username or 'root',
    password = url.password or '',
    database = url.path[1:],
)

If you want to check if you can connect, use ʻis_connected () `.

conn.is_connected()  #=> Bool

If you keep the connection open, you may lose the connection before you know it. To avoid this, ping regularly and reconnect if you can't connect.

conn.ping(reconnect=True)

Query execution

Create a cursor object and execute a query with the ʻexecute` method in the same way as Standard SQLite Connector.

cur = conn.cursor()

Get (SELECT)

You can fetch records with the fetchone / fetchmany / fetchall methods after executing the query.

cur.execute('SELECT * FROM users')

cur.fetchall()  #=> [(1, 'foo'), (2, 'bar')]

Prepared statements are also available.

cur.execute('SELECT * FROM users WHERE id = %s', [1])

cur.statement  #=> 'SELECT * FROM users WHERE id = 1'

cur.fetchone()  #=> (1, 'foo')

Update (INSERT / UPDATE / DELETE)

Commit with conn.commit () after executing the query.

try:
    cur.execute('INSERT INTO users (name) VALUES (%s)', ['foo'])
    conn.commit()
except:
    conn.rollback()
    raise

Get the result as a dictionary

If you pass the dictionary option when creating a cursor object, you can get the query execution result as a dictionary.

cur = conn.cursor(dictionary=True)

cur.fetchall()  #=> [{'id': 1, 'name': 'foo'}, {'id': 2, 'name': 'bar'}]

See below for options that can be passed to other cursor objects.

MySQL :: MySQL Connector/Python Developer Guide :: 10.6 Subclasses cursor.MySQLCursor