0

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?

klausz
  • 33
  • 6

1 Answers1

0

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

steve van christie
  • 1
  • 1
  • Steve, what do you mean by: *.tables? – klausz Jan 20 '21 at 16:59
  • Steve, what do you mean by: *.tables? But the central question or idea. Suppose I have access rights to all tables in the database which can be a limiting factor in Oracle. I would interpret: 'If we just need access to the data but not to the web2py table attributes, we get away without re-defining the tables but simply asking web2py to read the necessary info from the metadata in the .table files: .. This allows us to access any db.table without need to re-define it.' so that I access a database (with necessary access rights) and ask what tables are there? Getting all table names. – klausz Jan 20 '21 at 17:07
  • *.table is a file created when a database table is created with pydal, and will be updated if you have modified your define_table. please take a look into your databases folder then you will see *.table and sql.log files – steve van christie Jan 20 '21 at 20:30
  • I did not find a file *.table in the file directory. What I always have is a file for each table. But not inside the SQL commands. But what i found: SELECT name FROM sqlite_master WHERE type='table' Thats the corresponding to the Oracle select all_tables command. – klausz Jan 21 '21 at 10:30
  • The log file is there when I use py4web (web2py) but not when i use the pyDAL component through jupyter notebooks. – klausz Jan 21 '21 at 10:37
  • not enough info why you couldnt see *.table, it usually created by pydal either it standalone module or within any others framework included on jupyter env, so basically *.table contain information that represent what was written on the define_table. quick conclusion is when not define_table and set auto_import=True, the pydal will see the contain of *.table, and can communicate with the table (like example given above) – steve van christie Jan 21 '21 at 11:51