Executing SQL queries using Python
SQL (Structured Query Language) in the Python programming language is used to exchange data and information by communicating directly with relational database management systems. The Python programming language can execute SQL query statements through database modules such as the sqlite3 library module for SQLite or the Connector for MySQL database software.
Using Python with SQL software, users can implement CRUD database operations.
- C — Use it to create new SQL database data
- R — Read data and information from an existing SQL database
- U — Update data and information from an existing SQL database
- D — Delete data and information from an existing SQL database
Process databases using basic SQL software.
Using the Python programming language, Python users can execute SQL query command statements.
Connect to a SQL Database
↓
Create a Cursor
↓
Write the Desired SQL Query
↓
Execute the SQL Query Statement
↓
Commit the Necessary Query Changes
↓
Get the Desired Results
↓
Close the SQL Database Connection
Using SQLite Database Software with Python Programming.
The Python programming language includes or is built-in the sqlite3 database software module, making SQLite an easy database software choice for learning SQL database basics.
Connecting to a SQLite Database in Python.
import sqlite3
connection = sqlite3.connect(“employee.db”)
cursor = connection.cursor()
Here in this explanation.
- sqlite3 provides support for the SQLite database software in Python.
- The connect() function creates or opens a new database in SQLite.
- cursor() This function creates an object used to execute SQL query statements in Python.
Creating a table in SQL database software.
Here, Python users use the SQL CREATE TABLE command or statement, which creates a new database table structure.
cursor.execute(“””
CREATE TABLE IF NOT EXISTS Employee (
Emp_id INTEGER PRIMARY KEY,
Emp_name TEXT,
Department TEXT,
Salary INTEGER
)
“””)
connection.commit()
This creates a database table named Employee in SQLite database software.
The Employee table mainly contains the following.
| # | Column | Description |
| 1. | Emp_id | Employee id |
| 2. | Emp_name | Employee name |
| 3. | Department | Employee department name |
| 4. | Salary | Employee salary |
Python SQL Database Table INSERT Query.
Python users use the INSERT query command or statement in an SQL database to add or insert data into a new table or a previously created Employee database table.
cursor.execute(
“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,
(104 “Lalita”, “Purchase”, 99000)
)
connection.commit()
Here, the ? question mark symbol parameter in an SQL database statement is a placeholder for inserting table data information.
Using parameters is a more secure method than creating an SQL database query by adding user input to an SQL database string.
SELECT query in an SQL database.
The SELECT command statement in an SQL database is used to extract data and information from an existing Employee database table.
cursor.execute(“SELECT * FROM Employee”)
rows = cursor.fetchall()
for row in rows:
print(row)
Possible output of the SELECT command statement in an SQL database.
(104 “Lalita”, “Purchase”, 99000)
fetchone()
This Python database function extracts one row of information from the current SQL database.
row = cursor.fetchone()
print(row)
fetchall()
This Python database function extracts all remaining database table rows from the current SQL database.
rows = cursor.fetchall()
SELECT statement with a user-defined custom condition.
In SQL database software, the WHERE clause statement can be used to extract table data record information based on specific logic.
cursor.execute(
“SELECT * FROM Employee WHERE Salary > ?”,
(99000,)
)
for row in cursor.fetchall():
print(row)
This command statement extracts and displays employee record information in the SQL database for employees whose monthly salary is greater than ₹99,000.
UPDATE SQL database query.
The UPDATE command statement in SQL database software helps modify or update records in an existing employee database table.
For example, updating or changing Bhavishi’s salary.
cursor.execute(
“UPDATE Employee SET Salary = ? WHERE Emp_name = ?”,
(110000, “Bhavishi”))
connection.commit()
Here, Bhavishi’s updated salary in the employee database table is now ₹110,000.
DELETE SQL database query.
The DELETE command statement in SQL database software is used to delete records or information from the existing Employee database table.
cursor.execute(
“DELETE FROM Employee WHERE Emp_name = ?”,
(“Lalita”,)
)
connection.commit()
This command deletes the employee record in the SQL database for the employee whose name is Lalita.
Important – Always use the correct WHERE clause when deleting specific database record information in the existing employee database table. Remember, if you don’t use the WHERE clause properly, it may delete other table rows.
Complete example of a SQLite database command statement.
Here is a complete Python program example that represents several SQL command query statements.
import sqlite3
# Connect to database
connection = sqlite3.connect(“employee.db”)
cursor = connection.cursor()
# Create a complete SQL database table.
cursor.execute(“””
CREATE TABLE IF NOT EXISTS Employee (
Emp_id INTEGER PRIMARY KEY,
Emp_name TEXT,
Department TEXT,
Salary INTEGER
)
“””)
# Insert new table record data into the SQL database table.
cursor.execute(
“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,
(105 “Disha”, “Education”, 89000)
)
cursor.execute(
“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,
(106 “Harry”, “Travelling”, 88000)
)
connection.commit()
# Read data from SQL database table.
cursor.execute(“SELECT * FROM Employee”)
for Employee in cursor.fetchall():
print(Employee)
# Update data in SQL database table.
cursor.execute(
“UPDATE Employee SET Salary = ? WHERE Emp_name = ?”,
(112000, “Harry”)
)
connection.commit()
# Delete data from the SQL database table.
cursor.execute(
“DELETE FROM Employee WHERE Emp_name = ?”,
(“Disha”,)
)
connection.commit()
# Close the connection to the SQL database table.
connection.close()
Running table queries in MySQL database software.
For MySQL database software connection connectivity in the Python programming language, Python users can use the MySQL Connector/Python statement.
Install MySQL database software like this.
pip install mysql-connector-python
Connect to the MySQL database software in Python.
import mysql.connector
connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
password=”your_password”,
database=”employee”
)
cursor = connection.cursor()
SQL command statements are similar in the Python programming language, but the %s symbol is defined as the database table parameter placeholder used by MySQL Connector/Python.
Example of MySQL Connector.
cursor.execute(
“SELECT * FROM Employee WHERE Salary > %s”,
(100000,)
)
for row in cursor.fetchall():
print(row)
Some common popular Apply SQL database table query statements.
The purpose of an SQL query for a database table.
- CREATE TABLE – This SQL query command statement creates a new database table.
- INSERT – This SQL query command statement adds record information from a new database table to an existing database table.
- SELECT – This SQL query command statement selects and displays record information from an existing database table.
- UPDATE – This SQL query command statement updates record information from an existing database table.
- DELETE – This SQL query command statement deletes record information from an existing database table.
- WHERE – This SQL query command statement filters record data from a single record with a particular where clause.
- ORDER BY – This SQL query command statement sorts the results of table data information in a particular sequence order, either in ascending or descending order.
- GROUP BY – This SQL query command statement groups table data records in a particular order.
- JOIN – This SQL query command statement joins or groups data information from one table with other data information.
For example, EMPLOYEE Sorting employee records in a table by salary.
cursor.execute(
“SELECT * FROM Employee ORDER BY Salary DESC”
)
for row in cursor.fetchall():
print(row)
Using the commit() and rollback() functions in SQL databases.
When a query or database information is modified or updated in a SQL database table, such as an INSERT, UPDATE, or DELETE, these modifications typically need to be committed.
connection.commit()
If something goes wrong with a command statement in an SQL database, that database transaction can be rolled back and reverted.
connection.rollback()
This is a simple database pattern.
try:
cursor.execute(
“UPDATE Employee SET Salary = ? WHERE Emp_id = ?”,
(89000, 105)
)
connection.commit()
except Exception:
connect.rollback()
This helps prevent a failed database operation from leaving unwanted changes to a command statement in an SQL database.
Using the with clause for SQLite databases.
The Python programming language’s SQLite connection can also be used as a context manager.
import sqlite3
with sqlite3.connect(“employee.db”) as connection:
cursor = connection.cursor()
cursor.execute(
“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,
(107 “Amit”, “House Keeping”, 78000)
)
This method provides Python users with an easy way to manage a transaction.
SQL Injection Concepts in Python.
One of the most important rules when running SQL command statements from the Python programming language is:
Do not create SQL queries by directly adding user input to them.
Avoid this type of syntax.
query = “SELECT * FROM Employee WHERE emp_name = ‘” + emp_name + “‘”
cursor.execute(query)
Instead, Python users should use a parameterized query.
cursor.execute(
“SELECT * FROM Employee WHERE emp_name = ?”,
(emp_name,)
)
Connector to MySQL database/Python.
cursor.execute(
“SELECT * FROM Employee WHERE name = %s”,
(emp_name,)
)
Queries with database parameters help protect applications from SQL injection.
Some Key Points of Python SQL Databases.
SQL query statements can be executed using the database library in the Python programming language.
Here are some basic SQL Database steps.
- First, connect Python to a database.
- Next, create a cursor.
- Now write the SQL database query statement.
- Run the database query using the execute() function.
- Make necessary changes to the database if needed.
- Get the results from the database table with a SELECT query.
- Finally, close the open database connection.
The basic Python database connection pattern is.
import sqlite3
connection = sqlite3.connect(“employee.db”)
cursor = connection.cursor()
cursor.execute(“SELECT * FROM employee”)
for row in cursor.fetchall():
print(row)
connection.close()
A key point to remember when executing SQL queries using Python.
The Python programming language handles the program logic, while the SQL database software used handles multiple database table operations on the connected database. Together, these help database applications store, retrieve, modify, and manage data efficiently.
