Python Connect MySql Using Pymysql

python mysql pymysql

Python Connect MySql Using Pymysql

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 https://files.pythonhosted.org/packages/a7/7d/682c4a7da195a678047c8f1c51bb7682aaedee1dca7547883c3993ca9282/PyMySQL-0.9.2-py2.py3-none-any.whl
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...")
          try:
        # Creating the connection to the mysql database
 
            self.connection = pymysql.connect(host='localhost', user='root', password='helloworld', db='student')
          except Exception as e:
            print(e)
            sys.exit(0)

    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)
          try:
            cur = self.connection.cursor()
            cur.execute(sql)
            self.connection.commit()
          except Exception as e:
            print(e)


    def select(self):
        sql = "SELECT * FROM student_info"
          try:
            cur = self.connection.cursor()
            cur.execute(sql)
            # 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:
            print(e)

    def db_close(self):
        self.connection.close()

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

if __name__ == '__main__':
    main()

Save the above code as mysqlconnect.py and run below
(pyenv) juniour@juniour:$ python mysqlconnect.py
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 *