SQLite is a lightweight, serverless, self-contained database engine. It is widely used in Python for local storage and smaller projects. Python provides an inbuilt module, sqlite3, to interact with SQLite databases.
1. Introduction to SQLite
SQLite is an embedded database that stores data in a file on your disk. It is fast, easy to use, and requires no setup. SQLite databases are commonly used in applications like mobile apps and small web projects.
2. Connecting to SQLite Database
Python’s sqlite3 module allows you to connect to an SQLite database file. If the file does not exist, Python creates it automatically.
Example of Connecting to an SQLite Database:
import sqlite3
# Connect to SQLite database (creates file if it doesn't exist)
connection = sqlite3.connect("example.db")
# Create a cursor object
cursor = connection.cursor()
# Perform database operations...
# Close the connection
connection.close()
3. Creating a Table
Once connected to the database, you can create tables to store your data. You can use the CREATE TABLE SQL command to create a new table.
Example of Creating a Table:
# Create a table named 'students'
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL
)
''')
connection.commit() # Commit changes
4. Inserting Data into the Table
After creating a table, you can insert data using the INSERT INTO SQL command. You can use placeholders to avoid SQL injection attacks.
Example of Inserting Data:
# Insert data into the students table
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("John", 22))
connection.commit()
5. Querying Data from the Table
To retrieve data from a table, use the SELECT SQL command. You can fetch all or specific rows based on your conditions.
Example of Querying Data:
# Query all data from students table
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
# Display all rows
for row in rows:
print(row)
6. Updating Data in the Table
To update data in a table, use the UPDATE SQL command.
Example of Updating Data:
# Update a student's age
cursor.execute("UPDATE students SET age = ? WHERE name = ?", (23, "John"))
connection.commit()
7. Deleting Data from the Table
To delete data, use the DELETE SQL command.
Example of Deleting Data:
# Delete a student by name
cursor.execute("DELETE FROM students WHERE name = ?", ("John",))
connection.commit()
8. Closing the Connection
After performing all database operations, always remember to close the connection to free resources.
Example of Closing the Connection:
# Close the connection to the database connection.close()
9. Using Context Manager with SQLite
You can also use a context manager to automatically close the connection when the block of code is executed. This is a more Pythonic way of working with resources like a database connection.
Example Using Context Manager:
with sqlite3.connect("example.db") as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)
Conclusion
SQLite is a powerful and easy-to-use database for Python applications. With the sqlite3 module, you can efficiently manage your database operations, such as creating tables, inserting data, querying, updating, and deleting records.