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
