Reflection

[AliSayyah]

I think a reflection feature like what's done in SqlAlchemy could be nice.
Personally, this would be very useful for me.
I want to know your ideas and how would this fit with piccolo's API.
If we come to a conclusion, I'll work on it.

[dantownsend]

@AliSayyah I haven't used that feature before. What kind of API do you envisage?

[AliSayyah]

@dantownsend creating Table objects on the go based on tables in database, instead of hardcoding them.
This tables then will be stored in another object, Something like TableStorage , so they will be accessible in runtime. Loading mechanism shouldn't be much different than what schema generate does.

[dantownsend]

OK, makes sense. What is the main use case - is it somebody in a Python shell exploring a database? Or would a web app use this somehow?

[AliSayyah]

The use case I have in mind is in web apps. In scenarios that all tables aren't predefined and new ones must be created on the go and used. Python shell could be another use case.

[dantownsend]

OK, cool - feel free to give it a go.

[AliSayyah]

I'll do that. Thank you.

[AliSayyah]

@dantownsend To handle tables in different schemas on the database, schema name should be included in tablename attribute?

[dantownsend]

I haven't personally tried it, but specifying the schema name as part of the tablename should work:

class MyTable(Table, tablename='my_schema.my_table'):
    ...

So with your reflection API, if you set the tablename attribute to 'some_schema_name.some_table_name' it should work OK.

[AliSayyah]

@dantownsend Yes, that works. I think maybe adding a schemaname to the Table class as an optional parameter can be a cleaner way to handle schemas and it would be more user-friendly.
what are your thoughts about this?

[dantownsend]

I'm not sure - it could get confusing if you can define the schema name in multiple ways. For example:

class MyTable(tablename='my_schema.my_table', schema_name='my_schema'):
    ...

It would be useful in situations like this, where you're happy to keep the default table name:

class MyTable(schema_name='my_schema'):
    ...

It might be OK - just needs to be documented well, and should raise a ValueError if the schema name is unclear.

It Piccolo officially supports schema names, we would have to add it to migrations too - there would have to be a new Alter method called set_schema, in case someone changes the schema name.

[AliSayyah]

We can have validation on ‍‍tablename when schemaname is defined. that could be solvable.
I'm okay with defining schema name in tablename. If you think adding it will add to the confusion, then it's not a good idea.

[dantownsend]

Do what you think is best for now. There are pros and cons to each approach, as long as it's documented, then should be OK.

[AliSayyah]

@dantownsend
I think the related PR has the necessary base functionalities for now.
If in any way, you have doubts about reflection or this isn't something you envisioned to have in piccolo, I would be happy to change this or even use this for my needs from my own fork. I realize this isn't a popular feature.
Thanks for your help and guidance 🙏

[dantownsend]

I think it's a valuable feature - I'll try and review the PR today.