unable to see sqlite tables created through console using python console using gluon

[Shayn Raney]

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? 

[Niphlod]

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....

[Shayn Raney]

So I take it I need to redescribe the DB again using define_table()?  Ok, it works!  So strange.  I'm use to web.py SQL actions. 

>>> db.tables

[]

>>> 

>>> db.define_table('person',

Field('fname', 'string'),

Field('lname', 'string'),

Field('building', 'string')

)

<Table person (id,fname,lname,building)>

>>> rows = db(db.person.lname == 'Thorns').select()

>>> rows

<Rows (2)>

>>> for e in rows:

print e

<Row {'building': 'A', 'lname': 'Thorns', 'fname': 'Billy', 'id': 1L}>

<Row {'building': 'A', 'lname': 'Thorns', 'fname': 'Judy', 'id': 2L}>

>>> 

Thanks for the help. 

[Shayn Raney]

This is some rabbit hole,  looks like I need to do the following for some odd reason.  

db = DAL('sqlite://TestDB.sqlite', migrate_enabled=False)

[Niphlod]

IMHO you should read the manual. no rabbit holes included.

[Anthony]

On Wednesday, March 9, 2016 at 6:06:45 PM UTC-5, Shayn Raney wrote:

So I take it I need to redescribe the DB again using define_table()?  Ok, it works!  So strange.  I'm use to web.py SQL actions. 

The DAL models let you do a lot more via Python than you can do with web.py, which is not far removed from just writing raw SQL. If you want, in web2py you can simply use db.executesql(...) to execute any SQL you want without defining any models. Also, you can do:

db = DAL('sqlite://TestDB.sqlite', auto_import=True)

and it will automatically create model definitions based on the migration metadata (the definitions will be missing some web2py-only attributes that don't relate to the database itself, such as validators, labels, etc.).

Finally, if you need to work in a shell with the database/models of a particular web2py app, rather than starting a standard Python shell, you should instead do:

python web2py.py -S myapp -M

You'll then get a shell with the entire web2py environment, including any objects defined in your models -- so you would have access to the db object with all of its models defined.

Anthony

[Anthony]

On Wednesday, March 9, 2016 at 7:11:12 PM UTC-5, Shayn Raney wrote:

This is some rabbit hole,  looks like I need to do the following for some odd reason.  

db = DAL('sqlite://TestDB.sqlite', migrate_enabled=False)

No, you shouldn't need to do that (unless you want to protect against accidental changes).

Anthony