Introduction to SQLite

SQLite is a lightweight, file-based database engine that doesn’t require a separate server process. It’s embedded into your application, making it ideal for small to medium-sized applications where data persistence is required. Python’s built-in sqlite3 module provides a convenient interface to interact with SQLite databases.

Getting Started

Importing the sqlite3 module:

import sqlite3

Creating a Database Connection:

conn = sqlite3.connect('mydatabase.db')

This line creates a database named mydatabase.db in the current directory. If the database already exists, it will open it instead. To create an in-memory database, use ':memory:' as the database name.

Creating a Cursor:

cursor = conn.cursor()

A cursor is used to execute SQL statements. It’s like a pointer to the database.

Creating Tables

cursor.execute('''CREATE TABLE customers (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT NOT NULL,
              address TEXT,
              city TEXT,
              postalcode TEXT,
              country TEXT
              )''')

This code creates a table named customers with several columns. Note the use of triple quotes for multi-line strings and the AUTOINCREMENT keyword for automatically generating primary key values.

Inserting Data

cursor.execute("INSERT INTO customers (name, address, city, postalcode, country) VALUES ('John Doe', '301 Main St', 'New York', '10001', 'USA')")

This code inserts a new record into the customers table.

Retrieving Data

cursor.execute("SELECT * FROM customers")
rows = cursor.fetchall()
for row in rows:
    print(row)

This code selects all records from the customers table and prints them to the console.

Updating Data

cursor.execute("UPDATE customers SET address = '405 Main St' WHERE id = 1")

This code updates the address for the customer with ID 1.

Deleting Data

cursor.execute("DELETE FROM customers WHERE id = 2")

This code deletes the customer with ID 2 from the database.

Committing Changes

conn.commit()

This line commits the changes made to the database.

Closing the Connection

conn.close()

This line closes the database connection.

Error Handling

import sqlite3

try:
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    # ... your code ...
except sqlite3.Error as e:
    print("Error:", e)
finally:
    if conn:
        conn.close()

Advanced Topics

• Parameterized Queries: To prevent SQL injection, use parameterized queries:
  Python

  cursor.execute("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", (name, address, city, postalcode, country))
  

  Use code with caution.
• Transactions: Group multiple SQL statements into a transaction using begin_transaction() and commit() or rollback().
• Creating Indexes: Improve query performance by creating indexes on frequently searched columns:
  Python

  cursor.execute("CREATE INDEX idx_name ON customers(name)")
  

  Use code with caution.
• Using SQLite Functions: Create custom SQL functions in Python:
  Python

  def add(x, y):
      return x + y
  conn.create_function("add", 2, add)
  cursor.execute("SELECT add(1, 2)")
  

  Use code with caution.
• SQLite Browser: Use a graphical tool like SQLite Browser to explore your database.

Best Practices

• Use clear and meaningful table and column names.
• Normalize your database design to avoid data redundancy.
• Index columns that are frequently searched.
• Use parameterized queries to prevent SQL injection.
• Commit changes regularly to avoid data loss.
• Close the database connection when finished.

Conclusion

SQLite is a versatile and easy-to-use database for Python applications. By understanding the basic concepts and best practices, you can effectively store, retrieve, and manage data within your projects.