CODEWITHSUNDEEP
pythonmysqlweb2py
Makis Ladoykakis
Makis Ladoykakis

Reputation: 111

Web2py SQLFORM.grid with executesql

I am making a web2py application and I have my two mysql tables defined in my models db.py file:

db.define_table('table1',
    Field('id','integer'),
    Field('name','string'),
    migrate=False)

db.define_table('table2',
    Field('id','integer'),
    Field('name','string'),
    migrate=False)

I want my application to return a union of these tables:

data=db.executesql('SELECT * FROM table1 union select * from table2;')

in a SQLFORM.grid but apparently

form=SQLFORM.grid(data, create=False, deletable=False, editable=False, maxtextlength=100, paginate=10)

is not the way to go.

Can somebody help me please? It must be really simple but I'm having trouble finding the solution.

Thank you

Upvotes: 1

Views: 1824

Answers (1)

Anthony
Anthony

Reputation: 25536

The grid is designed to take a table or query, so you cannot pass a Rows object or arbitrary SQL. The best approach would be to create a view in the database and create a new DAL model definition associated with that view (be sure to set migrate=False, as you don't want the DAL to attempt to create a table with the name of the view). Then you can pass the view model to the grid:

db.define_table('t1_t2_union_view',
    Field('id','integer'),
    Field('name','string'),
    migrate=False)

grid = SQLFORM.grid(db.t1_t2_union_view, ...)

The above works because web2py will treat the model of the database view like any other database table, issuing a query to select all of its records. There is no need for executesql in this case because the union of the tables is handled in the database by the view.

Actually, you can simplify the table definition to:

db.define_table('t1_t2_union_view', db.table1, migrate=False)

When you pass an existing table to .define_table(), you get a new table with the same field defintions as the original, which is what we want here.

If creating separate views for each possible union is not feasible, a possible alternative would be to retrieve the data via executesql and then iterate through the records, inserting each one into an in-memory SQLite database table, which could then be passed to the grid:

union_tables = ('table1', 'table2')
temp_db = DAL('sqlite:memory')
union_table = temp_db.define_table('union_table', db[union_tables[0]])

records = db.executesql(sql, as_dict=True)
for record in records:
    union_table.insert(**union_table._filter_fields(record))

grid = SQLFORM.grid(union_table, create=False, editable=False, deletable=False)

Setting as_dict=True results in a list of dictionaries being returned, which makes it easier to do the inserts, as the keys of the dictionaries are the field names needed for the inserts.

Note, this method is somewhat inefficient, so you would have to test it to see how it performs with your workload.

Upvotes: 4

Related Questions