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 StudentDTO.py, then connect our MySql database and then try all CRUD operation.

import mysql.connector
class StudentDTO():        
    def showDatabase(obj):
      myCon = mysql.connector.connect(
      host="localhost",
      user="duser",
      password="dpass")
      
      mycursor = myCon.cursor()
      mycursor.execute("SHOW DATABASES")
      for x in mycursor:
        #print(x)

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'),          
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor)

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(
      host="localhost",
      user="duser",
      password="dpass",
      database="wtrdb")

    def showStudents(self):

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

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])
      mycursor.close()

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):
    try:
        mycursor = self.myCon.cursor()     
          
        today=date.today()
        tdate = today.strftime("%Y/%d/%m")
        #date_time=datetime.datetime.now()
        print(tdate)
        sql_insert_query="insert into tbStudent (firstName, LastName, RegDate) values ('Bill','Gates','"+ tdate +"')";  
          
        mycursor.execute(sql_insert_query)
        self.mydb.commit();
        print("New Student Added")
       
    except mysql.connector.Error as error:
        print("Failed to insert query into tbStudent table {}".format(error))
    finally:
        mycursor.close()

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()
config.read('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

Online Python Course | Python Interview Questions

Python MySQL Examples

Learn python programming with free python coding tutorials.