How to declare a pre-defined sqlite database

[pythonic...@gmail.com]

Hi,

I'm new to web2py. I have a SQLite database that must be pre-created and pre-populated (done by a separate python script) and would like to use it in my web2py application. There are currently 22 tables with anything from 2 to 12 columns and I anticipate considerable changes will be made to the structure as I develop this.

My understanding is that I need to use the "define_table" DAL thing in my model.

However, my questions:

a) Is there a way to not have to do this but have web2py pick it up automatically?

b) If not, is there a way to automatically create the DAL code? I can create a Python script to do it for me, but it seems like something someone would have done already.

Thanks.

[黄祥]

i think you can do it with the extract_sqlite_models.py that ships with web2py. the path is web2py/scripts/extract_sqlite_models.py.

you must have python installed when execute in windows environment.

best regards,

stifan

[pythonic...@gmail.com]

Thanks, that seems the kind of thing I want, however it seems to be on about "legacy_db" - what does that mean/do?

Also, the output is rather shorter than I expected. Each table entry only contains:

#--------
legacy_db.define_table('wfs100getfeature',
    migrate=False)

Is this all I need? (I'm not in a position to test it myself - I'm new to web2py and don't have any sort of application yet).

====

Also, there's a trivial bug:

    print 'USAGE:\n\n    extract_mysql_models.py data_basename\n\n'

Shouldn't say mysql there.  :-)

[黄祥]

pardon me, which script that you execute? because in the scripts folder there is 4 scripts for extract the table into models (mysql, oracle, pgsql and sqlite). and which web2py version did you use and what is your os environment? please use the script that match with your database backend.

and for legacy db please take a look at the book in dal chapter

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Legacy-databases-and-keyed-tables

best regards,

stifan

[pythonic...@gmail.com]

Hi Stifan,

pardon me, which script that you execute? because in the scripts folder there is 4 scripts for extract the table into models (mysql, oracle, pgsql and sqlite). and which web2py version did you use and what is your os environment? please use the script that match with your database backend.

I used extract_sqlite_models.py - which is why I reported the bug about the text saying MySQL. On Windows using Python 2.7 and the database is sqlite.

and for legacy db please take a look at the book in dal chapter

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Legacy-databases-and-keyed-tables

Thanks, I've just had a read through the legacy section (it's short), but it's not helped much. As best I can tell from reading that, basically web2py will only be able to use the id column; so I won't be able to query by other columns (which is what I need)? Also, I don't have an auto-incrementing id column in my tables or any primary key in most of them (nor do I particularly want one).

I'm still no closer to understanding this I'm afraid. Does this mean there's no good way to use non-web2py tables in web2py?

Thanks,

Jonathan

[Niphlod]

"legacy" db are the ones that were not created in web2py applications.
The concept behind is that you can still query whatever database you want just using db.define_table(......, migrate=False).
That being said, without an auto-incrementing column or a PK you'd loose quite a bit of DAL features/shortcuts. It's hard to tell if you're going to face those limitations without knowing what you want to do with those databases.

[pythonic...@gmail.com]

Thanks. That allows me to narrow it down to two questions:

1) I'm just going to want to use various select queries against views/tables (including joins) in the "legacy" database, nothing else. This database will never need to be altered by web2py. Will I be able to do that?

2) If I want to do want to write anything, I guess web2py will let me have a separate native database which I can use for that. You can have multiple databases right? A mix for native and legacy? Neither will need to interact directly with the other and the logical separation would be well suited to this application.

Cheers.

[Niphlod]

On Sunday, January 5, 2014 1:57:24 PM UTC+1, pythonic...@gmail.com wrote:

Thanks. That allows me to narrow it down to two questions:

1) I'm just going to want to use various select queries against views/tables (including joins) in the "legacy" database, nothing else. This database will never need to be altered by web2py. Will I be able to do that?

sure, but don't expect being able to use something like db.table(5) as a shortcut 'cause the missing PK. selecting with the "usual" db(db.table.field == 'something').select() will work out of the box, unless your fields have some really strange names, but those can be circumvented.
 

2) If I want to do want to write anything, I guess web2py will let me have a separate native database which I can use for that. You can have multiple databases right? A mix for native and legacy? Neither will need to interact directly with the other and the logical separation would be well suited to this application.

yep, you can have as much dbs as you like.

db0 = DAL(....)
db1 = DAL(....)
db2 = DAL(....)

etc etc etc

[pythonic...@gmail.com]

Thanks! I'll give it a go and see what happens. :-)