8.13. SQLAlchemy Core Text
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!