DAL is a database abstraction layer. you need to define tables EVERY time you need to access them. define_table() accidentally creates table if migrations are turned on, but define_table() doesn't map to "create a table" .... it stands for "define an entity that is mapped to a table".
tl;dr: if you kill the process and reinitiate the DAL connection, DAL doesn't do introspection on the tables present on the database. it'll interact only with tables which have been previously defined. see http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer for all the gory details.... On Wednesday, March 9, 2016 at 11:49:38 PM UTC+1, Shayn Raney wrote: > > I've been prototyping a few projects in a stand alone python script, all > of this out the web2py framework I am seeing an issue where I can not > access an existing sqlite tables and data I've created/inserted. But when > I directly connect to it with SQLite console or other tools I have, the > tables and data do exist. > > Example DB I create: > >>> > >>> from gluon import DAL, Field > >>> > >>> db = DAL('sqlite://TestDB.sqlite') > >>> db.define_table('person', > Field('fname', 'string'), > Field('lname', 'string'), > Field('building', 'string') > ) > <Table person (id,fname,lname,building)> > >>> > >>> db.person.insert(fname='Billy', lname='Thorns', building='A') > 1L > >>> db.person.insert(fname='Judy', lname='Thorns', building='A') > 2L > >>> db.person.insert(fname='Edd', lname='Spurs', building='A') > 3L > >>> > >>> rows = db(db.person.lname == 'Thorns').select() > >>> for e in rows: > print e > > <Row {'building': 'A', 'lname': 'Thorns', 'fname': 'Billy', 'id': 1L}> > <Row {'building': 'A', 'lname': 'Thorns', 'fname': 'Judy', 'id': 2L}> > >>> db.commit() > > But when I reopen the 'TestDB.sqlite' file in a new python shell I > suddenly see the data or table does not exist. > > >>> from gluon import DAL, Field > >>> > >>> db = DAL('sqlite://TestDB.sqlite') > >>> rows = db(db.person.lname == 'Thorns').select() > > Traceback (most recent call last): > File "<pyshell#11>", line 1, in <module> > rows = db(db.person.lname == 'Thorns').select() > File > "C:\Users\Navajo\Documents\code\web2py_win\gluon\packages\dal\pydal\base.py", > line 921, in __getattr__ > return BasicStorage.__getattribute__(self, key) > AttributeError: 'DAL' object has no attribute 'person' > >>> db.tables > [] > >>> > > If I open the table within a SQLite console I see there is data: > > SQLite version 3.11.1 2016-03-03 16:17:53 Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> .open TestDB.sqlite > sqlite> select * from person; > > 1|||Thorns|A|Billy > 2|||Thorns|A|Judy > 3|||Spurs|A|Edd > > > > Any suggestions on how I can reopen a file I've made? > > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
