Understanding SQLAlchemy ORM and SQLAlchemy Core

SQLAlchemy is a popular Python library for working with relational databases. It provides two main approaches to working with databases: SQLAlchemy ORM (Object-Relational Mapping) and SQLAlchemy Core. In this post, we'll explore the differences between these two approaches and discuss when each one is most applicable.

SQLAlchemy ORM

SQLAlchemy ORM is a higher-level interface that allows us to interact with the database using Python objects, rather than writing raw SQL. It provides a way to define database models as Python classes and interact with them using familiar object-oriented programming concepts like inheritance and relationships. In basic terms, we do not have to write SQL syntax with SQLAlchemy ORM. If SQL syntax scares you, ORM will be your Lord and Saviour.

Here's an example:

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

# Create a database model using SQLAlchemy ORM
Base = declarative_base()

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

# Create a database engine
engine = create_engine('mysql://user:password@localhost/mydatabase')

# Create a database table
Base.metadata.create_all(engine)

# Create a new user using SQLAlchemy ORM
new_user = User(name='Areola Daniel')

# Add the new user to the database using a SQLAlchemy ORM session
session = Session(engine)
session.add(new_user)
session.commit()
session.close()

To query the database:

user = session.query(User).first()

# Print new user
print(f'{user.id}: {user.name}')  # Output 1: Areola Daniel
session.close()

In this example, we're using SQLAlchemy ORM to define a User model as a Python class that maps to a database table. We're then using a SQLAlchemy ORM session to add a new user to the database and query the database to retrieve that user. SQLAlchemy ORM allows us to interact with the database using Python objects and methods, rather than writing raw SQL.

SQLAlchemy Core

SQLAlchemy Core is a lower-level interface that provides a way to work with databases using SQL expressions and statements. If you prefer to write SQL directly and want more fine-grained control over database interactions, then SQLAlchemy Core is for you. It provides a way to create and execute SQL expressions using Python syntax, as well as a way to work with raw SQL statements.

Here's an example:

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

# Create a database engine
engine = create_engine('mysql://user:password@localhost/mydatabase')

metadata = MetaData()

users = Table('users', metadata,
    Column('id', Integer(), primary_key=True),
    Column('name', String(128))
)

# Create a database table
metadata.create_all(engine)

# Create a new user using SQLAlchemy Core
ins = users.insert().values(name = 'Areola Daniel')

# Connect to database
conn = engine.connect()

# Execute insert function
conn.execute(ins)

To query the database:

result = conn.execute(text('SELECT * FROM users'))

for row in result:
    print(row)  # Output (1, 'Areola Daniel')
conn.close()

In this example, we are using SQLAlchemy Core to interact with our MySQL database, creating a table, inserting data, and retrieving data using SQL expressions.

In conclusion, if we need to perform simple operations, such as selecting, inserting, updating, and deleting data, SQLAlchemy ORM is a good choice because it provides an easy-to-use API, but if we need to perform more complex operations or work with database-specific features, SQLAlchemy Core might be a better choice because it gives us a more fine-grained control over the SQL statements that are executed.