sqlite legacy support

16 views
Skip to first unread message

Timmie

unread,
Dec 7, 2009, 4:39:19 AM12/7/09
to web2py-users
Dear web2py users,
does the support for legacy tables introduced in 1.7.x also legacy
support sqlite tables?

Thanks,
Timmie

DenesL

unread,
Dec 7, 2009, 7:14:02 AM12/7/09
to web2py-users
"Support for legacy tables" can have two meanings:

1) Legacy tables that need an alias for the id field.
sqlite supports this case.

2) Legacy tables that use primary keys (not an id field) to get the
records.
sqlite does not support this AFAIK.

Denes.

Tim Michelsen

unread,
Dec 7, 2009, 4:35:50 PM12/7/09
to web...@googlegroups.com
> "Support for legacy tables" can have two meanings:
>
> 1) Legacy tables that need an alias for the id field.
> sqlite supports this case.


> 2) Legacy tables that use primary keys (not an id field) to get the
> records.
> sqlite does not support this AFAIK.


What I have is a CSV file.
The data is from:
http://www.ncdc.noaa.gov/oa/climate/rcsg/cdrom/ismcs/alphanum.html

I converted this into a SQLITE file with sqlite-browser.

How can I make web2py connect to that data base?
Normall, web2py would require to generate its ID field.
But I would rather use the "Number" column as id / identifier.

Could you please give me some hints?

Thanks in advance,
Timmie

Thadeus Burgess

unread,
Dec 7, 2009, 4:55:59 PM12/7/09
to web...@googlegroups.com
number is not unique, can it even be used as a PK?

-Thadeus
> --
>
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>
>
>

DenesL

unread,
Dec 7, 2009, 5:23:13 PM12/7/09
to web2py-users
Tim,

for some reason I can't access
but as Thadeus said there has to be column with unique values which
can be used as the ID field for the table.
If cleaning the "Number" column is too much trouble you can just add a
sequence number to each record and that can be used as the ID.
After that you can import from CSV using the admin.

Denes.

Tim Michelsen

unread,
Dec 7, 2009, 7:19:05 PM12/7/09
to web...@googlegroups.com
> number is not unique, can it even be used as a PK?
thanks for the response.

How can I use the first number as PK in this better version of the list:
http://www.wetterzentrale.de/klima/stnlst.html

Please assist.

Thanks in advance,
Timmie

DenesL

unread,
Dec 8, 2009, 7:04:23 AM12/8/09
to web2py-users
In your models

db.define_table('t',
Field('staid','integer'),
Field('stacall','string'),
Field('location','string',notnull=False,default=''),
Field('state','string',notnull=False,default=''),
Field('lat','string'),
Field('lon','string'),
Field('elev','integer',notnull=False,default=0))

and create a CSV like this

t.staid,t.stacall,t.location,t.state,t.lat,t.lon,t.elev
010017,ENFR,FRIGG,NO,5956N,00200E,0
010015,ENBL,FORDE/BRINGELAND,NO,6127N,00552E,0150
010030,,HORNSUND RIVER,NO,7700N,01530E,0012
...

note that if elev is missing you must put a 0 (zero).

Tim Michelsen

unread,
Dec 9, 2009, 6:19:36 PM12/9/09
to web...@googlegroups.com
> and create a CSV like this
>
> t.staid,t.stacall,t.location,t.state,t.lat,t.lon,t.elev
> 010017,ENFR,FRIGG,NO,5956N,00200E,0
> 010015,ENBL,FORDE/BRINGELAND,NO,6127N,00552E,0150
> 010030,,HORNSUND RIVER,NO,7700N,01530E,0012
> ....
>
> note that if elev is missing you must put a 0 (zero).
Thanks.

By this appraoch you import the data into the database used by web2py.
What if the data base already exists as a independant sqlite file and I
just want web2py connect to that one?

How would I show web2py (my model) which field to use as ID?

Thanks,
Timmie

mdipierro

unread,
Dec 9, 2009, 7:28:46 PM12/9/09
to web2py-users
normally web2py uses the field called "something.id" as id. All it
does it to ignore it since it will use its own internal "id" on
import.

Brian M

unread,
Dec 9, 2009, 8:27:19 PM12/9/09
to web2py-users
You can tell your model the path to the existing sqlite database file
with something like

db_path = os.path.join('path','to','your','existing.db") #path to
wherever you existing DB is
db = DAL('sqlite://'+db_path)

As for telling it which existing field to use as the ID, as DenesL
pointed out if the field isn't already named ID I don't think you can
at the moment.

If you really want to use the station number (t.staid) as your ID, why
not just add a new field called ID to your existing table (using
sqlite-browser) and then set the value to match t.staid? Something
like

UPDATE your_table SET ID = your_table.staid;

Now you've got an integer field called ID and web2py should be happy.
Well at least as long as you aren't planning on using web2py to add
new station entries - I think that it assumes ID is part of a sequence
and will automatically assign the next number for you, which wouldn't
be the case here. Also, be aware that if you set ID as an integer
field (which web2py is going to want) your Station Number of "010010"
is going to turn into ID 10010, so watch out if you need those leading
zeros to mean something.

Are you planning on using web2py to regularly pull in that station
list or will it be static once you've taken care of the initial
import? If it's static, then the above trick of adding your own ID
field should work. If you need to update it regularly with web2py then
you might need to keep looking. If necessary you could just bypass the
DAL for the inserting and use your own SQL and db.executesql()
instead.

#untested code
#read the contents of csv file into a dictionary
csv_contents = csv.DictReader(open(csv_path),dialect='excel') #assumes
you've already converted that source HTML doc to CSV

#loop through the stations
for station in csv_contents:
data = [station['Number'], station['Number'], station['Call',
station['Name + country/state'], station['Lat'], station['Lon'],
station['Elev (meters)']]
#notice that station['Number'] is included twice - once for the ID
and again for staid
db.executesql("""INSERT INTO your_table (ID, staid, stacall,
location, lat, lon, elev) VALUES (?, ?, ?, ?, ?, ?, ?)""", data)


~Brian

On Dec 9, 5:19 pm, Tim Michelsen <timmichel...@gmx-topmail.de> wrote:

Tim Michelsen

unread,
Dec 10, 2009, 7:50:47 PM12/10/09
to web...@googlegroups.com
> You can tell your model the path to the existing sqlite database file
> with something like
[...]

Thanks a lot for your verbose help! I really appreciate it.

> As for telling it which existing field to use as the ID, as DenesL
> pointed out if the field isn't already named ID I don't think you can
> at the moment.
>
> If you really want to use the station number (t.staid) as your ID, why
> not just add a new field called ID to your existing table (using
> sqlite-browser) and then set the value to match t.staid? Something
> like
I am not a database guru. So I still do not understand why t.staid could
not be the PK (= ID).

Regards,
Timmie

mdipierro

unread,
Dec 10, 2009, 11:34:29 PM12/10/09
to web2py-users
For a legacy db you can do

db.define_table(...Field('staid','id'),...)

so staid is the id. It assumes id is an autoincrement or integer
field.

Brian M

unread,
Dec 11, 2009, 1:41:41 AM12/11/09
to web2py-users
t.staid could be the primary key as far as the database is concerned.
The issue is that web2py expects the primary key field to be called
ID.

I'd give Massimo's suggestion of db.define_table(...Field
('staid','id'),...) a shot - he's the man, so he ought to know.

Massimo - what exactly does Field('staid','id') do? Is that just
telling web2py that field staid should be treated as the ID field? Is
'id' now a valid fieldtype just like 'string' or 'integer'?

~Brian

On Dec 10, 6:50 pm, Tim Michelsen <timmichel...@gmx-topmail.de> wrote:

mdipierro

unread,
Dec 11, 2009, 9:33:37 AM12/11/09
to web2py-users
It declares that there is a field 'staid' of type 'id' and this
overrides the default 'id' field. Basically 'staid' will be the
primary key and will be used in place of 'id'. This is a new feature
that has not been tested much but it should work fine.

Massimo

Yarko Tymciurak

unread,
Dec 11, 2009, 11:03:46 AM12/11/09
to web2py-users

On Dec 11, 12:41 am, Brian M <bmere...@gmail.com> wrote:
> t.staid could be the primary key as far as the database is concerned.
> The issue is that web2py expects the primary key field to be called
> ID.
>
> I'd give Massimo's suggestion of db.define_table(...Field
> ('staid','id'),...) a shot - he's the man, so he ought to know.
>
> Massimo - what exactly does Field('staid','id') do? Is that just
> telling web2py that field staid should be treated as the ID field? Is
> 'id' now a valid fieldtype just like 'string' or 'integer'?

This is probably another example where we would have benefitted from a
code review / design review.

"Field()" is constructor (manual, p.153), and used to define table
fields within the define_table constructor (the way that has been
web2py/DAL, in one form or another, "forever").

This is perhaps the first "special" form of "Field()", and the first
one to define default behavior of the "ID" field (which is why it is
natural at first blush to use the "Field()" constructor for this, but
as we see from this thread it is not intuitive, and can confuse by
virtue of a new, "special" form of a heretofore clear constructor).

define_table() has a special first parameter, which is the table name,
and an additional parameter which defines "special" table _behavior_,
that is "migrate=some_value".

Probably a better form for this particular change in default behavior
would have been something like: id='stiad'

Having an attribute of table called "id", once you look at the code,
shows all the places that the "magic field" (literal) "id" exists,
and should be replaced by an attribute (with default of id='id'; but
I digress...

I think, for define_table, a parameter of the form: id='staid'

would be both easier to explain (changes some default behavior of the
table, just as migrate does), more consistent with the historical
implementation, and at the same time would give the added benefit of
showing up a common, and problematic coding style w/in gluon of using
FNMs ("flaming magic numbers" - that is, a repetitive constant value
where a symboic value would make the code both more readable, and more
maintainable).

- Yarko

mdipierro

unread,
Dec 11, 2009, 11:13:00 AM12/11/09
to web2py-users
I reviewed it! There is nothing special in Field(...,'id').

All tables must have a field of type 'id' with two exceptions:
- if you do not specify one, one is created automatically and called
'id' as the type (Field('id','id'), the default)

- legacy database can have more complex keys (Denes' keyed tables) and
they do not need an 'id' field (so far works only for mssql and ingres
I think).

Massimo

On Dec 11, 10:03 am, Yarko Tymciurak <resultsinsoftw...@gmail.com>
wrote:

mdipierro

unread,
Dec 11, 2009, 11:21:11 AM12/11/09
to web2py-users
Yarko,

This feature as well as the new Keyed Tables (which do somewhat what
you say) were proposed, implemented in trunk, and there was an RFC on
this mailing list. They both made in trunk one month ago. I understand
you do not like the syntax but you cannot complain about it now that
it is done.

I believe the current implementation is much cleaner than the one you
propose, in fact it was implemented without creating any new API.
Unfortunately these features are only one month old therefore they are
not in the book, need more testing and need more documentation. The
Keyed tables for now only form for some dbs. The functionality will be
extended as needed by users while keeping the current syntax.

There is no FNM (?!). In fact the 'id' type always existed. The 'id'
field has always been of 'id' type since web2py 1.0 in October 2007.

Massimo


On Dec 11, 10:03 am, Yarko Tymciurak <resultsinsoftw...@gmail.com>
wrote:

Yarko Tymciurak

unread,
Dec 11, 2009, 11:36:35 AM12/11/09
to web2py-users
On Dec 11, 10:13 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I reviewed it! There is nothing special in Field(...,'id').
>
> All tables must have a field of type 'id' with two exceptions:

> - if you do not specify one, one is created automatically and called
> 'id' as the type (Field('id','id'), the default)

... and this is what I meant when I said there is something special
about 'id' - it is not like other fields, e.g. "special";

maybe this is ok - but there is evidence in this thread of confusion
(and, yes, I am aware it is new...)

>
> - legacy database can have more complex keys (Denes' keyed tables) and
> they do not need an 'id' field (so far works only for mssql and ingres
> I think).

... I am not familiar w/ Denes's extension (and was not commenting on
it; but thanks for pointing it out, that it is different).

Yarko Tymciurak

unread,
Dec 11, 2009, 11:57:58 AM12/11/09
to web2py-users
On Dec 11, 10:21 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Yarko,
>
> This feature as well as the new Keyed Tables (which do somewhat what
> you say) were proposed, implemented in trunk, and there was an RFC on
> this mailing list. They both made in trunk one month ago. I understand
> you do not like the syntax but you cannot complain about it now that
> it is done.

... I am merely commenting on what I saw in this thread, and raising a
suggestion that google code-review could be more useful than group
threads (and comments / decisions stay with revision history). I
hear that you think group / email threads are "good enough" - maybe
so, but I stand by my suggestion that this thread is evidence that the
code-review process in place on google code would be beneficial beyond
what was in place.

Perhaps you are right - perhaps "Field()" is appropriate here, even
though everything about it's handling is special (all the other field
options, as far as I can tell, do not apply).

I do see this was / is not clear on this thread, and the discussion /
decisions are not part of the repository (a benefit which google code-
review will add).

>
> I believe the current implementation is much cleaner than the one you
> propose, in fact it was implemented without creating any new API.

Perhaps; no new API either way... but your reply seems as if in
response to an offense... none intended;

> Unfortunately these features are only one month old therefore they are
> not in the book, need more testing and need more documentation.

Denes has documeted the items not documented in this list; the code-
review thing on google code would add a level of documetation too.

> The
> Keyed tables for now only form for some dbs. The functionality will be
> extended as needed by users while keeping the current syntax.
>
> There is no FNM (?!).

FMN - magic numbers; in CS it is something we graded down for
strongly in our University (of course, this was years ago);

What this refers to is constants used, where a "name" - an abstract,
descriptive form would serve better, and even more strongly where the
same constant was repeatedly used. There are _some_ cases where a
constant is clearer, and surely in Python so much is processed as
strings.

But in this case I specifically refer to a string - the SAME string -
where it is used in a multiple of ways, and thus is an example of the
intent of the term I fondly carry with me all these years from
University, "FMN" - that is, something that could be made clearer.

A simple grep for 'id' in gluon/sql.py shows it appears 40 times; of
this, it appears in a line where 'type' is somehow part of the line (a
use of the constant string which I would say is valid, and clear) all
of 7 times. That leaves 33 times where - upon perusing the code -
one is left to "parse" on their own: is this referring to the
definition of the field type? Is this a reference to the field type
in a DB backend descriptor? Is this looking for the field NAMED id?

In short, it is an example of why my professors even thought up the
term FMN - much like:

result = read.port[3]

is not as clear as (say)

result = read.port[USB_CHIP_SELECT]

These really are basics of communicating, I think - the "literature"
of coding.


> In fact the 'id' type always existed. The 'id'
> field has always been of 'id' type since web2py 1.0 in October 2007.

I understand. That is not what I am talking about. Your code has
'id' as the field type; 'id' as the field name, and possibly other
kinds of references.
Reply all
Reply to author
Forward
0 new messages