Use SQLAlchemy

[katefike]

Sources

https://docs.sqlalchemy.org/en/20/orm/index.html

[katefike]

What's the difference between SQLAlchemy and SQLAlchemy Core?

SQLAlchemy is a popular Python library that provides both SQLAlchemy Core and SQLAlchemy ORM. Here are the key differences between the two:

SQLAlchemy Core:

• SQLAlchemy Core is a query builder and provides a programmatic means to generate SQL queries and DDL (Data Definition Language) statements.
• It focuses on the schema and uses a schema-centric view, similar to traditional SQL.
• The results of queries built with SQLAlchemy Core are returned as tuples, not objects.
• It is useful when you need to programmatically build queries, especially when the information is only available at runtime.
• You can use SQLAlchemy Core to execute raw SQL queries by creating an engine and using the connection and execute methods.
• Example code using SQLAlchemy Core:

from sqlalchemy import create_engine

engine = create_engine('mysql://scott:tiger@localhost/test')
connection = engine.connect()
result = connection.execute("select username from users")
for row in result:
    print("username:", row['username'])
connection.close()

SQLAlchemy ORM:

• SQLAlchemy ORM is an object-relational mapper that represents database relations as Python objects.
• It provides a higher-level abstraction and follows an object-centric view, where the schema is encapsulated with business objects.
• You can define Python classes that are treated as tables, and the attributes of the class are treated as columns.
• It automates common database operations like create, read, update, and delete (CRUD) operations.
• It is useful when you want to build your application following the Model-View-Controller (MVC) pattern, with database-backed objects as the "model".
• Example code using SQLAlchemy ORM:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)

# Query all users
users = session.query(User).all()
for user in users:
    print("username:", user.username)

In summary, if you need to programmatically build queries at runtime, use SQLAlchemy Core. If you want to build your application with a model-based approach and have database-backed objects, use SQLAlchemy ORM.

Sources:

• StackOverflow
• SQLAlchemy Core Documentation
• Full Stack Python - SQLAlchemy
• GeeksforGeeks - SQLAlchemy Core - Creating Table
• Sluice Art Fair