SQLAlchemy 1.4 and 2.0 Transitional Introduction

Introduction:

SQLAlchemy is the database toolkit for Python. It was introduced in 2005 and first released in February 2006. It provides a single system for all things Python and relational database-related. It provides us tools for managing connections with the database, interacting with the database, executing queries, and more. Its current release is 1.4.2 which is considered to be transitional for SQLAlchemy 2.0. The latest version, SQLAlchemy 1.4 / 2.0 Transitional, was released on September 22, 2021.
It consists of SQL Core and SQL ORM. The ORM (Object Relational Mapper) is built on top of the Core.

SQLAlchemy Core:

SQL Expression Language is a toolkit which is the most prominent part of Core. It provides a system of constructing SQL expressions represented by objects which carry out a transaction and return a result set.

The engine establishes connectivity with the database which provides a connection pool that consists of a connection and ends up returning a resultset.

The engine maintains a dialect and a connection pool. Dialect translates SQLAlchemy constructs for a specific kind of database and database driver, whereas a connection pool holds a collection of database connections in memory for fast re-use.

The ORM

The ORM maps a user-defined object model to database tables. It consists of a pattern called the unit of work which keeps track of everything during a transaction. It extends the core SQL Expression Language and adds support for queries in an object-oriented manner. It converts database rows into instances of objects that are user-defined. It provides a system for objects to be related to each other through collections and many to one relationships. ORM is state-oriented whereas the Core SQL Expression language is command-oriented.

ORM is an object-oriented way of dealing with databases. In ORM, we consider tables as classes and fields as attributes.

For example:

The above table can be represented in the ORM as

1
2
3
4
5
6
7
8
user_table = Table(
  "student",
  metadata,
  Column('id', Integer, primary_key = True),
  Column("name", String(50), nullable = False),
  Column(“age”, Integer),
  Column("position", String(255)),
)

Any operation on the table is performed on the instance of this class using Python and SQL. The ORM software converts the code into SQL queries

Migrating to 2.0

SQLAlchemy 2.0 introduces new APIs and features that are transitional to SQLAlchemy 1.4. It also introduces the SQL caching system, ORM relational execution model, declarative mapping, and Aynchio support for both Core and ORM.

The new version fully removes old patterns that have been discouraged for many years, such as

• Making the experience of the Core and ORM APIs much more similar and cross-compatible

• A new emphasis on explicitness and non-ambiguity

SQLAlchemy 2.0 is runnable only on Python 3 (minimum Python 3.6). SQLAlchemy now supports Asynchio by providing a new Asyncio front-end interface to connection (in Core) and to session (in ORM). This is considered a major update to SQLAlchemy 2.0.

The usage of the future flag is enabled and the connection object has two methods, Connection.commit() and Connection.rollback(), which help you to commit when you need it as the library-level “AutoCommit” is removed from both the Core and the ORM.

SQLAlchemy 1.4- The Transition

SQLAlchemy 1.4 implements the 2.0 architecture and feature set fully. However, it still provides cross-compatibility with SQLAlchemy 1.3, Python 2 support.

The major changes that are done are:

• 2.0 is Python 3 only and 1.4 still supports Python 2

• Engine changes-“emulated”, auto-commit is removed, “connectionless” execution is removed

• Result set changes- returns row objects that are completely tuple-like, many new features for iterating and slicing up rows

• The majority of Python computation involving SQL compilation is now cached and hence uses fewer CPU resources**.**

• ORM Query is unified with the select, update, and delete. All Core and ORM SELECT statements can be rendered by the select objects directly to obtain the results.

• The result set is unified between Core and ORM, results in both systems come back in the same way.

• Asynchio API for core and ORM is up and running, using a recently discovered approach to bridge Async/sync APIs

• It has support for SQL Regular Expression Operators.

DBAPI

The acronym DBAPI stands for “Python Database API Specification.”. This is a low-level API that is generally used in a Python application to talk to a database. Access to the database is done through connection objects. New constructors and type objects were added in the latest version 2.0 and also new constants and methods were added to provide better database bindings.

Dialects

SQLAlchemy uses the system known as dialects to communicate with various types of databases and DBAPI implementations. The dialects that are supported by the latest version are:

• PostgreSQL

• MySQL and MariaDB

• SQLite

• Oracle

• Microsoft SQL Server

However, the dialects require an appropriate DBAPI driver installation.

Key Features of SQLAlchemy:

• No ORM is required as the Core itself is considered a fully-featured toolkit and the ORM is built on the top of the Core.

• It has a high-performance architecture with a high level of maturity

• It is DBA Approved

• It has a pattern called “Unit Of Work” which keeps track of everything during the transaction

• The queries can be constructed based on Python functions

• User-defined classes can be mapped to database tables

• Supports Inheritance Mapping, Raw SQL Statement mapping

How SQLAlchemy is better than other ORMs:

There are many other ORM implementations written in Python. A few of them are Django ORM, SQLObject,  peewee, and  PonyORM. SQLAlchemy is one of the most famous Object Relational Mappers because it provides a data mapper pattern that allows user-defined Python classes to be mapped to the database tables using an object-oriented mechanism known as session. A few of the reasons for how SQLAlchemy is better are as follows:

• Open-Source and it has good community support.

• It has Object-Data Mapping

• It is framework-independent

• Supports different databases such as PostgreSQL, MySQL, and MariaDB, SQLite, Oracle, Microsoft SQL Server, etc.

Cons of using SQLAlchemy:

• Requires a lot of time to learn about the feature set

• Heavy-weight API

Use Cases:

Asyncio support

SQLAlchemy now supports Asyncio-compatible database drivers. It interacts with the database using the objects AsyncConnection for Core and AsyncSession for ORM. SQLAlchemy uses the greenlet library for internal execution to propagate async-await keywords from database drivers to user APIs.

Example 1:

This example illustrates the Asyncio engine/connection interface. The “create_async_engine ()” function creates an AsyncEngine that offers an async version of the traditional Engine. AsyncEngine provides an AsyncConnection through its AsyncEngine.connect () and AsyncEngine.begin () methods that provide managers with both asynchronous contexts. Then use the AsyncConnection.execute () method to provide AsyncConnection buffered results, or the AsyncConnection.stream () method to provide AsyncResult to the streaming server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import asyncio

from sqlalchemy
import Column
from sqlalchemy
import Integer
from sqlalchemy
import MetaData
from sqlalchemy
import String
from sqlalchemy
import Table
from sqlalchemy.ext.asyncio
import create_async_engine

meta = MetaData()
t1 = Table(
  "t1", meta, Column("id", Integer, primary_key = True), Column("name", String)
)

async def async_main():
  # creating an instance of AsyncEngine object as“ engine”
engine = create_async_engine(
  "postgresql+asyncpg://scott:tiger@localhost/test",
  echo = True,
)

# creating an instance“ conn” using“ AsyncConnection” object
async with engine.begin() as conn:

  await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)

await conn.execute(
  t1.insert(), [{
    "name": "some name 1"
  }, {
    "name": "some name 2"
  }]
)

async with engine.connect() as conn:

  result = await conn.execute(t1.select())

print(result.fetchall())

async_result = await conn.stream(t1.select())

async
  for row in async_result:
  print(row)

asyncio.run(async_main())

Example 2:
In this example, we are illustrating the usage of many to many relationships . We are creating a relationship between an “Order” and a Collection of “Item” objects via an association object called “OrderItem”. We are using foreign key constraints on the table OrderItem which links to the two tables “Order” and "Item”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
from datetime
import datetime

from sqlalchemy
import and_
from sqlalchemy
import Column
from sqlalchemy
import create_engine
from sqlalchemy
import DateTime
from sqlalchemy
import Float
from sqlalchemy
import ForeignKey
from sqlalchemy
import Integer
from sqlalchemy
import String
from sqlalchemy.ext.declarative
import declarative_base
from sqlalchemy.orm
import relationship
from sqlalchemy.orm
import Session

Base = declarative_base()

class Order(Base):
  __tablename__ = "order"

order_id = Column(Integer, primary_key = True)
customer_name = Column(String(30), nullable = False)
order_date = Column(DateTime, nullable = False,
  default = datetime.now())
order_items = relationship(
  "OrderItem", cascade = "all, delete-orphan", backref = "order"
)

def __init__(self, customer_name):
  self.customer_name = customer_name

class Item(Base):
  __tablename__ = "item"
item_id = Column(Integer, primary_key = True)
description = Column(String(30), nullable = False)
price = Column(Float, nullable = False)

def __init__(self, description, price):
  self.description = description
self.price = price

def __repr__(self):
  return "Item(%r, %r)" % (self.description, self.price)

class OrderItem(Base):
  __tablename__ = "orderitem"
order_id = Column(Integer, ForeignKey("order.order_id"), primary_key = True)
item_id = Column(Integer, ForeignKey("item.item_id"), primary_key = True)
price = Column(Float, nullable = False)

def __init__(self, item, price = None):
  self.item = item
self.price = price or item.price

item = relationship(Item, lazy = "joined")

if __name__ == "__main__":
  engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

session = Session(engine)

# create catalog
tshirt, mug, hat, crowbar = (
  Item("SA T-Shirt", 10.99),
  Item("SA Mug", 6.50),
  Item("SA Hat", 8.99),
  Item("MySQL Crowbar", 16.99),
)
session.add_all([tshirt, mug, hat, crowbar])
session.commit()

# create an order
order = Order("john smith")

# add three OrderItem associations to the Order and save
order.order_items.append(OrderItem(mug))
order.order_items.append(OrderItem(crowbar, 10.99))
order.order_items.append(OrderItem(hat))
session.add(order)
session.commit()

# query the order, print items
order = session.query(Order)
  .filter_by(customer_name = "john smith")
  .one()
print(
        [
            (order_item.item.description, order_item.price)
            for order_item in order.order_items
        ]
)

# print customers who bought 'MySQL Crowbar'
on sale
q = session.query(Order)
  .join("order_items", "item")
q = q.filter(
  and_(Item.description == "MySQL Crowbar", Item.price > OrderItem.price)
)

print([order.customer_name
  for order in q])