>>> from sqlalchemy import update

8.13.1. SetUp

>>> from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
>>> 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)),
... )
>>>
>>> mission = Table('mission', metadata,  # one to many relationship
...     Column('id', Integer, primary_key=True),
...     Column('astronaut_id', ForeignKey('astronaut.id'), nullable=False),
...     Column('year', Integer, nullable=False),
...     Column('name', String(50), nullable=False),
... )
>>>
>>> ASTRONAUTS = [
...     {'firstname': 'Mark', 'lastname': 'Watney'},
...     {'firstname': 'Melissa', 'lastname': 'Lewis'},
...     {'firstname': 'Rick', 'lastname': 'Martinez'},
... ]
>>>
>>> MISSIONS = [
...     {'astronaut_id': 1, 'year': 2035, 'name': 'Ares3'},
...     {'astronaut_id': 2, 'year': 2030, 'name': 'Ares1'},
...     {'astronaut_id': 2, 'year': 2035, 'name': 'Ares3'},
...     {'astronaut_id': 3, 'year': 2035, 'name': 'Ares3'},
... ]
>>>
>>> with engine.begin() as db:
...     metadata.create_all(db)
...     result = db.execute(astronaut.insert(), ASTRONAUTS)
...     result = db.execute(mission.insert(), MISSIONS)

8.13.2. Constructs

  • SQL Expression language constructs: select, where, join, ...

  • Composability - we can build and rearrange SQL using Python objects

  • Database agnosticism - query will run on lots of different backends

  • Support for refactoring

>>> from sqlalchemy import select
>>>
>>>
>>> query = select(astronaut)
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.all()
[(1, 'Mark', 'Watney', None, None, None, None, None),
 (2, 'Melissa', 'Lewis', None, None, None, None, None),
 (3, 'Rick', 'Martinez', None, None, None, None, None)]

8.13.3. Text

  • If you have perfect query - use text()

  • Usually for more complex queries that's very specific

  • It can be changed to SQL constructs later if needed

  • Works with the ORM too

>>> from sqlalchemy import text
>>>
>>>
>>> query = text('SELECT * FROM astronaut')
>>>
>>> with engine.begin() as db:
...     result = db.execute(query)
>>>
>>> result.all()
[(1, 'Mark', 'Watney', None, None, None, None, None),
 (2, 'Melissa', 'Lewis', None, None, None, None, None),
 (3, 'Rick', 'Martinez', None, None, None, None, None)]

Note, use bound parameters for variables that change (user input) in oder to avoid SQL injection. Do not ever concatenate user input to SQL queries!

8.13.4. References