Executing SQL queries is an essential task when working with databases in Python. SQL (Structured Query Language) is a standardized language used to communicate with relational database management systems (RDBMS) such as MySQL, Oracle, or PostgreSQL. Python provides several libraries and modules that enable you to connect to and interact with databases using SQL.
In this article, we will explore how to execute SQL queries using Python and the MySQL Connector/Python library. We will cover the following topics:
- Connecting to a database
- Executing a SELECT query
- Executing an INSERT query
- Executing an UPDATE query
- Executing a DELETE query
- Closing a connection to a database
Connecting to a Database
Before you can execute any SQL queries, you need to establish a connection to a database. To do this, you will need to install and import the MySQL Connector/Python library. You can install the library using pip:
pip install mysql-connector-python
Once the library is installed, you can import it in your Python code using the following statement:
import mysql.connector
Next, you need to create a “Connection” object and connect to the database. The “connect” method of the “mysql.connector” module takes a set of keyword arguments that specify the connection details, such as the hostname, username, password, and database name. Here is an example of how to connect to a database:
cnx = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="database_name"
)
In the above example, the “host” argument specifies the hostname of the database server, the “user” and “password” arguments specify the login credentials, and the “database” argument specifies the name of the database to connect to.
Executing a SELECT query
Once you have established a connection to a database, you can execute a SELECT query to retrieve data from a table. To execute a SELECT query, you need to create a “Cursor” object using the “cursor” method of the “Connection” object, and then use the “execute” method of the “Cursor” object to execute the query. The “execute” method returns a result set, which you can iterate over to process the rows of data.
Here is an example of how to execute a SELECT query and process the result set:
cursor = cnx.cursor()
query = "SELECT * FROM table_name"
cursor.execute(query)
for row in cursor:
print(row)
In the above example, the “cursor” method of the “Connection” object is called to create a “Cursor” object, an SQL SELECT query is defined, and the “execute” method of the “Cursor” object is used to execute the query. The result set is then iterated over using a “for” loop, and the rows of data are printed to the console.
Executing an INSERT query
To insert data into a table using Python and MySQL Connector/Python, you can use the “execute” method of the “Cursor” object and pass the INSERT query as a parameter, along with any values to be inserted. You will also need to call the “commit” method of the “Connection” object to save the changes to the database.
Here is an example of how to execute an INSERT query:
cursor = cnx.cursor()
query = "INSERT INTO table_name (column1, column2) VALUES (%s, %s)"
values = ("value1", "value2")
cursor.execute(query, values)
cnx.commit()
In the above example, the “cursor” method of the “Connection” object is called to create a “Cursor” object, an SQL INSERT query is defined, and the “execute” method of the “Cursor” object is used to execute the query, passing the values to be inserted as a tuple. The “commit” method of the “Connection” object is then called to save the changes to the database.
Executing an UPDATE query
To update data in a table using Python and MySQL Connector/Python, you can use the “execute” method of the “Cursor” object and pass the UPDATE query as a parameter, along with any values to be updated. You will also need to call the “commit” method of the “Connection” object to save the changes to the database.
Here is an example of how to execute an UPDATE query:
cursor = cnx.cursor()
query = "UPDATE table_name SET column1 = %s WHERE column2 = %s"
values = ("new_value", "value2")
cursor.execute(query, values)
cnx.commit()
In the above example, the “cursor” method of the “Connection” object is called to create a “Cursor” object, an SQL UPDATE query is defined, and the “execute” method of the “Cursor” object is used to execute the query, passing the values to be updated as a tuple. The “commit” method of the “Connection” object is then called to save the changes to the database.
Executing a DELETE query
To delete data from a table using Python and MySQL Connector/Python, you can use the “execute” method of the “Cursor” object and pass the DELETE query as a parameter. You will also need to call the “commit” method of the “Connection” object to save the changes to the database.
Here is an example of how to execute a DELETE query:
cursor = cnx.cursor()
query = "DELETE FROM table_name WHERE column1 = %s"
value = "value1"
cursor.execute(query, (value,))
cnx.commit()
In the above example, the “cursor” method of the “Connection” object is called to create a “Cursor” object, an SQL DELETE query is defined, and the “execute” method of the “Cursor” object is used to execute the query, passing the value to be used in the WHERE clause as a tuple. The “commit” method of the “Connection” object is then called to save the changes to the database.
Closing a connection to a database
It is important to close the connection to the database when you are finished performing operations, as this will release any resources that were allocated to the connection, such as memory or file handles. To close the connection, you can call the “close” method of the “Connection” object.
Here is an example of how to close a connection to a database:
cnx.close()
In the above example, the “close” method of the “Connection” object is called to close the connection to the database.
Conclusion
In this article, we have learned how to connect to a MySQL database using Python and MySQL Connector/Python, and how to perform basic CRUD (create, read, update, delete) operations using SQL queries. We have also learned how to close a connection to a database when we are finished with it.
To learn more about using Python and MySQL Connector/Python to interact with MySQL databases, you can refer to the MySQL Connector/Python documentation.
I hope you found this article helpful. Thank you for reading!
Exercises
Here are some exercises with solutions to help you practice what you just learned:
Write a Python program that connects to a MySQL database and inserts a new row into a table. The table should have three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer). The program should ask the user to enter a name and age, and then insert these values into the table.
import mysql.connector
cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")
cursor = cnx.cursor()
name = input("Enter name: ")
age = input("Enter age: ")
query = "INSERT INTO table_name (name, age) VALUES (%s, %s)"
values = (name, age)
cursor.execute(query, values)
cnx.commit()
print("Inserted successfully!")
cnx.close()
Write a Python program that connects to a MySQL database and retrieves all rows from a table. The table should have three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer). The program should print the name and age of each row in the table.
import mysql.connector
cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")
cursor = cnx.cursor()
query = "SELECT * FROM table_name"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row[1], row[2])
cnx.close()
Write a Python program that connects to a MySQL database and updates a specific row in a table. The table should have three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer). The program should ask the user to enter an id and a new name, and then update the name of the row with the corresponding id.
import mysql.connector
cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")
cursor = cnx.cursor()
id = input("Enter id: ")
new_name = input("Enter new name: ")
query = "UPDATE table_name SET name = %s WHERE id = %s"
values = (new_name, id)
cursor.execute(query, values)
cnx.commit()
print("Updated successfully!")
cnx.close()
Write a Python program that connects to a MySQL database and deletes a specific row from a table. The table should have three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer). The program should ask the user to enter an id, and then delete the row with the corresponding id.
import mysql.connector
cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")
cursor = cnx.cursor()
id = input("Enter id: ")
query = "DELETE FROM table_name WHERE id = %s"
values = (id,)
cursor.execute(query, values)
cnx.commit()
print("Deleted successfully!")
cnx.close()
Write a Python program that connects to a MySQL database and displays the names and ages of all rows in a table, sorted by age in descending order. The table should have three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer).
import mysql.connector
cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")
cursor = cnx.cursor()
query = "SELECT * FROM table_name ORDER BY age DESC"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row[1], row[2])
cnx.close()