intro
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:
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={{contact.name}}>
21
<li>
22
<h3>{{contact.name}} </h3>
23
<form method="POST">
24
<input type="hidden" name="delete" value="True">
25
<input type="hidden" name="item" value="{{contact.name}}">
26
</li>
27
<li>
28
{{contact.phone}}
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:
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:
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 “app.py” 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 app.py 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('http://www.google.com')
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
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.
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 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:
1
if delete =="True":
2
try:
3
query= "DELETE FROM contacts WHERE name ='"+item+"'"
4
cursor.execute(query)
5
requests.get('http://www.google.com')
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:
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('http://www.google.com')
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:
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:
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:
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.
