DEV Community

Cover image for Connecting MySQL Database to Python Flask Application
Ayush Agarwal
Ayush Agarwal

Posted on • Updated on • Originally published at blogs.ayushdev.com

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
Enter fullscreen mode Exit fullscreen mode
  1. After the package install successfully, import it in your "app.py" file. To do that, use the below code snippet.
import mysql.connector
Enter fullscreen mode Exit fullscreen mode
  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')
Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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 '{}' """)
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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

cursor.execute()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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! ๐Ÿ™

Top comments (2)

Collapse
 
jelili profile image
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.

Collapse
 
ayushdev_24 profile image
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.