24Introduction to Databases with SQLite
What Is SQLite?
SQLite is a lightweight, self-contained database engine that stores data in a single file. It doesn’t require a separate server process and is perfect for small applications, prototypes, and learning SQL.
Setting Up SQLite in Python
Python comes with a built-in module called `sqlite3`. You don’t need to install anything extra.
import sqlite3
# Connect to a database (or create it)
conn = sqlite3.connect('my_database.db')
# Create a cursor object to interact with the database
cursor = conn.cursor()Creating a Table
Tables store data in rows and columns. You define tables using SQL statements.
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
email TEXT UNIQUE
)
''')
conn.commit()Inserting Data
Add new rows to the table using `INSERT INTO` statements.
cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)',
('Alice', 30, 'alice@example.com'))
conn.commit()Querying Data
Use `SELECT` statements to fetch data from your tables.
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)Updating and Deleting Data
Modify existing rows or remove them using `UPDATE` and `DELETE`.
# Update Alice's age
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (31, 'Alice'))
conn.commit()
# Delete a user
cursor.execute('DELETE FROM users WHERE name = ?', ('Alice',))
conn.commit()Best Practices
- Always commit changes with `conn.commit()` to save them.
- Close the connection with `conn.close()` to free resources.
- Use parameterized queries (`?`) to prevent SQL injection.
- Organize SQL commands and database logic into functions or classes for larger projects.
Mini Project Step
Create a `contacts.db` database. Make a table called `contacts` with columns: name, phone, and email. Add at least 5 contacts, then write Python functions to add a new contact, list all contacts, update a phone number, and delete a contact.