Menu
python

Basic MySQL Operations

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

 

 

1. INSERT

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

 

 

2. SELECT

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

 

3. WHERE

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

 

4. ORDER BY

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

 

5. DELETE

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

 

6. DROP TABLE

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

 

7. UPDATE

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

 

8. LIMIT

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

 

9. JOIN

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

 

 

Inner Join Example:

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

 

python