SQLALCHEMY: how to use @events.listens_for in flask_sqlalchemy-SQLalchemy

Question

i need some help in automating some tasks in sqlalchemy. when a new user registers on my website i need 5 personnel to be added.

i would like to use the sqlalchemy events because if i can get it to work it will come in handy later for other automations.

i am following Mr Miguel Grinberg's Tutorial on how to make a flask application and to define my models (db.Model not Base).

i read that to use @event.listens_for(User.__table__, "after_insert") i have to use a declarative base. is there a way around this? My model structure involves close to 15 tables!

  class User(db.Model):
        __tablename__ = "user"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True)
        email = db.Column(db.String(64), unique=True, index=True)
        username = db.Column(db.String(64), unique=True, index=True)
        password_hash = db.Column(db.String(128))
        store = db.relationship('Store', uselist=False, backref="user")



@event.listens_for(User.__table__, "after_insert")
def add_personnel(*args, **kwargs):
    for i in range(5):
        db.session.add(Personnel,store_id = User.query.last().id)
    db.session.commit()
    return None

class Personnel(db.Model):
    __tablename__ = "personnel"
    id = db.Column(db.Integer, primary_key=True)
    store_id = db.Column(db.Integer, db.ForeignKey('store.id'))
    first_name = db.Column(db.String(64))
    last_name = db.Column(db.String(64))
    username = db.Column(db.String(64))
    cell_phone = db.Column(db.Integer)
    pin_number = db.Column(db.Integer)
    email = db.Column(db.String(64))
    orders = db.relationship("Order", backref='personnel', lazy='dynamic')

**TRACEBACK**



    Traceback (most recent 
    call last):
      File "manage.py", line 3, in <module>
        from app.models import User
      File "/home/#######/PycharmProjects/#######/app/models.py", line 62, in <module>
        @event.listens_for(User.__table__, "after_insert")
      File "/home/#######/PycharmProjects/#######//venv/local/lib/python2.7/site-packages/sqlalchemy/event/api.py", line 124, in decorate
        listen(target, identifier, fn, *args, **kw)
      File "/home/#######/PycharmProjects/#######/venv/local/lib/python2.7/site-packages/sqlalchemy/event/api.py", line 89, in listen
        _event_key(target, identifier, fn).listen(*args, **kw)
      File "/home/#######/PycharmProjects/#######/venv/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 194, in listen
        dispatch_collection = getattr(target.dispatch, identifier)
      File "/home/#######/PycharmProjects/#######/venv/local/lib/python2.7/site-packages/sqlalchemy/event/base.py", line 95, in __getattr__
        raise AttributeError(name)
    AttributeError: after_insert

Answer 1

I got i working just like this using the connection from the arguments.

@db.event.listens_for(Order, "after_insert")
def insert_order_to_printer(mapper, connection, target):
    po = PrinterOrder.__table__
    connection.execute(po.insert().values(store_id=target.store_id, order_id=target.id, scenario=target.order_type))

where order is:

class Order(db.Model):
    #stuff

Answer 2

I got it. There are two models here. Both inherit db.Model. to insert data into InventoryContents i run the event defined bellow. Now, if this is not 100% proper usage of the @db.event.listens_for i apologize. But for now it works as expected.

class MenuContentOrderEntry(db.Model):
    __tablename__ = "menu_content_order_entry"
    id = db.Column(db.Integer, primary_key=True)

    store_id = db.Column(db.Integer, db.ForeignKey('store.id'))
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
    checkout_id = db.Column(db.Integer, db.ForeignKey('checkout.id'), default=None)

    menu_item_content_id = db.Column(db.Integer, db.ForeignKey('menu_item_content.id'))
    menu_item_id = db.Column(db.Integer, db.ForeignKey('menu_item.id'))
    # Todo 24/3/2017 - add name to the contents in case a user deletes it.
    content_name = db.Column(db.String(64), default=None)

    menu_item_order_id = db.Column(db.Integer, db.ForeignKey('menu_item_order_entry.id'))

    # this indicates whether we will print the order or not.
    # ignore: 0, print: 1, do not print:2
    print_on_pos = db.Column(db.Integer, default=0)

    datetime = db.Column(db.String(64), default=func.now(), nullable=False)

    # help full columns for inventory
    quantity = db.Column(db.Integer, default=0)
    default = db.Column(db.Integer, default=None)
    changed = db.Column(db.Integer, default=None)

class InventoryContents(db.Model):
    """

    This model will show the inventory changes made. Purchases and in-store consumption.
    A payee can be a waiter (in-store) or a supplier.

    """

    # Todo 14/2/2017 - add some sort of order_date and received_date.

    id = db.Column(db.Integer, primary_key=True)
    store_id = db.Column(db.Integer, db.ForeignKey('store.id'))
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))

    # if an item belongs to a checkout id then it was consumed and money was collected for it.
    checkout_id = db.Column(db.Integer, db.ForeignKey('checkout.id'), default=None)

    name = db.Column(db.String(64))

    datetime = db.Column(db.String(64))  # Todo 14/2/2017 - convert to datetime

    payee = db.Column(db.String(64))

    units = db.Column(db.String(64))  # [-], kg, L, ml

    cost = db.Column(db.Float)  # per unit

    quantity = db.Column(db.Float)  # per unit

    revenue = db.Column(db.Float)


@db.event.listens_for(MenuContentOrderEntry, "after_insert")
def add_content_to_inventory_contents(mapper, connection, target):
    inv = InventoryContents.__table__
    content = MenuItemContent.query.filter_by(id=target.menu_item_content_id).first()
    order = Order.query.filter_by(id=target.order_id).first()
    waiter_id = order.personnel_id
    date_time = order.datetime
    waiter_username = Personnel.query.filter_by(id=waiter_id).first().username
    content_name = content.name


    menu_association = MenuAssociation.query.filter_by(
        menu_content_id=target.menu_item_content_id,
        menu_item_id=target.menu_item_id).first()

    default = target.default
    changed = target.changed

    flag = False
    if (default == 1) and (changed == 1):
        flag = False
    elif (default == 0) and (changed == 0):
        flag = False
    elif (default == 1) and (changed == 0):
        flag = True
    elif (default == 0) and (changed == 1):
        flag = True
    else:
        pass
    print(flag)
    if not flag:
        pass
    else:
        # Todo 24/3/2017 - add those that are used.
        connection.execute(inv.insert().values(store_id=target.store_id,
                                               name=content_name,
                                               order_id=target.order_id,
                                               payee=waiter_username,
                                               datetime=date_time,
                                               cost=menu_association.cost,
                                               quantity=target.quantity))

Answer 3

You actually can do this, just by typing @event.listens_for(User, "after_insert") as described in this answer.

Note that you need to use SQLAlchemy Connection instead of sessions as you used in your sample event. Check this answer, it has great examples of processing database inserts.