Re: [sqlalchemy] Injecting User info into _history table to track who performed the change
Going beyond what Simon did.. I typically make make a table like `user_transaction`, which has all of the relevant information for the transaction: * User ID * Timestamp * Remote IP Using the sqlalchemy hooks, I'll then do something like: * update the object table with the user_transaction id or * use an association table that tracks a user_transaction_id to an object id and version FYI, Simon -- as of a few weeks ago, that pattern is now part of the pyramid sqlalchemy starter template! On Monday, March 15, 2021 at 6:46:02 AM UTC-4 Simon King wrote: > I use pyramid as a web framework, and when I create the DB session for > each request, I add a reference to the current request object to the > DB session. The session object has an "info" attribute which is > intended for application-specific things like this: > > > https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.info > > Then, in the before_flush event handler, I retrieve the request object > from session.info, and then I can add whatever request-specific info I > want to the DB. > > Simon > > On Sun, Mar 14, 2021 at 4:05 PM JPLaverdure wrote: > > > > Hi Elmer, > > > > Thanks for your reply ! > > My issue is not with obtaining the info I want to inject (the logged in > users's email), I already have that all ready to go :) > > > > My whole database is versioned using the history_meta.py example from > SQLAlchemy > > > https://docs.sqlalchemy.org/en/13/_modules/examples/versioned_history/history_meta.html > > > > I was hoping for a simple way to inject the user info into the _history > row creation steps. > > > > The SQLAlchemy example makes use of this event listener: > > > > def versioned_session(session): > > > > @event.listens_for(session, "before_flush") > > def before_flush(session, flush_context, instances): > > for obj in versioned_objects(session.dirty): > > create_version(obj, session) > > for obj in versioned_objects(session.deleted): > > create_version(obj, session, deleted=True) > > > > So I'm tempted to follow the same strategy and just override this > listener to supplement it with the user info but I'm wondering how to pass > in non SQLAlchemy info into its execution context... > > > > So basically, I have the info I want to inject, I'm just not sure how to > pass it to SQLAlchemy > > > > Thanks, > > > > JP > > > > On Friday, March 12, 2021 at 6:55:19 p.m. UTC-5 elmer@gmail.com > wrote: > >> > >> Hi JP, > >> > >> Depending on how you've implemented your history tracking, that routine > is quite far removed from your web framework and getting a neat, clean way > of dealing with that might not be within reach. > >> > >> However, most web frameworks have some concept of a threadlocal request > (or function to retrieve it), which you could invoke and if such a request > exists, you could use that to load whatever user identity you have > available on there (again, the details differ, but this tends to be a > shared feature). From there you can store the user either as a foreign key, > or a unique identifier like email. Which one you pick would depend on how > you want the history to be affected when you delete a user record for > example. > >> > >> > >> > >> On Fri, Mar 12, 2021 at 11:58 PM JPLaverdure > wrote: > >>> > >>> Hello everyone, > >>> > >>> We already have the ability to timestamp the creation of the history > row, but it would also be interesting to be able to track the user > responsible for the content update. > >>> I would like to get suggestions on the best way to achieve this. > >>> > >>> I realize this is somewhat outside the scope of sqlalchemy as the > notion of a "logged in user" is more closely related to the context of the > app/webapp using SQLAlchemy as its ORM but maybe other people would benefit > from having a way to inject arbitrary data in the history table. > >>> > >>> Ideally, I would like the insert in the _history table to be atomic, > so I feel like hooking an update statement to an event might not be the way > to go. > >>> I'm tempted to modify the signature of before_flush but I'm not sure > where it gets called. > >>> > >>> Any help is welcome ! > >>> Thanks > >>> > >>> JP > >>> > >>> -- > >>> 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 sqlalchemy+...@googlegroups.com. > >>> To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/82a24998-14e1-4ff4-a725-dd25c20a8bf2n%40googlegroups.com > . > >> > >> > >> > >> -- > >> > >> Elmer > > > > -- > > SQLAlchemy - > > The
Re: [sqlalchemy] Injecting User info into _history table to track who performed the change
Ohhh, that sounds perfect ! And since I already make use of class based views, that should be dead simple to put in place. Thanks for the support, guys Cheers, JP On Mon, 15 Mar 2021 at 06:46, Simon King wrote: > I use pyramid as a web framework, and when I create the DB session for > each request, I add a reference to the current request object to the > DB session. The session object has an "info" attribute which is > intended for application-specific things like this: > > > https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.info > > Then, in the before_flush event handler, I retrieve the request object > from session.info, and then I can add whatever request-specific info I > want to the DB. > > Simon > > On Sun, Mar 14, 2021 at 4:05 PM JPLaverdure > wrote: > > > > Hi Elmer, > > > > Thanks for your reply ! > > My issue is not with obtaining the info I want to inject (the logged in > users's email), I already have that all ready to go :) > > > > My whole database is versioned using the history_meta.py example from > SQLAlchemy > > > https://docs.sqlalchemy.org/en/13/_modules/examples/versioned_history/history_meta.html > > > > I was hoping for a simple way to inject the user info into the _history > row creation steps. > > > > The SQLAlchemy example makes use of this event listener: > > > > def versioned_session(session): > > > > @event.listens_for(session, "before_flush") > > def before_flush(session, flush_context, instances): > > for obj in versioned_objects(session.dirty): > > create_version(obj, session) > >for obj in versioned_objects(session.deleted): > > create_version(obj, session, deleted=True) > > > > So I'm tempted to follow the same strategy and just override this > listener to supplement it with the user info but I'm wondering how to pass > in non SQLAlchemy info into its execution context... > > > > So basically, I have the info I want to inject, I'm just not sure how to > pass it to SQLAlchemy > > > > Thanks, > > > > JP > > > > On Friday, March 12, 2021 at 6:55:19 p.m. UTC-5 elmer@gmail.com > wrote: > >> > >> Hi JP, > >> > >> Depending on how you've implemented your history tracking, that routine > is quite far removed from your web framework and getting a neat, clean way > of dealing with that might not be within reach. > >> > >> However, most web frameworks have some concept of a threadlocal request > (or function to retrieve it), which you could invoke and if such a request > exists, you could use that to load whatever user identity you have > available on there (again, the details differ, but this tends to be a > shared feature). From there you can store the user either as a foreign key, > or a unique identifier like email. Which one you pick would depend on how > you want the history to be affected when you delete a user record for > example. > >> > >> > >> > >> On Fri, Mar 12, 2021 at 11:58 PM JPLaverdure > wrote: > >>> > >>> Hello everyone, > >>> > >>> We already have the ability to timestamp the creation of the history > row, but it would also be interesting to be able to track the user > responsible for the content update. > >>> I would like to get suggestions on the best way to achieve this. > >>> > >>> I realize this is somewhat outside the scope of sqlalchemy as the > notion of a "logged in user" is more closely related to the context of the > app/webapp using SQLAlchemy as its ORM but maybe other people would benefit > from having a way to inject arbitrary data in the history table. > >>> > >>> Ideally, I would like the insert in the _history table to be atomic, > so I feel like hooking an update statement to an event might not be the way > to go. > >>> I'm tempted to modify the signature of before_flush but I'm not sure > where it gets called. > >>> > >>> Any help is welcome ! > >>> Thanks > >>> > >>> JP > >>> > >>> -- > >>> 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 sqlalchemy+...@googlegroups.com. > >>> To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/82a24998-14e1-4ff4-a725-dd25c20a8bf2n%40googlegroups.com > . > >> > >> > >> > >> -- > >> > >> Elmer > > > > -- > > 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
Re: [sqlalchemy] Using Abstract Base Classes with ORM Table Classes
you no longer have to use DeclarativeMeta at all, you can use a class decorator: https://docs.sqlalchemy.org/en/14/orm/mapping_styles.html#declarative-mapping-using-a-decorator-no-declarative-base if you are on 1.3, there's a way to get the same effect in 1.3 using the instrument_declarative function: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html?highlight=instrument_declarative#sqlalchemy.ext.declarative.instrument_declarative which can be turned into an equivalent decorator. that said I have not yet experimented with mapping classes that are also extending ABCMeta so I'm not sure if there are other issues. On Sun, Mar 14, 2021, at 8:53 PM, Richard Damon wrote: > I have a lot of tables that have some similar functionality that I would > like to factor out into a base mix-in class that provides some common > methods. Some of these methods will want to use a method that must be > defined in the actual table ORM class, and would be an abstract method > in the base. If you just blindly do this you get the Python error of > multiple metaclasses, so I need to define a metaclass that inherets from > both ABCMeta and DeclarativeMeta, and then the Table ORM classes need to > mention declarative_base, the mixin and metaclass=mymeta. This works but > looks wordy. > > If instead I try to put the mix-in between declarative_base and the > table class in the heirarchy, SQLAlchemy complains that it is missing > information for it to be a table (which is correct). If I put it as a > base to declarative_base the SQLAlchemy gets errors that it needs to > implement the abstract methods (and I of course can only do this once). > > I could make the mixin not use the ABCMeta as its metaclass, but then if > I forget to define the abstract method in the table, I get no > complaints, at best I could catch the call to the abstract method > because it wasn't overridden. > > Is this the way it is supposed to work, or am I missing some other trick? > > Side question, when doing this sort of mix-in, does the order of the > mix-in and declarative_base matter, or is there a real preference? > > -- > Richard Damon > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e8d4c401-95fe-957f-7d65-3e37cd5150c6%40Damon-Family.org. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1f4f4ffc-d7dc-4ead-b031-a1e77c13586a%40www.fastmail.com.
Re: [sqlalchemy] Injecting User info into _history table to track who performed the change
I use pyramid as a web framework, and when I create the DB session for each request, I add a reference to the current request object to the DB session. The session object has an "info" attribute which is intended for application-specific things like this: https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.info Then, in the before_flush event handler, I retrieve the request object from session.info, and then I can add whatever request-specific info I want to the DB. Simon On Sun, Mar 14, 2021 at 4:05 PM JPLaverdure wrote: > > Hi Elmer, > > Thanks for your reply ! > My issue is not with obtaining the info I want to inject (the logged in > users's email), I already have that all ready to go :) > > My whole database is versioned using the history_meta.py example from > SQLAlchemy > https://docs.sqlalchemy.org/en/13/_modules/examples/versioned_history/history_meta.html > > I was hoping for a simple way to inject the user info into the _history row > creation steps. > > The SQLAlchemy example makes use of this event listener: > > def versioned_session(session): > > @event.listens_for(session, "before_flush") > def before_flush(session, flush_context, instances): > for obj in versioned_objects(session.dirty): > create_version(obj, session) >for obj in versioned_objects(session.deleted): > create_version(obj, session, deleted=True) > > So I'm tempted to follow the same strategy and just override this listener to > supplement it with the user info but I'm wondering how to pass in non > SQLAlchemy info into its execution context... > > So basically, I have the info I want to inject, I'm just not sure how to pass > it to SQLAlchemy > > Thanks, > > JP > > On Friday, March 12, 2021 at 6:55:19 p.m. UTC-5 elmer@gmail.com wrote: >> >> Hi JP, >> >> Depending on how you've implemented your history tracking, that routine is >> quite far removed from your web framework and getting a neat, clean way of >> dealing with that might not be within reach. >> >> However, most web frameworks have some concept of a threadlocal request (or >> function to retrieve it), which you could invoke and if such a request >> exists, you could use that to load whatever user identity you have available >> on there (again, the details differ, but this tends to be a shared feature). >> From there you can store the user either as a foreign key, or a unique >> identifier like email. Which one you pick would depend on how you want the >> history to be affected when you delete a user record for example. >> >> >> >> On Fri, Mar 12, 2021 at 11:58 PM JPLaverdure wrote: >>> >>> Hello everyone, >>> >>> We already have the ability to timestamp the creation of the history row, >>> but it would also be interesting to be able to track the user responsible >>> for the content update. >>> I would like to get suggestions on the best way to achieve this. >>> >>> I realize this is somewhat outside the scope of sqlalchemy as the notion of >>> a "logged in user" is more closely related to the context of the app/webapp >>> using SQLAlchemy as its ORM but maybe other people would benefit from >>> having a way to inject arbitrary data in the history table. >>> >>> Ideally, I would like the insert in the _history table to be atomic, so I >>> feel like hooking an update statement to an event might not be the way to >>> go. >>> I'm tempted to modify the signature of before_flush but I'm not sure where >>> it gets called. >>> >>> Any help is welcome ! >>> Thanks >>> >>> JP >>> >>> -- >>> 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 sqlalchemy+...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/82a24998-14e1-4ff4-a725-dd25c20a8bf2n%40googlegroups.com. >> >> >> >> -- >> >> Elmer > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/58bb6713-18f4-4d69-8d7b-a27772711bd5n%40googlegroups.com. -- SQLAlchemy - The Python SQL Toolkit and Object
Re: [sqlalchemy] Create Sqlalchemy ORM class from regular class gets "has no attribute ''_sa_instance_state''"
I haven't followed your code in detail, but I think the problem might be here:
clazz = school.Class('12', 'A')
students = [
Student("Name1", "Sname1", clazz=clazz, code='aa7'),
Student("Name2", "Sname2", clazz=clazz, code='bb7'),
Student("Name3", "Sname3", clazz=clazz, code='cc7')
]
You are creating an instance of "school.Class", which is the
non-sqlalchemy base class. You probably meant to create an instance of
"Class", which is the SQLAlchemy-mapped subclass, didn't you?
Simon
On Fri, Mar 12, 2021 at 11:10 AM ScottBot wrote:
>
> I have a game that I am coding for school (not a project or homework) and I
> am trying to use a SQLite database with sqlalchemy to store the game info. I
> don't know what the problem is or how I can fix it. Any help is appreciated.
> https://stackoverflow.com/questions/66591466/sqlalchemy-orm-from-regular-class
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/b3e6fb67-6cab-4484-8c39-a01999640e67n%40googlegroups.com.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcFYWK7yu9hW42LgUDB3LL%2BFAuCJqogpvYDjBe1MHigNQ%40mail.gmail.com.
Re: [sqlalchemy] SQLAlchemy database record created on import of module
I suggest you set up an event listener for the "after_attach" event on your session: https://docs.sqlalchemy.org/en/13/orm/events.html#sqlalchemy.orm.events.SessionEvents.after_attach Then you can set a breakpoint in the listener (or raise an exception, or use the traceback module to print a stack trace) to find out where in your code you are adding this object to the session. Hope that helps, Simon On Fri, Mar 12, 2021 at 4:17 AM Advanced Spectrum wrote: > > I have a Base class like this: > > class FlagTable(Base): > > __tablename__ = "FLAG_TABLE" > > FLAG_TABLE_ID = Column(Integer, primary_key=True, autoincrement="auto") > COLUMN_1 = Column(Boolean) > COLUMN_2 = Column(Boolean) > COLUMN_3 = Column(Boolean) > > I have a Python function in 'FlagTable.py' that would create a record with > the ORM FlagTable object: > > from FlagTable import FlagTable > > def addFlagsToFlagTable(arg1, arg2, arg3): > > myFlags= FlagTable(COLUMN_1=arg1, COLUMN_2=arg1, COLUMN_3=arg1) > session.add(myFlags) > session.commit() > > #main code starts here > callFunc = addFlagsToFlagTable(true, false, false) > > > When I step through the code in debugger mode I see that the record I want to > add to my postgres database table gets added at the time of the import. It > does not even wait for when the addFlagsToFlagTable function gets called in > my main code. My *'Base = declarative_base()'* statement is in another class > which is related to FlagTable class. Can someone educate me a bit on what is > going on? > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/7306a853-4017-4018-bc13-fa01aa8bb5adn%40googlegroups.com. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexc6Wqeyf5w8CPsrm_jK_YJgV6au7UUQ-2fxxzHja6QQSg%40mail.gmail.com.
