In this tutorial, we’ll see a step by step guide on how to connect MySQL Database with Python and will execute a query using Python.

Setup and Configurations

We need to install some of the tools before making a connection between MySQL Databases and Python.

  1. MySQL Server and workbench. (one endpoint) – To correctly set up MySQL server and workbench, Follow the steps provided in this tutorial. After setup, Open the workbench and it should look something like this.
MySQL DB with Python
  1. Install Python, here you can find the step by step guide to install python on windows
  2. MySQL-Python-Connector (Connection medium) – This acts as a connection medium between both MySQL Databases and Python. To install this, go to Windows PowerShell and run the following pip command.
pip install mysql-connector-python

Now our MySQL server is running and connector is installed on the machine, Next step is to use Python to connect to a MySQL Database.

1. Importing Connector

We have to import the connector that we have installed.

import mysql.connector as mysqlConnector

2. Connect to the MySQL Server Instance running

We will use the connect() method of the connector to connect to the instance. This method takes several arguments.

  • host – This is the name of the host on which server is running. Since we are running on localhost, we will use localhost as its value.
  • user – This is the name of the user while setting up the MySQL Server. In our case, we have used the name root.
  • password – Password that is set while setting up the MySQL Server. In our case, we have used the root as a password.
  • databases – It takes the name of the database that we wish to connect.
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')

To check whether the connection is successful or not we can use a simple if-else condition.

if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")

Output:

Connection Successful :)

3. Creating a Cursor

Now we have successfully connected to the MySQL Instance, Next step is to run SQL queries which will output a resultset. To traverse through the result set we need to create a cursor.

cur = conn.cursor()

Now we can use this cursor object to execute a SQL Query and traverse the resultset using the loops. Let us run a command to show the pre-defined databases in MySQL. In Python, we can use execute() function which takes a SQL Query as a string that we want to run.

cur.execute("SHOW DATABASES")
for row in cur:
    print(row)

Output:

('information_schema',)
('mysql',)
('performance_schema',)
('sys',)

4. Closing the connection

After completing the work, It is important to close the connection that we have made in the end using the close() method.

conn.close()

In this tutorial, we have successfully learned how to connect MySQL DB to Python and executed a SQL query successfully.

5. Resources

Happy Learning 🙂