How to connect Python with MySQL

In this article, we’ll look at how to connect Python to MySQL, one of the most used relational databases on the web.

Hey you  programmer, okay? Let’s learn more about Python!

To connect a Python program to MySQL we will need to have MySQL installed on our machine, I’ll skip this step because I imagine you should already have experience in installing the database server

First let’s install the mysql-connector extension, which will allow you to connect Python with MySQL:

python -m pip install mysql-connector

Once it is installed, you should import it into your software.

The next step is to use the connect method to establish the connection

Here’s how to do it:

import mysql.connector

conn = mysql.connector.connect(host="localhost", user="test", passwd="xxx", db="mydb")

print(conn)

Where host is the server’s ip, here we are using our machine, that is, localhost

And the user would be the MySQL database user that we created when installing it and therefore the passwd password for this user

We also have the db parameter, which serves to select the target database

When printing this variable that the connection was made, you should receive a mysql.connector object

If you get an error, it went wrong

In the terminal console it will explain which procedure went wrong, you must correct it to establish a connection

If you want to make a SELECT, for example, you must create a cursor, check it out:

cursor = conn.cursor()

cursor.execute("SELECT * FROM table")

result = cursor.fetchall()

for x in result:
 print(x)

These are the steps to retrieve database data and insert it into a variable

For more mysql.connector functions see the documentation

There you will find other instructions such as: create database, create table, UPDATE, DELETE, and so on.

Conclusion

In this article we learned  how to connect Python to MySQL

We use an extension called mysql.connector

We established a connection to our MySQL database via Python, and we made a SELECT query to demonstrate how to use the lib

Want to learn more about Python? Click here!