What methods of working with databases do you know?

• Built-in sqlite3 library for working with SQLite.
• ORM (Object-Relational Mappers):
  • SQLAlchemy: powerful and flexible ORM, supports many databases.
  • Django ORM: integrated with Django, convenient for web development.
  • Pony ORM: declarative ORM, focuses on performance and simplicity.
• Low-level database drivers:
  • psycopg2 for PostgreSQL.
  • mysql.connector or PyMySQL for MySQL.
  • pyodbc for universal access via ODBC.
• NoSQL databases:
  • PyMongo for MongoDB (document-oriented).
  • Redis-py for Redis (key-value, data structures).
  • Cassandra-driver for Apache Cassandra (columnar).

<br>

Comparison of some approaches:

Method	Level of abstraction	Advantages	Disadvantages
sqlite3	Low	Built-in, easy to use	Only for SQLite
ORM (SQLAlchemy)	High	Working with objects, portability	Learning curve, overhead
Low-level drivers	Low	Full control, performance	More complex code, DBMS-specific

<br>

Example of using SQLAlchemy with SQLite:

# SQLAlchemy with SQLite
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Creating database engine (SQLite in memory)
engine = create_engine('sqlite:///:memory:')

# Base class for declarative models
Base = declarative_base()

# Defining a model
class User(Base):
    __tablename__ = 'users' # Table name
    id = Column(Integer, primary_key=True) # Primary key
    name = Column(String) # String field
    age = Column(Integer) # Integer field

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

# Creating all tables in the engine
Base.metadata.create_all(engine)

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

# Adding data
new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit() # Saving changes

# Querying data
users = session.query(User).all()
print(users) # Output: [User(id=1, name='Alice', age=30)]

# Closing session
session.close()