Python Tutorial Part 8: Working with Databases and SQLite

In this article, we'll explore how to work with databases in Python, focusing on SQLite, a lightweight and self-contained database engine.

Introduction to Databases

A database is a structured collection of data that allows for efficient storage, retrieval, and manipulation of information. Python provides various libraries for interacting with databases, including SQLite, MySQL, and PostgreSQL.

SQLite Database

SQLite is a popular choice for small to medium-sized projects due to its simplicity and ease of use. It doesn't require a separate server process and can be used directly within Python applications.

Using SQLite with Python

To use SQLite in Python, you'll need to import the sqlite3 module, which provides an interface for interacting with SQLite databases.

Example:

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    email TEXT NOT NULL
                )''')

# Insert data into the table
cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
cursor.execute("INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')")

# Commit the transaction
conn.commit()

# Fetch and print data from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the cursor and connection
cursor.close()
conn.close()

In this example, we create a SQLite database file (example.db), create a users table, insert data into the table, fetch and print data, and finally close the cursor and connection.

Conclusion

SQLite is a lightweight and easy-to-use database engine that integrates seamlessly with Python. By leveraging the sqlite3 module, you can perform various database operations within your Python applications, making it an excellent choice for projects that require a simple and efficient database solution.