how to find and reference field names in tables

[Alex Glaros]

is it possible to do something like this?

For row in tables:  # all the tables

    if row.field_name == 'last_name': # only tables with this field name

        query = row.organizationID == db.Organization.id #how to reference other fields and data in the located table?

or

query = ((tables.field_name == 'last_name') & (tables.field_name == 'organizationID') & (tables.organizationID == db.Organization.id)) #not sure how to reference data in table that has field_name of "organizationID"

thanks,

Alex Glaros

[Anthony]

Are you saying you have a "tables" table in your db that stores all the table and field names of the other tables in your db, and you need to know how to query it to get particular information, or are you looking for a way to loop over all the tables you have defined and identify those that include particular fields? Once you have identified the tables of interest, it is not clear what you are then looking to do. Maybe you want something like this:

for tablename in db.tables:
    table = db[tablename]
    if 'last_name' in table and 'organizationID' in table:
        rows = db(table.some_field == some_value).select()
        [do something with rows]

If you need to get a list of field names in a table, you can get that via table.fields.

Anthony

[Alex Glaros]

I want to discover the field names in db.py (I do not have them listed in an already-existing table)

can you help with syntax for a query?

query = ((db.tables.field == 'organizationID') & (db.tables.field == 'last_name') & (db.tables.table_name.organizationID == 'California')) # so search is looking for mixed data types, one is field name, another is that field name with a specific value

The resulting query in grid would look like below (without the numbers in parens):

(1) table_name (2) field_name (3) data_value

[Dave S]

On Wednesday, October 28, 2015 at 6:35:27 AM UTC-7, Anthony wrote:

Are you saying you have a "tables" table in your db that stores all the table and field names of the other tables in your db, and you need to know how to query it to get particular information, or are you looking for a way to loop over all the tables you have defined and identify those that include particular fields? Once you have identified the tables of interest, it is not clear what you are then looking to do. Maybe you want something like this:

for tablename in db.tables:
    table = db[table]

should this be table = db[tablename]  ?
 

    if 'last_name' in table and 'organizationID' in table:
        rows = db(table.some_field == some_value).select()
        [do something with rows]

If you need to get a list of field names in a table, you can get that via table.fields.

Anthony

On Wednesday, October 28, 2015 at 12:39:05 AM UTC-4, Alex Glaros wrote:

is it possible to do something like this?

For row in tables:  # all the tables

    if row.field_name == 'last_name': # only tables with this field name

        query = row.organizationID == db.Organization.id #how to reference other fields and data in the located table?

or

query = ((tables.field_name == 'last_name') & (tables.field_name == 'organizationID') & (tables.organizationID == db.Organization.id)) #not sure how to reference data in table that has field_name of "organizationID"

/dps
 

thanks,

Alex Glaros

[Dave S]

On Wednesday, October 28, 2015 at 9:39:52 AM UTC-7, Alex Glaros wrote:

I want to discover the field names in db.py (I do not have them listed in an already-existing table)

can you help with syntax for a query?

query = ((db.tables.field == 'organizationID') & (db.tables.field == 'last_name') & (db.tables.table_name.organizationID == 'California')) # so search is looking for mixed data types, one is field name, another is that field name with a specific value

Should the check for field name be in the query, or in a separate python conditional?

for tablename in db.tables:
  table = db[tablename]

  if 'organizationID' in table.fields and "last_name" in table.fields:
    query=table.organizationID == 'California'

 

The resulting query in grid would look like below (without the numbers in parens):

 

(1) table_name (2) field_name (3) data_value

To display (1) and (2) in the grid, pass the strings in as a computed field?

/dps
 

[Anthony]

You can't do a query looking for tables -- you have to handle that part of it in Python with code like I showed. Once you have the table names, you can then do whatever queries you want.

Anthony

[Anthony]

for tablename in db.tables:
    table = db[table]

should this be table = db[tablename]  ?

Yes, thanks. I updated the original answer.

Anthony

[Alex Glaros]

ok, got it.  Thanks Anthony!

[lucas]

ok cool,

so now we know how to iterate through the fields of a table.  how can we grab or get the db.py default value for each field also?

thanx lucas

[Anthony]

Just access the .default attribute of each field.

Anthony