Connecting MySQL Database to Python Flask Application

Hello everyone! I hope you all are doing well.

I am working on a full-stack web application project. For doing the backend of the project, I decided to use Flask for CRUD requests, and for the database of the project,MySQL.

In summary, the tech stack for the backend is:

• Flask
• Python
• MySQL

So after deciding upon the stack, the next challenge was to talk to the SQL database from front end using Flask. For this, we needed to connect the database to the front end.

After researching and going through lots of tutorials and docs, I finally managed to connect the database to my project.

In this blog, I will summarize how I achieve the connection so that the next time someone needs it, he doesn't need to wander over different resources. Below are the steps on how to do it.

1. Install MySQL on your local system or use Remote MySQL.

2. Create a database. If on local, you would have automatically got a local instance with username root, and the password is the one you used while installing.
   If you used Remote MySQL, the database name and password would have been generated by them, which you can use in phpmyadmin use your database.

3. Now, in the database Create a Schema, let's suppose we create one named "test."

4. Next, in your application, we need to install a MySQL connector, a flask package, and eventually help us connect to the database. To install it, run the following command:
   

pip install mysql-connector-python

1. After the package install successfully, import it in your "app.py" file. To do that, use the below code snippet.

import mysql.connector

1. Now, the next part is connecting to the database. We have done all the prerequisites.

2. To connect to the database, named "test," we do the following.
   

conn=mysql.connector.connect(host="127.0.0.1",
user="root",
password="yourpassword",
database="test",
auth_plugin='mysql_native_password')

"conn" is a variable that points to the connection. Remember, if you used Remote MySQL, use the username and password generated by it in the code.
The above code is for the local MySQL workbench.

1. Till now, we have achieved the connection. But we need a pointer to point to that connection to perform operations on the database from our python file.

To that, we use "cursor" in the following way,

cursor=conn.cursor()

That's it now. Our connection to the MySQL database from our python app.py is successful. We can now perform all types of SQL operations. Let us take an example and see how we do it.

Inside any of your CRUD routes, use "cursor.execute()", inside the parenthesis, we can insert SQL queries. Suppose we have a table named "users" inside our database test.

We want to insert email and password inside that table. We do it using the following method,

cursor.execute("""SELECT * FROM `users` WHERE `EMAIL` LIKE '{}' AND  `PASSWORD` LIKE '{}' """)

The above code searches the table users in the test database. If we wish to see if the search query matched or not, we can do that too.

Assume we want to store the search results in a variable named "user."

user=cursor.fetchall()

So the above was about searching. Similarly, we can insert into the database to using "INSERT" inside:

cursor.execute()

If we make changes to a database like creating an entry, deleting an entry, basically performing CRUD operation on the database. We need to commit those changes to DB after the operation. For that, we use the following command.

conn.commit()

This was all about connecting and using MySQL Database with your Python Flask applications. I hope the blog will help you.

Feel free to reach out if you have any doubts.

Lastly, Your support keeps me going, and I give my 100 percent to these blogs! If you've found value, consider fueling the blog with a coffee ☕️ donation at the below link.

Buy me a COFFEE!

Thank you! 🙏

Comments

[Jelili Ayorinde]

Teaching and tutoring has a method if you want your audience to learn, not introduce or apply right teaching methodology makes learning difficult for your intended audience. Explaining your exercise in steps brings clarity to your explanations and arrest your audience's attention to follow your tutorial to the end. Here in your tutorial, you only import MySQL connector, we don't know whether MySQL itself need to be imported, just like when you are using sqlite3 in python, you will need to import sqlite3 itself before any connection would be established, and subsequently querying database actions could start. I am not seeing that fully in your lecture.

[Ayush Agarwal]

Thank You for mentioning it Jelili. Yes, I get your point and admit it's a mistake from my end. I will try to imrpove from next time and hopefully will bring better articles.

Thanks for taking out time and reading it.