Re: [sqlite] Handling Timezones

[Stephen Chrzanowski]

I suppose either way you're going to run into accuracy problems, but that
is the nature of the beast.  IP wasn't ever developed to be a geographical
thing, but we're trying to get geographical info from 'guessing'.  I guess
it boils down to one of three things;

A> Where are Wills users going to be when accessing the site?  Internal or
external?  From a hotel room or from another office?
B> Can you trust that the user will change their clocks on their machines?
C> Does Wills company go with that kind of network routing?  (I don't go to
hotels often, so I can't state whether each building has their own external
IP or if they route as you suggest)

If "A", and if they're using internal IPs, you could still get a good idea
of where the users are based on their internal IP, pending you're not
running the same network range in multiple locations.  (Network guys love a
challenge sometimes. ;))

I suppose another option would be that when a user logs into whatever
service Will is offering, then a field asking for the current local time
would get the most real accurate time.

On Fri, Aug 1, 2014 at 7:32 PM, Donald Shepherd 
wrote:

> Actually what Rob and I were pointing out was that the chances of showing
> up in Taiwan when you're in Tennessee is actually quite high in a corporate
> environment - he gets moved from the UK to Germany, I get moved from
> Australia to Phoenix, AZ, my wife gets moved from Australia to Switzerland
> and that's just a random sample.  It's not uncommon at all for large
> companies to route traffic through a single gateway, and as a result using
> geolocation to detect timezones is very prone to problems if people want to
> access a site from inside a large company, whereas using client-based logic
> avoids this.
>
>
> On 2 August 2014 09:27, Stephen Chrzanowski  wrote:
>
> > I understand that with routing and such, you can end up outside where you
> > really are (With my IP, I'm shown just outside of Toronto when I'm
> actually
> > two hours out), but the chances of showing up in Taiwan when you're in
> > Tennessee is doubtful.  The point of the matter is that you'll get real
> > time data in regards to where the user might be located and from there,
> > you'll get a general idea on when a good time to call is.
> >
> > There are also bounce VPNs which would make it look like I'm in Texas
> when
> > I'm in Toronto.  Depending on how I route my traffic here, I can be
> > anywhere in the world.
> >
> >
> > On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:
> >
> > > Hi everyone,
> > >
> > > Wow, such great responses! So my background is not with this type of
> > > development, so I never really thought about these types of problems
> > > before. Thank you all for the help!
> > >
> > > -will
> > > ___
> > > 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
> >
> ___
> 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] Handling Timezones

[Donald Shepherd]

Actually what Rob and I were pointing out was that the chances of showing
up in Taiwan when you're in Tennessee is actually quite high in a corporate
environment - he gets moved from the UK to Germany, I get moved from
Australia to Phoenix, AZ, my wife gets moved from Australia to Switzerland
and that's just a random sample.  It's not uncommon at all for large
companies to route traffic through a single gateway, and as a result using
geolocation to detect timezones is very prone to problems if people want to
access a site from inside a large company, whereas using client-based logic
avoids this.


On 2 August 2014 09:27, Stephen Chrzanowski  wrote:

> I understand that with routing and such, you can end up outside where you
> really are (With my IP, I'm shown just outside of Toronto when I'm actually
> two hours out), but the chances of showing up in Taiwan when you're in
> Tennessee is doubtful.  The point of the matter is that you'll get real
> time data in regards to where the user might be located and from there,
> you'll get a general idea on when a good time to call is.
>
> There are also bounce VPNs which would make it look like I'm in Texas when
> I'm in Toronto.  Depending on how I route my traffic here, I can be
> anywhere in the world.
>
>
> On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:
>
> > Hi everyone,
> >
> > Wow, such great responses! So my background is not with this type of
> > development, so I never really thought about these types of problems
> > before. Thank you all for the help!
> >
> > -will
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Stephen Chrzanowski]

I understand that with routing and such, you can end up outside where you
really are (With my IP, I'm shown just outside of Toronto when I'm actually
two hours out), but the chances of showing up in Taiwan when you're in
Tennessee is doubtful.  The point of the matter is that you'll get real
time data in regards to where the user might be located and from there,
you'll get a general idea on when a good time to call is.

There are also bounce VPNs which would make it look like I'm in Texas when
I'm in Toronto.  Depending on how I route my traffic here, I can be
anywhere in the world.


On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:

> Hi everyone,
>
> Wow, such great responses! So my background is not with this type of
> development, so I never really thought about these types of problems
> before. Thank you all for the help!
>
> -will
> ___
> 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] Abnormal memory usage

[Stephen Chrzanowski]

Attachments aren't permitted in this group.  Please put the upload
somewhere else and provide a public link.

Step-Trace your application to make sure that you're not spamming up
'opening' connections.  I've written a lot of programs with Sqlite3 (Up to
3.7 I think, haven't upgraded as of yet, but I think that is a good project
for tonight since I build from the amalgamation) and any time I've noticed
a memory leak, its been my not closing the connections to a table resource.


On Thu, Jul 31, 2014 at 3:26 PM, Small Stone 
wrote:

> Hello all
>
> I have an issue with my app. The sample project is attached. It opens a big
> bunch of connections (5K) then closes them in an endless loop.
> In ProcessExplorer I see that memory is not deallocated as expected, and
> the consumption grows to high-water mark (screenshot is in attachment as
> well).
>
> Please note that if the debugger is attached (i.e. CRT debug heap is used),
> the memory is released as expected.
> I'm running sqlite 3.8.5 x86 under w2k8 r2.
>
> --
> Regards
>
> ___
> 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

[Simon Slavin]

On 2 Aug 2014, at 12:15am, Jay Kreibich  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

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Jay Kreibich]

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  wrote:

> On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin  wrote:
> 
>> 
>>> On 1 Aug 2014, at 4:45pm, Jim Callahan 
>> 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  @  

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Keith Medcalf]

>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

[Keith Medcalf]

>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

[Jim Callahan]

On Fri, Aug 1, 2014 at 3:41 PM, Simon Slavin  wrote:

>
> > On 1 Aug 2014, at 8:11pm, Jim Callahan 
> 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

[Simon Slavin]

> On 1 Aug 2014, at 8:11pm, Jim Callahan  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

[Jim Callahan]

On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin  wrote:

>
> > On 1 Aug 2014, at 4:45pm, Jim Callahan 
> 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

[sqlite] Ordered, union all of ordered views

[Eleytherios Stamatogiannakis]

Hello,

We have two virtual tables that are ordered, and we do a "union all" of 
them asking from SQLite to keep the result ordered.


So the query looks like this:

select * from (select x from ordered_VT1 UNION ALL select x from 
ordered_VT2) order by x;


Both of ordered_VT1,  ordered_VT2 report back (via BestIndex) that their 
results are ordered on x. Note that when SQLite negotiates with the 
ordered_VT1,2 (via BestIndex), it doesn't even ask them if an ordering 
on x already exists.


Right now SQLite does a full scan of ordered_VT1, and then ordered_VT2 
before starting to produce results.


Shouldn't it do a merge union all of the two? Is there some way to help 
SQLite's planner to "see" that such a possibility exists?


Kind regards,

l.
___
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

[Simon Slavin]

> On 1 Aug 2014, at 4:45pm, Jim Callahan  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

[sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

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

[sqlite] Abnormal memory usage

[Small Stone]

Hello all

I have an issue with my app. The sample project is attached. It opens a big
bunch of connections (5K) then closes them in an endless loop.
In ProcessExplorer I see that memory is not deallocated as expected, and
the consumption grows to high-water mark (screenshot is in attachment as
well).

Please note that if the debugger is attached (i.e. CRT debug heap is used),
the memory is released as expected.
I'm running sqlite 3.8.5 x86 under w2k8 r2.

--
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parser stack overflow in view

[Richard Hipp]

On Tue, Jul 29, 2014 at 5:00 AM, Michael  wrote:

> I have a view with about 6 Unions and a depth of about 6 subselects in
> each select.
> Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
> Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many queries
> with this problem now...
>


The LALR(1) parser stack is limited to a depth of 100, by default.  You can
change that at compile-time using -DYYSTACKDEPTH=nnn  where "nnn" is some
number.  If you make "nnn" equal to zero, then the LALR(1) parser stack is
obtained from sqlite3_realloc() and it can grow without bound.  In your
particular case, you can get your query to run successfully by increasing
the default stack size by just one to -DYYSTACKDEPTH=101.

We have discussed making the stack unlimited depth by default.  But there
is a small performance and size penalty for doing that.  And, honestly, if
your query needs more than 100 levels of LALR(1) stack, it is going to be
difficult for a human to read anyhow, and probably needs to be refactored.
So I think we will keep the default 100-level limit for the time being and
let individual applications extend the limit at compile-time, if they need
to.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi everyone,

Wow, such great responses! So my background is not with this type of
development, so I never really thought about these types of problems
before. Thank you all for the help!

-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Only allow child record if another field in parent is false.

[Eduardo Morras]

On Fri, 1 Aug 2014 09:26:14 +1200
Richard Warburton  wrote:

> Hi,
> 
> Consider:
> 
> CREATE TABLE enrolment (
> id  INTEGER PRIMARY KEY,
> child   INT NOT NULL REFERENCES child (id),
>  start   INT NOT NULL,
> leaver  INT NOT NULL DEFAULT 0 CHECK (leaver BETWEEN 0 AND 1), -- BOOL
> );
> 
> CREATE TABLE enrolmentItem (
> id  INTEGER PRIMARY KEY,
> enrolment   INT NOT NULL REFERENCES enrolment (id) ON DELETE CASCADE,
>  day INT NOT NULL,
> start   INT NOT NULL,
> end INT NOT NULL CHECK (start < end),
> );
> 
> I'm looking for an elegant way to prevent enrolments having
> enrolmentItems if leaver is set to 1.
> This way, you can't change the leaver field if enrolmentItems are
> attached, and you can't add an enrolmentItem if leaver is set to 1.
> 
> I think 2 triggers could work, but it seems overkill.  I could have a
> leaver field in enrolmentItem referencing leaver in enrolment and put
> a check against that, but I'd be storing unneeded data.
> 
> Ideally, I'd like a check in enrolmentItem that can examine a
> different field in the referenced enrolment record.
> 
> Suggestions?

Check 
http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012 for 
parent-child relationship example, it's for in-table relation but the in-code 
documentation is awesome, you can "steal" some ideas from it.

> 
> Thanks.


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users