What is a Database?

A databases is organized collections of data that allow for efficient storage, retrieval, and management of information.

Key characteristics:

Persistence: Data remains after your program ends

Structure: Organized data storage in a logical way

Efficiency: Fast retrieval of large amounts of data

Data Integrity: Rules ensure your data stays valid

Concurrency: Multiple users can access data simultaneously

Types of Databases

Relational Databases (SQL)

Organize data into one or more tables with columns and rows. Tables relate to each other through foreign keys (in image above - the ZIP_id column).

SQL (Structured Query Language) is a standardized programming language used to manage relational databases and perform various operations on the data in them.

NoSQL Databases

Flexible data models that do not require a fixed schema, often used for large sets of distributed data.

In next slides we'll focus on Relational Databases and working with them in Python.

Interacting with Databases: SQL CRUD Operations

The standard language for interacting with relational databases is called SQL (Structured Query Language).

With SQL, you can write commands to:

CREATE tables

READ data from tables

UPDATE data in tables

DELETE data from tables

                # Example SQL for CRUD operations
                INSERT INTO users (name, age) VALUES ('Ivan', 30);  -- Create
                SELECT * FROM users;                                -- Read
                UPDATE users SET age = 31 WHERE name = 'Ivan';      -- Update
                DELETE FROM users WHERE name = 'Ivan';              -- Delete
            

While SQL is powerful, writing raw SQL queries directly in your Python code can sometimes be a bit cumbersome and can lead to potential problems, especially for beginners.

Introduction to SQLite

SQLite is a lightweight, file-based SQL database that does not require a separate server process and allows access via a simple integration with almost any programming language.

Why Choose SQLite?

Simple to set up and use - no complicated server setup; just needing a file to be accessed or created.

Highly portable - The entire database consists of a single file on disk, making it extremely easy to copy and move.

Surprisingly capable - Supports most SQL standards, making it capable enough for many small to medium applications.

SQLite is used by literally millions of application - check Well-Known Users of SQLite

Interacting with SQLite within VSCode

To view or edit your SQLite Databases you can use the VSCode Extension: SQLite3 Editor

It allows you to edit SQLite3 files like you would in spreadsheet applications.

Using SQLite with Python

Using SQLite with Python is straightforward thanks to the built-in sqlite3 module.

This means you don't need to install any additional packages to start working with SQLite in Python.

But you must know how to write SQL queries and how to use the SQLite module

Example: SQLite CRUD Operation in Python

What is SQLAlchemy?

SQLAlchemy is a Python library that makes working with databases easier.

It's an ORM (Object-Relational Mapper) which means

You work with Python objects instead of SQL queries

It translates your Python code into database operations

You can switch between different database systems with minimal code changes

Analogy between Tables and Classes

            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                age INTEGER NOT NULL
            )
        

            class User:
                def __init__(self, name, age):
                    self.name = name
                    self.age = age
        

Supported Database Backends

SQLAlchemy supports various database backends, including:

SQLite: A lightweight, file-based database engine that is suitable for small-scale applications and testing.

MySQL: A popular open-source relational database management system known for its reliability and performance.

PostgreSQL: An advanced open-source relational database management system known for its robustness and extensibility.

Oracle: A proprietary relational database management system widely used in enterprise environments.

Microsoft SQL Server: A relational database management system developed by Microsoft, commonly used in Windows-based environments.

DB2: A relational database management system developed by IBM, often used in large-scale enterprise applications.

And more...

Installation

You can install SQLAlchemy using pip:

                pip install sqlalchemy
            

Connect to a database

To connect to a database, SQLAlchemy uses an Engine object, which represents a source of database connectivity and behavior.

You can create an Engine using the create_engine function.

                from sqlalchemy import create_engine

                # Create an engine to connect to a SQLite database
                engine = create_engine('sqlite:///users.db')

                # create engine for MySQL dialect with MySQL Connector DBAPI:
                engine = create_engine("mysql+mysqlconnector://usr:pass@localhost/users")

                # create engine for PostgreSQL dialect with psycopg2 DBAPI:
                pg_engine = create_engine('postgresql+psycopg2://usr:pass@localhost/users')
            

An Engine allows SQLAlchemy to interact with a database. It is the starting point for any SQLAlchemy application

The typical usage of create_engine() is once per particular database URL, held globally for the lifetime of a single application process

Reference: Engine Configuration @http://docs.sqlalchemy.org

Create Table (Defining a Model)

In SQLAlchemy database tables are represented as Python classes (called Models).

Each table class inherits from the Declarative Base class and defines its columns as class attributes.

                from sqlalchemy import create_engine
                from sqlalchemy.orm import declarative_base
                from sqlalchemy import Column, Integer, String

                # Create an engine
                engine = create_engine('sqlite:///users.db')

                # Declare a base using declarative_base
                Base = declarative_base()

                # Define the User class inheriting from Base
                class User(Base):
                    __tablename__ = 'user'
                    id = Column(Integer, primary_key=True)
                    name = Column(String)
                    age = Column(Integer)

                # Create the tables in the database
                Base.metadata.create_all(engine)
            

Note that the tables won't be created automatically just by defining the model. You'll need to create an engine, and call Base.metadata.create_all(engine)

Create Session

Once connected, SQLAlchemy uses a Session object to manage interactions with the database.

Use session objects to add new records, fetch data, and update or delete existing records.

You must close the session after working with it, as database connections are limited resources. If you don't release them properly

You'll run out of available connections

Performance will degrade

Your application might crash with "too many connections" errors

            from sqlalchemy import create_engine
            from sqlalchemy.orm import sessionmaker

            engine = create_engine('sqlite:///example.db')
            Session = sessionmaker(bind=engine)

            # Create a session
            session = Session()

            # Do your database operations
            # ...

            # Commit your changes
            session.commit()

            # Close the session when done
            session.close()
        

Create Session: Using Context Manager (Recommended)

Using a context manager with session is the cleaner, more reliable approach

                from sqlalchemy import create_engine
                from sqlalchemy.orm import sessionmaker

                engine = create_engine('sqlite:///example.db')
                Session = sessionmaker(bind=engine)

                # Use a context manager
                with Session() as session:
                    # Do your database operations
                    # ...

                    # Commit your changes
                    session.commit()

                # Session automatically closes when the block ends
            

Create (Insert Data)

To insert new records into a tbale, create new instances of your mapped classes and add them to the session.

Once added, commit the session to persist the changes to the database.

                # Create a new user instance
                new_user = User(name="Ivan", age=30)

                # Add the new user to the session
                session.add(new_user)

                # Add multiple users at once
                session.add_all([
                    User(name="Maria", age=22),
                    User(name="Stoyan", age=19)
                ])

                # Commit the session to persist the changes
                session.commit()
            

Read (Query Data)

We use the session.query(User) to start a query for User objects.

We can use methods like .all(), .first(), .filter() to get the data we need.

                # Get all users
                all_users = session.query(User).all()
                for user in all_users:
                    print(user)

                # Get first user
                first_user = session.query(User).first()
                print(first_user)

                # Filter results
                young_users = session.query(User).filter(User.age < 21).all()
                for user in young_users:
                    print(user)
            

Update (Modify Data)

We find the user we want to update and then modify their attributes

                # Find a user and update her age
                user = session.query(User).filter(User.name == "Maria").first()
                user.age = 23  # type:ignore
                session.commit()

                # Update multiple records at once
                session.query(User).filter(User.age < 25).update({"age": 10})
                session.commit()

            

Delete (Remove Data)

We find the user we want to delete using a query and then use session.delete() to mark it for deletion

                # Find and delete a specific user
                user = session.query(User).filter(User.name == "Maria").first()
                session.delete(user)

                # Delete multiple records
                session.query(User).filter(User.age == 5).delete()

                session.commit()
            

Example: CRUD Operation on User Table

Example: SQLAlchemy CRUD Operations

This was just a brief introduction to the basics of databases and SQLAlchemy.

To learn more, you can explore:

Relationships between tables: How to define connections between different models (e.g., a user can have multiple orders).

More advanced querying techniques: Using more complex filters, joins, and aggregations.

Working with different database types: Configuring SQLAlchemy to connect to PostgreSQL, MySQL, etc.

Using SQLAlchemy with real world applications