0

I am working with SQLAlchemy and I am getting confused between Engine, Metadata, Base, and Sessions.

I did find some useful links: here, here and here, but I think it would help to know why the code below is not working.

import sqlalchemy as sqlalc
from sqlalchemy import Column, INTEGER, Integer,ForeignKey, Table, VARCHAR, TIMESTAMP, MetaData, create_engine, inspect, cast, select, SmallInteger
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

url = 'mysql://info@127.0.0.1/info'
engine = create_engine(url, echo=True)

metadata = MetaData()
metadata.bind = engine
metadata.create_all(engine)

connection = engine.connect()

Base = declarative_base()

Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

When I execute, I get the following error:

sqlalchemy.exc.UnboundExecutionError: MetaData object is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against.
Clay Campbell
  • 168
  • 13

1 Answers1

2

Definitions

  • Engine: an abstraction for a database connection, or connection pool
  • Metadata: an object that knows about database objects, primarily tables
  • Base: the base class for ORM models
  • Session: an object that keeps track of new, removed and changed ORM model instances while they are in use; it sends changes to the database (Session.commit()) or reverts them (Session.rollback())

Code

This code

metadata = MetaData()
metadata.bind = engine
metadata.create_all(engine)

"works" because you have bound (or associated) an engine with your MetaData instance: the engine enables connection to a database to create any tables that have been registered with the MetaData instance. However you should be aware that binding like this is deprecated in SQLAlchemy v1.4. The preferred approach is to pass the engine to create_all like this:

metadata = MetaData()
metadata.create_all(engine)

This code

Base = declarative_base()
Base.metadata.create_all()

does not work because there is no engine associated with Base.metadata. As before, the preferred approach is to pass an engine to create_all:

Base.metadata.create_all(engine)

In both cases, executing create_all will do nothing unless tables have been registered with the metadata instances. For core, this usually happens through table definitions:

metadata = MetaData()
t = Table(
    't',
    metadata, # <- this will associate the table with the metadata
    Column(...),
)

In the ORM, (using declarative mapping), the association is made by inheriting from Base:

Base = orm.declarative_base()


class MyModel(Base): # <- this will register mytable with Base's metadata
    __tablename__ = 'mytable'
    ...
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153