Re: [sqlalchemy] Injecting User info into _history table to track who performed the change

[JPLaverdure]

Hi,

Thanks for your support guys.

I've implemented logging the user's email in the _history tables by adding 
this Column definition inside my history_meta.py file:
cols.append(
  Column(
"user",
String,
info=version_meta,
  )
)
but i'm running into a good load of SAWarnings stating that there is an 
implicit combining of the "user" column taking place
(I have multi-table inheritance setup for some entities, those are the ones 
throwing the warning)
I don't get why the column "changed" (which holds the timestamp of the 
change) and is defined in exactly the same way does not generate these 
warnings ?
What configuration setting am I missing here ?

I found this 
https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
But it doesn't seem to fit 100% with what I'm seeing inside history_meta.py

Thanks !!
On Monday, March 15, 2021 at 4:33:40 p.m. UTC-4 Jonathan Vanasco wrote:

> 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 

Re: [sqlalchemy] (cx_Oracle.DatabaseError) ORA-00972

[Mike Bayer]

can you please include the SQLAlchemy version and Oracle version in use? as 
well as an example Table model.  These issues should be resolved for Oracle, 
the label names are truncated automatically.



On Mon, Mar 22, 2021, at 8:28 AM, Aurèle Durand wrote:
> Hello,
> 
> SqlAchemy automatically specify the table name in front of columns and thus 
> my query parameters are too long and I get the "(cx_Oracle.DatabaseError) 
> ORA-00972" error on Oracle. For example if my table name is 
> "TABLE_NAME_TOO_LONG" and my columns are "id" and "name" a request will look 
> like this:
> 
> SELECT "TABLE_NAME_TOO_LONG".id, "TABLE_NAME_TOO_LONG".name FROM 
> "TABLE_NAME_TOO_LONG" where ... 
> 
> I could use alias for select request in order to bypass this issue if I 
> understand well 
> (https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_using_aliases.htm).
> 
> However for an insert I cannot find any solution. 
> 
> Is there a way to set an alias to a table name for an insert ? or remove the 
> table name ?
> 
> Best regards ;)
> 
> 
> 
> 

> -- 
> 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/03d4cb6c-ce89-47ee-bf5d-58d0c162667fn%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/c0da71de-d3e6-4fa9-98ff-9ad5f7dc0b43%40www.fastmail.com.

[sqlalchemy] Re: (cx_Oracle.DatabaseError) ORA-00972

['Jonathan Vanasco' via sqlalchemy]

Try passing a small number to `label_length` in your `create_engine`.  
Something like `label_length=5` might work.  I typically use 4-6 on 
Production/Staging servers, and no argument on Development.


* 
https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine.params.label_length

I don't have Oracle, so I am not sure if this fixes your exact problem or 
just related ones.

`label_length` will limit the length of aliases that sqlalchemy generates.  
so you would see something like this:

- SELECT very_long_table_name_i_mean_it_is_long.id AS 
very_long_table_name_i_mean_it_is_long_id, 
very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
AS 
very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
FROM very_long_table_name_i_mean_it_is_long
 LIMIT ? OFFSET ?

+ SELECT very_long_table_name_i_mean_it_is_long.id AS _1, 
very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
AS _2 
FROM very_long_table_name_i_mean_it_is_long
 LIMIT ? OFFSET ?

If the exception is caused by the generated alias (notice the underscore 
separator) 
`very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo`
 
being too long, that will solve your problem.

but if the exception is caused by (notice the dot separator in table/column 
addressing) 
"very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo"
 
, then I don't know the remedy.

 
On Monday, March 22, 2021 at 8:28:10 AM UTC-4 durand...@gmail.com wrote:

> Hello,
>
> SqlAchemy automatically specify the table name in front of columns and 
> thus my query parameters are too long and I get the 
> "(cx_Oracle.DatabaseError) ORA-00972" error on Oracle. For example if my 
> table name is "TABLE_NAME_TOO_LONG" and my columns are "id" and "name" a 
> request will look like this:
>
> SELECT "TABLE_NAME_TOO_LONG".id, "TABLE_NAME_TOO_LONG".name FROM 
> "TABLE_NAME_TOO_LONG" where ... 
>
> I could use alias for select request in order to bypass this issue if I 
> understand well (
> https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_using_aliases.htm
> ).
>
> However for an insert I cannot find any solution. 
>
> Is there a way to set an alias to a table name for an insert ? or remove 
> the table name ?
>
> Best regards ;)
>
>
>

-- 
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/0435579f-8db8-4525-b3b3-54e5edeb243fn%40googlegroups.com.

[sqlalchemy] (cx_Oracle.DatabaseError) ORA-00972

[Aurèle Durand]

Hello,

SqlAchemy automatically specify the table name in front of columns and thus 
my query parameters are too long and I get the "(cx_Oracle.DatabaseError) 
ORA-00972" error on Oracle. For example if my table name is 
"TABLE_NAME_TOO_LONG" and my columns are "id" and "name" a request will 
look like this:

SELECT "TABLE_NAME_TOO_LONG".id, "TABLE_NAME_TOO_LONG".name FROM 
"TABLE_NAME_TOO_LONG" where ... 

I could use alias for select request in order to bypass this issue if I 
understand well 
(https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_using_aliases.htm).

However for an insert I cannot find any solution. 

Is there a way to set an alias to a table name for an insert ? or remove 
the table name ?

Best regards ;)


-- 
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/03d4cb6c-ce89-47ee-bf5d-58d0c162667fn%40googlegroups.com.