8.8. SQLAlchemy Core Select

Important

Method chaining
Note the dot . at the end of the select line

✘>>> from sqlalchemy import select

8.8.1. SetUp

✘>>> from sqlalchemy import create_engine, MetaData, Table, Column
>>> from sqlalchemy import Integer, String, Date, Numeric, Enum
>>> from sqlalchemy import select, or_
>>>
>>>
>>> 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:
...     astronaut.create(db)
...     result = db.execute(astronaut.insert(), ASTRONAUTS)

8.8.2. Select All Columns

✘>>> query = select(astronaut)
>>>
>>> print(query)
SELECT astronaut.id, astronaut.firstname, astronaut.lastname, astronaut.agency, astronaut.birthdate, astronaut.age, astronaut.height, astronaut.weight
FROM astronaut

8.8.3. Select Specified Columns

✘>>> query = select(astronaut.c.firstname, astronaut.c.lastname)
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut

8.8.4. Where Clause

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where(astronaut.c.firstname == 'Mark')
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
WHERE astronaut.firstname = :firstname_1

8.8.5. Where OR

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where(or_(astronaut.c.firstname == 'Mark',
...               astronaut.c.firstname == 'Melissa'))
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
WHERE astronaut.firstname = :firstname_1 OR astronaut.firstname = :firstname_2

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where((astronaut.c.firstname == 'Mark')
...         | (astronaut.c.firstname == 'Melissa'))
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
WHERE astronaut.firstname = :firstname_1 OR astronaut.firstname = :firstname_2

8.8.6. Where AND

Multiple where() clauses are automatically joined by AND

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where(astronaut.c.firstname == 'Mark').
...     where(astronaut.c.lastname == 'Watney')
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
WHERE astronaut.firstname = :firstname_1 AND astronaut.lastname = :lastname_1

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     where((astronaut.c.firstname == 'Mark')
...         & (astronaut.c.lastname == 'Watney'))
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
WHERE astronaut.firstname = :firstname_1 AND astronaut.lastname = :lastname_1

8.8.7. Order By

✘>>> query = (
...     select(astronaut.c.firstname, astronaut.c.lastname).
...     order_by(astronaut.c.lastname)
... )
>>>
>>> print(query)
SELECT astronaut.firstname, astronaut.lastname
FROM astronaut
ORDER BY astronaut.lastname

8.8.8. References