In this tutorial you will learn how to work with MySql database in python.
Following are the example we explain this post, which will clear the idea about how to work with mysql database from python code.
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.
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.
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)
Now let’s insert some student record into MySql database from python code.
You should pay attention to following points
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()
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