SQL (Structured Query Language) is used to interact with databases. Python, combined with SQL, allows you to manage, analyze, and manipulate data efficiently. This training will teach you how to connect Python with databases, execute queries, and handle data.<\/p>\n\n\n\n
sqlite3<\/code> (built-in)<\/li>\n\n\n\n- For MySQL:
pip install mysql-connector-python<\/code><\/li>\n\n\n\n- For PostgreSQL:
pip install psycopg2<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n- Import necessary libraries in Python:<\/li>\n<\/ol>\n\n\n\n
import sqlite3 # For SQLite
import mysql.connector # For MySQL
import psycopg2 # For PostgreSQL<\/pre>\n\n\n\nConnecting to a Database<\/h2>\n\n\n\n\n- SQLite Example<\/strong><\/li>\n<\/ul>\n\n\n\n
conn = sqlite3.connect('example.db')
cursor = conn.cursor()<\/pre>\n\n\n\n\n- MySQL Example<\/strong><\/li>\n<\/ul>\n\n\n\n
conn = mysql.connector.connect(
host=\"localhost\",
user=\"yourusername\",
password=\"yourpassword\",
database=\"yourdatabase\"
)
cursor = conn.cursor()<\/pre>\n\n\n\n
\n- PostgreSQL Example<\/strong><\/li>\n<\/ul>\n\n\n\n
conn = psycopg2.connect(
host=\"localhost\",
user=\"yourusername\",
password=\"yourpassword\",
database=\"yourdatabase\"
)
cursor = conn.cursor()<\/pre>\n\n\n\nExecuting SQL Queries<\/h2>\n\n\n\n\n- Create Table<\/strong><\/li>\n<\/ul>\n\n\n\n
cursor.execute(\"\"\"
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
)
\"\"\")<\/pre>\n\n\n\n
\n- Insert Data<\/strong><\/li>\n<\/ul>\n\n\n\n
cursor.execute(\"INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20)\")
conn.commit()<\/pre>\n\n\n\n
\n- Select Data<\/strong><\/li>\n<\/ul>\n\n\n\n
cursor.execute(\"SELECT * FROM students\")
rows = cursor.fetchall()
for row in rows:
print(row)<\/pre>\n\n\n\n
\n- Update Data<\/strong><\/li>\n<\/ul>\n\n\n\n
cursor.execute(\"UPDATE students SET age = 21 WHERE id = 1\")
conn.commit()<\/pre>\n\n\n\n
\n- Delete Data<\/strong><\/li>\n<\/ul>\n\n\n\n
cursor.execute(\"DELETE FROM students WHERE id = 1\")
conn.commit()<\/pre>\n\n\n\nUsing Parameters Safely<\/h2>\n\n\n\n
Use placeholders to prevent SQL injection:<\/p>\n\n\n\n
cursor.execute(\"SELECT * FROM students WHERE age = %s\", (21,))
rows = cursor.fetchall()<\/pre>\n\n\n\nClosing Connection<\/h2>\n\n\n\n
Always close the cursor and connection:<\/p>\n\n\n\n
cursor.close()
conn.close()<\/pre>\n\n\n\nBest Practices<\/h2>\n\n\n\n\n- Use parameterized queries to prevent SQL injection<\/li>\n\n\n\n
- Commit changes only when necessary<\/li>\n\n\n\n
- Handle exceptions with try-except blocks<\/li>\n\n\n\n
- Use context managers (
with<\/code> statement) for automatic closing<\/li>\n<\/ul>\n\n\n\nSummary<\/h2>\n\n\n\n\n- Python can connect to multiple types of databases.<\/li>\n\n\n\n
- You can execute all types of SQL queries using Python.<\/li>\n\n\n\n
- Using SQL with Python makes data management and analysis more efficient.<\/li>\n<\/ul>\n\n\n