Setting variables on connection

[mpdevilleres]

Greetings,

I have a use case where I need to set the variable for each session or connection. and I am able to accomplish it using the asyncpg directly

import asyncpg
con = await asyncpg.connect('postgresql://tenants@localhost:5433/internal?application_name=app_name')
r = await con.fetchrow("SELECT current_setting('application_name')")
Out[1]: <Record current_setting='app_name'>

con = await asyncpg.connect('postgresql://postgres@localhost:5433/internal?options=-c%20app.name%3Dapp_name')
r = await con.fetchrow("SELECT current_setting('app.name')")
r
Out[2]: <Record current_setting='app_name'>

but doing it with gino as shown below doesn't work.

from gino import Gino
db = Gino()
await db.set_bind('postgresql://tenants@localhost:5433/internal?application_name=app_name')
r = await db.all("SELECT current_setting('application_name')")
print(r)
Out[3]: [('',)]

db = Gino()
await db.set_bind('postgresql://postgres@localhost:5433/internal?options=-c%20app.name%3Dapp_name')
r = await db.all("SELECT current_setting('app.name')")
print(r)
Out[4]: asyncpg.exceptions.UndefinedObjectError: unrecognized configuration parameter "app.name"

I also tried passing connect_args={"application_name":"myapp"} as recommended by zzzeek on set_bind. but I still fail to make it work.

Is there anyway for gino to achieve my use case?

[Chaostheorie]

Have you tried using an approach similar to this answer from SO?

You might just be able to use something like:

# Get your db instance yada

# Set value
await db.one_or_none("select context('application_name', 'my awesome gino application');")

# Get value
await db.one_or_none("select context('application_name');")

Can't test this code ATM, but maybe context vars are the way to go for psql.

You might otherwise need to refer to #683

[mpdevilleres]

Hi @Chaostheorie,

Thank you for your response, I am not sure how to do this as a wrapper. the reason I wanted to pass the parameter in the url so that I don't need to change any of my existing queries.

If you have any idea i would appreciate discussing it and trying it out.

Thanks,

[Chaostheorie]

@mpdevilleres I understand the intention to not alter existing queries.

I'm gonna assume you're either using gino with an adapter, such as gino-quart, or directly, which should allow you to manipulate the individual connection.

I'm not sure if you're able to get away without changing queries completely. You might either need to prepend a statement to you queries (no idea how but you could try to play with SQLalchemy) or set a session related variable. I have found on related question on SO.

I would love to further discuss on this but I'm currently busy with studying and my responses might take some time. I hope the above mentioned articles will be helpful. You might also want to open question in asyncpg's repo about this, since it's used under the hood.