Get list of Tables in app

I’m trying to dinamically get a list of all the tables in the app and all the columns of each table.

I know I can get all columns from a table using app_tables.my_table.list_columns() or getattr(app_tables, 'my_table').list_columns().

However, I couldn’t find a way to get a list of tables in a certain app. Is there any programatically way of doing this?

If you download your source code, whether as a single file, or via Git, then you can find the schema in the main .yaml file.

This is not officially supported.

Yeah, I found a forum topic that says exactly this.
However, I wanted something dinamically.

I wanted a code that gets this for any app, not needing to clone and check each app.

So have I, but that’s as close as I’ve gotten. Someone else here might have an idea, though.

1 Like

You can have a table with the urls of the apps you are interested on, but you will need to manually update it.

Or you can have a function logging in to your Anvil account, then requesting the list of apps, then downloading each one of them.

I have an app that does it and builds the dependency tree of tables and app dependencies.

I’m on my cell now, so I can’t check. But I remember that the url that gives you the list of apps was easy to get from the old editor.

You will only get the apps shared with you or your own apps. If your account includes other developers that own unshared apps, you will not see them.

The thing is, I need a list of tables from a specific app, the current app importing the dependency, not a list of my apps.

If you only want to know something about one app, then look at the url of the link that downloads your app, and download it.

No… That’s not what I meant.

I want to create a function for an app that will be used as a dependency for other apps. It would have a function like this:

app_tables_dict = dependency.get_app_tables_dict()
# app_tables_dict would be something like:
# {'my_table_1': ["column_a", "column_b", ...]}

For each app the function would return something different, specific of that app.
So, I don’t need a list of app, I need a list of tables in an app. And this list is not of one unique app, but any app that runs the dependency function.

It needs to be dinamically. I can’t download the app and check the files because I would need to do that for each app and store it manually. I would also need to redo that evertime I add a new table. It would be easier to just manually type it in another table or as an enum, but that’s not what I want.

To be honest, what I want to do is something like:

d = {}
tables_in_app = app_tables.list_tables() # get a list of tables
for t in tables_in_app:
   d[t] = t.list_columns()
return d

You go to the button that allows you to download the app in the ide (sorry, still on the cell, I can’t right now), right click, inspect, and check the url. I’m pretty sure that url includes the app id.

In the dependency app, you replace the app id in that url with the id of the app that is running, so you have the url to download your app. You can get thecurrent app id dynamically, Idon’t remember how, I’ll let you know when I’m at the computer.

Then, in the function, you sign in to you Anvil account, you can see the url for signing in by manually signing in. Then, once the function has signed in, you use the url for downloading (after replacing the app id), download the app and dig in the downloaded goodies.

EDIT

I just remembered that I had already addressed this: Programmatically access data about my Anvil apps - #4 by stefano.menci

So the goal is to get the schema at runtime because the app, and its schema, is not known in advance. This routine could be used in hundreds or thousands of apps, each of which will have its own set of tables.

There is probably some code buried in the Anvil Uplink package, that might be able to get a list of tables. That’s probably stable, for backwards compatibility, but it’s not documented or supported.

I also seem to recall a way to get the list of columns from a table… Yes. It’s in the API reference as list_columns().

If you have direct SQL access to your datatables, you can get it from there:

import anvil.tables
import psycopg2
import psycopg2.extras

def get_table_names():
    conn = psycopg2.connect(anvil.tables.get_connection_string())
    with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
        cur.execute("SELECT table_name FROM INFORMATION_SCHEMA.views WHERE table_schema ilike 'app_schema_%';")
        results = cur.fetchall()

    return [r['table_name'] for r in results]

>>>> get_table_names()
['table_1', 'table_2', 'table_3']

I know that’s not for everyone (requires dedicated or enterprise license) but it does deliver what you’re asking for.

Ok, reading this other topic I now understand better what you meant. That’s a little to much work than I was expecting, but also gives me a lot more info. I’ll take a look at this for the time being. Very interesting info there. Didn’t know you could do that.

Yes, exactly this.

That’s what I tought, but couldn’t find anywhere in the docs and couldn’t think of another way to do so. I posted here since, if there was a way, some of you would know (and I was right, using @stefano.menci 's method).

Yes, that’s from the Postgre’s data objects, but I don’t have direct SQL access, unfortunatelly.

The answer to the question is unsupported.

̶T̶h̶i̶s̶ ̶i̶s̶ ̶t̶h̶e̶ ̶u̶n̶s̶u̶p̶p̶o̶r̶t̶e̶d̶ ̶a̶n̶s̶w̶e̶r̶ ̶t̶h̶a̶t̶ ̶w̶o̶r̶k̶s̶ ̶a̶n̶y̶w̶a̶y̶. EDIT: DONT DO THIS, USE THE NOW SUPPORTED METHOD ANNOUNCED BY STU 7 POSTS DOWN :tada: :tada: :point_down:


def get_list_of_tables_in_app_with_cols():
  #  Only works from inside a server module if you have a 'Users' table.
  return { k: [x["name"] for x in v.list_columns()]
          for k, v in 
            anvil.server.call("anvil.private.tables.get_app_tables").items() }

Use at your own risk, ymmv, objects in code may be closer than they appear, etc. etc.
Edit: :point_down: :point_down: :point_down: :point_down: :point_down: :point_down: :point_down: Look 7 posts down!

Results should look like:

5 Likes

Can you use this to get the tables of another app?

You should not be able to, irrc it passes the call to a Clojure method that uses the the app id to query the anvil database directly in postgres and returns all of the tables as table objects with their names.

It’s what gets called when the server module spins up to build the attributes for the app_tables object.

Since you can’t change the internal app id, you can’t get other tables. If I had to guess, ‘linked tables’ just means the table is assigned with more than one internal app id, so it would also just show up as accessible to the ‘current’ app id.

I am also unsure if this will work once they move to accelerated tables (tables.v2) as standard. There is a private method called "anvil.private.tables.v2.get_app_tables" but I am unsure if it works the same way.

I also could be completely wrong about how it works at all because I might not understand Java/Clojure as well as I think.

This is awesome! I know it’s unsuported / undocumented and should be used with caution, but that’s exactly what I needed!

I just tested this by adding the method to a server module in the app dependency and called from another app. It worked as if used natively by the running app.

I also tested the same method in an app with accelerated tables activated. Don’t know if they will change something in the future, but it worked as intended.

2 Likes

Thanks for testing it, I don’t have accelerated tables turned on on this app i’m testing.

I will say, if you are just using it to get the table names like I did above, that’s totally fine.
However if you have accelerated tables turned on, just make sure you don’t use the ‘table’ objects returned as the values of "anvil.private.tables.get_app_tables".
If you tried to use them with accelerated tables turned on you may get aberrant behavior.
(I talked to Stu about this once and it started to get wonky real fast)

2 Likes

Thanks for the tip.
The idea is to just get the names and, later, maybe use those names with getattr(app_tables, name_from_the_request) to do other things. So the usage of the tables will still be on documented anvil functions.

1 Like

We’ve added support for getting table names

You can now do

table_names = list(app_tables)

You can also iterate over app_tables


for table_name in app_tables:
    ...

and if you’re using accelarted tables we support subscript notation, and introspection


for table_name in app_tables:
    my_table = app_tables[table_name]
    # if not using accelerated tables
    # getattr(app_tables, table_name)


print(dir(app_tables)) # includes table names
5 Likes