In this article, we will learn how to set up a Flask MySQL database connection. So let’s get started!!
Structured Query Language
SQL allows us to access and manipulate databases. In SQL, we can perform various tasks such as:
- Adding records to databases
- Creating tables
- Perform CRUD (Create, Read, Update, Delete) operations
SQL is the query language that the database systems use. To set-up databases, we require RDMS like MySQL, PostgreSQL etc.
Do check out our SQL tutorial on the JournalDev website to gain more knowledge about the query language.
More about MySQL Database Tables
Let us now look at a typical MySQL DB table:
The rows are called records and the columns are called fields.
Therefore, in the above table, we have six records and four fields. To interact with the Table elements, we use SQL statements.
Some of the SQL statements are:
- SELECT FROM – This statement SELECTs fields(all or a few) FROM a table.
- WHERE – This conditional statement is usually used with other statements. Using this, we can select specific records satisfying some given conditions.
- UPDATE – This statement updates a table
- EDIT – This statement edits a field of record/records
- DELETE – This statement deletes a record/records
Setting-up MySQL server for our Application
In this section, we will download and establish our MySQL server
1. Installing XAMPP on your Server
Now to use MySQL, we require a software tool to handle the administration of MySQL over the web.
In this tutorial, we will work with phpMyAdmin. If you are familiar with any other software; You can use that as well.
Xampp software provides the PHPMyAdmin web interface. You can download XAMPP from here.
Or directly go to Google and search for download Xampp. The first link itself will do the job !!
Download the right version for your operating system and architecture.
2. Start Apache and MySQL
Once XAMPP is installed and loaded, start the following two processes:
- Apache Webserver – to serve HTTP requests
- MySQL Server – for the database
Do note the default port for MySQL is 3306. Now in the browser, go to https://localhost.
This is the Host webpage for Xampp. Click on phpMyAdmin on the top right, to go to the php web interface.
- Create a new Database by clicking new in the left column.
- Keep a suitable Name for the DB. In my case, it is simply Flask
Go ahead and create a table in the DB. Enter the table name in the space given as shown in the picture and hit Go.
3. Installing Flask- MySQL library in our system
Flask uses flask_mysqldb connector to use MySQL. Run the following command to install the package:
pip install flask_mysqldb
Setting up a Flask MySQL Database Connection
Now we will connect and use MySQL to store data into our DB. If you’re not sure how to create a flask application, check out the flask introduction tutorial.
1. Connecting Flask Application with MySQL
The procedure we follow to connect Flask-MySQL is as follows:
from flask import Flask,render_template, request from flask_mysqldb import MySQL app = Flask(__name__) app.config['MYSQL_HOST'] = 'localhost' app.config['MYSQL_USER'] = 'root' app.config['MYSQL_PASSWORD'] = '' app.config['MYSQL_DB'] = 'flask' mysql = MySQL(app)
2. Setting up MySQL connection cursor
Just with the above set-up, we can’t interact with DB tables. For that, we need something called a cursor.
So Cursor provides a way for Flask to interact with the DB tables. It can scan through the DB data, execute different SQL queries, and as well as Delete table records.
The cursor is used in the following way:
mysql = MySQL(app) #Creating a connection cursor cursor = mysql.connection.cursor() #Executing SQL Statements cursor.execute(''' CREATE TABLE table_name(field1, field2...) ''') cursor.execute(''' INSERT INTO table_name VALUES(v1,v2...) ''') cursor.execute(''' DELETE FROM table_name WHERE condition ''') #Saving the Actions performed on the DB mysql.connection.commit() #Closing the cursor cursor.close()
Since MySQL is not an auto-commit DB, we need to commit manually, ie, save the changes/actions performed by the cursor execute on the DB .
3. Coding a Flask application
Now we will build a small Flask application that will store data submitted by the user in the MySQL DB Table. Consider the following Application Code:
from flask import Flask,render_template, request from flask_mysqldb import MySQL app = Flask(__name__) app.config['MYSQL_HOST'] = 'localhost' app.config['MYSQL_USER'] = 'root' app.config['MYSQL_PASSWORD'] = '' app.config['MYSQL_DB'] = 'flask' mysql = MySQL(app) @app.route('/form') def form(): return render_template('form.html') @app.route('/login', methods = ['POST', 'GET']) def login(): if request.method == 'GET': return "Login via the login Form" if request.method == 'POST': name = request.form['name'] age = request.form['age'] cursor = mysql.connection.cursor() cursor.execute(''' INSERT INTO info_table VALUES(%s,%s)''',(name,age)) mysql.connection.commit() cursor.close() return f"Done!!" app.run(host='localhost', port=5000)
When the user submits the data, it is added into the MySQL DB via the cursor.execute command. My table name is info_table.
The form.html will be:
<form action="/login" method = "POST"> <p>name <input type = "text" name = "name" /></p> <p>age <input type = "integer" name = "age" /></p> <p><input type = "submit" value = "Submit" /></p> </form>
4. Implementing the Code
Now fire up the server and go to “/form” (see Flask forms)
Enter the details and hit Submit
Now let’s check it in the phpMyAdmin web interface
That’s it, guys!! This was all about setting up Flask MySQL connections. In the next article, we will look into Flask-PostgreSQL.
See you in the next time 🙂