Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
Roger,
Sorry for unintended slights.
My haste and terseness may have confused matters.
Another long story (below), but if you are in a hurry, my question is:
TERSE QUESTION
Is the sqlite3_table_column_metadata() SQLite C API function also wrapped
by the APSW Python Library?
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata
Or is there another way to get the primary key without scraping the SQL
(string) field in the SQLite3 master table?
LONG STORY
I like Jessica Hamrick's Python dbtools library for the final conversion
to the Python pandas library data structures. I have posted the necessary
changes to her blog:
I like it, but I need to import a VIEW from SQLite to Pandas (I want to
shield my users from scary SQL JOINS and just present them one flat table).
Underlying Python/SQLite libraries support SQL VIEWS (see SQLite mailing
list Views as Virtual Tables -- Command line vs. Called Interface. So, in
your code, need to change cmd = SELECT name FROM sqlite_master WHERE
type='table'
to
cmd = SELECT name FROM sqlite_master WHERE type IN ('table','view')
SQLite VIEWs are read only (not update-able)
so also need error msg-s if type='view' in insert, update and delete.
I would do myself, but I just downloaded Git yesterday and am not yet
familiar with version control.
http://www.jesshamrick.com/2013/04/13/on-collecting-data/
I also noticed that Jessica Hamrick's dbtools was scraping the SQL (string)
column in the SQLite master table with regular expressions to obtain the
column names, column types and the primary key (to use in creating pandas
object). I pointed out that the Python APSW library has wrapped the SQLite3
C API functions (sqlite3_column_name sqlite3_column_decltype) for the
column name and column type. But, I don't see how to get the primary key in
APSW. Is the sqlite3_table_column_metadata() SQLite C API function
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata
also wrapped by the APSW Python Library? I posted:
Also, you don't have to scrape the colnames and types with regular
expressions; there is an api for that.
In the APSW SQLite Python library, there is a cursor.getdescription()
method that:
returns information about each column is a tuple of (column_name,
declared_column_type). The type is what was declared in the CREATE TABLE
statement - the value returned in the row will be whatever type you put in
for that row and column. (This is known as manifest typing which is also
the way that Python works. The variable a could contain an integer, and
then you could put a string in it. Other static languages such as C or
other SQL databases only let you put one type in - eg a could only contain
an integer or a string, but never both.) The APW calls the SQLite C API
functions:
sqlite3_column_name
sqlite3_column_decltype
So, [with APSW] you [would be] are relying on SQLite3's native parsing and
not someone else's random regex or homebrew parser.
http://rogerbinns.github.io/apsw/cursor.html#cursor-class
BTW, open source, Notepad++, has nice syntax highlighting for Python.
I have an afternoon and evening full of meetings, but I will attempt this
fix myself tomorrow (Tuesday) morning.
Cheers.
Jim Callahan
Orlando, FL
On Sun, Aug 3, 2014 at 11:31 AM, Roger Binns rog...@rogerbinns.com wrote:
Disclosure: I am the apsw author
On 08/02/2014 10:19 AM, Jim Callahan wrote:
I got apsw to work, but it had a curious side-effect
-- it clobbered my IPython prompt (replaced prompt with smiley faces).
APSW certainly didn't do that. It doesn't do anything - you have to make
calls and get responses.
If you use the APSW shell then it will use ANSI escape sequences to colour
the output. However this is only done if the output is a terminal, and can
be turned off. (That is the case for Linux Mac. For Windows you also
need to install colorama.)
For those who are interested.
1. downloaded apsw -- does not work with Python's package manager pip
http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
APSW moved from googlecode a while back. It is at:
https://github.com/rogerbinns/apsw
This explains why:
http://www.rogerbinns.com/blog/moving-to-github.html
APSW is actually now on pypi. Someone else put it up there and it has no
connection to me. It is also extremely unlikely to install because it
doesn't handle the SQLite dependency, nor have Windows binaries.
3. commented out import apswrow from suggested script (not found, not
needed)
That has no connection to APSW either. It is written by someone else to
turn rows returned from a tuple into also having the column names.
Roger
___
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
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
TERSE QUESTION Is the sqlite3_table_column_metadata() SQLite C API function also wrapped by the APSW Python Library? http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata I don't see it presently. Or is there another way to get the primary key without scraping the SQL (string) field in the SQLite3 master table? http://www.sqlite.org/pragma.htm See the following PRAGMA's for retrieving table/index/foreign key info. They return data in standard table format and work notwithstanding the support of the API and should work just fine with any interface to the SQLite library (even from the command line tool): PRAGMA table_info PRAGMA index_list PRAGMA index_info PRAGMA foreign_key_List ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
Disclosure: I am the apsw author On 08/02/2014 10:19 AM, Jim Callahan wrote: I got apsw to work, but it had a curious side-effect -- it clobbered my IPython prompt (replaced prompt with smiley faces). APSW certainly didn't do that. It doesn't do anything - you have to make calls and get responses. If you use the APSW shell then it will use ANSI escape sequences to colour the output. However this is only done if the output is a terminal, and can be turned off. (That is the case for Linux Mac. For Windows you also need to install colorama.) For those who are interested. 1. downloaded apsw -- does not work with Python's package manager pip http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries APSW moved from googlecode a while back. It is at: https://github.com/rogerbinns/apsw This explains why: http://www.rogerbinns.com/blog/moving-to-github.html APSW is actually now on pypi. Someone else put it up there and it has no connection to me. It is also extremely unlikely to install because it doesn't handle the SQLite dependency, nor have Windows binaries. 3. commented out import apswrow from suggested script (not found, not needed) That has no connection to APSW either. It is written by someone else to turn rows returned from a tuple into also having the column names. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
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',
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
Keith,
I got apsw to work, but it had a curious side-effect
-- it clobbered my IPython prompt (replaced prompt with smiley faces).
For those who are interested.
1. downloaded apsw -- does not work with Python's package manager pip
http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
2. Ran downloaded installation program (forgot to close IPython window)
3. commented out import apswrow from suggested script (not found, not
needed)
4. changed file name to my file VotersLAF.db
# have to download apsw directly without using Python's pip
# http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
# downloaded Windows Python 2.7 64 bit version
# apsw-3.8.5-r1.win-amd64-py2.7.exe
import apsw
# import apswrow (apswrow not found and not needed)
cn=apsw.Connection('VotersLAF.db')
for row in cn.cursor().execute('select * from activevoters limit 3'): print
row
It worked, it read the correct data from the SQLite3 SQL VIEW. Thanks!
Jim Callahan
Orlando, FL
On Sat, Aug 2, 2014 at 5:35 AM, Jim Callahan jim.callahan.orla...@gmail.com
wrote:
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
[sqlite] Views as Virtual Tables -- Command line vs. Called Interface
At the SQLite3 command line I can't tell the difference between a view and
a table without looking at the schema (that's a good thing).
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.
My question is: why is the view processing being handled by the calling
language instead of by SQLite? Shouldn't the Call Level Interface recognize
when a table is actually a view, process the view, but return a resultset
without the calling program being any the wiser?
SPECIFICS
I created a database, a table and a view using the SQLite Command interface.
SQLite Version 3.8.0.1
database: VotersLAF
table:Voters
view: ActiveVoters
Simple query (table version)
SELECT FirstName, LastName FROM Voters LIMIT 3;
Simple query (view version)
SELECT FirstName, LastName FROM ActiveVoters LIMIT 3;
Both queries work from the SQLite 3 command line.
From Python, the first query (using the table) works, but the second query
(using the view) fails, with a message referring to a join that is internal
to view and not visible from the query:
OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'
The view ActiveVoters limits the number of rows and adds some additional
information via JOINS. In this case the error message is referring GenderID
which is a simple look up from 'M' and 'F' to 'Male' and 'Female' which
works at the SQLite Command Line Interface and is irrelevant to the query
at hand, but for the fact that it is included in the view definition.
Table: Voters
VoterID
FirstName
LastName
GenderID
Table: Gender
GenderID
GenderName
View: ActiveVoters
Voters.VoterID
FirstName
LastName
Voters.GenderID
Gender.GenderName
I used some -- comments in the view definition.
This is not the list for a Python question, but if it helps or if anyone is
curious, I just downloaded the Anaconda distribution this week and am
running Python 2.7 on Windows 7.
Windows 7 Service Pack 1
Python 2.7.7
Anaconda 2.0.1 (64 bit) (default June 11, 2014 10:40:02)
[MSC v.1500 AMD 64 bit (AMD64) ]
IPython 2.1.0
The interactive shell is IPython with the pylab option.
IPython --pylab
import numpy as np
import matplotlib as pt
import sqlite3
import pandas as pd
from sqlalchemy import create_engine
# OBSOLETE: con = sqlite3.connect('VotersLAF.db')
# SQLite database via the pysqlite driver.
# Note that pysqlite is the same driver as the
# sqlite3 module included with the Python distrib.
# Connect String: sqlite+pysqlite:///file_path
# http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html
#module-sqlalchemy.dialects.sqlite.pysqlite
engine = create_engine('sqlite+pysqlite:///VotersLAF.db')
# TABLE Version works
pd.read_sql_query('SELECT FirstName, LastName FROM Voters LIMIT 3', engine)
# VIEW Version does not work
pd.read_sql_query('SELECT FirstName, LastName FROM ActiveVoters LIMIT 3',
engine)
OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'
Why does the Python program know anything about the view? Why isn't table
handling transparent like at the SQLite3 command line?
Thanks,
Jim Callahan
Orlando, FL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
On 1 Aug 2014, at 4:45pm, Jim Callahan jim.callahan.orla...@gmail.com wrote: column is not present in both tables This is usually the result of using the syntax JOIN table USING column because SQL requires columns of that name to be present in both tables. Instead of that phrasing see if you can use this one: JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn If that doesn't help ... SQLite Version 3.8.0.1 Is that the version your IPython interface is using ? Can you give us the output of SELECT sqlite_version() when done through the iPython interface ? And I'm afraid we may also need to see the view definition, so can you tell us whatever you used for your CREATE VIEW command ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin slav...@bigfraud.org wrote:
On 1 Aug 2014, at 4:45pm, Jim Callahan jim.callahan.orla...@gmail.com
wrote:
column is not present in both tables
This is usually the result of using the syntax JOIN table USING column
because SQL requires columns of that name to be present in both tables.
Instead of that phrasing see if you can use this one:
JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
I changed the syntax from:
LEFT JOIN Gender USING (GenderID)
to
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
Again it worked on the SQLite command line, but not when called from
Python.
If that doesn't help ...
SQLite Version 3.8.0.1
Is that the version your IPython interface is using ? Can you give us the
output of
SELECT sqlite_version()
when done through the iPython interface ?
pd.read_sql_query('SELECT sqlite_version()', engine)
0 sqlite_version()
3.6.21
And I'm afraid we may also need to see the view definition, so can you
tell us whatever you used for your CREATE VIEW command ?
CREATE VIEW ActiveVoters2
AS SELECT
Voters.CountyID,
Voters.VoterID,
LastName, Suffix, FirstName,MidName,
Supress,
ResAddress1,
ResAddress2,
ResCity, ResST, ResZip9,
MailAddress1,
MailAddress2,
MailAddress3
MailCity, MailST, MailZip9, MailCountry,
Voters.GenderID,
Voters.RaceID,
BirthDate,
RegDate,
Voters.PartyID,
Precinct, PGroup, PSplit, PSuffix,
Voters.StatusID,
CD, HD, SD, CC, SB,
AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
2014 -- FVRS
Email, -- Added Email-- Thursday July 24,
2014 -- FVRS
County.CountyName,
Gender.GenderName,
Race.RaceName,
Party.PartyName,
Status.StatusName,
VoterHistoryCol.ENov2012,
VoterHistoryCol.EAug2012,
VoterHistoryCol.EPPP2012,
VoterHistoryCol.ENov2010,
VoterHistoryCol.EAug2010,
VoterHistoryCol.ENov2008,
VoterHistoryCol.EAug2008,
VoterHistoryCol.EPPP2008,
(CASE WHEN substr(BirthDate,6,5) = 11-06 -- Election Day 2012:
Nov 6, 2012
THEN 2012 - substr(BirthDate,1,4) -- Had birthday
ELSE 2012 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeENov2012, -- Age as of Nov 6,
2012
(CASE WHEN substr(BirthDate,6,5) = 08-26 -- Election Day 2014:
Aug 26, 2014
THEN 2014 - substr(BirthDate,1,4) -- Had birthday
ELSE 2014 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeEAug2014, -- Age as of Aug 26,
2014
(CASE WHEN substr(BirthDate,6,5) = 11-04 -- Election Day 2014:
Nov 4, 2014
THEN 2014 - substr(BirthDate,1,4) -- Had birthday
ELSE 2014 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeENov2014 -- Age as of Nov 4, 2014
FROM Voters
INNER JOIN County ON County.CountyID = Voters.CountyID
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
INNER JOIN Race ON Race.RaceID = Voters.RaceID
INNER JOIN Party ON Party.PartyID = Voters.PartyID
INNER JOIN Status ON Status.StatusID = Voters.StatusID
INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;
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)?
Jim
Simon.
___
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
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
On 1 Aug 2014, at 8:11pm, Jim Callahan jim.callahan.orla...@gmail.com wrote: 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)? A VIEW is just a way of saving a SELECT statement for execution later. If you execute the SELECT statement from the VIEW definition as if it was a separate SELECT statement, do you get an error message of some kind ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
On Fri, Aug 1, 2014 at 3:41 PM, Simon Slavin slav...@bigfraud.org wrote: On 1 Aug 2014, at 8:11pm, Jim Callahan jim.callahan.orla...@gmail.com wrote: 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)? A VIEW is just a way of saving a SELECT statement for execution later. Correct a view is a saved SELECT statement. My question is the division of labor. IMHO, from a DBA virtual table perspective shouldn't the engine closest to the data (SQLite in this case) parse and run the SELECT statement specified by the view? and just return a resultset to the calling program? Why is Python parsing the AS SELECT clause of the CREATE VIEW statement? Shouldn't Python just pass 'SELECT FirstName LastName FROM ActiveVoters LIMIT 3' to SQLite and SQLite parse, recognize that ActiveVoters is a VIEW, run the SQL and substitute it like a macro-preprocessor before anyone (especially the calling program) is the wiser? I can't ask the Python list if I can't specify the correct behavior of a correct implementation of the call level interface. I have many time used a view in place of a table in MS Access and indeed, I connected MS Access via ODBC to an ancestor of this database and MS Access saw an ancestor of this view as a table. I expect something similar from Python, R or Java. A view is not just supposed to be a convenience from the command line interface and unusable from other programs; is it? Jim If you execute the SELECT statement from the VIEW definition as if it was a separate SELECT statement, do you get an error message of some kind ? Works OK at command line and does not give any error messages. Almost impossible to do from Python at my current level of ignorance. Simon. ___ 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
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
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
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
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. You can send the database to me and I will take a look at it. I don't have the numpy/scipy/R/sqlalchemy installed, but just the database itself should be helpful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
When the command line and an application do different things, it is usually a
versioning issue. I’d verify what version of the SQLite library your Python
application is using. My guess is something older, possibly with a bug or
edge-case in the way it handles aliasing of views.
-j
On Aug 1, 2014, at 2:11 PM, Jim Callahan jim.callahan.orla...@gmail.com wrote:
On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin slav...@bigfraud.org wrote:
On 1 Aug 2014, at 4:45pm, Jim Callahan jim.callahan.orla...@gmail.com
wrote:
column is not present in both tables
This is usually the result of using the syntax JOIN table USING column
because SQL requires columns of that name to be present in both tables.
Instead of that phrasing see if you can use this one:
JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
I changed the syntax from:
LEFT JOIN Gender USING (GenderID)
to
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
Again it worked on the SQLite command line, but not when called from
Python.
If that doesn't help ...
SQLite Version 3.8.0.1
Is that the version your IPython interface is using ? Can you give us the
output of
SELECT sqlite_version()
when done through the iPython interface ?
pd.read_sql_query('SELECT sqlite_version()', engine)
0 sqlite_version()
3.6.21
And I'm afraid we may also need to see the view definition, so can you
tell us whatever you used for your CREATE VIEW command ?
CREATE VIEW ActiveVoters2
AS SELECT
Voters.CountyID,
Voters.VoterID,
LastName, Suffix, FirstName,MidName,
Supress,
ResAddress1,
ResAddress2,
ResCity, ResST, ResZip9,
MailAddress1,
MailAddress2,
MailAddress3
MailCity, MailST, MailZip9, MailCountry,
Voters.GenderID,
Voters.RaceID,
BirthDate,
RegDate,
Voters.PartyID,
Precinct, PGroup, PSplit, PSuffix,
Voters.StatusID,
CD, HD, SD, CC, SB,
AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
2014 -- FVRS
Email, -- Added Email-- Thursday July 24,
2014 -- FVRS
County.CountyName,
Gender.GenderName,
Race.RaceName,
Party.PartyName,
Status.StatusName,
VoterHistoryCol.ENov2012,
VoterHistoryCol.EAug2012,
VoterHistoryCol.EPPP2012,
VoterHistoryCol.ENov2010,
VoterHistoryCol.EAug2010,
VoterHistoryCol.ENov2008,
VoterHistoryCol.EAug2008,
VoterHistoryCol.EPPP2008,
(CASE WHEN substr(BirthDate,6,5) = 11-06 -- Election Day 2012:
Nov 6, 2012
THEN 2012 - substr(BirthDate,1,4) -- Had birthday
ELSE 2012 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeENov2012, -- Age as of Nov 6,
2012
(CASE WHEN substr(BirthDate,6,5) = 08-26 -- Election Day 2014:
Aug 26, 2014
THEN 2014 - substr(BirthDate,1,4) -- Had birthday
ELSE 2014 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeEAug2014, -- Age as of Aug 26,
2014
(CASE WHEN substr(BirthDate,6,5) = 11-04 -- Election Day 2014:
Nov 4, 2014
THEN 2014 - substr(BirthDate,1,4) -- Had birthday
ELSE 2014 - substr(BirthDate,1,4) - 1 -- Haven’t had birthday
END) AS AgeENov2014 -- Age as of Nov 4, 2014
FROM Voters
INNER JOIN County ON County.CountyID = Voters.CountyID
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
INNER JOIN Race ON Race.RaceID = Voters.RaceID
INNER JOIN Party ON Party.PartyID = Voters.PartyID
INNER JOIN Status ON Status.StatusID = Voters.StatusID
INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;
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)?
Jim
Simon.
___
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
--
Jay A. Kreibich J A Y @ K R E I B I.C H
Intelligence is like underwear: it is important that you have it, but showing
it to the wrong people has the tendency to make them feel uncomfortable. --
Angela Johnson
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
On 2 Aug 2014, at 12:15am, Jay Kreibich j...@kreibi.ch wrote: When the command line and an application do different things, it is usually a versioning issue. I’d verify what version of the SQLite library your Python application is using. My guess is something older, possibly with a bug or edge-case in the way it handles aliasing of views. His shell is 3.8.6, his IPython interface has SQLite 3.6.21. Unfortunately it's not easy for me to test with that version or anything close to it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
