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 3 Answers
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 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.
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)) 
