8

This example shows how to use it with "non-declarative" - http://docs.sqlalchemy.org/en/latest/core/ddl.html#sqlalchemy.schema.DDL

How can I use it with the ORM declarative syntax?

For example, with this structure:

Base = declarative_base(bind=engine)     
class TableXYZ(Base):
    __tablename__ = 'tablexyz'
Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
Richard Green
  • 2,037
  • 2
  • 20
  • 38

2 Answers2

10

Silly example, but think this is what you're looking for, should get you going:

from sqlalchemy import event
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session
from sqlalchemy.schema import Column, DDL
from sqlalchemy.types import Integer

Base = declarative_base()
engine = create_engine('sqlite:////tmp/test.db', echo=True)

class TableXYZ(Base):
    __tablename__ = 'tablexyz'
    id = Column(Integer, primary_key=True)

#event.listen(
#   Base.metadata, 'after_create',
#   DDL("""
#   alter table TableXYZ add column name text
#   """)

event.listen(
    TableXYZ.__table__, 'after_create',
    DDL("""
    alter table TableXYZ add column name text
    """)
)
Base.metadata.create_all(engine)

Running the above results in - note "name text" for the added column:

sqlite> .schema tablexyz
CREATE TABLE tablexyz (
    id INTEGER NOT NULL, name text, 
    PRIMARY KEY (id)
);

I have my code in declarative and use the event.listen to add triggers and other stored procedures. Seems to work well.

Kijewski
  • 25,517
  • 12
  • 101
  • 143
Tony Gibbs
  • 2,369
  • 1
  • 19
  • 18
  • That's a good first crack... but I would really appreciate finer granularity on the creation -ie (and I haven't run your code yet) but I would imagine that it will fire the DDL after any table in the metadata list has been created, whereas I would prefer to do it on a table by table level (as I may amend some of my schema and don't want to drop and recreate every single trigger on every single table). On the + side .. you have at least proposed a working option ... – Richard Green Aug 23 '12 at 09:47
  • Added an example of some finer grain control (table level). – Tony Gibbs Aug 23 '12 at 16:46
1

It should be the same with "non-declarative" and "declarative".

You register your event by specifying (with your class and the doc's event & function) :

event.listen(TableXYZ, 'before_create', DDL('DROP TRIGGER users_trigger'))

Syntax is something like:

event.listen(Class, 'name_of_event', function)
b4stien
  • 1,810
  • 13
  • 14