Python is a high-level programming language used in various domains such as artificial intelligence, web development, data science, etc. It can also be used in handling database management systems (DBMS) such as MySQL by using performing SQL queries to store, retrieve and manipulate data from tables in the database.
Below we can see a break down of all the steps that are required to perform queries on a MySQL database using Python.
For Python to work with MySQL, we must install a library named MySQL Connector. This will allow us to communicate with our MySQL server.
Attached is the pip command we can use to install the MySQL connector for Python.
pip install mysql-connector-python
To import the connector in our Python project, we can use the following import statement:
import mysql.connector
The next step is to create a connection to the MySQL server. To do so, we can use the .connect() method of the mysql.connector library.
Below is a code snippet showing how to connect with our MySQL server.
connection = mysql.connector.connect(host="localhost",user="database user",password="users password",database="name of the database")
The .connect() function takes in four arguments. Below, we can see what these arguments mean.
host: Here, we provide the hostname or the IP address where our MySQL database is hosted. We can set it to localhost we are running MySQL on our local machine.
user: This refers to the user account name on the MySQL server. Here we provide our MySQL username for authentication.
password: Here, we give the password against our username. If we do not have a password, we can leave it empty.
database: Over here, we provide the name of the database to which we want to connect.
To run an SQL query, we can use the cursor object. Below are the steps we must follow to run an SQL query using the cursor object.
First, we must create an cursor object. We can see the code sample attached below that shows how to create a cursor object named mycursor.
mycursor = connection.cursor()
To run a query, we call the function .execute() for the cursor object. In which we pass the query string as an argument.
mycursor.execute("insertQueryHere")
Now that we have executed an SQL query to display its results, we can use the .fetchall() function of the cursor object.
Below are the steps we must follow.
First, we must call the fetchall() function to return all rows from the last executed statement. Below we can see the syntax of how to use the function.
myresult = mycursor.fetchall()
We can iterate over the object and print each row to display the query result. Below, we can see a for loop that displays all returned rows.
for x in myresult:print(x)
Below, we can see a Python application that establishes a connection with a MySQL server running on the localhost of a system and performs the SQL query Select * from users on the hosted database named test_db that contains a table of users with their names and id.
# import the connector
import mysql.connector
# establish a connection to MySQL database
connection = mysql.connector.connect(
host="localhost",
user="educative",
password="secret",
database="test_db"
)
# create a cusrsor object to perform quries
mycursor = connection.cursor()
# query for a table named users
mycursor.execute("SELECT * from users")
# fetch all retrieved rows
myresult = mycursor.fetchall()
# display all rows onto the console
for x in myresult:
print(x)When we run the application, we see that the table contents are displayed on the terminal. The table only contains one user entry with the name Educative and the id 1 as seen in the output window.
In conclusion, combining MySQL with Python can help us create powerful data-driven applications. With the smooth integration between Python and MySQL, developers can perform data manipulation easily on various Python projects ranging from small-scale web applications to large-scale business solutions.
Free Resources