Python Connect MySql Using Pymysql

python mysql pymysql

Python Connect MySql Using Pymysql

In order to connect Python to a database you need a driver, which is a library used to Interact with the database. For MySQL database we will use module name pymysql

Pymysql installation
(pyenv) root@juniour:~# pip install pymysql
Collecting pymysql
Using cached
Requirement already satisfied: cryptography in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from pymysql) (2.3.1)
Requirement already satisfied: asn1crypto>=0.21.0 in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from cryptography->pymysql) (0.24.0)
Requirement already satisfied: cffi!=1.11.3,>=1.7 in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from cryptography->pymysql) (1.11.5)
Requirement already satisfied: six>=1.4.1 in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from cryptography->pymysql) (1.11.0)
Requirement already satisfied: idna>=2.1 in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from cryptography->pymysql) (2.7)
Requirement already satisfied: pycparser in /mnt/c/Users/Shubham/python_world/pyenv/lib/python3.5/site-packages (from cffi!=1.11.3,>=1.7->cryptography->pymysql) (2.18)
Installing collected packages: pymysql
Successfully installed pymysql-0.9.2

Open mysql, create database student and create a table named student_info under student database.

mysql> CREATE TABLE student_info (id INT AUTO_INCREMENT, name VARCHAR(100), age INT, gender  VARCHAR(10), PRIMARY KEY (id));
Query OK, 0 rows affected (0.23 sec)
mysql> desc student_info;
| Field  | Type         | Null | Key | Default | Extra          |
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100) | YES  |     | NULL    |                |
| age    | int(11)      | YES  |     | NULL    |                |
| gender | varchar(10)  | YES  |     | NULL    |                |
4 rows in set (0.00 sec)

Connect mysql using module pymysql
Here you have to make changes

self.connection = pymysql.connect(host='localhost', user='db_user', password='secret', db='student')

Change the following option as of your database:

  1. host
  2. user
  3. password
  4. db
import sys
import pymysql
import collections

class dbConnection:
    def __init__(self):
        print("Initilizing Database...")
        # Creating the connection to the mysql database
            self.connection = pymysql.connect(host='localhost', user='root', password='helloworld', db='student')
          except Exception as e:

    def insert(self, name, age, gender):

        """  This insert function will insert the value in to the table student_info"""
          sql = """INSERT INTO student_info(name, age, gender) VALUES('{}', '{}', '{}')""".format(name, age, gender)
            cur = self.connection.cursor()
          except Exception as e:

    def select(self):
        sql = "SELECT * FROM student_info"
            cur = self.connection.cursor()
            # cur.description will give you table column names
            headers = list(map(lambda x: x[0], cur.description))
            for value in cur.fetchall():
                print(collections.OrderedDict(zip(headers, value)))
        except Exception as e:

    def db_close(self):

def main():
    db = dbConnection()
    print("Inserting 2 row ...")
    db.insert('Alice', 15, 'male')
    db.insert('Marie', 13, 'female')
    print("Retrieving row from student_info ...")

if __name__ == '__main__':

Save the above code as and run below
(pyenv) juniour@juniour:$ python
Initilizing Database...
Inserting 2 row ...
Retrieving row from student_info ...
OrderedDict([('id', 1), ('name', 'Alice'), ('age', 15), ('gender', 'male')])
OrderedDict([('id', 2), ('name', 'Marie'), ('age', 13), ('gender', 'female')])

You can see I have inserted two row to the table student_info and retrieved the row again form the table student_info.

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *