Python and Sqlite3

[neroth]

A bit of background: I've been working on a python script to do some calculations on a land use model. Basically, I take amount of space developed in the future and pull out a table that has the amount of space by geographic area within the larger area. I load that table, as well as some other tables with information about the types of growth into a sqlite database using python.

Then I try to run a query against the data that creates a view of the data assembled and ready to run calculations on. I can review the tables; they're loaded and committed to the database.

But, executing the sql returns an error that the first field in the select statement doesn't exist.

I can then take that same sql, paste it into the "Execute SQL" box in Firefox's sqlite manager, and it executes without problem.

I'm using python 2.6.6 (32bit) and the built in sqlite3 module. I get the same error on two different computers. Any idea what's going on?

Here's the sql:

CREATE VIEW preptable2_testing AS SELECT a.sequence, a.subarea, a.zn, a.cnt, a.hhsize, a.ephh, b.landuseabbr, b.landusetype, b.squarefeet, b.far, b.netdensity, b.pctvacant, b.priority, b.normorrand, b.expsize FROM (SELECT c.sequence, c.subarea, c.zn, c.luid, c.cnt, d.pphh as hhsize, d.ephh as ephh FROM testing as c LEFT JOIN seq as d ON c.sequence = d.sequence) as a  LEFT JOIN lu as b ON a.luid = b.luid

The error handed back by the exception is:

no such column: a.sequence

[woooee]

This just says that there is no column in the DB named "a.sequence". There is no way to tell any more from a single line of code.

[neroth]

The above query worked fine if I pasted it into SQLite manager in Firefox. I found that If I converted it from a CREATE VIEW into CREATE TABLE it worked fine. Seems that there was something going on with the python DB-API for SQLite. I can live with creating temporary tables instead of the views for intermediate steps, and I'd always planned on making final calculations into tables.

Thanks,