Dynamic Schema selection on query

[keinagae]

Hi in my case i am working on a system where we have separate schema for user and structure is same so t it possible to like when i am querying i can specify to which schema it should use?

[dantownsend]

There's a few different options which should work.

Option 1 - extra_nodes

Define extra_nodes (see docs) on PostgresEngine:

DB = PostgresEngine({
    'database': 'my_database',
    'extra_nodes': {
        'schema_1': PostgresEngine({
            'database': 'my_database',
            'server_settings': {
                'search_path': 'schema_1',
            },
        }),
        'schema_2': PostgresEngine({
            'database': 'my_database',
            'server_settings': {
                'search_path': 'schema_2',
            },
        }),
    }
})

Then you can pass a node argument into each query:

await MyTable.select().run(node="schema_1")

Option 2 - define tables multiple times

class MyTable(Table):
    name = Varchar()

class MyTableSchema1(MyTable, schema="schema_1", tablename="my_table"):
    ...

class MyTableSchema2(MyTable, schema="schema_2", tablename="my_table"):
    ...

# Gets data from schema 1
>>> await MyTableSchema1.select()

# Gets data from schema 2
>>> await MyTableSchema2.select()

Option 3 - dynamically create tables

Like above, but if you have loads of schemas you could just create the table classes dynamically:

class MyTable(Table):
    name = Varchar()

from piccolo.table import create_table_class

MyTableSchema1 = create_table_class("MyTableSchema1", bases=(MyTable,), class_kwargs={"schema": "schema1"})


# This now gets the data from schema 1:
>>> await MyTableSchema1.select()

[dantownsend]

I don't know if a contextvar would work. If you have loads of different users, you might be able to make option 1 work by adding extra_nodes dynamically at run time.

if 'my_user' not in DB.extra_nodes:
    DB.extra_nodes['my_user'] = PostgresEngine(...)

await MyTable.select(node="my_user")

[keinagae]

with you suggestion i am receiving TypeError: Table.select() got an unexpected keyword argument 'node'

[dantownsend]

You have to pass it to the run method like this:

await MyTable.select().run(node="my_node")

run is usually optional, but we need it in this situation to specify the node.

[keinagae]

how does Date column actually works i am receiving a strange issue

instance = await AdgroupReports.objects().get(
                AdgroupReports.campaign_id == int(item['campaignId'])
                and AdgroupReports.date == arrow.get(item['date']).date()
                and AdgroupReports.ad_group_id==int(item['adGroupId']))
print(instance,arrow.get(item['date']).date(),item)

and out is following

('9b771c75-a402-44b1-9932-66f17e065630',63,2024-05-15,1000000000021348,1000000000024334,null,5,0,0.0,0,0.0,0.0,null,null,0.0,0.0,0.0,0.0,null,null) 2024-05-14 {'date': '2024-05-14', 'adGroupId': '1000000000024334', 'campaignId': '1000000000021348', 'impressions': '15', 'clicks': '0', 'ctr': '0', 'conversions14d': '0', 'conversionRate14d': '', 'averageCpc': '', 'sales14d': '0', 'cost': '0', 'acos14d': '', 'roas14d': '', 'averageWinningBid': '4.25'}

instance :

('9b771c75-a402-44b1-9932-66f17e065630',63,2024-05-15,1000000000021348,1000000000024334,null,5,0,0.0,0,0.0,0.0,null,null,0.0,0.0,0.0,0.0,null,null)

date:
2024-05-14

item:

 {'date': '2024-05-14', 'adGroupId': '1000000000024334', 'campaignId': '1000000000021348', 'impressions': '15', 'clicks': '0', 'ctr': '0', 'conversions14d': '0', 'conversionRate14d': '', 'averageCpc': '', 'sales14d': '0', 'cost': '0', 'acos14d': '', 'roas14d': '', 'averageWinningBid': '4.25'}

in above output if you check for date 14 i am getting record for date 15 any idea what could be issue?

[dantownsend]

It might because OF the syntax you're using for AND.

https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/where.html#complex-queries-and-or

Try & instead of and.