SQL Transactions in a Flask CRUD Application


In this blog, we will guide you through SQL transactions in a Flask application and help you configure transaction modes with DbVisualizer.

Tools used in the tutorial
Tool Description Link

When administering a database, DBAs are responsible for a wide variety of tasks. When performing them, they face all kinds of problems related to their database instances regarding performance, availability, and security. Those problems and changes within our database infrastructure are tracked thanks to transactions.

In this tutorial, I will discuss how to execute SQL transactions in a Flask application to achieve CRUD (Create, Read, Update, Delete) functionalities. To visualize our application’s database tables, I will be using DbVisualizer. DbVisualizer is a feature-rich, user-friendly multi-database solution for developers and database administrators that offers a single powerful interface across several operating systems. We will cover how to configure transaction operations using DbVisualizer.


To follow this tutorial, you will need the following:

Setting up the Flask Application

In this section, we will talk about building the UI of a contact manager application and its backend CRUD functions. Let’s start by building the user interface of our Flask application.

Building the User Interface

The UI of this application will allow us to showcase CRUD functionalities in our Flask application. Create a new folder to house your Flask project. In this folder, create another folder named “templates.” In the templates folder, create a new HTML file named “index.html” and copy the HTML code below into it.

1 <center> 2 <h1>Contact Manager</h1>  3 <h2>Add Contact</h2>  4 <form method = "POST"> 5     <p>name <input type = "text" name = "name" /></p> 6     <p>phone <input type = "integer" name = "phone" /></p> 7     <p><input type = "submit" value="Submit" /></p> 8     {% with messages = get_flashed_messages() %} 9     {% if messages %} 10       {% for message in messages %} 11         <h3 style="color:red;">{{ message }}</h3> 12       {% endfor %} 13   {% endif %} 14 {% endwith %} 15 </form> 16   17 <h2>Contact List</h2> 18   19     {% for contact in contacts %} 20     <ul key={{}}> 21      <li> 22       <h3>{{}} </h3> 23      <form method="POST"> 24        <input type="hidden" name="delete" value="True"> 25        <input type="hidden" name="item" value="{{}}"> 26     </li> 27    <li> 28      {{}} 29     </li> 30     <button type="submit"> x </button> 31 </form> 32 </ul> 33 {% endfor %} 34 </center>

In the code above, we created a list element to display all contacts in the database. We also created two forms that send data using the POST method. One collects data from users in input fields to create a contact, and the other collects the name of the contact to be deleted.

Building the Backend

Great! Our UI is now ready.

Let’s write the Flask backend that will receive data from it. Before we jump straight to the code, we will need to install a few libraries by running the commands below in your terminal.

1 pip install flask 2 pip install mysql.connector 3 pip install requests

Also, we will need to create a MySQL database for our application using phpMyAdmin - The query command; CREATE DATABASE [IF NOT EXISTS] db_name should do (replace db_name with your database name). You can also achieve it using DbVisualizer by creating a connection to your database server, as seen in this guide. After establishing a connection, navigate to the “Database” tab, right-click on “Databases” in the connections dropdown, and select “Create databases,” as shown in the image below:

Navigating the database connections dropdown.
Navigating the database connections dropdown

Finally, enter a name for your database and click on “Execute,” as shown in the image below:

Executing the database creation
Executing the database creation

Now, with the libraries and database creation out of the way, let’s get to writing the backend for our application.

Create a python application in your Flask project named “” and paste the python code below:

1 from flask import Flask,render_template, request, flash 2 import mysql.connector 3 import requests 4 from requests import ConnectionError 5   6 app = Flask(__name__) 7 app.config["SECRET_KEY"] = "APP_SECRET_KEY" 8   9 #Database config 10 app.config['MYSQL_HOST'] = 'localhost' 11 app.config['MYSQL_USER'] = 'root' 12 app.config['MYSQL_PASSWORD'] = '' 13 app.config['MYSQL_DB'] = 'ContactManager' 14   15 #Creating a connection cursor 16 db = mysql.connector.connect(user=app.config['MYSQL_USER'], database=app.config['MYSQL_DB']) 17 cursor = db.cursor(dictionary=True) 18   19 try: 20     #query to create a table 21     query="CREATE TABLE contacts (name VARCHAR(255), phone VARCHAR(255))" 22     #executing the query 23     cursor.execute(query) 24     #Saving the Actions performed on the DB 25     db.commit() 26 except: 27     pass

In the code above, we created a database connection to our SQL database called “ContactManager” and provided its credentials. Next, we created a table called “contacts” and provided a name and phone number column for it by executing the SQL query. We also made sure to catch the query using a try-except to avoid duplicate contact in the database.

Create a python application in your Flask project named “” and paste the python codNext, we need to create a function and URL to handle requests to the “index.html” page we created earlier. Copy the code below and paste it into the file:e below:

1 2 @app.route('/', methods = ['POST', 'GET']) 3 def form(): 4     db = mysql.connector.connect(user=app.config['MYSQL_USER'], database=app.config['MYSQL_DB']) 5     cursor = db.cursor(dictionary=True) 6     if request.method == 'POST': 7         name = request.form.get('name') 8         phone = request.form.get('phone') 9         delete = request.form.get('delete') 10         item = str(request.form.get('item')) 11         if delete =="True": 12             query= "DELETE FROM contacts WHERE name ='"+item+"'" 13             cursor.execute(query) 14             requests.get('') 15   16     else: 17         query= "SELECT * FROM contacts WHERE name ='"+name+"'" 18         cursor.execute(query) 19         contact = cursor.fetchone() 20         if contact: 21             query = "UPDATE contacts SET phone='"+phone+"' WHERE name='"+name+"'" 22             cursor.execute(query) 23         else: 24         cursor.execute("INSERT INTO contacts VALUES(%s,%s)",(name, phone)) 25     cursor.execute( 26         "SELECT * FROM contacts") 27     contacts = cursor.fetchall() 28     db.commit() 29     return render_template('index.html', contacts=contacts) 30   31 #Closing the cursor 32 cursor.close() 33   34 if __name__ == "__main__": 35

In the code above, we created a function called ”form” and linked it to the root URL for our application allowing it to receive both POST and GET requests using the @app.route() method. Next, we focused on implementing the CRUD functionalities:

For the create functionality, we achieved it by collecting the data received from the create a contact form on the Html page. We used this to execute an SQL INSERT query by passing it as the values required in the `contacts` table.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

The delete functionality was built by receiving a delete request from the Html page, then collecting the data for the item to be deleted in the POST request, and finally using it in the WHERE parameters when executing the SQL DELETE query.

The Update functionality was quite straightforward. We achieved it by checking if the item received from the create a contact form in the Html page already exists by executing the SQL SELECT query using the item name as an identifier in the WHERE clause. If the item existed already, we ran an SQL UPDATE query on the item setting the contact phone to the new value provided.

Finally, for the read functionality, we achieved everything by running a SQL SELECT query to retrieve all contacts in our database, then passed all the data to our `index.html` template.

After that, we can run the application using the following command:

1 python

With all these functionalities in place, you should have an application like the one in the GIF below:

Testing the Flask application’s CRUD functionalities.
Testing the Flask application’s CRUD functionalities

Transactions in the Flask Application

This section will cover adding SQL transactions to the queries in our Python CRUD functions and testing the different transaction commands. The transaction commands are:

    The transactional command `COMMIT` is used to save all database changes made earlier by a transaction. This command saves all of the modifications made since the last commit or rollback. You can commit with either raw SQL queries or the `.commit()` method of your database instance. You can find an example in our `` file just before the return statement, where we committed all changes that took place when we ran the function.
    The transaction command `ROLLBACK` is used to erase or undo all previously made database modifications. All modifications made since the last commit or rollback command are undone with this command. You can also use a `.rollback()` method of the db instance to call it in python as opposed to running raw SQL queries. Suppose we had to ping an online endpoint each time we wanted to delete a contact from our database. If that’s the case, we need to catch connection errors to this endpoint due to network issues or unavailability of the endpoint service and rollback database changes that occurred during the error. See the implementation below:
1 if delete =="True": 2     try: 3       query= "DELETE FROM contacts WHERE name ='"+item+"'" 4       cursor.execute(query) 5       requests.get('') 6       query_message = "success" 7     except ConnectionError: 8       db.rollback() 9       query_message = "There was a network error and changes have been rolled back !" 10       flash(query_message)

In the gif below, we provide you with the result of the `ROLLBACK` command:

Testing the ROLLBACK implementation in the Flask Application.
Testing the ROLLBACK implementation
    The `SAVEPOINT` command establishes a point in the transaction at which all previous modifications are stored. A `SAVEPOINT` command can be issued solely for modifications made since the last save point. This command is useful when you wish to undo modifications and go back in time to a certain transaction. A good use case for our database is when we don’t want to commit any permanent changes to our database in case of an error and easily roll back to the initial point. If the process is without error, we can finally commit the previously made changes to our database. You can find the python implementation below:
1 try: 2     cursor.execute("BEGIN") 3     cursor.execute("INSERT INTO contacts VALUES(%s,%s)",(name, phone)) 4     query = "UPDATE contacts SET phone='",+",0000",+",' WHERE name='",+name+",'" 5     cursor.execute(query) 6     cursor.execute("SAVEPOINT SP1") 7     query = "UPDATE contacts SET phone='"+"4000"+"' WHERE name='"+name+"'" 8     cursor.execute(query) 9     cursor.execute("SAVEPOINT SP2") 10     query = "UPDATE contacts SET phone='"+"5000"+"' WHERE name='"+name+"'" 11     cursor.execute(query) 12     cursor.execute("SAVEPOINT SP3") 13     requests.get('') 14     db.commit() 15   16 except ConnectionError: 17     cursor.execute("ROLLBACK TO SP1") 18     db.commit() 19     query_message = "There was a network error and changes have been rolled back to SP1!" 20 flash(query_message)

In the code above when a user enters a contact, the phone number is updated to different values for each savepoint until it is finally committed permanently. However, if there is a connection error, the value is rolled back to a savepoint of our choice. In this case, it’s the first savepoint. The result of the code looks like this:

Testing the SAVEPOINT implementation.
Testing the SAVEPOINT implementation
    To erase a previously generated SAVEPOINT, use the RELEASE SAVEPOINT command. Once a SAVEPOINT has been released, we can no longer use the ROLLBACK command to reverse transactions that have happened since the last SAVEPOINT.

Setting Transaction Modes In DbVisualizer

In this section, we will learn about different transaction modes in SQL and how to set them using DbVisualizer.


MySQL's auto-commit feature is enabled by default. When the auto-commit mode is enabled, each SQL statement is evaluated as a transaction, and the outcome determines whether the statement is committed or rolled back. Successful statements are committed, whereas failing statements are immediately rolled back. Run the SQL command to manually disable auto-commit mode:

1 SET @@autocommit := 0;

If you use DbVisualizer, keep in mind that on the transactions page, you will be able to toggle the auto-commit settings and save it by clicking on apply, alternatively, you can also use DbVisualizer to automatically set the autocommit mode by following this guide:

Setting the autocommit mode in DbVisualizer.
Setting the autocommit mode in DbVisualizer


This blog has shown you how to develop a Flask application and implement transactions in it - transactions will always help you to manage errors that could arise when database operations are running. Feel free to reach out to me on LinkedIn if you have any questions - other than that, learn about the plethora of features available in DbVisualizer from the documentation over here, and you should be good to go.

Dbvis download link img
About the author
Ochuko Onojakpor
Ochuko Onojakpor

Ochuko is a full-stack Python/React software developer and freelance Technical Writer. He spends his free time contributing to open source and tutoring students on programming in collaboration with Google DSC.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

DELETE Queries – Advanced CRUD explanation part 4

author Lukas Vileikis tags CRUD DELETE 6 MINS 2023-06-08

UPDATE Queries - Advanced CRUD explanation part 3

author Lukas Vileikis tags CRUD UPDATE 7 MINS 2023-06-08

SELECT Queries - Advanced CRUD explanation part 2

author Lukas Vileikis tags CRUD SELECT 6 MINS 2023-06-07

INSERT Queries - Advanced CRUD explanation part 1

author Lukas Vileikis tags CRUD INSERT 4 MINS 2023-06-07

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04

Inner Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 10 MINS 2023-11-30

Leveraging Snowflake's Time Travel

author Ochuko Onojakpor tags SNOWFLAKE TIME TRAVEL 11 MINS 2023-11-28

MariaDB vs MySQL: The Ultimate Comparison

author Antonello Zanini tags MARIADB MySQL 7 MINS 2023-11-23

Change Data Capture: A Comprehensive Guide

author Ochuko Onojakpor tags DATA CAPTURE 14 MINS 2023-11-21

SQL vs NoSQL Databases: Which is Better?

author Ochuko Onojakpor tags NOSQL SQL 16 MINS 2023-11-20

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗