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
