Understanding SQLAlchemy ORM and SQLAlchemy Core
Published: 2023-03-21 10:36:34

**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:

```python
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:

```python
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:
```python
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:

```python
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.