Python Mysql

PyMySQL - Python module for Mysql

PyMySQL is a python driver for operating with MySQL in Python

Installing PyMySQL

PyMySQL can be installed using pip command

pip install pymysql 

or with pip3

pip3 install pymysql 

Creating Database

Login to mysql server and enter password when prompted

sudo mysql -u root -p

Create database

create database ncdb

Select database with use command

use ncdb

Use following command to create a table

CREATE TABLE customers( `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(50), `number` VARCHAR(20));

Sample command run

mysql> CREATE TABLE customers( `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    ->         `name` VARCHAR(50), `number` VARCHAR(20));
Query OK, 0 rows affected (0.18 sec)

Use show tables command to verify

mysql> show tables;
+----------------+
| Tables_in_ncdb |
+----------------+
| customers      |
+----------------+
1 row in set (0.00 sec)

CRUD (Create, Read, Update, Delete) Operations

Create Operation

import pymysql

import pymysql 

Connect to database using pymysql method

connection = pymysql.connect(host='localhost', user='root', password='password', db='nc-test')

Use cursor() method of connection to get instance of MySQLCursor

MySQLCursor class provides execute method that can execute operations such as SQL statements

Use cursor() to insert values

connection.cursor().execute('Insert into customers("name", "number") values("Umar Zhency", "9911")')

Sample code

import pymysql

connection = pymysql.connect(host='localhost', user='nc', password='password', db='ncdb')

name = 'Umar Zhency'
number = '9911'

try:
    with connection.cursor() as cursor:
        sql = "Insert into customers(name, number) VALUES (%s, %s)"
        try:
            cursor.execute(sql, ('Umar Zhency', '9911'))
            print("Insert statement executed")
        except Exception as e:
            print("Exception while executing insert statement:", e)
    connection.commit()
finally:
    connection.close()

Following output is produced on running above code

$ python insert.py
Insert statement executed

Check if table is updated

mysql> select * from customers;
+----+-------------+--------+
| id | name        | number |
+----+-------------+--------+
|  1 | Umar Zhency | 9911   |
+----+-------------+--------+
1 row in set (0.00 sec)

Read Operation

Following code can be used to perform read operation

import pymysql

connection = pymysql.connect(host='localhost', user='nc', password='password', db='ncdb')

try:
    with connection.cursor() as cursor:
        sql = "SELECT id, name, number from customers"
        try:
            cursor.execute(sql)
            result = cursor.fetchall()

            print("Id\t\tName\t\t\tNumber")
            print("━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━┫")
            for row in result:
                print(str(row[0]) + "\t\t" + row[1] + "\t\t\t" + row[2])

        except Exception as e:
            print("Exception while executing select operation:",e)

    connection.commit()
finally:
    connection.close()

Output

Id		Name			Number
━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━━┅┅┅┅┅━━━━┫
1		Umar Zhency			9911

Update Operation

Example code for updating values in MySQL Database

cat update.py
import pymysql

connection = pymysql.connect(host='localhost', user='nc', password='password', db='ncdb')

try:
    with connection.cursor() as cursor:
        sql = "UPDATE customers SET name=%s, number=%s WHERE id=%s"
        try:
            cursor.execute(sql, ('Zhency Uhmar', '1199', 1))
            print("Update statement executed")
        except Exception as e:
            print("Exception while executing update statement:", e)

    connection.commit()
finally:
    connection.close()

Run code

$ python update.py
Update statement executed

Check if table is updated

mysql> select * from customers;
+----+--------------+--------+
| id | name         | number |
+----+--------------+--------+
|  1 | Zhency Uhmar | 1199   |
+----+--------------+--------+
1 row in set (0.00 sec)

Delete Operation

Delete a record as

try:
    with connection.cursor() as cursor:
        sql = "DELETE FROM ncdb WHERE id = %s"
        try:
            cursor.execute(sql, (1))
            print("Delete statement executed")
        except:
            print("Exception while executing delete statement:", e)

    connection.commit()
finally:
    connection.close() 

Run code

$ python delete.py
Delete statement executed

Check if table is updated

mysql> select * from customers;
Empty set (0.00 sec)