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.
- 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.
- Install Python, here you can find the step by step guide to install python on windows
- 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 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 :(")
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
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)
('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
In this tutorial, we have successfully learned how to connect MySQL DB to Python and executed a SQL query successfully.
Happy Learning 🙂