>When I try to query a view (created at the SQLite command line) from
>IPython (I will provide specifics, but I want to ask a more general
>question first); Python complains about one of the joins inside the view.
>So, the called language interface is not passing to Python the view as a
>virtual table/resultset, but instead Python is parsing the view and and
>trying (and failing) to execute it.
>If necessary, I can send the whole Lafayette County, FL database (public
>record extract) via private email. Lafayette County is one of the smallest
>counties in Florida with only 4,556 voters which makes it ideal for
>developing convoluted SQL before moving the SQL to the big counties like
>Orange, Broward or Miami-Dade.
>Unfortunately, the Anaconda Python environment is a 250 megabyte
>(compressed) download.
>I am trying to understand enough so that I can write an intelligent
>question to the Python/SQLAlchemy/SQLite list.
>Why does Python get to see the innards of a View; when the query just
>involves columns (in a view) that flow straight through from the base table
>(as opposed to being joined from some other table)?
> Why does Python get to see the innards of a View; when the query just
> involves columns (in a view) that flow straight through from the base
> table
> (as opposed to being joined from some other table)?
None of the "normal" Python wrappers or interfaces do the things you attribute
to them.
2014-08-01 16:13:39 [D:\Temp]
>sqlite test.db
SQLite version 3.8.6 2014-08-01 01:40:33
Enter ".help" for usage hints.
sqlite> create table Voters ( VoterID integer primary key, firstname text,
lastname text, GenderID integer not null);
sqlite> create table Gender ( GenderID integer primary key, GenderName text not
null);
sqlite> create view ActiveVoters as select * from Voters join Gender using
(GenderID);
sqlite> insert into voters values (null, 'Freddie', 'Kruger', 1);
sqlite> insert into voters values (null, 'Marko', 'Pinhead', 1);
sqlite> insert into voters values (null, 'Lizzy', 'Borden', 2);
sqlite> insert into gender values (1, 'Male');
sqlite> insert into gender values (2, 'Female');
sqlite> select * from activevoters limit 3;
1|Freddie|Kruger|1|Male
2|Marko|Pinhead|1|Male
3|Lizzy|Borden|2|Female
sqlite> .quit
2014-08-01 16:13:44 [D:\Temp]
>python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn = sqlite3.Connection('test.db')
>>> cr = cn.cursor()
>>> cr.execute('select * from ActiveVoters limit 3').fetchall()
[(1, u'Freddie', u'Kruger', 1, u'Male'), (2, u'Marko', u'Pinhead', 1, u'Male'),
(3, u'Lizzy', u'Borden', 2, u'Female')]
>>> for row in cr.execute('select * from ActiveVoters limit 3').fetchall():
>>> print row
...
(1, u'Freddie', u'Kruger', 1, u'Male')
(2, u'Marko', u'Pinhead', 1, u'Male')
(3, u'Lizzy', u'Borden', 2, u'Female')
>>>
2014-08-01 16:15:19 [D:\Temp]
>python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> import apswrow
>>> cn=apsw.Connection('test.db')
>>> for row in cn.cursor().execute('select * from activevoters limit 3'): print
>>> row
...
Row(voterid=1, firstname=u'Freddie', lastname=u'Kruger', genderid=1,
gendername=u'Male')
Row(voterid=2, firstname=u'Marko', lastname=u'Pinhead', genderid=1,
gendername=u'Male')
Row(voterid=3, firstname=u'Lizzy', lastname=u'Borden', genderid=2,
gendername=u'Female')
>>>
Works just fine. The SQL adaption layer in your chosen Python -> SQLite
interface must be doing something wacky.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
