Build query without DB connection

[Brendan Barnwell]

It seems that the "db" object given by the DAL wraps up not only the database definition, but also a "live" connection to the database.  This means that if the db object is stashed somewhere, it can go "stale" and attempts to use it later will lead to strange errors.  Is that so?

What I'm trying to do is to pre-build a query constraint like "db.MyTable.myfield < 2", within a module.  Later in controller code I may retrieve these stored constraints and string them together using &.  But right now it seems I have to build the constraints anew within a function, because if I do something like "stored_query = db.MyTable.myfield < 2" at the top level of module code, the db object goes stale.  Later I get errors such as "name conflict in table list", apparently because it doesn't realize the stored "db.MyTable" is the same as "real_db.MyTable" (where "real_db" is the new live db object for the current request).

It is not a huge deal to regenerate the constraints by calling a function, but it would be nice if they could just be stored as static objects.  Is there any way to use the DAL's nice query-building syntax without a live DB connection, and then later used one or more stored query constraints when querying a live db connection?  Something like:

# in somemodule.py
db = ? # get non-live DB object to build query
query1 = db.MyTable.myfield < 2
query2 = db.MyTable.otherfield == "blah"

# in a controller
import somemodule

def controller_function():
    # now db has a live connection
    db(somemodule.query1 & somemodule.query2).select()

Is such a thing possible?

[Massimo Di Pierro]

In web2py, and in general in code that runs multithreaded you should not define variables in modules at top level and then import them because they are cached and then shared by multiple threads. That is not thread safe and will cause all kind of problems. Modules should define functions and classes, not objects. You should only import those functions and classes. 

[Brendan Barnwell]

On Wednesday, September 20, 2017 at 10:31:04 PM UTC-7, Massimo Di Pierro wrote:

In web2py, and in general in code that runs multithreaded you should not define variables in modules at top level and then import them because they are cached and then shared by multiple threads. That is not thread safe and will cause all kind of problems. Modules should define functions and classes, not objects. You should only import those functions and classes. 

I think it is a bit extreme to say that modules can only define functions and classes.  Clearly they can also contain, for instance, "constant declarations" like "something = 2".  My question is whether there is a way to define a query constraint as a "constant" in a similar way.

An expression like "db.MyTable.myfield > 2" does not conceptually need to know anything about the field, the table, or even the database at the time it is created.  In this situation "query = db.MyTable.myfield > 2" would be no different from a constant assignment like "MY_CONSTANT = 7".  There is no reason why the expression itself needs access to any thread state.  It is only when the query is actually executed that it needs to be attached to a live database connection --- but that may be at a later time.  What I'm asking is if there is a way to use the syntactic sugar of the DAL without any connection to an actual database, just to create a query expression that can be LATER attached to a database object.

It appears the answer is no.  Is that what you are saying?

[Anthony]

You might try something like this in your module:

dummy_db = DAL('sqlite:memory', do_connect=False, migrate_enabled=False)

def define_tables(db):
    db.define_table('mytable', Field('field1'), Field('field2'))

define_tables(dummy_db)

query1 = dummy_db.mytable.field1 == 'some value'
query2 = dummy_db.mytable.field2 == 'other value'

By setting do_connect=False, no connection will be made to the database. Note, the URI used for the dummy database does not have to be the same as the real database, but it should refer to the same type of DBMS (mainly to ensure the the proper SQL dialect is used to generate the SQL).

Then in your model and/or controller code:

from mymodule import define_tables, query1, query2

db = DAL(...)

define_tables(db)

rows = db(query1 & query2).select()

Note, with the above approach, the dummy DAL object defined in the module is not the same DAL object defined in the model/controller code where the query is executed. However, in order to generate queries in the module, we still need to define the tables on the dummy DAL object. So, the above example puts the table definition code in a function, which can be called within the module as well as the model/controller code in order to define the same tables on the real DAL object as well as the dummy object.

Finally, you should consider whether there is really much benefit to defining the queries in a module in this way.

Anthony