SQLite is a popular database management system known for being quick, self-contained, and easy-to-use. It's widely used for applications that need to store and retrieve data efficiently but do not need the complexity of a full-scale database management system. In this article, we will walk through the fundamental operations required to manage data in SQLite: inserting, reading, updating, and deleting (collectively known as CRUD operations).
Creating a Database
Before performing any operations, you need to either connect to an existing database or create a new one. Here's how you can do that using Python:
import sqlite3
# Connecting to SQLite
database = sqlite3.connect('my_database.db')
# Create a cursor object
cursor = database.cursor()
Creating a Table
Before inserting data, you should have a table. Let's create a simple table named employees with id, name, and position columns.
cursor.execute('''
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
position TEXT NOT NULL
)
''')
database.commit()
Inserting Data
Now that we have our table, let's insert some data into it.
# Inserting Data
cursor.execute("INSERT INTO employees (name, position) VALUES (?, ?)",
("Alice", "Developer"))
cursor.execute("INSERT INTO employees (name, position) VALUES (?, ?)",
("Bob", "Designer"))
# Save (commit) the changes
database.commit()
Reading Data
Reading or querying data from SQLite is done using the SELECT statement. Here's a simple query to fetch all records from our employees table:
# Retrieving data
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
print(row)
The output will be:
(1, 'Alice', 'Developer')
(2, 'Bob', 'Designer')
Updating Data
Updating records in SQLite involves specifying which records to update and with what information. For example, let's update Bob's position to "Senior Designer":
# Updating data
cursor.execute("UPDATE employees SET position = ? WHERE name = ?",
("Senior Designer", "Bob"))
database.commit()
Deleting Data
To delete records in SQLite, you use the DELETE statement. Let's delete the record of Alice from the table:
# Deleting data
cursor.execute("DELETE FROM employees WHERE name = ?",
("Alice",))
database.commit()
Best Practices
Always close your database connection after completing your operations to conserve memory and ensure data integrity:
# Close the connection
database.close()
Conclusion
This guide covered the essential CRUD operations using SQLite with Python. Understanding these basics allows you to handle simple database tasks efficiently. SQLite is lightweight yet powerful, making it suitable for many applications, from small apps to large systems requiring embedded databases. With practice, you'll be able to build more complex queries and robust applications.
