Python

Working with SQLite

SQLite is a popular and lightweight database management system that can be used with Python. It is a serverless database that stores data in a binary file on a device. It is often used for small to medium-sized applications and is a great choice for beginners to learn about databases and how they work.

This section requires you to have an understanding of SQL and how SQL is written, it is not imperative that you know it inside out, however it would be advantageous to have a brief understanding before you try and write some code yourself.

To use SQLite with Python, you will need to install the sqlite3 module. This module provides a Python interface for interacting with SQLite databases. Once you have installed the module, you can create a connection to a SQLite database by calling the connect() function and passing it the name of the database file you want to use.

Connecting to a Database:
import sqlite3

# Connect to a SQLite database
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
Creating a Table:
# Create a table

create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT,
        email TEXT
    )
"""
cursor.execute(create_table_query)
Inserting Data:
# Insert data into the table

insert_query = "INSERT INTO users (username, email) VALUES (?, ?)"
user_data = ("john_doe", "john@example.com")
cursor.execute(insert_query, user_data)
conn.commit()  
# Commit the transaction
Querying Data:
# Query data from the table
select_query = "SELECT * FROM users"
cursor.execute(select_query)

users = cursor.fetchall()

for user in users:
    print(user)
Encapsulation for Database Handling:
class DatabaseManager:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def execute_query(self, query, data=None):
        if data:
            self.cursor.execute(query, data)
        else:
            self.cursor.execute(query)
        self.conn.commit()

    def fetch_data(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()

# Usage
db_manager = DatabaseManager("mydatabase.db")
insert_query = "INSERT INTO users (username, email) VALUES (?, ?)"
user_data = ("jane_doe", "jane@example.com")
db_manager.execute_query(insert_query, user_data)

select_query = "SELECT * FROM users"
users = db_manager.fetch_data(select_query)
for user in users:
    print(user)
Benefits of Encapsulation for Database Handling:
  • Data Integrity: Encapsulation ensures that data operations are performed correctly, minimizing the risk of data corruption or incorrect modifications.
  • Error Handling: Encapsulation allows you to handle database-related exceptions and errors in a centralized manner.
  • Resource Management: Proper encapsulation manages database connections and transactions effectively, preventing resource leaks.
  • Code Organization: Encapsulating database operations enhances code readability and maintainability by abstracting implementation details.
SQLite Specifics:
  • SQLite is a lightweight, serverless, and self-contained database engine.
  • It’s well-suited for smaller applications and projects where you need local data storage without setting up a separate database server.

While Python is extremely powerful with SQLite, it also hosts a few libraries that are capable of connecting to more heavy duty databases, therefore allowing you to build bigger and more scalable applications, but having a strong understanding of SQL itself is imperative.

Test your knowledge!

Challenge yourself!

Continue Reading

X