Free Web Development Online Tutorials, Learn to Code
Python with MySql Tutorial, Learn How to connect MySql from Python Code

Python MySql Connection Tutorial

MySql database connection in Python Class

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

  • 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)

MySql Python CRUD Operation Example

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.

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

Comment
Name
Email
Website
Subscribe
 
Python MySQL Examples

Python with MySQL
Python programming examples | Join Python Course