SQLite3 is a lightweight, disk-based database that doesn’t require a separate server process. It is useful for small to medium-sized projects. Python provides a built-in module, sqlite3, to interact with SQLite databases.
Here's a basic example to help you get started with sqlite3 in Python:
1. Connecting to a Database
If the database file does not exist, SQLite will create it for you.
Python Code
import sqlite3
# Connect to the database (or create it)
conn = sqlite3.connect('example.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
2. Creating a Table
You can create tables using SQL commands.
Python Code
# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT)''')
# Commit the changes
conn.commit()
3. Inserting Data
You can insert data into the table using the INSERT INTO SQL command.
Python Code
# Insert a row of data
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
('Alice', 30, 'alice@example.com'))
# Commit the changes
conn.commit()
4. Querying Data
You can retrieve data from the database using the SELECT SQL command.
Python Code
# Query all rows
cursor.execute("SELECT * FROM users")
# Fetch all results
rows = cursor.fetchall()
# Print the results
for row in rows:
print(row)
5. Updating Data
You can update existing records using the UPDATE SQL command.
Python Code
# Update a user's age
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, 'Alice'))
# Commit the changes
conn.commit()
6. Deleting Data
You can delete records using the DELETE SQL command.
Python Code
# Delete a user
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))
# Commit the changes
conn.commit()
7. Closing the Connection
Always close the database connection when you’re done to free up resources.
Python Code
# Close the connection
conn.close()
Summary of SQLite Commands
Connect to a database: conn = sqlite3.connect('example.db')
Create a cursor: cursor = conn.cursor()
Create a table: cursor.execute('CREATE TABLE ...')
Insert data: cursor.execute('INSERT INTO ...')
Query data: cursor.execute('SELECT ...')
Update data: cursor.execute('UPDATE ...')
Delete data: cursor.execute('DELETE FROM ...')
Commit changes: conn.commit()
Close the connection: conn.close()
This should give you a solid foundation for working with SQLite in Python. Let me know if you have any specific questions or need further examples!