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

db.Model is a declarative base.

univerio
– univerio

2016-12-20 21:47:18 +00:00
Commented Dec 20, 2016 at 21:47 — univerio
– univerio, Commented Dec 20, 2016 at 21:47

George Pamfilis · Accepted Answer · 2017-07-17 15:20:40Z

10

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

answered Jul 17, 2017 at 15:20

George Pamfilis

1,5072 gold badges21 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mahmoud Magdy Over a year ago

for update connection.execute(po.update().values(scenario=target.order_type))

Community · Accepted Answer · 2017-05-23 11:46:40Z

2

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.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Dec 21, 2016 at 8:03

Sergey Shubin

3,2684 gold badges27 silver badges40 bronze badges

Comments

George Pamfilis · Accepted Answer · 2017-03-25 17:38:30Z

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))

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related