What are the ways you know to work with databases…

Built-in modules:
sqlite3 - for working with the lightweight SQLite database. Suitable for small projects or for use as a file database on the client side.

import sqlite3

# Connecting to the database (creates a file if it doesn't exist)
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Creating a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

# Inserting data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))

# Committing changes
conn.commit()

# Fetching data
cursor.execute("SELECT name, age FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Closing the connection
conn.close()

Python DB API (PEP-249):
The standard interface defined by most third-party database drivers for various databases (PostgreSQL, MySQL, Oracle, etc.). Allows writing database-agnostic code.

Specialized drivers/connectors:
Libraries implementing Python DB API for specific databases:

psycopg2 - for PostgreSQL.
mysql-connector-python or PyMySQL - for MySQL.
cx_Oracle - for Oracle.
pymssql - for Microsoft SQL Server.

import psycopg2

# Connecting to PostgreSQL
try:
    conn = psycopg2.connect(
        host="localhost",
        database="mydatabase",
        user="myuser",
        password="mypassword"
    )
    cursor = conn.cursor()

    # Executing SQL query
    cursor.execute("SELECT * FROM products;")
    results = cursor.fetchall()
    for row in results:
        print(row)

    conn.close()
except psycopg2.Error as e:
    print(f"Error connecting to database: {e}")

ORM (Object-Relational Mapping):
Frameworks that allow working with databases using Python objects instead of SQL queries directly. They simplify database interactions, provide abstraction, and help prevent SQL injections.

SQLAlchemy - one of the most powerful and flexible ORMs in Python. Supports many DBMS, offers both "Core" (for SQL-level operations) and "ORM" (for object-oriented approach).
Django ORM - built-in ORM of the Django framework, very convenient for working with databases in Django web applications.
Peewee - simple and lightweight ORM.

Example with SQLAlchemy Core:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select

# Connecting to the database (e.g., SQLite)
engine = create_engine('sqlite:///mydatabase.db')

# Metadata object to store database metadata
metadata = MetaData()

# Defining a table
users_table = Table(
    'users',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('age', Integer)
)

# Creating the table in the database (if it doesn't exist)
metadata.create_all(engine)

# Establishing a connection
with engine.connect() as connection:
    # Inserting data
    connection.execute(
        users_table.insert().values(name='Bob', age=25)
    )

    # Selecting data
    select_query = select(users_table)
    result = connection.execute(select_query)

    for row in result:
        print(row)

Example with SQLAlchemy ORM:

from  sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Connecting to the database
engine = create_engine('sqlite:///mydatabase_orm.db')

# Base class for ORM models
Base = declarative_base()

# Defining ORM model
class User(Base):
    __tablename__ = 'users_orm'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

    def __repr__(self):
        return f"<User(name='{self.name}', age={self.age})>"

# Creating the table (if it doesn't exist)
Base.metadata.create_all(engine)

# Creating a session factory
Session = sessionmaker(bind=engine)
session = Session()

# Creating and adding an object
new_user = User(name='Charlie', age=40)
session.add(new_user)

# Committing the transaction
session.commit()

# Retrieving the object
user = session.query(User).filter_by(name='Charlie').first()
print(user)

# Closing the session
session.close()

NoSQL databases:
For working with NoSQL databases, specialized libraries are used, such as:
- pymongo - for MongoDB.
- redis - for Redis.
- cassandra-driver - for Cassandra.

The choice of method depends on the project requirements, the type of database used, the need for ORM, and the project's complexity.

What are the ways you know to work with databases?