SQLite Basics

Learn the fundamentals of SQLite and how to work with databases using Python, a powerful and easy-to-use language. …

Learn the fundamentals of SQLite and how to work with databases using Python, a powerful and easy-to-use language.

What is SQLite?

SQLite is a lightweight, self-contained database management system that allows you to store, manipulate, and retrieve data efficiently. It’s an excellent choice for small-scale applications, personal projects, or when working with limited resources. SQLite databases are stored in a single file, making it easy to manage and transport.

Key Features of SQLite

• Self-contained: SQLite doesn’t require a separate database server process.
• Serverless: You don’t need to set up a separate server to run your database.
• ACID compliant: SQLite adheres to the Atomicity, Consistency, Isolation, and Durability (ACID) principles for reliable transactions.
• Supports SQL syntax: SQLite supports most of the SQL syntax features.

Setting Up SQLite in Python

To work with SQLite in Python, you’ll need to install the sqlite3 module. This is included with the Python Standard Library, so you can skip installing any additional packages.

import sqlite3

Creating a Database Connection

Create a connection to your SQLite database using the connect() function:

# Create a database object
conn = sqlite3.connect('example.db')

# Print the connection details
print(conn)

This will create a new database file called example.db in the current working directory. If you want to work with an existing database, simply replace 'example.db' with your database file path.

Creating Tables

Use SQL syntax to create tables within your SQLite database. For example:

# Create a table using SQL syntax
conn.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    );
''')

# Print the last error message (if any)
print(conn)

This will create a new users table with three columns: id, name, and email. Make sure to replace 'users' with your desired table name.

Inserting Data

Use SQL syntax to insert data into your tables. For example:

# Create some sample data
data = [
    ('John Doe', 'john@example.com'),
    ('Jane Doe', 'jane@example.com')
]

# Insert the data using a cursor object
cur = conn.cursor()
cur.executemany('INSERT INTO users (name, email) VALUES (?, ?)', data)

# Commit the transaction to save changes
conn.commit()

# Print the last error message (if any)
print(cur)

This will insert two sample records into your users table.

Querying Data

Use SQL syntax to retrieve data from your tables. For example:

# Create a cursor object
cur = conn.cursor()

# Execute an SQL query to fetch all users
cur.execute('SELECT * FROM users')

# Fetch the results using the fetchall() method
results = cur.fetchall()

# Print each user record
for row in results:
    print(row)

# Close the database connection when finished
conn.close()

This will retrieve and display all records from your users table.

By following this comprehensive guide, you’ve learned the fundamentals of SQLite and how to work with databases using Python. With practice and experience, you’ll become proficient in using SQLite for your personal projects or professional applications.