A simple CRUD application using python and mysql

Tram Ho

Hello everyone! So I’ve continued to come back with a series of lessons about Python after a long time. 😃
In the previous article, I introduced you about python module and how to use it. Today, I will synthesize the knowledge that we have learned for a long time to create a simple CRUD (Create – Read – Update – Delete) application using python and MySQL database. 😃 .
And don’t let you wait any longer, let’s get started, let’s go!

Application detai

The application I chose to do the demo here is a simple student management application with functions such as viewing student list, adding new students, searching students, updating student information and deleting student.
And in order for you to not be confused when reading your code, I would like to briefly introduce the files included in my app:

  • constant.py : This is the file for me to set up my database configuration including information like host, username, password, database.
  • database.py : This file will contain the connection initialization function as well as functions that handle create, update, find, etc.
  • util.py : This file I will define some utility functions are used.
  • student.py : In this I will declare a Student class to facilitate the transformation of data when exporting to the terminal 😃
  • main.py : And here is what file I need to reintroduce

My directory structure will look like this:

And as mentioned, we will use the MySQL database to manage the data. So how to use MySQL in python here, let’s come to the next section.

How to use MySQL in Python

Install MySQL Connector

Python needs a MySQL driver to access MySQL, so the first thing we need to do is install it. Here I use the “MySQL Connector” driver and fortunately we only need 1 terminal command to install it. Open a terminal and type this command:

Note: sometimes you need to use sudo permission to install if you get a permission denied error.

Initiate connection to the database

After successfully installing the driver, we just need to import mysql.connector into xxx.py file and then use the following syntax to initiate the connection:

OK, so we have initialized the connection to the database, now let’s fight again.

Application building

First, you need to create a students table with the following fields: id, code, name, class.

Create a Student class

Create a Student class to facilitate transforming data obtained from the database.

In this class I have define a getStudentInfor function to print the data of each student to get to the screen.

Create the util file

This util file I define functions like transformData to convert data from Tuples to Student to make it easier to display (you must import the Student class to use it), enterData to import data for updating or creating. The function raw_input(...).split(',') works to split the string we enter into separate values ​​and returns a List of required data. Finally printListStudents to print the list of existing students to the screen.

Create database file

Here you should note the following:

  • To execute the sql statement, we need to use the execute(sqlStatement) function via the cursor object that was originally obtained from the connection.
  • Use the fetchall() function to retrieve all of the records obtained from the execution of the last sql statement.
  • The commit() function is used to request changes to the table, if we do not use this function, the changes made to the table will not be saved.
  • The rowcount property of the cursor object can be used to see the number of records affected by the sql statement.

Run the application

After we are done, we will write the main.py file and run it.
Here are some results I have tested:
List:

Search:

Create:

summary

So we have put together a simple CRUD application that combines python and MySQL with a relatively successful result.
I hope through this article will help you a bit about python as well as how to use mysql in python. Better understanding how to create a class or module in python. And more specifically, it contributes to consolidating knowledge about python for you to follow my series of articles about python. Thank you all.
This is the entire source code of this application, you can clone for reference if you do not understand offline: https://github.com/phanvantan73/python_mysql

Share the news now

Source : Viblo