py4web DAL, web2py - (Legacy) database exploration - how to get all tables, fields, etc?

Question

Unfortunately there is no py4web tag (that and one for modules should be added) so i put it under web2py as DAL is the same. If someone can create a tag (maybe talking to Massimo ..) its appreciated.

Its possible to access nearly whatever database with py4web/web2py. With autoimport its not necessary to define the fields again.

from pydal import DAL, Field

db = DAL('sqlite://legacydb.sqlite', folder='subdirectory/additionalsubdirectory', auto_import=True)

Now it should be possible to get all tables, columns etc. (In Oracle: select * from all_tables, select * from user_tab_columns etc are available for this)

db.tables

Its not working. (Its said its empty but there are two tables.

db.person.fields 

Even knowing that a table person is there, the fields are not shown.

But its working when the database and the tables are created and I 'aks' immediately afterwards.

How can this be done?

Answer 1

please ensure that it refer to the same database folder, with *.table available there, if run pydal on py4web or terminal ensure that you execute db.commit() if not, it just store in memory not writen on database

e.g.

in jupyter notebook (created two notebook, 1 for define_table with auto_import=False and the other without define_table with auto_import=True)

notebook1

from pydal import DAL, Field
from datetime import datetime
now = datetime.now()
db = DAL('sqlite://jupyter_pydal.sqlite', folder = 'databases', auto_import = False)
db.define_table('test',
    Field('string_0'),
    Field('text_0', 'text'),
    Field('integer_0', 'integer'), 
    Field('double_0', 'double'),
    Field('date_0', 'date'),
    Field('datetime_0', 'datetime'),
    #format = lambda r: f'{r.name}' )
    format = lambda r: '%s' % (r.name) )
if db(db.test).isempty():
    p0 = db.test.insert(string_0 = 'string_0', text_0 = 'text_0', integer_0 = 0, double_0 = 0, date_0 = now, datetime_0 = now)
    p1 = db.test.insert(string_0 = 'string_1', text_0 = 'text_1', integer_0 = 1, double_0 = 1, date_0 = now, datetime_0 = now)
db.commit()
db._uri
db._dbname
db.tables
db.test.fields
db.test.string_0.type
rows = db(db.test).select()
print(rows)

notebook2

from pydal import DAL, Field
db = DAL('sqlite://jupyter_pydal.sqlite', folder = 'databases', auto_import = True)
db._uri
db._dbname
db.tables
db.test.fields
db.test.string_0.type
rows = db(db.test).select()
print(rows)

result for command

db._uri
db._dbname
db.tables
db.test.fields
db.test.string_0.type
rows = db(db.test).select()
print(rows)

on notebook2 is same like notebook1

but when remove *.table on database folder and rerun notebook2 the result for

db.tables
db.test.fields
db.test.string_0.type
rows = db(db.test).select()
print(rows)

is not found on notebook2

ref for pydal on web2py site

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Using-DAL-without-define-tables