Executing SQL Querries

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:

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()

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()

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()

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()

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()