How To Use MySQL Database With Flask?

A framework is a powerful code library that simplifies and accelerates web application development by offering reusable code for common operations. Within the Python ecosystem, there are various frameworks available, such as Flask, Tornado, Pyramid, and Django, each catering to different needs.

Flask, known for its simplicity and elegance, is a lightweight web application framework. Unlike some other frameworks, Flask allows developers the freedom to choose the tools and libraries that best suit their requirements. Additionally, Flask is well-suited for single-page web applications, utilizing side tabs to seamlessly present different content sections.

This blog is tailored to anyone aspiring to create dynamic websites using the Python Flask MySQL combination. By the end of this article, you will gain a moderate level of expertise in developing websites with Flask, harnessing the power of Python and Flask MySQL connection.

Prerequisites

• Knowledge of Python.
• Requirement of MySQL Workbench and the Flask Framework.
• Usage of Visual Studio Code, Spyder, or any other code editor for application development.

What is Python Flask?

Guido Von Rossum created Python, an open-source, high-level, general-purpose programming language, in 1991. It’s widely used in fields like Machine Learning, Artificial Intelligence, Server-Side Scripting, Data Analytics, and Deep Learning. Python enables you to work more quickly and efficiently when integrating systems. It has a large and active global community, with many tech behemoths such as Google, Facebook, and Netflix relying on it.

Flask is a Python-based microweb framework. It is classified as a microframework because it does not necessitate the use of any specific tools or libraries. It lacks a database abstraction layer, form validation, or any other components that rely on pre-existing third-party libraries to perform common functions.

Flask, on the other hand, supports extensions that can add application features as if they were built into Flask itself. Extensions are available for object-relational mappers, form validation, upload handling, various open authentication technologies, and several framework-related tools.

Flask has grown in popularity among Pythonistas. It has the second most stars on GitHub among Python Web Development frameworks as of October 2020, trailing only Django, and was voted the most popular web framework in the Python Developers Survey 2018.

Related: Developing RESTful Flask APIs with Python: A Comprehensive Guide 101

Key Features of Python

Python has several advantages that make it a popular choice in today’s IT (Information Technology) market. Python’s key features include the following:

• Easy Learning: Python is a beginner-friendly language, so most people with a basic understanding of programming can quickly adapt to the syntax and start coding. Python’s English Script, simple Phrase Structure, and Code Readability make it easy to learn and understand for both programmers and non-programmers.
• Scalability: Unlike other programming languages such as Java and R, Python can easily handle large amounts of data. Furthermore, it can assist you with problems that other programming languages cannot solve.
• Increased Community Support: Python is a free and open-source programming language that comes with a slew of tools and extensive documentation. Furthermore, it has one of the most active programming and data science communities.
• Durable and Portable: Python is a powerful and adaptable programming language. This means that Python code written on one computer can be easily transferred to another and run without issue.

What is MySQL?

MySQL is a popular Relational Database Management System (RDBMS) for managing Relational Databases. MySQL defines, updates, and queries the database using Structured Query Language (SQL). SQL is the most popular language for accessing and managing records in a database. MySQL, which is supported by Oracle, is an open-source and free database software licensed under the GNU General Public License.

When compared to Microsoft SQL Server and Oracle Database, MySQL is faster, more scalable, and an easier-to-use Database Management System. It is built on the Client-Server model, which means that the database is typically run on a server and the data is accessed via the network by clients and workstations.

MySQL’s ease of use gives it a clear advantage over competitors such as Oracle databases and Microsoft SQL Server. Furthermore, when working with this DBMS platform, you can use any programming language of your choice for free. 

Another advantage of using MySQL is its compatibility with Linux, Unix, Windows, and other operating systems. Furthermore, MySQL allows you to choose its mode of implementation, allowing you to use it either online or after installing it on your local system.

The server responds to the clients’ Graphical User Interface (GUI) requests with the desired output. MySQL works with a variety of operating systems and languages such as PHP, PERL, JAVA, C++, C, and others.

Key Features of MySQL

• It supports a variety of tools, including SQL Server Management Studio, Database Tuning Advisor, SQL Server Profiler, and BI tools.
• Online assistance and live assistance are available.
• Database engine that supports SQL and other high-level languages such as Java.
• When developing SQL-enabled applications, the Database engine comes in handy for backend programming.
• Intelligent Query Processing Enhancements:- This is Artificial Intelligence (AI) technology that improves query optimizer output, making SQL Server more efficient

Setting Up MySQL Server

In this section, we will download and establish our MySQL server to be able to establish Python Flask and MySQL integration.

• XAMPP Installation on a Server
• Fire up Apache and MySQL
• Installing the Flask MySQL library

XAMPP Installation on a Server

• To use MySQL, we now need a software tool to handle MySQL administration over the web.
• You can use phpMyAdmin. You can also use other software if you are familiar with it.
• The PHPMyAdmin web interface is provided by the Xampp software. You can get XAMPP from this page.
• Alternatively, you can go directly to Google and search for download Xampp. The first link alone will suffice!!
• Download the version that is appropriate for your operating system and architecture.

Fire up Apache and MySQL

Start the following two processes once XAMPP has been installed and loaded:

• The Apache Webserver is used to serve HTTP requests.
• MySQL Server – the database server

• Keep in mind that the default MySQL port is 3306. Go to https://localhost in your browser now.

• This is the Xampp Host webpage. To access the PHP web interface, click on phpMyAdmin in the upper right corner.

• Here,
• By clicking new in the left column, you can create a new database.
• Maintain a suitable name for the database.

• Create a table in the database. Enter the table name in the space provided, as shown in the image, and press Go.

Installing the Flask MySQL library

When it comes to integrating a MySQL database into a Flask web application, the Flask MySQLdb connector is a great choice. To use this connector, you’ll need to install the package using the following command:

pip install flask_mysqldb

This will give you access to all the tools you need to connect your Flask application to a MySQL database. With Flask MySQLdb, you can easily perform database operations and manipulate data using SQL queries – making it an essential tool for any Flask developer.

Setting Up Flask MySQL Database

Here are the 4 steps to get started with Flask MySQL Database Connection:

• Flask MySQL Step 1: Connecting a Flask Application to a MySQL Database
• Flask MySQL Step 2: Configuring the MySQL Connection Cursor
• Flask MySQL Step 3: Programming a Flask Application
• Flask MySQL Step 4: Putting the Code into Action

Flask MySQL Step 1: Connecting a Flask Application to a MySQL Database

The following is the procedure we use to connect Flask MySQL

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)

Flask MySQL Step 2: Configuring the MySQL Connection Cursor

We can’t interact with DB tables with the setup described above. We’ll need something called a cursor for that.

Cursor thus provides a means for Flask to interact with the database tables. It can scan the database for data, execute SQL queries, and delete table records.

The cursor is employed in the following manner:

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()

Because MySQL is not an auto-commit DB, we must manually commit, i.e. save the changes/actions performed by the cursor execute on the DB.

Flask MySQL Step 3: Programming a Flask application

Now we’ll create a small Flask application that will store user-submitted data in the MySQL DB Table. Take a look at 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, the cursor inserts it into the MySQL DB.

command to be executed Info table is the name of my table.

The form.html will be as follows:

<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>

Flask MySQL Step 4: Putting the Code into Action

• Now start the server and navigate to “/form”
• Enter the information and press the Submit button.
• Let’s take a look at it in the phpMyAdmin web interface now.

This concludes the setting up of Python Flask MySQL database connection.

Conclusion

You learned how to set up Python Flask MySQL to integrate a MySQL database with your Python application in this blog. You also saw some unique Python Flask and MySQL features.

You learned some programming along the way that will come in handy when it comes to establishing a connection, creating tables, and inserting and updating records in a database application.

Visit our Website to Explore Hevo

You may want to go one step further and analyze the Webhooks data. This will require you to transfer data from the Webhooks account to a Data Warehouse using various complex ETL processes. Hevo Data will automate your data transfer process, allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

This platform allows you to transfer data from 150+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of setting up Python Flask MySQL in the comments section below!