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

Recommended Posts

Touch MySQL from Python 3
Use MySQL from Python
Use MySQL from Python
Use MySQL from Anaconda (python)
Touch a Python object from Elixir
sql from python
MeCab from Python
Receive textual data from mysql with python
Connecting from python to MySQL on CentOS 6.4
API explanation to touch mastodon from python
Use thingsspeak from python
Operate Filemaker from Python
Use fluentd from python
Access bitcoind from python
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
Python from or import
Run python from excel
Install python from source
Execute command from Python
[ev3dev × Python] Touch sensor
Write Python in MySQL
Python beginners touch Pytorch (3)
Operate neutron from Python!
Operate LXC from Python
Manipulate riak from python
Force Python from Fortran
Use BigQuery from python.
Python beginners touch Pytorch (1)
Python beginners touch Pytorch (2)
Connect python to mysql
Execute command from python
[Python] Read From Stdin
Use mecab-ipadic-neologd from python
Flatten using Python yield from
Call CPLEX from Python (DO cplex)
Deep Python learned from DEAP
Post from Python to Slack
Grammar features added from Python3.6
Cheating from PHP to Python
Make MeCab available from Python3
Information obtained from tweet_id (Python)
OCR from PDF in Python
Run illustrator script from python
Anaconda updated from 4.2.0 to 4.3.0 (python3.5 updated to python3.6)
Study from Python Hour4: Object-oriented ②
Query Athena from Lambda Python
Access Oracle DB from Python
Study from Python Hour3: Functions
Start / stop GCE from python
Stop Omxplayer from Python code
Switch from python2.7 to python3.6 (centos7)
Connect to sqlite from python
Install pyenv from Homebrew, install Python from pyenv
Study from Python Hour4: Object-oriented ①
Touch Apache Beam in Python
Python naming convention (from PEP8)
With skype, notify with skype from python!
Use e-Stat API from Python
Register redmine issue from Python
Connection pooling with Python + MySQL