Understanding MetaData() from SQLAlchemy in Python

Question

I am trying to understand what the MetaData() created object is in essence. It is used when reflecting and creating databases in Python (using SQLAlchemy package).

Consider the following working code:

/ with preloaded Engine(sqlite:///chapter5.sqlite) and metadata = MetaData(): when I call metadata in the console, it returns 'MetaData(bind=None)' /

# Import Table, Column, String, and Integer
from sqlalchemy import Table, Column, String, Integer

# Build a census table: census
census = Table('census', metadata,
               Column('state', String(30)),
               Column('sex', String(1)),
               Column('age', Integer()),
               Column('pop2000', Integer()),
               Column('pop2008',Integer()))

# Create the table in the database
metadata.create_all(engine)

Of course by typing type(metadata) I get exactly what type of object metadata is: sqlalchemy.sql.schema.MetaData. In SQLAlchemy documentation it is written

MetaData is a container object that keeps together many different features of a database (or multiple databases) being described.

However, I am confused, because in the code we only create a table that "points" to metadata. After that, when we call the create_all method on metadata (empty by far), pointing to the database (which is pointed by engine).

Probably my question is silly, but:

How does python exactly connect these instances? Probably the declaration of the census table links metadata to the column names in a two-sided way.

---

Note: The code is from an exercise from datacamp course.

Answer 1

I think you asked how does python (SQLAlchemy you presumably mean) connect the table to the metadata and the metadata to the database and engine.

So database tables in SQLAlchemy belong (are linked to) a metadata object. The table adds itself to the metadata; there is a tables property on the metadata object that acts a lot like a list:

rue, nullable=False), Column('host_id', Integer(), ForeignKey('slots.id'), table=, nullable=False), Column('active', Boolean(), table=), Col umn('port', Integer(), table=, nullable=False), Column('description', String(length=120), table=), Column('username', String(length=40), tab le=), Column('password', String(length=40), table=), schema=None), 'network_location_associations': Table('network_location_associations', M etaData(bind=None), Column('network_id', Integer(), ForeignKey('networks.id'), table=), Column('location_id', Integer(), ForeignKey('locations. id'), table=), schema=None), 'machines': Table('machines', MetaData(bind=None), Column('id', Integer(), ForeignKey('items.id'), table=

, primary_key=True, nullable=False), Column('eth0', String(), table=), Column('eth1', String(), table=), Column('eth2', String(), table=), Colu mn('eth3', String(), table=), Column('wlan0', String(), table=), Column('ipmi', String(), table=), schema=None), 'machine_profiles': Table('mach ine_profiles', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('DisplayPort', Integer(), table=), Column('HDMI', Integer(), table=), Column('RAM', String(length=10), table=), schema=None), 'geometry': Table('geometry', MetaData(b ind=None), Column('slot_id', Integer(), ForeignKey('slots.id'), table=, primary_key=True, nullable=False), Column('room_id', Integer(), ForeignKey('rooms.id'), tabl e=, nullable=False), Column('x_mm', Float(), table=, nullable=False), Column('y_mm', Float(), table=, nullable=False), Column('z_mm', Float(), t able=, nullable=False), Column('rotation_deg', Float(), table=, nullable=False), Column('tilt_deg', Float(), table=, nullable=False), Column('ro ll_deg', Float(), table=, nullable=False), Column('on_floor', Boolean(), table=, nullable=False), schema=None), 'publicaddresses': Table('publicaddresses' , MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('ip', Integer(), table=, nullable=False), C olumn('slot_id', Integer(), ForeignKey('slots.id'), table=, nullable=False), schema=None), 'connections': Table('connections', MetaData(bind=None), Column('i d', Integer(), table=, primary_key=True, nullable=False), Column('src_slot_id', Integer(), ForeignKey('slots.id'), table=), Column('src_index', Inte ger(), table=), Column('src_type', Enum('HDMI', 'DisplayPort', 'miniDP', 'VGA', 'DVI', 'Power', 'CAT6', 'WallNet', 'Hybrid', 'UnknownVideo'), table= ), Column('dst_slot_id', Integer(), ForeignKey('slots.id'), table=), Column('dst_index', Integer(), table=), Column('dst_type', Enum('HDMI', 'Displa yPort', 'miniDP', 'VGA', 'DVI', 'Power', 'CAT6', 'WallNet', 'Hybrid', 'UnknownVideo'), table=), schema=None), 'types': Table('types', MetaData(bind=None), Column ('type', Integer(), table=, primary_key=True, nullable=False), Column('name', String(length=60), table=), schema=None), 'roles': Table('roles', MetaData(bind=No ne), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('name', String(), table=, nullable=False), Column('description', String(), table= , nullable=False), Column('display_driver', Boolean(), table=, nullable=False), schema=None), 'rooms': Table('rooms', MetaData(bind=None), Column('id', Integer( ), table=, primary_key=True, nullable=False), Column('location_id', Integer(), ForeignKey('locations.id'), table=, nullable=False), Column('parent_id', Integer( ), table=), Column('name', String(length=50), table=), Column('x_mm', Float(), table=, nullable=False), Column('y_mm', Float(), table=, nullable=F alse), Column('z_mm', Float(), table=, nullable=False), Column('rotation_deg', Float(), table=, nullable=False), Column('width_mm', Float(), table=, null able=False), Column('height_mm', Float(), table=, nullable=False), Column('depth_mm', Float(), table=, nullable=False), Column('has_workstations', Boolean(), ta ble=, nullable=False), schema=None), 'displays': Table('displays', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Co lumn('hostname', String(length=100), table=), Column('formation', Enum('2x3', '1x2', 'corkboard', 'desktop', 'desktop-shared'), table=, nullable=False), s chema=None), 'slots': Table('slots', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('location_id', Integer(), ForeignKe y('locations.id'), table=, nullable=False), Column('hostname', String(), table=), Column('item_id', Integer(), ForeignKey('items.id'), table=), Column('r ole_id', Integer(), ForeignKey('roles.id'), table=), Column('parent_id', Integer(), ForeignKey('slots.id'), table=), Column('ip', Integer(), table=), Col umn('ip_ipmi', Integer(), table=), Column('ip_wlan', Integer(), table=), Column('optional', Boolean(), table=), Column('notes', String(), table=), Column('classification', Enum('U', 'S', 'TS'), table=), Column('os', String(), table=), Column('release', String(), table=), Column('track', String(), t able=), Column('uuid', Binary(), table=), Column('displaydata', String(), table=), schema=None)})

len()models.Base.metadata.tables File "", line 1 len()models.Base.metadata.tables ^ SyntaxError: invalid syntax len(models.Base.metadata.tables) 22

The reason you need the metadata object is:

• To have a single unit of work for creating and dropping related tables

• To have a place to collect all the results of a reflection operation

• To sort related tables based on their dependencies so that foreign key constraints can be created in the right order.

So, the metadata object contains SQLAlchemy'sidea of what it thinks a database might look like. It's typically populated either from reflection or from you creating table objects (possibly through the declarative base extension).

You can directly associate a metadata object with a real database engine by setting the bind parameter in the metadata constructor. Alternitevly, you can make the link when you use the metadata either in create calls or in reflection calls.