🐍 Python Tutorial: Working with Databases
Python offers excellent support for interacting with databases. The most common database you'll start with is SQLite, but Python can also connect to PostgreSQL, MySQL, and many others using libraries.
1. Using SQLite with sqlite3
SQLite is a lightweight, file-based database that's built into Python via the sqlite3 module.
import sqlite3
# Connect to a database (or create one)
conn = sqlite3.connect('example.db')
c = conn.cursor()
# Create a table
c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)''')
# Insert data
c.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))
# Commit and close
conn.commit()
conn.close()2. Fetching Data
After inserting data, you can retrieve it using SELECT queries:
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("SELECT * FROM users")
rows = c.fetchall()
for row in rows:
print(row)
conn.close()3. Preventing SQL Injection
Always use parameterized queries instead of string concatenation to avoid SQL injection attacks.
username = "Bob"
c.execute("SELECT * FROM users WHERE name = ?", (username,))4. Other Database Libraries
- psycopg2 — PostgreSQL driver
- mysql-connector-python — MySQL connector
- SQLAlchemy — ORM for working with multiple databases