2. MySQL with Python

In this chapter, we will see the methods by which we can connect Python to MySQL database,

2.1. Initial setup

For connecting Python to the MySQL, we need to install a ‘connector’ and create a ‘database’. After this, we can read or write data to the database,

  • First install a connector which allows Python to connect with the database. Install any one of the following connector,
$ pip install mysql-connector

or

$ pip install mysqlclient
  • These the connectors can be imported in python code as below. Rest of the codes will be same for both the connectors.
(mysql-connector)
import mysql.connector as mc

or

(mysqlclient)
import MySQLdb as mc

Note

The ‘mysql-connector’ is not supported by Django-framework. The good option is ‘mysqlclient’ which is supported by Django as well.

  • Next, we need to create a database in MySQL. Let’s create a new database with name ‘pythonSQL’,
$ mysql -u root -p
Enter password:

mysql> CREATE DATABASE pythonSQL;

2.2. Connect and load data

Following code can be used to connect and load the data to database. Note that, the commands in the c.execute(…) statements are exactly same as the commands in the previous chapters.

# create_fill_database.py

import mysql.connector as mc

# connect to database
conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL')
c = conn.cursor()  # cursor to perform operations

def create_table():
    """ Create table in the database """

    # optional: drop table if exists
    c.execute('DROP TABLE IF EXISTS writer')
    c.execute('CREATE TABLE writer \
            (                   \
              id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, \
              name  VARCHAR(30) NOT NULL UNIQUE,  \
              age   int \
            )'
    )


def insert_data():
    """ Insert data to the table """

    c.execute("INSERT INTO writer (name) VALUES ('Pearl Buck')")

    c.execute(" INSERT INTO writer VALUES \
        (NULL, 'Rabindranath Tagore', 80), \
        (NULL, 'Leo Tolstoy', 82)" \
    )

    c.execute(" INSERT INTO writer (age, name) VALUES \
        (30, 'Meher Krishna Patel')" \
    )

def commit_close():
    """ commit changes to database and close connection """

    conn.commit()
    c.close()
    conn.close()

def main():
    """ execute create and insert commands """

    create_table()
    insert_data()
    commit_close()  # required for save the changes


# standard boilerplate to call main function
if __name__ == '__main__':
    main()
  • Next, run the above file to save the data in the database,
$ python create_fill_database.py

2.3. Read data from table

Following code can be used to read data from the table,

# read_database.py

import mysql.connector as mc

conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL')
c = conn.cursor()

def read_data():
    c.execute('SELECT * FROM writer')
    writers = c.fetchall() # data is read in the form of list
    for writer in writers: # print individual item in the list
        print(writer)      # data at each row is saved as tuple


def main():
    read_data()

if __name__ == '__main__':
    main()
  • To see the output, execute the code,
$ python read_database.py
(1, 'Pearl Buck', None)
(2, 'Rabindranath Tagore', 80)
(3, 'Leo Tolstoy', 82)
(4, 'Meher Krishna Patel', 30)
  • In this way, we can get the data from the table and perform various operations on the data.
  • Also, we can use all those queries with python, as queries in the execute statements are same as queries in previous chapter.

2.4. Connection in try-except block

We can use following code to put the connection string in the try except block, so that we can get proper message for not connecting with the database,

# connect_try.py

import mysql.connector as mq
from mysql.connector import errorcode

try:
    conn = mq.connect(host='localhost', user='root', password='d', db='pythonSQL')
    print("Connected")
except mq.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print("Connection closed")
    conn.close()
$ python connect_try.py
Connected
Connection closed
  • Put the incorrect username or password in connect_try.py file and execute the file again,
$ python connect_try.py
Something is wrong with your user name or password
  • For wrong database name, we will get following error,
$ python connect_try.py
Database does not exist