SQL like interface?

Anvil Q&A
#1

What I’m trying to do: I’ve collected some data through UI in an apploication. Now I want to run different queries / filters / delete in adhoc manner.

What I’ve tried and what’s not working: Since I don’t have SQL access to databased I’ll have to write UI interface for each and every table which is not very efficient

Is there a generic interface that anyone has built which can be used to run sql queries against the data? Or any other creative way to work around this (not having direct db access)

#2

TBH I feel like there IS direct access to the data. If you read the docs and see repeating panels and the like you can see that you can access data with just a few lines of code. I’m no SQL expert, but I certainly find it easier in Anvil (isn’t everything?).

#3

You can also access tables by name, e.g. table = getattr(app_tables, db_name)

You can use dictionary expansion to build a list of column specifications for exact matches, e.g.:

matches = {
  'user': anvil.users.get_user(),
  'enabled': True
}

table.search(**matches)

You can use Anvil’s query operators to build some flexible queries based on value ranges, etc. Querying Data Tables

You should be able to do what you want without the direct SQL access by putting together various techniques.

#4

At first, this may not appear to be an appropriate answer to your question, but data table form permissions set to ‘can search, edit, and delete’, along with ‘write back’ bindings in a repeating panel give you quick and malleable create, edit, delete, and query access to any/every column in your tables (see the search and sort example above - the Anvil ORM search criteria can be edited as needed to satisfy your SQL-like query requirements). The basic examples above just give you a standardized UI interface to Create Read Update and Delete tables, and apply filer/sort criteria to your table views.

NOTE: form permissions are per app, as opposed to per data table, so you can create a single quick admin app like the examples above, with fast access to a shared data table, without exposing form permissions in your client app(s).

#5

What about the server console?

You open it with:

image

You use it as any server module:

image

You are never going to have the flexibility of SQL, but I use this often to clean up, initialize, query, etc.

2 Likes