8.9. SQLAlchemy Core Result

8.9. SQLAlchemy Core Result — Python

.all()
.first()
.one() - returns exactly one row
.one_or_none()

8.9.1. SetUp

>>> from sqlalchemy import create_engine, MetaData, Table, Column
>>> from sqlalchemy import Integer, String, Date, Numeric, Enum
>>> from sqlalchemy import select
>>>
>>>
>>> engine = create_engine('sqlite:///:memory:', future=True)
>>> metadata = MetaData()
>>>
>>> astronaut = Table('astronaut', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('firstname', String(50), nullable=False),
...     Column('lastname', String(50), nullable=False),
...     Column('agency', Enum('NASA', 'ESA', 'POLSA')),
...     Column('birthdate', Date),
...     Column('age', Integer),
...     Column('height', Numeric(3,2)),
...     Column('weight', Numeric(3,2)),
... )
>>>
>>> ASTRONAUTS = [
...     {'firstname': 'Mark', 'lastname': 'Watney'},
...     {'firstname': 'Melissa', 'lastname': 'Lewis'},
...     {'firstname': 'Rick', 'lastname': 'Martinez'},
... ]
>>>
>>> with engine.begin() as db:
...     metadata.create_all(db)
...     result = db.execute(astronaut.insert(), ASTRONAUTS)

8.9.2. List[tuple]

It will download all the data from database
This technique is not particularly efficient for large databases

>>> query = select(astronaut.c.firstname, astronaut.c.lastname)
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> list(result)
[('Mark', 'Watney'),
 ('Melissa', 'Lewis'),
 ('Rick', 'Martinez')]

8.9.3. List[dict]

It will download all the data from database
This technique is not particularly efficient for large databases

>>> query = select(astronaut.c.firstname, astronaut.c.lastname)
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> list(result.mappings())
[{'firstname': 'Mark', 'lastname': 'Watney'},
 {'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Rick', 'lastname': 'Martinez'}]

8.9.4. One

Must be exactly one result, otherwise the exception is raised
Exception MultipleResultsFound

>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where(astronaut.c.lastname == 'Watney')
... )
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.one()
('Mark', 'Watney')

8.9.5. One or None

>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where(astronaut.c.lastname == 'Watney')
... )
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.one_or_none()
('Mark', 'Watney')

8.9.6. All

>>> query = select(astronaut.c.firstname, astronaut.c.lastname)
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.all()
[('Mark', 'Watney'),
 ('Melissa', 'Lewis'),
 ('Rick', 'Martinez')]

8.9.7. First

Fetches the first result from a cursor object
CursorResult object has no attribute 'last'

>>> query = select(astronaut.c.firstname, astronaut.c.lastname)
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.first()
('Mark', 'Watney')

8.9.8. Columns

Result objects now supports slicing at the result level. We can SELECT some rows, and change the ordering and/or presence of columns after the fact using .columns() method [1]:

>>> query = (
...     select(astronaut).
...     order_by(astronaut.c.lastname)
... )
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> for lastname, firstname in result.columns('lastname', 'firstname'):
...     print(f'{lastname=}, {firstname=}')
...
lastname='Lewis', firstname='Melissa'
lastname='Martinez', firstname='Rick'
lastname='Watney', firstname='Mark'

Note, that the .columns() method defines the order for unpacked object. It overwrites the default ordering from SELECT clause.

8.9.9. Scalars

When you have a row, but there is only one column that you care about
We don't want the rows back, we want a list of values

A single column from the results can be delivered without using rows by applying the .scalars() modifier. This accepts and optional column name, or otherwise assumes the first column:

>>> query = (
...     select(astronaut.c.firstname).
...     order_by(astronaut.c.lastname)
... )
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.scalars('firstname').all()
['Melissa', 'Rick', 'Mark']

Note, that for performance reasons we narrowed down the SELECT clause only to those values we want to receive.