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

katefike / sage

Use SQLAlchemy #16

Sources

What's the difference between SQLAlchemy and SQLAlchemy Core?