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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
