TopolearnTable metadata is what lets SQLAlchemy generate correct SQL for any database instead of hand-written strings.
In Core you describe your schema as Python objects. A MetaData object is a collection of tables; each Table has a name and Columns with types such as Integer, String(50), DateTime or ForeignKey("users.id").
With the metadata in place, metadata.create_all(engine) issues CREATE TABLE for every table that doesn't exist yet. It's handy for tests and prototypes; real projects evolve the schema with migrations (Alembic), because create_all never alters an existing table.
The payoff is the SQL expression language. Instead of strings you build statements from table objects: insert(users).values(name="ada"), select(users).where(users.c.name == "ada"). Columns live under table.c, and comparing a column with a value produces a SQL expression, not a bool. SQLAlchemy turns these into dialect-specific SQL with bound parameters, so quoting, parameter style and dialect differences are handled for you.
print(stmt)shows the SQL a statement will produce, useful for debugging.conn.execute(insert(users), [dict, dict])performs an efficient executemany.- Results from
select()are the sameRowobjects you get fromtext().
Many applications use only Core, especially for reporting or bulk data work. If you use the ORM, the declarative models in the next lesson create these same Table objects for you behind the scenes.
Example
from sqlalchemy import (MetaData, Table, Column, Integer, String,
create_engine, insert, select)
metadata = MetaData()
users = Table("users", metadata,
Column("id", Integer, primary_key=True),
Column("name", String(50), nullable=False))
engine = create_engine("sqlite://")
metadata.create_all(engine)
with engine.begin() as conn:
conn.execute(insert(users), [{"name": "ada"}, {"name": "grace"}])
stmt = select(users.c.id, users.c.name).where(users.c.name == "grace")
print(stmt)
# SELECT users.id, users.name
# FROM users
# WHERE users.name = :name_1
with engine.connect() as conn:
print(conn.execute(stmt).all()) # [(2, 'grace')]Remember
- MetaData holds Table objects; Columns carry types and constraints
- Columns live under table.c and comparisons build SQL expressions
- create_all creates missing tables but never alters existing ones
- print(stmt) shows the generated SQL
Watch out for
- Relying on create_all to apply schema changes to an existing database.