Introduction to Databases with SQLite

Introduction to Databases with SQLite

In this lesson, we will explore the fundamentals of databases, focusing on SQLite, a lightweight and easy-to-use database management system. By the end of this lesson, you will understand what databases are, how to create and manipulate them using SQLite, and how to integrate them with your Python applications.

Learning Objectives

By the end of this lesson, you will be able to: - Understand the basic concepts of databases and SQLite. - Create a new SQLite database and tables. - Insert, update, delete, and query data using SQL commands. - Handle SQLite databases in Python using the sqlite3 module.

What is a Database?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Databases are managed by Database Management Systems (DBMS), which provide an interface for users to interact with the data.

Types of Databases

• Relational Databases: Use tables to store data, with relationships between them (e.g., MySQL, PostgreSQL).
• NoSQL Databases: Store data in a non-tabular form (e.g., MongoDB, Cassandra).
• In-Memory Databases: Store data in the main memory for fast access (e.g., Redis).

What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It is the most used database engine in the world, embedded in many applications, including browsers and mobile apps.

Key Features of SQLite

• Lightweight: SQLite is a serverless database; it does not require a separate server process.
• Cross-Platform: Works on various operating systems, including Windows, macOS, and Linux.
• Self-Contained: The entire database is stored in a single file on disk.
• Zero Configuration: No setup or administration required.

Setting Up SQLite in Python

To use SQLite in Python, we need the built-in sqlite3 module. This module provides a straightforward API for interacting with SQLite databases.

Step 1: Importing the sqlite3 Module

import sqlite3

This line imports the sqlite3 module, allowing us to use its features to manage our SQLite database.

Step 2: Connecting to a Database

To connect to a database, we use the connect function. If the specified database does not exist, SQLite will create it.

connection = sqlite3.connect('my_database.db')

This code creates a new SQLite database file named my_database.db in the current directory. If the file already exists, it connects to that database.

Creating a Table

Once connected to a database, we can create tables to store our data. A table consists of rows and columns, where each column has a specific data type.

Step 3: Creating a Cursor Object

Before executing SQL commands, we need a cursor object. The cursor allows us to execute SQL statements and retrieve results.

cursor = connection.cursor()

Step 4: Writing SQL to Create a Table

Here’s how to create a simple table to store user information:

create_table_query = '''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
);
'''

cursor.execute(create_table_query)

This SQL statement creates a table named users with three columns: id, name, and age. The id column is the primary key, which uniquely identifies each record.

Inserting Data

Now that we have a table, we can insert data into it. We will use the INSERT INTO SQL command.

Step 5: Inserting Records

insert_query = '''
INSERT INTO users (name, age) VALUES (?, ?);
'''

cursor.execute(insert_query, ('Alice', 30))
cursor.execute(insert_query, ('Bob', 25))

In this example, we insert two records into the users table. The ? placeholders are used to prevent SQL injection attacks, allowing us to safely pass values.

Committing Changes

After making changes to the database (like inserting or updating records), we must commit those changes. This is done using the commit method of the connection object.

connection.commit()

Querying Data

To retrieve data from the database, we use the SELECT SQL command. Let’s see how to query the users table.

Step 6: Selecting Records

select_query = '''
SELECT * FROM users;
'''

cursor.execute(select_query)
rows = cursor.fetchall()

for row in rows:
    print(row)

This code retrieves all records from the users table and prints each row. The fetchall() method returns all rows as a list of tuples.

Updating Data

To modify existing records, we use the UPDATE SQL command.

Step 7: Updating Records

update_query = '''
UPDATE users SET age = ? WHERE name = ?;
'''

cursor.execute(update_query, (31, 'Alice'))

This code updates Alice's age to 31 in the users table.

Deleting Data

To remove records from the database, we use the DELETE SQL command.

Step 8: Deleting Records

delete_query = '''
DELETE FROM users WHERE name = ?;
'''

cursor.execute(delete_query, ('Bob',))

This code deletes Bob's record from the users table.

Closing the Connection

It is essential to close the database connection when you are finished to free up resources.

connection.close()

Common Mistakes and How to Avoid Them

• Forgetting to commit changes: Always remember to call connection.commit() after modifying the database.
• Not closing the connection: Always close your connection using connection.close() to avoid memory leaks.
• SQL Injection: Always use parameterized queries (with ? placeholders) to prevent SQL injection attacks.

Best Practices

• Use meaningful table and column names: This makes it easier to understand the data structure.
• Keep your database schema normalized: Avoid redundancy by organizing data efficiently.
• Backup your database regularly: Prevent data loss by maintaining backups.

Key Takeaways

• SQLite is a lightweight, serverless database management system that is easy to use with Python.
• Use the sqlite3 module to connect to and manipulate SQLite databases in Python.
• Understand the basic SQL commands: CREATE, INSERT, SELECT, UPDATE, and DELETE.
• Always commit changes and close your database connections.

In this lesson, we have covered the fundamentals of working with SQLite databases in Python. You can now create databases, manipulate data, and integrate SQLite into your applications. In the next lesson, we will explore Basic Web Development with Flask, where we will learn how to build web applications using Python and Flask.

Practice Exercises

1. Create a new SQLite database: Create a new SQLite database named test_db.db and create a table called products with columns for id, product_name, and price.

2. Insert data into the products table: Insert at least three products into the products table, ensuring to use parameterized queries.

3. Query the products table: Write a query to retrieve all products from the products table and print their details.

4. Update a product's price: Update the price of one of the products in the products table.

5. Delete a product: Delete one of the products from the products table.

Practical Assignment

Create a mini-project that involves managing a simple inventory system using SQLite. Your project should include the following functionalities: - Create a database and a table for storing inventory items. - Insert new items into the inventory. - Retrieve and display all items from the inventory. - Update the details of an item in the inventory. - Delete an item from the inventory.

• SQLite is a lightweight and self-contained database management system.
• Use the sqlite3 module in Python to interact with SQLite databases.
• Understand basic SQL commands: CREATE, INSERT, SELECT, UPDATE, and DELETE.
• Always commit changes and close your database connections to maintain data integrity.
• Follow best practices for naming conventions and database design to ensure clarity and efficiency.