In this blog, we will guide you through SQL transactions in a Flask application and help you configure transaction modes with DbVisualizer.
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.
Prerequisites
To follow this tutorial, you will need the following:
- Flask
- Python 3.*+
- DbVisualizer
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.
<center>
<h1>Contact Manager</h1>
<h2>Add Contact</h2>
<form method = "POST">
<p>name <input type = "text" name = "name" /></p>
<p>phone <input type = "integer" name = "phone" /></p>
<p><input type = "submit" value = "Submit" /></p>
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<h3 style="color:red;">{{ message }}</h3>
{% endfor %}
{% endif %}
{% endwith %}
</form>
<h2>Contact List</h2>
{% for contact in contacts %}
<ul key={{contact.name}}>
<li>
<h3>{{contact.name}} </h3>
<form method="POST">
<input type="hidden" name="delete" value="True">
<input type="hidden" name="item" value="{{contact.name}}">
</li>
<li>
{{contact.phone}}
</li>
<button type="submit"> x </button>
</form>
</ul>
{% endfor %}
</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.
pip install flask
pip install mysql.connector
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:
Finally, enter a name for your database and click on “Execute,” as shown in the image below:
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 “app.py” and paste the python code below:
from flask import Flask,render_template, request, flash
import mysql.connector
import requests
from requests import ConnectionError
app = Flask(__name__)
app.config["SECRET_KEY"] = "APP_SECRET_KEY"
#Database config
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'ContactManager'
#Creating a connection cursor
db = mysql.connector.connect(user=app.config['MYSQL_USER'], database=app.config['MYSQL_DB'])
cursor = db.cursor(dictionary=True)
try:
query="CREATE TABLE contacts (name VARCHAR(255), phone VARCHAR(255))" #query to create a table
cursor.execute(query) #executing the query
#Saving the Actions performed on the DB
db.commit()
except:
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.
Next, 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 app.py file:
@app.route('/', methods = ['POST', 'GET'])
def form():
db = mysql.connector.connect(user=app.config['MYSQL_USER'], database=app.config['MYSQL_DB'])
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
name = request.form.get('name')
phone = request.form.get('phone')
delete = request.form.get('delete')
item = str(request.form.get('item'))
if delete =="True":
query= "DELETE FROM contacts WHERE name ='"+item+"'"
cursor.execute(query)
requests.get('http://www.google.com')
else:
query= "SELECT * FROM contacts WHERE name ='"+name+"'"
cursor.execute(query)
contact = cursor.fetchone()
if contact:
query = "UPDATE contacts SET phone='"+phone+"' WHERE name='"+name+"'"
cursor.execute(query)
else:
cursor.execute("INSERT INTO contacts VALUES(%s,%s)",(name, phone))
cursor.execute(
"SELECT * FROM contacts")
contacts = cursor.fetchall()
db.commit()
return render_template('index.html', contacts=contacts)
#Closing the cursor
cursor.close()
if __name__ == "__main__":
app.run(debug=True)
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.
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:
python app.py
With all these functionalities in place, you should have an application like the one in the GIF below:
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:
COMMIT:
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 app.py file just before the return statement, where we committed all changes that took place when we ran the function.
ROLLBACK:
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:
if delete =="True":
try:
query= "DELETE FROM contacts WHERE name ='"+item+"'"
cursor.execute(query)
requests.get('[http://www.google.com](http://www.google.com)')
query\_message = "success"
except ConnectionError:
db.rollback()
query\_message = "There was a network error and changes have been rolled back !"
flash(query\_message)
In the gif below, we provide you with the result of the ROLLBACK command:
SAVEPOINT:
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:
try:
cursor.execute("BEGIN")
cursor.execute("INSERT INTO contacts VALUES(%s,%s)",(name, phone))
query = "UPDATE contacts SET phone='"+"0000"+"' WHERE name='"+name+"'"
cursor.execute(query)
cursor.execute("SAVEPOINT SP1")
query = "UPDATE contacts SET phone='"+"4000"+"' WHERE name='"+name+"'"
cursor.execute(query)
cursor.execute("SAVEPOINT SP2")
query = "UPDATE contacts SET phone='"+"5000"+"' WHERE name='"+name+"'"
cursor.execute(query)
cursor.execute("SAVEPOINT SP3")
requests.get('[http://www.google.com](http://www.google.com)')
db.commit()
except ConnectionError:
cursor.execute("ROLLBACK TO SP1")
db.commit()
query\_message = "There was a network error and changes have been rolled back to SP1!"
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:
RELEASE SAVEPOINT
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.
The AUTOCOMMIT Mode
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:
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:
Conclusion
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.
About the author
Ochuko Onojakpor 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.
Top comments (0)