Python and MySQL

This article shows how to connect to mysql from python.

The steps are as follows

• create your venv
• install the driver package
• confirm the connectivity parameters
• proceed

The best recommendation is to use a connection pool. The driver uses connection pooling, you just need to use it. This will allow avoiding keeping creating a new connection and destroying it, while you can use connections maintained alive as part of a pool.

Install Driver

The driver is installed with the following command

pip install mysql-connector-python

There are other drivers, however this one seems to be easiest to install

Confirm Connectivity Parameters

So we use a database installed locally (localhost) on mariadb server lisening to standard port 3306, a database called test, user test and password ‘test1’. The table we created is tinf with fields id and comments, both varchar;

OK so now we want to connect to it. This is how we do it. The config below contains all the parameters, although we could have omitted the host and the port, since they are the standard ones.

import mysql.connector

dbconfig = {
    'database': 'test',
    'user': 'test',
    'password': 'test1',
    'host': 'localhost',
    'port': 3306
}

pool = mysql.connector.pooling.MySQLConnectionPool(pool_name='starter_pool', pool_size=3, **dbconfig)
cnx = pool.get_connection()
cursor = cnx.cursor()
cursor.execute('select id, comments from tinf where id like %s', ('1i%',))
for row in cursor.fetchall():
    print(row[0], row[1])

cursor.close()
cnx.close()

Note a couple of things

• pool should be long lived
• each time you create a connection
• then you need a cursor. This is like a Statement at java, you post the commands against this object, not the connection
• At the end you close both the cursor and the connection, although I suspect that closing the connection (which in this case means releasing resources and returning the connection to the pool) will also close the cursor

Transactions

Of course there are transactions in this context. If you need transactions, there are two steps

Set autocommit to false to the connection, otherwise it is by default true

Then you can call either commit() method or rollback() also on the connection. Of course this is written in some context that takes exceptions into account etc.