Learning Management Platform for Written Tutorial Series.

Python 3 Database - Create a MySQL powered CRUD application

Python 3 Database - Create a MySQL powered CRUD application

Python MySQL Database

In this lesson, we will look at how to interact with a MysQL database from a python application. By the time that you are done with this lesson, you should be able to create basic applications that can add data, retrieve, update or delete records from the database.

This tutorial is written for Python 3

Topics to be covered

We will cover the following topics in this section.

  • MySQL Python Connector
  • Security considerations when writing database queries
  • SELECT query
  • INSERT query
  • UPDATE query
  • DELETE query

MySQL Python Connector

The connector provides drivers that enable communication between MySQL database server and our Python application. You can download the drivers from the MySQL official website or you can install using a pip to install the connector.

In this example, we will use pip to install MySQL. I am using MacOS for this lesson so if the commands don't exactly work as written here then I suggest you hit google with a search that is specific to your operating system.

Open the terminal and run the following command.

pip install mysql-connector-python

HERE,

  • The above command install Python connector on your machine. pip3 tells MacOS to use version 3 of Python.

That is it for installing the connector. Let's now create a new folder and call it PyMySql and open it in an IDE or text-editor

Our application will have two (2) files namely users_crud.py and users.py

  • users_crud.py this file will contain the database logic for creating, reading, updating and deleting records.
  • users.py - this file will contain the code for a simple console based interactive interface. The following images shows how our interface will look like.

Python MySQL database

As you can see from the above example, our users will be able to select a number between 1 to 5 that corresponds to a specific action and then the application will respond appropriately.

Before we start working on our application, let's first create our database and users table in MyQSL using the SQL scripts below

CREATE DATABASE python_DB;

USE python_DB;

CREATE TABLE `python_DB`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`));

SELECT query

This query is used to retrieve all the records from our table and display them in the console. An example of this is shown in the image below

Python MySQL SELECT query

Add the following code to users_crud.py

from mysql.connector import connect

connection = connect(host='localhost',database='python_db',user='root',password='xxx',auth_plugin='mysql_native_password')

print('Attempting to connect to the database...')

if connection.is_connected():
    print("Connection to the database established successfully")

def display_all_users():
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users;")
    records = cursor.fetchall()

    heading = f"Total registered users in the system: {cursor.rowcount}"
    print(heading)
    print ("-" * len(heading))
    for row in records:
        print(f"ID: {row[0]}")
        print(f"Username: {row[1]}")
        print(f"Email: {row[2]}")
        print(f"Created At: {row[4]}\n")
    
    cursor.close()
    connection.close()
    print("MySQL connection is closed")

HERE,

  • from mysql.connector import connect imports the connect function from the module mysql.connector
  • connection = connect(...;) creates a variable named connection that all the functions in our module will use. Note - make sure the connection parameters match the ones on your development machine.
  • print('Attempting to connect to the database...') prints a simple message in the terminal that let's the user know that the application is attempting to connect to the database.
  • if connection.is_connected(): checks to see if the value of the is_connected() method is True then prints a message in the terminal to let the user know that the connection was successful.
  • def display_all_users(): defines a function that runs a simple SELECT query against our database then uses a for loop to print out the results in the terminal.
  • cursor = connection.cursor() creates a cursor object from our connection variable.
  • cursor.execute("SELECT * FROM users;") executes the SELECT query.
  • records = cursor.fetchall() fetches all the returned rows into a variable called records.

We have a feature in our application that allows the user to search for records in the database. The method definition is as follows

def search_users(query):
    sql_stmt = f"SELECT * FROM users WHERE username LIKE '%{query}%' OR email LIKE '%{query}%'"

    cursor = connection.cursor()
    cursor.execute(sql_stmt)
    records = cursor.fetchall()

    heading = f"search for '{query}' returned: {cursor.rowcount} rows"
    print(heading)
    print ("-" * len(heading))
    for row in records:
        print(f"ID: {row[0]}")
        print(f"Username: {row[1]}")
        print(f"Email: {row[2]}")
        print(f"Created At: {row[4]}\n")
    
    cursor.close()
    connection.close()
    print("MySQL connection is closed")

HERE,

  • sql_stmt = f"SELECT * FROM users WHERE username LIKE '%{query}%' OR email LIKE '%{query}%'" the SELECT statement uses wildcards to search for usernames or email addresses that contain what has been submitted in the query parameter.

That is all we need to display the records in the terminal. Use the comments section to as if you need more clarifications on the source code.

INSERT query

In this section, we will add a function that will be called when we want to create a new user in the database.

Add the following code to users_crud.py

def add_new_user(user):
    sql_stmt = """INSERT INTO users (username,email,password) VALUES (%s,%s,%s)"""
    
    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,user)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully inserted into the database using prepared stament")

HERE,

  • def add_new_user(user): defines a function add_new_user(user) that accepts a tuple parameter of user
  • sql_stmt = """INSERT INTO users (username,email,password) VALUES (%s,%s,%s)""" creates a prepared statement. A prepared statement is a SQL statement that is created using placeholders in place of actual values that are submitted by the user. This is done for security reasons. It protects agains't SQL Injection attacks. The placeholders are all specified using %s.
  • cursor = connection.cursor(prepared=True) creates a curso object from our connection variable and we pass in a keyword argument that is set to True for the parameter prepared.
  • cursor.execute(sql_stmt,user) calls the execute method of the cursor object that accepts two parameters. The first parameter is the SQL statement while the second parameter represents the actual values that will be substituted for the placeholders in our statement.
  • connection.commit() calls the commit method of the connection object. This method runs the SQL query against the database.
  • cursor.close(); connection.close() closes the cursor and connection objects to free-up resources.

UPDATE query

Let's now add the method that will update the user record in the database.

Add the following code

def update_user(user):
    sql_stmt = "UPDATE users SET updated_at = NOW(), username = %s,email = %s,password = %s WHERE id = %s"

    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,user)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully updated in the database using prepared stament")

HERE,

  • sql_stmt = "UPDATE users SET updated_at = NOW(), username = %s,email = %s,password = %s WHERE id = %s" just like we did with the INSERT statement, we are using prepared statements with placeholders then we submit the query for execution.

DELETE query

Let's now look at how we can remove records from the database

Add the following method.

def delete_user(id):
    sql_stmt = "DELETE FROM users WHERE id = %s"
    param = (id)
    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,param)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully deleted from the database using prepared stament")

HERE,

  • sql_stmt = "DELETE FROM users WHERE id = %s" uses a prepared statement that accepts the parameter of id

Application Source Code

The complete source code for users_crud.py is as follows.

from mysql.connector import connect

connection = connect(host='localhost',database='python_db',user='root',password='xxx',auth_plugin='mysql_native_password')

print('Attempting to connect to the database...')
if connection.is_connected():
    print("Connection to the database established successfully")

def display_all_users():
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users;")
    records = cursor.fetchall()

    heading = f"Total registered users in the system: {cursor.rowcount}"
    print(heading)
    print ("-" * len(heading))
    for row in records:
        print(f"ID: {row[0]}")
        print(f"Username: {row[1]}")
        print(f"Email: {row[2]}")
        print(f"Created At: {row[4]}\n")
    
    cursor.close()
    connection.close()
    print("MySQL connection is closed")

def add_new_user(user):
    sql_stmt = """INSERT INTO users (username,email,password) VALUES (%s,%s,%s)"""
    
    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,user)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully inserted into the database using prepared stament")


def search_users(query):
    sql_stmt = f"SELECT * FROM users WHERE username LIKE '%{query}%' OR email LIKE '%{query}%'"

    cursor = connection.cursor()
    cursor.execute(sql_stmt)
    records = cursor.fetchall()

    heading = f"search for '{query}' returned: {cursor.rowcount} rows"
    print(heading)
    print ("-" * len(heading))
    for row in records:
        print(f"ID: {row[0]}")
        print(f"Username: {row[1]}")
        print(f"Email: {row[2]}")
        print(f"Created At: {row[4]}\n")
    
    cursor.close()
    connection.close()
    print("MySQL connection is closed")


def update_user(user):
    sql_stmt = "UPDATE users SET updated_at = NOW(), username = %s,email = %s,password = %s WHERE id = %s"

    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,user)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully updated in the database using prepared stament")

def delete_user(id):
    sql_stmt = "DELETE FROM users WHERE id = %s"
    param = (id)
    cursor = connection.cursor(prepared=True)
    cursor.execute(sql_stmt,param)
    connection.commit()
    cursor.close()
    connection.close()

    print("Record successfully deleted from the database using prepared stament")

The complete source for users.py is as follows

from users_crud import display_all_users, add_new_user,search_users,update_user,delete_user

title = "Users management - Create, Read, Update & Delete"

print('-' * len(title))
print(title)
print('-' * len(title))
print('')
print('Select the action that you need to perform')
print('')
print('1. Display all users')
print('2. Add new user')
print('3. Search users')
print('4. Update user')
print('5. Delete user\n')
choice = input("Enter your selection number: ")
print('')

if choice == "1":
    display_all_users()

elif choice == "2":
    username = input("Username: ")
    email = input("Email: ")
    password = input("Password: ")

    user = (username,email,password)

    add_new_user(user)

elif choice == "3":
    query = input("Enter your search term: ")

    search_users(query)
elif choice == "4":
    user_id = input("Enter the user id: ")
    username = input("Username: ")
    email = input("Email: ")
    password = input("Password: ")

    user = (username,email,password,user_id)

    update_user(user)
elif choice == "5":
    user_id = input("Enter the user id: ")

    delete_user(user_id)
else:
    print("Invalid Selection. Please select a number that is between 1 and 5")

Summary

Let's wrap up on what we have learnt in this lesson.

  • MySQL Python Connector - is a package that allows us to interact with MySQL databases from our application.
  • MySQL Python Connector can be downloaded directly from the website for MySQL or it can be installed using pip. It is mandatory to install it before working with the mysql.* package.
  • Prepared statement are more secure when interacting with the database and you should always use them whenever you accept input from the users and it needs to be written to the database
  • MySQL Python Connector supports SELECT, INSERT, DELETE & UPDATE statements which can be parameterized.

What next?

If you enjoyed this lesson then show us your appreciation by creating a free accounts on our site. As always we appreciate your comments down below.


...