Here’s a guide on how to use common SQL operations (INSERT, SELECT, WHERE, ORDER BY, DELETE, DROP TABLE, UPDATE, LIMIT, and JOIN) in Python using SQL databases. I'll show you examples using MySQL, but these operations are similar across other SQL databases (PostgreSQL, SQLite, etc.).
Inserts data into a table.
import mysql.connector
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Insert data into the table
insert_query = "INSERT INTO employees (name, position, hire_date) VALUES (%s, %s, %s)"
cursor.execute(insert_query, ("Alice", "Developer", "2024-01-01"))
connection.commit()
print("Data inserted successfully.")
cursor.close()
connection.close()
Retrieves data from a table.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Select all data from the table
select_query = "SELECT * FROM employees"
cursor.execute(select_query)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connection.close()
Filters the results based on a condition.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Select data with a WHERE clause
select_query = "SELECT * FROM employees WHERE position = %s"
cursor.execute(select_query, ("Developer",))
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connection.close()
Sorts the results by a specified column.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Select data and order by the name column
select_query = "SELECT * FROM employees ORDER BY name ASC"
cursor.execute(select_query)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connection.close()
Removes records from a table.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Delete records where the position is 'Intern'
delete_query = "DELETE FROM employees WHERE position = %s"
cursor.execute(delete_query, ("Intern",))
connection.commit()
print("Records deleted successfully.")
cursor.close()
connection.close()
Deletes an entire table.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Drop the employees table
drop_table_query = "DROP TABLE employees"
cursor.execute(drop_table_query)
connection.commit()
print("Table dropped successfully.")
cursor.close()
connection.close()
Modifies existing records in a table.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Update the position of an employee
update_query = "UPDATE employees SET position = %s WHERE name = %s"
cursor.execute(update_query, ("Senior Developer", "Alice"))
connection.commit()
print("Record updated successfully.")
cursor.close()
connection.close()
Limits the number of records returned by a query.
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Select the first 5 records
select_query = "SELECT * FROM employees LIMIT 5"
cursor.execute(select_query)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connection.close()
Combines rows from two or more tables based on a related column between them.
Assume you have another table departments:
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(255)
);
connection = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = connection.cursor()
# Join employees and departments tables
join_query = """
SELECT employees.name, employees.position, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
"""
cursor.execute(join_query)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connection.close()