SQLite is a lightweight, easy-to-use relational database management system embedded within Python’s standard library. It is perfect for small to medium-sized applications, development, testing, and even for mobile and desktop applications. In this article, we will explore how to integrate SQLite with Python, focusing on the essential concepts, operations, and best practices.
Part II can be found here.
Getting Started with SQLite in Python
To start using SQLite in Python, you import the sqlite3 module. Note: Sqlite3 comes pre-installed with Python so there is no need to perform pip install sqlite3.
The code below is the standard way to interact with sqlite in Python.
import sqlite3
# Connect to a database (or create one if it doesn't exist)
conn = sqlite3.connect('example.db')
# Create a cursor object
cursor = conn.cursor()Creating a Table
Once connected, you can create tables using SQL commands. The cursor object executes these commands, and changes are committed to the database using the commit() method.
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
email TEXT
)
''')
conn.commit()SQLite supports the following field types, which are categorized into six storage classes:
- NULL: The value is a NULL value.
- INTEGER: A signed integer, typically stored as 1, 2, 3, 4, 6, or 8 bytes, depending on the value.
- REAL: A floating-point value, stored as an 8-byte IEEE floating-point number.
- TEXT: A text string, stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE).
- BLOB: A blob of data, stored exactly as it was input.
- NUMERIC: Represents numeric values and is used to store numbers (See explanation below).
In addition to these basic storage classes, SQLite uses dynamic typing, which means you can use other type names (e.g., VARCHAR, CHAR, BOOLEAN), but they are mapped to one of the five storage classes above.
The NUMERIC Affinity in SQLite
The NUMERIC type affinity is versatile and can be considered a “catch-all” for numeric data. Here’s how it works:
- Primary Intent: It attempts to store values as integers (INTEGER) or floating-point numbers (REAL) whenever possible.
- Text Conversion: If the data cannot be interpreted as a number, it will be stored as TEXT.
- Flexibility: This flexibility allows for storing a wide range of numeric values without strictly enforcing a specific numeric format.
Inserting Data
Inserting data into the SQLite database is done using the INSERT INTO SQL command. It’s crucial to use parameterized queries to avoid SQL injection attacks.
# Insert data
cursor.execute('''
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
''', ('Alice', 30, 'alice@example.com'))
conn.commit()For bulk inserts, executemany() can be used for efficiency.
# Bulk insert data
users = [
('Bob', 24, 'bob@example.com'),
('Charlie', 29, 'charlie@example.com')
]
cursor.executemany('''
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
''', users)
conn.commit()For testing you can insert without specifying column names. Obviously this should only be used in a testing environment.
# Test insert data
INSERT INTO users VALUES (?, ?, ?, ...)Querying Data
Fetching data is done using the SELECT SQL command. Results can be retrieved using methods like fetchone(), fetchall(), or fetchmany().
# Query data
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)Updating Data
Updating existing records uses the UPDATE SQL command. Again, parameterized queries should be used to ensure security.
# Update data
cursor.execute('''
UPDATE users SET age = ? WHERE name = ?
''', (31, 'Alice'))
conn.commit()Deleting Data
Deleting records from the database is done using the DELETE SQL command.
# Delete data
cursor.execute('''
DELETE FROM users WHERE name = ?
''', ('Charlie',))
conn.commit()Closing the Connection
It is important to close the database connection when it is no longer needed to free up resources.
# Close the cursor and connection
cursor.close()
conn.close()Advanced Features
SQLite supports various advanced features such as transactions, foreign keys, and full-text search. Utilizing these features can enhance the functionality and performance of your database operations.
Transactions ensure that a series of operations are executed as a single unit of work, providing atomicity, consistency, isolation, and durability (ACID properties).
# Using transactions
try:
conn.execute('BEGIN TRANSACTION')
cursor.execute('UPDATE users SET age = age + 1')
conn.commit()
except:
conn.rollback()Foreign Keys can enforce referential integrity between tables, ensuring that relationships between records are maintained.
# Enable foreign keys
conn.execute('PRAGMA foreign_keys = ON')Full-Text Search allows for efficient searching of large text fields within your database.
# Create a virtual table for full-text search
cursor.execute('''
CREATE VIRTUAL TABLE docs USING FTS5(content)
''')
conn.commit()
# Insert data into the virtual table
cursor.execute('''
INSERT INTO docs (content) VALUES (?)
''', ('This is a test document.',))
conn.commit()
# Search the virtual table
cursor.execute('SELECT * FROM docs WHERE docs MATCH ?', ('test',))
print(cursor.fetchall())Best Practices
- Use Parameterized Queries: Always use parameterized queries to prevent SQL injection attacks.
- Close Connections: Ensure connections and cursors are closed to prevent memory leaks.
- Handle Exceptions: Use try-except blocks to handle database errors gracefully.
- Optimize Queries: Use indexes and optimize your SQL queries for better performance.
- Backup Regularly: Regularly backup your database to prevent data loss.
Full Python Example:
Note: I normally use try-except-finally blocks when interacting with a database.
import sqlite3
try:
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
# Create a cursor object
cursor = conn.cursor()
# Create sign_up table
cursor.execute('''
CREATE TABLE IF NOT EXISTS sign_up (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL,
telephone TEXT NOT NULL,
pin INTEGER NOT NULL
)
''')
conn.commit()
# Insert a sample record
cursor.execute('''
INSERT INTO sign_up (name, email, telephone, pin) VALUES (?, ?, ?, ?)
''', ('John Doe', 'john.doe@example.com', '555-1234', 1234))
conn.commit()
# Fetch and print the sample record
cursor.execute('SELECT * FROM sign_up WHERE name=?', ('John Doe',))
record = cursor.fetchone()
print("Inserted Record:", record)
# Delete the sample record
cursor.execute('DELETE FROM sign_up WHERE name=?', ('John Doe',))
conn.commit()
# Verify deletion
cursor.execute('SELECT * FROM sign_up WHERE name=?', ('John Doe',))
record = cursor.fetchone()
print("Record after deletion:", record)
except sqlite3.Error as e:
print(f"An error occurred: {e}")
finally:
# Close the cursor and connection
if cursor:
cursor.close()
if conn:
conn.close()Conclusion
SQLite is a versatile and powerful database system that, when combined with Python, provides an excellent solution for data management in various applications. By following the best practices and understanding the basic operations, you can effectively leverage SQLite in your Python projects. Whether you are developing a small application or a complex system, SQLite’s simplicity and efficiency make it a great choice for managing your data.
Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com
