THANK YOU!!! On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> Works just fine. The SQL adaption layer in your chosen Python -> SQLite > interface must be doing something wacky. Thank you for demonstrating that alternative packages do work. apsw looks good and a search of GitHub suggests that the dbtools package may implement the simple SQLite bridge to pandas that I need. "This module handles simple interfacing with a SQLite database. Inspired by ipython-sql <https://pypi.python.org/pypi/ipython-sql>, dbtools returns pandas DataFrame <http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe> objects from SELECT queries, and can handle basic forms of other SQL statements ( CREATE, INSERT, UPDATE, DELETE, and DROP). The goal is *not* to replicate the full functionality of SQLAlchemy <http://www.sqlalchemy.org/> or really to be used for object-relational mapping at all. This is meant to be used more for scientific data collection (e.g., behavioral experiments) as convenient access to a robust form of storage." https://github.com/jhamrick/dbtools I will test both apsw and dbtools after a breakfast meeting this morning. Background for group -- my short range goal is to fix page 175 of O'Reilly book "Python for Data Analysis by Wes McKinney. I have already contributed to errata using (a now dubious) SQLAlchemy solution. Both apsw and dbtools have potential to be much better solutions. This fix to the SQLite to Python/Pandas bridge is crucial for my next short term goal of developing simple code examples for this SQLite dataset for creating crosstabs and thematic maps in both Python and R. This in turn is part of a longer term project to implement the statistical methods vaguely described in Sasha Issenberg's book, "The Victory Lab" and any new statistical applications that may be used in the future. I greatly appreciate the help of this group at this critical juncture. I was about to abandon the entire Python branch of the project. Jim Callahan Orlando, FL On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
