SQLite is a C-based library that provides a lightweight, disk-based database. Unlike other relational database management systems (RDBMS), such as MySQL or PostgreSQL, SQLite does not require a separate server process, making it a self-contained, serverless, and zero-configuration database engine.
SQLite3 is a built-in module in Python that allows you to work with SQLite databases. This makes it simple to manage databases directly within your Python applications, without the need for an external database system.
• Lightweight: SQLite is small and efficient, perfect for applications requiring a compact database engine.
• Serverless: No server is required; the database is stored in a single file.
• Self-contained: It is a zero-configuration database, meaning you don’t have to set up or configure a database service.
• Cross-platform: SQLite databases can be used on various operating systems without modification.
• Built-in Python Support: Python's sqlite3 module makes it easy to integrate SQLite into Python applications.
The sqlite3 module in Python provides an interface for interacting with SQLite databases. Here’s a basic overview of how to use SQLite3 in Python:
1. Connecting to a Database: You can connect to an existing SQLite database or create a new one. If the file does not exist, it will be created.
2. Executing SQL Queries:You can execute SQL queries such as CREATE, INSERT, UPDATE, DELETE, and SELECT to manage the database.
3. Cursor: The cursor object is used to execute SQL commands and retrieve data from the database.
4. Commit and Close: After making changes to the database, you need to commit those changes. Finally, you should close the database connection.
To start working with SQLite in Python, you first need to connect to a database. You can either connect to an existing database file or create a new one.
import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('example.db')
# Create a cursor object to interact with the database
cursor = connection.cursor()
Once connected, you can create tables using SQL's CREATE TABLE statement. The table will be created in the SQLite database.
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
email TEXT
)
''')
# Commit the transaction
connection.commit()
To insert data into the database, you can use SQL's INSERT INTO statement.
# Insert data into the table
cursor.execute('''
INSERT INTO users (name, age, email)
VALUES ('John Doe', 25, 'john@example.com')
''')
# Commit the transaction
connection.commit()
You can retrieve data from the database using SQL's SELECT statement.
# Query all rows in the 'users' table
cursor.execute('SELECT * FROM users')
# Fetch and display all results
rows = cursor.fetchall()
for row in rows:
print(row)
You can modify existing data using SQL's UPDATE statement.
# Update a user's email
cursor.execute('''
UPDATE users
SET email = 'newemail@example.com'
WHERE name = 'John Doe'
''')
# Commit the transaction
connection.commit()
You can remove data using SQL's DELETE statement.
# Delete a user from the table
cursor.execute('''
DELETE FROM users
WHERE name = 'John Doe'
''')
# Commit the transaction
connection.commit()
After you're done interacting with the database, you should close the connection.
# Close the connection to the database
connection.close()
| Operation | SQL Query |
|---|---|
| Create Table | CREATE TABLE table_name (columns) |
| Insert Data | INSERT INTO table_name (columns) VALUES (values) |
| Select Data | SELECT columns FROM table_name |
| Update Data | UPDATE table_name SET column=value WHERE condition |
| Delete Data | DELETE FROM table_name WHERE condition |
1. Parameterized Queries: Helps prevent SQL injection by using placeholders for data input in SQL statements.
cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('Jane Doe', 30, 'jane@example.com'))
2. Transactions: SQLite automatically wraps every command in a transaction unless you disable autocommit.
3. Fetching Results:
• cursor.fetchone(): Fetches one row.
• cursor.fetchall(): Fetches all rows.
4. Error Handling: You can catch and handle exceptions with sqlite3.Error to ensure graceful handling of errors.