What is the difference between SQLAlchemy Core and ORM?

SQLAlchemy Core and ORM are two different components of the SQLAlchemy library in Python. The main difference between the two is the level of abstraction they provide when working with SQL databases:

• SQLAlchemy Core: It is a low-level SQL toolkit that provides a SQL abstraction layer and allows you to work directly with SQL databases using Python. It is designed to be flexible, fast, and compatible with a wide range of databases.
• SQLAlchemy ORM: It is a higher-level API built on top of SQLAlchemy Core that provides an Object Relational Mapper (ORM) for working with databases. The ORM allows you to map Python classes to database tables, and to interact with the data in those tables using instances of those classes. It provides a more Pythonic and abstracted way of working with databases, making it easier for many use cases, but may not provide the same level of control and performance as SQLAlchemy Core.

In summary, SQLAlchemy Core is for those who need a low-level and flexible SQL toolkit, while SQLAlchemy ORM is for those who want a more Pythonic and abstract way of working with databases. Let’s understand it in more detail

What is SQLAlchemy Core

 SQLAlchemy Core is a low-level SQL toolkit provided as part of the SQLAlchemy library in Python. It provides a SQL abstraction layer, allowing you to work with SQL databases in a more Pythonic way, while still retaining full control over the SQL being executed. It is designed to be flexible, fast, and compatible with a wide range of databases, including MySQL, PostgreSQL, and SQLite.

Understanding its Features and Benefits

1. The Core of SQLAlchemy is a powerful and flexible low-level API that allows developers to interact directly with the database using SQL. It provides a set of classes and functions that can be used to create and execute SQL statements, giving developers fine-grained control over the database and allowing for complex queries and performance-critical applications.
2. One of the key features of the Core is the ability to interact directly with the database using SQL. This allows developers to have full control over the SQL statements being executed and take advantage of any database-specific features. Additionally, the Core offers flexibility in that developers can write their own SQL statements.
3. Another important feature of the Core is the ability to have fine-grained control over the database, including transaction management, connection management, and the ability to create and execute custom SQL statements. This is especially useful in performance-critical applications where fine-tuning database interactions can make a significant impact on performance.
4. Overall, the Core of SQLAlchemy is well-suited for complex queries or performance-critical applications where the ORM might not be able to handle the complexity or performance requirements. It allows for direct interaction with the database using SQL and offers fine-grained control over them, making it a valuable tool for developers.

Example 

This example shows how you can use SQLAlchemy Core to create a connection to an SQLite database, execute a SELECT statement, retrieve the results, and loop through the rows. The text function is used to create a SQL expression, which is then passed to the execute method of the engine object. The fetchall method retrieves all rows from the result set as a list of dictionaries, where each dictionary represents a row and the keys are the column names.

Python3

from sqlalchemy import create_engine, text

# create a connection to a SQLite database
engine = create_engine('sqlite:///example.db')

# execute a SELECT statement
result = engine.execute(text("SELECT * FROM users"))

# retrieve all rows as a list of dictionaries
rows = result.fetchall()

# loop through the rows and print the data
for row in rows:
    print(row)

# close the connection
result.close()

What is SQLAlchemy ORM

SQLAlchemy ORM (Object Relational Mapper) is a higher-level API built on top of SQLAlchemy Core, providing an easier way to interact with databases using Python classes and objects. It allows you to map Python classes to database tables, and to interact with the data in those tables using instances of those classes. This can simplify database operations and allow you to write more Pythonic code. The ORM also provides a range of advanced features such as lazy loading, caching, and transactional control, making it a powerful tool for managing database-driven applications.

Understanding its Features and Benefits 

1. The ORM (Object-Relational Mapper) is a high-level API that allows developers to interact with the database using Python objects. It provides a set of classes that correspond to tables in the database, and these classes can be used to create, retrieve, update, and delete records in the database.
2. One of the main features of the ORM is the ability to interact with the database using Python objects. This provides a more intuitive and Pythonic way of working with the data, allowing developers to focus on their application logic rather than worrying about the intricacies of SQL. The ORM also abstracts away the need to write raw SQL statements, making it more accessible to developers who are not familiar with SQL.
3. Another important feature of the ORM is the automatic mapping between Python objects and database rows. This is handled by the ORM itself, which takes care of translating between the Python objects and the database rows. This eliminates the need for manual mapping and reduces the amount of boilerplate code that needs to be written.
4. The ORM also adds convenience to the development process. It provides a higher level of abstraction, which makes it easier to reason about the data and application logic, and it also provides a lot of functionality out of the box, such as support for transactions and connection pooling.

Example

This example demonstrates how you can use SQLAlchemy ORM to define a database model, create a connection to an SQLite database, insert data, and retrieve data. The declarative_base class is used to create a base class for the model classes, and the Column class is used to define the columns in the table. The session maker function is used to create a factory for creating database sessions and session.add_all method is used to add multiple instances of the User model to the session. The session.commit method is used to persist the changes to the database and the session.query method is used to create a query for retrieving data from the Users from the table.

Python3

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

# create a SQLite database engine
engine = create_engine('sqlite:///example.db')

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

# create a declarative base
Base = declarative_base()

# define a model class


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

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


# create the database tables
Base.metadata.create_all(engine)

# insert some data
session = Session()
session.add_all([
    User(name='Alice', age=30),
    User(name='Bob', age=35),
    User(name='Charlie', age=40),
])
session.commit()

# retrieve the data
users = session.query(User).all()
for user in users:
    print(user)

# close the session
session.close()

Output:

id=1, name='Alice', age=30
id=2, name='Bob', age=35
id=3, name='Charlie', age=40

SQLAlchemy Core vs ORM

	Core	ORM
Level of Abstraction	The Core is a low-level API that allows developers to interact directly with the database using SQL, while the ORM is a high-level API that allows developers to interact with the database using Python objects. This means that the Core provides more fine-grained control over the database and allows for more complex queries, but it also requires developers to be familiar with SQL and may require more code to be written	The ORM provides a more intuitive and Pythonic way of working with the data, but it also has a higher overhead and may not be as performant as using the Core.
The trade off between convenience and performance	The Core provides more fine-grained control over the database and allows for more complex queries, but it requires developers to be familiar with SQL and may require more code to be written.	The ORM provides a lot of convenience and abstraction, making it easier to reason about the data and application logic, but it also has a higher overhead and may not be as performant as using the Core.
Complex Queries	The Core allows for more complex queries and fine-tuning of the performance by allowing developers to write raw SQL statements and has fine-grained control over the database.	The ORM is more suitable for CRUD operations and simple queries.
Familiarity with SQL	The Core requires more familiarity with SQL and may require more code to be written.	The ORM abstracts away the need to write raw SQL statements and is more accessible to developers who are not familiar with SQL.
Mapping	The Core does not handle mapping and developers would need to manually map the data.	The ORM also automatically handles mapping between the Python objects and the database rows, adding convenience to the development process and reducing the amount of boilerplate code that needs to be written
Transactions and connection pooling	The Core does not provide built-in support for these features, developers need to handle these aspects manually.	The ORM provides built-in support for transactions and connection pooling, making it more convenient for developers to manage these aspects of the database interactions.