DEV Community

loading...
Cover image for Connecting MySQL Database to Python Flask Application

Connecting MySQL Database to Python Flask Application

_ayushagarwal11 profile image Ayush Agarwal Originally published at blogs.ayushdev.com ・3 min read

Hello everyone! I hope you all are doing well.

I am working on a full-stack web application project as a part of my internship. For doing the backend of the project, we decided to use Flask for CRUD requests, and for the database of the project, we chose 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 our SQL database from our front end using Flask. For this, we needed to connect our 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 we 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.

Discussion (0)

pic
Editor guide