Python MySql Connection Tutorial

In this tutorial you will learn how to work with MySql database in python.

MySql database connection in Python Class

Following are the example we explain this post, which will clear the idea about how to work with mysql database from python code.

  • Connect MySQL in Python
  • Read from MySQL Table in Python
  • Insert into MySQL Table in Python
  • Update MySQL Table using Python
  • Delete data from MySQL Table using Python

Connect MySQL in Python

First you need to import mysql.connector in your python class.

In this tutorial you will learn how to work with MySql database in python, here we create a python class called, then connect our MySql database and then try all CRUD operation.

import mysql.connector
class StudentDTO():        
    def showDatabase(obj):
      myCon = mysql.connector.connect(
      mycursor = myCon.cursor()
      mycursor.execute("SHOW DATABASES")
      for x in mycursor:

At above example, I have not specified the database name, which means it will get all databases of that specified server, which has been granted permission to that username, we can then loop through the database collection

Note: If you are using python 3.7 and trying to connection mysql 8.* on windows, then you may need to use pymysql.connect the connection.

Now mysqlclient supports python 3.7 slight differntly. You can download mysqlclient

You need to install pymysql

import pymysql
myConnection = pymysql.connect(
    host=config.get('dbinfo', 'dbhost'),
    user=config.get('dbinfo', 'dbusername'),
    password=config.get('dbinfo', 'dbpassword'),
    database=config.get('dbinfo', 'database'),          

Now let's try something like real-time software development requirement, in example we create functionalities to fetch, add, update and delete student details from our database.

MySql Python CRUD Operation Example

after fetching data from MySql database, we can print the whole row object, also can print the each column value

from School.Student import Student
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from datetime import date

class StudentDTO():        

    def __init__(self):
        self.myCon = mysql.connector.connect(

    def showStudents(self):

      mycursor = self.myCon.cursor()
      mycursor.execute("SELECT firstName, LastName, RegDate FROM tbStudent")
      myRecordset = mycursor.fetchone()
      for r in myRecordset:

Notice, in above code we have created the connection object in constructor, so we don’t need to write the same code for each function, we just use the same self.myCon connection object.

Fetch data from MySql in python

Let's fetch all students list from database and display on screen, notice while displaying on screen, we can display in two different ways, either display the complete row as object or display each column value of that row.

  def showStudents(self): 
      mycursor = self.myCon.cursor()
      mycursor.execute("SELECT firstName, LastName, RegDate FROM tbStudent")
      myRecordset = mycursor.fetchall()
      for r in myRecordset:
        print(r[0], r[1])

To display each column value of current row, use print(r[0], r[1])

to print the complete row as object you can use print(r)

Insert data into MySql database from python

Now let’s insert some student record into MySql database from python code.

You should pay attention to following points

  • Class reference you need to add, and namespaces
  • how to add date in python query
  • try, catch, error handling in python
def AddStudent(self):
        mycursor = self.myCon.cursor()     
        tdate = today.strftime("%Y/%d/%m")
        sql_insert_query="insert into tbStudent (firstName, LastName, RegDate) values ('Bill','Gates','"+ tdate +"')";  
        print("New Student Added")
    except mysql.connector.Error as error:
        print("Failed to insert query into tbStudent table {}".format(error))
Read INI file with Python ConfigParser

Now, probably you have noticed that in above connection object all values has been hardcoded, but in real-time application development you may need to keep all database credentials in one secure place then read from there, that way it will be easy to change database credential related information when you deploy in different environment.

So you need to retrieve the database credential information from some ini file, you may to use ConfigParser class like code below.

from configparser import ConfigParser
config = ConfigParser()'mypy.ini')
self.myCon = mysql.connector.connect(
host=config.get('dbinfo', 'dbhost'),
user=config.get('dbinfo', 'dbusername'),
      password=config.get('dbinfo', 'dbpassword'),
      database=config.get('dbinfo', 'database'))

You may also need to learn how to read database configuration values from ini file in python code

Python MySQL Examples
Learn python programming with free python coding tutorials.
Other Popular Tutorials
Python with MySQL
Python programming examples | Join Python Course