Get raw sql from pydal staement

[Kevin Keller]

I woukd like to append another sql before or alternatively slightly alter an sql when e. G. Pydal does a select.

Like for example I like to state which role to use before executing the sql in order to imolemtent some sort of user impersonation. 

Can I push the what woukd be sql statement from a pydal query into a string variable and then alter the variable so I can pass it back to pydal and execute it with executesql with pydal?

[Kevin Keller]

I think I can work with _lastsql.

From here: 

https://zetcode.com/python/pydal/

[Kevin Keller]

Found the perfect answer uktinselty in the web2py book under "generating raw sql"

[Alexander Beskopilny]

pydal tips
https://web2py.wordpress.com/tag/count/

[Val K]

Show concrete example please

суббота, 2 июля 2022 г. в 16:27:50 UTC+3, ab9...@gmail.com:

[Kevin Keller]

Samples are here:

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Generating-raw-sql

In essence you use the pydal command with an underscore.

My use case is that I need to change the users database role before executing a query or inject an extra value before a select that is not part of the table 

to perform some database user impersonation even though I am connected to the database with a service account. 

The only other thing I need to take care of is to avoid racing conditions in my case. 

I ll probably see if I can give each query an ID  use a separate sqlite (in fillesystem or in-memory) to keep track of query execution and if 2 queries 

are running at the same time I make one query 

Now with this I can generate the SQL as a string. Modify as I see fit and then use pydals executerawsql to actually run the query.

Very handy!

Select Sample.

Here is _select

>>> print db(db.person.name == 'Alex')._select()
SELECT "person"."id", "person"."name" FROM "person" WHERE ("person"."name" = 'Alex');

--
You received this message because you are subscribed to the Google Groups "py4web" group.
To unsubscribe from this group and stop receiving emails from it, send an email to py4web+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/py4web/3a8947cf-1697-4f2b-b218-e408bd0c132dn%40googlegroups.com.

[Val K]

Chage db-role? Role effects on connection so you shouldnt use connection pull and open/close connecrion on each request

суббота, 2 июля 2022 г. в 17:35:37 UTC+3, kell...@gmail.com:

[Val K]

I mean concrete example what you want to modify in the query

суббота, 2 июля 2022 г. в 17:54:34 UTC+3, Val K:

[Val K]

There are transaction stuff and temporary tables so you dont need to track which query executed

суббота, 2 июля 2022 г. в 18:00:28 UTC+3, Val K:

[Kevin Keller]

Its very specfic a for use case I am doing with Snowflake. 

I dont want to have multiple connection strings and db connections open per user. I rather use one service account with connecton pooling and then use this command:

USE ROLE XXX; 

https://docs.snowflake.com/en/sql-reference/sql/use-role.html

Possibly also: 

ALTER USER CURRENT_USER()  RENAME TO (username from python session). 

Before I execute the command. 

I dont want to track queries executed in temporary or any tables within Snowfalke because its an OLAP database and point lookups are costly. 

I ll rather look it up outside with sqlite (because OLTP)  or maybe even just a dict in memory. 

There is a way to create OTLP style tables in Snowfalke, but this is overkill for this use case. 

To view this discussion on the web visit https://groups.google.com/d/msgid/py4web/140b6494-f1f5-40f7-ae18-7b0f863fd6f2n%40googlegroups.com.

[Kevin Keller]

The user option I was thinking of to track which user is actually executing the query is to put it in front of. select: 

SELECT *USERNAME FROM PYTHON SESSION* as USERNAME, * from customers; 

I still need to change the role if access rights are controlled by the DB layer and not by the python/controller layer. 

[Val K]

You can't use any in-memory stuff as it will crash as soon as you run more than one worker (although gunicorn has preload_app option which means 'load once then just fork' so you can create shared Lock, but from python doc "Note that safely forking a multithreaded process is problematic.", also forking is available only on *nix platforms).

So to use connection pool you have to create on-disk lock per connection, i.e. you need to have something like connection_id to store it in sqlite db (normal db, not in-memory),

суббота, 2 июля 2022 г. в 19:09:23 UTC+3, kell...@gmail.com:

[Kevin Keller]

That makes sens, yeah I'll need to track this in an on disk sqlite. 

Thanks for clearing this up 

To view this discussion on the web visit https://groups.google.com/d/msgid/py4web/72b108e7-f519-46e8-be38-f83ab7f2325en%40googlegroups.com.