I think the problem is that you are trying to use ORM-level events (https://docs.sqlalchemy.org/en/latest/orm/events.html), but you aren't using the ORM to insert your data. Those events would fire if you created a Session, added your Message objects to that Session, and then flushed it. You are bypassing the ORM and using the insert() construct directly, so the events don't fire.
Unfortunately I don't think there's any way right now to use "ON CONFLICT DO UPDATE" from the ORM. There is Session.merge (https://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#merging), but that's an in-python check to see if the record already exists, rather than allowing the DB to do it. It might work for you, depending on how fast you need to process these messages. Simon On Fri, Oct 19, 2018 at 6:32 PM Chris Johnson <[email protected]> wrote: > > Python Version: 3.6.6 > SQLAlchemy version: 1.2.12 > PostgreSQL version: 10.5 > > This is my fist time using PGSQL over MySQL or SQLite and my first project > using SQLAlchemy over SQLite or the Django ORM. I'm also not very good at > programming in general so please forgive the simple questions. > > I have a script that I'm using to record incoming messages from an API and/or > a websocket, including edits to messages. The script is using upserts to save > data to the database. I tried adding an event listeners for updates and > inserts but the event listeners are not firing off their function when a > message is inserted or updated. Here is the test code I'm working with: > > class User(newBase): > __tablename__ = 'user' > __table_args__ = {'autoload':True} > class Message(newBase): > __tablename__ = 'message' > __table_args__ = {'autoload':True} > class Lobby(newBase): > __tablename__ = 'lobby' > __table_args__ = {'autoload':True} > class Community(newBase): > __tablename__ = 'community' > __table_args__ = {'autoload':True} > > > for table in tables: > for row in _yield_limit(olddb.query(table), table.frame): > logging.debug(row.frame) > messagedata = row.raw > message = wsrsi.sortframe(messagedata) > if message[0] == 'message': > message = message[1] > if message.iserased == True: > eraser = message.erasedby > eraserid = eraser.id > userInsert(eraser) > else: > eraser = None > eraserid = None > userInsert(message.author) > > > messageins = insert(Message).values( > id = message.id, > iserased = message.iserased, > erasedby = eraserid, > author = message.author.id, > body = message.body, > private = message.isprivate, > created = convTime(message.timecreated), > edited = convTime(message.timeedited), > lobby = message.lobbyid, > usermentions = message.mentions, > ) > > message_dict = { > c.name: c > for c in messageins.excluded > } > > > update_message = messageins.on_conflict_do_update( > index_elements = ["id"], > set_=message_dict > ) > > > try: > newdb.execute(update_message) > except IntegrityError as e: > print('Foreign key violation error, logging.') > with open('fkerrors.txt', 'a+') as f: > f.write(str(e)+'\n') > f.write(str(message.raw)) > f.write('\n================================\n') > f.close() > except ValueError as e: > if str(e) == 'A string literal cannot contain NUL (0x00) > characters.': > print('Null character found in message') > with open('nullerrors.txt', 'a+') as f: > f.write(str(e)+'\n') > f.write(str(message.raw)) > f.write('\n================================\n') > f.close > > For reference, `table` is an array of tables from an old MySQL database I've > inherited that I'm migrating to PGSQL. > > This is my event listener code: > > @listens_for(Message, 'before_update') > def saveHistory(mapper, connect, target): > historicaldata = dict(target) > print("SaveHistory triggered\n") > > > event.listen(Message, 'before_insert', saveHistory) > event.listen(Message, 'before_update', saveHistory) > > I've tried arranging this in several different ways including using the > decorator on the table classes but no matter how I structure this the event > listener does not fire on insert. I've done a lot of searching but I haven't > been able to find any information on using event listeners specifically with > PGSQL upserts so I don't know if the problem is with my code or if it's just > not supported when using upserts. Am I doing something wrong? > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
