sqlite weirdness

K

kyosohma

Hi,

I am trying to use sqlite to create a local database for an
application I am writing and I am getting some screwy results from it.
Basically, I have a set of values in the database and I am trying to
select a date range and sum those values.

My problem is that it only sums up every other date range and ignores
the in between ranges.

Here's the table structure:

sql = '''CREATE TABLE tbl_TimeEntries (dateworked DATE,
empid INTEGER,
reg REAL,
ot REAL,
ce REAL,
hol REAL,
sklv REAL,
vac REAL,
ct REAL,
conv REAL,
misc REAL,
comments TEXT,
PRIMARY KEY (dateworked, empid))

'''

I have data in it from 12/18/2006 - 01/26/2007.

I use the following bits of sql:

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/17/2006' AND dateworked <= '12/30/2006' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/31/2006' AND dateworked <= '01/13/2007' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'01/14/2007' AND dateworked <= '01/27/2007' AND empid = 281"

The first and third return the correct sums. The middle one returns a
list of null values.

If I select everything in the database for that user, I get this:

[(u'12/18/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'12/19/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'12/20/2006', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'12/21/2006', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'12/22/2006', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'12/25/2006', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'12/26/2006', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'12/27/2006', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'12/28/2006', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'12/29/2006', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/01/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/02/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/03/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/04/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/05/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/08/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/09/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/10/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/11/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'01/12/2007', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/15/2007', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/16/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/17/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/18/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/19/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/22/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/23/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/24/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/25/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/26/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u'')]
[(80.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]

So the data us there, but the sql only works part of the time. My SQL
works if my database is in SQL Server, but not sqlite. Is my SQL
malformed? Is it something about dates in sqlite? Or is it something
else?

Thanks a lot. I apologize in advance for the long post.

Mike
 
D

Duncan Booth

So the data us there, but the sql only works part of the time. My SQL
works if my database is in SQL Server, but not sqlite. Is my SQL
malformed? Is it something about dates in sqlite? Or is it something
else?
Click to expand...

Your dateworked field seems to have strings rather than dates so the
expression "dateworked >= '12/31/2006' AND dateworked <= '01/13/2007'"
as a string comparison isn't going to match any records. Sqllite handles
data types differently than other SQL databases: read the documentation to
find out how to store dates, or else store the dates in ISO format
(YYYY/MM/DD) where string comparisons will give the correct results.
 
J

John Machin

Hi,

I am trying to use sqlite to create a local database for an
application I am writing and I am getting some screwy results from it.
Basically, I have a set of values in the database and I am trying to
select a date range and sum those values.

My problem is that it only sums up every other date range and ignores
the in between ranges.

Here's the table structure:

sql = '''CREATE TABLE tbl_TimeEntries (dateworked DATE,
empid INTEGER,
reg REAL,
ot REAL,
ce REAL,
hol REAL,
sklv REAL,
vac REAL,
ct REAL,
conv REAL,
misc REAL,
comments TEXT,
PRIMARY KEY (dateworked, empid))

'''

I have data in it from 12/18/2006 - 01/26/2007.

I use the following bits of sql:

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/17/2006' AND dateworked <= '12/30/2006' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/31/2006' AND dateworked <= '01/13/2007' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'01/14/2007' AND dateworked <= '01/27/2007' AND empid = 281"

The first and third return the correct sums. The middle one returns a
list of null values.

If I select everything in the database for that user, I get this:

[(u'12/18/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'12/19/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'12/20/2006', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'12/21/2006', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'12/22/2006', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'12/25/2006', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'12/26/2006', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'12/27/2006', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'12/28/2006', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'12/29/2006', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/01/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/02/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/03/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/04/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/05/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/08/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/09/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/10/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/11/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'01/12/2007', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/15/2007', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/16/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/17/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/18/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/19/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/22/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/23/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/24/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/25/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/26/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u'')]
[(80.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]

So the data us there, but the sql only works part of the time. My SQL
works if my database is in SQL Server, but not sqlite. Is my SQL
malformed? Is it something about dates in sqlite? Or is it something
else?

Thanks a lot. I apologize in advance for the long post.
Click to expand...

Try this:
select * from tbl_TimeEntries where empid = 281 order by
dateworked
and see what you get.

Very short story: sqlite doesn't have a native date type (look at the
results it's returning (e.g. u'12/18/2006')), and is doing a *string*
comparison. SQL Server has a native date type and knows your locale.
If you always work with dates in 'YYYY-MM-DD' format, you should have
less bother across locales and across databases.

More detail on the sqlite website.

HTH,
John
 
K

kyosohma

Hi,
Click to expand...
I am trying to use sqlite to create a local database for an
application I am writing and I am getting some screwy results from it.
Basically, I have a set of values in the database and I am trying to
select a date range and sum those values.
Click to expand...
My problem is that it only sums up every other date range and ignores
the in between ranges.
Click to expand...
Here's the table structure:
Click to expand...
sql = '''CREATE TABLE tbl_TimeEntries (dateworked DATE,
empid INTEGER,
reg REAL,
ot REAL,
ce REAL,
hol REAL,
sklv REAL,
vac REAL,
ct REAL,
conv REAL,
misc REAL,
comments TEXT,
PRIMARY KEY (dateworked, empid))

I have data in it from 12/18/2006 - 01/26/2007.
Click to expand...
I use the following bits of sql:
Click to expand...
"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/17/2006' AND dateworked <= '12/30/2006' AND empid = 281"
Click to expand...
"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/31/2006' AND dateworked <= '01/13/2007' AND empid = 281"
Click to expand...
"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'01/14/2007' AND dateworked <= '01/27/2007' AND empid = 281"
Click to expand...
The first and third return the correct sums. The middle one returns a
list of null values.
Click to expand...
If I select everything in the database for that user, I get this:
Click to expand...
[(u'12/18/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'12/19/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'12/20/2006', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'12/21/2006', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'12/22/2006', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'12/25/2006', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'12/26/2006', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'12/27/2006', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'12/28/2006', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'12/29/2006', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/01/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/02/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/03/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/04/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/05/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/08/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/09/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/10/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/11/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'01/12/2007', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/15/2007', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/16/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/17/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/18/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/19/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/22/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/23/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/24/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/25/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/26/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u'')]
[(80.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]
Click to expand...
So the data us there, but the sql only works part of the time. My SQL
works if my database is in SQL Server, but not sqlite. Is my SQL
malformed? Is it something about dates in sqlite? Or is it something
else?
Click to expand...
Thanks a lot. I apologize in advance for the long post.
Click to expand...

Try this:
select * from tbl_TimeEntries where empid = 281 order by
dateworked
and see what you get.

Very short story: sqlite doesn't have a native date type (look at the
results it's returning (e.g. u'12/18/2006')), and is doing a *string*
comparison. SQL Server has a native date type and knows your locale.
If you always work with dates in 'YYYY-MM-DD' format, you should have
less bother across locales and across databases.

More detail on the sqlite website.

HTH,
John
Click to expand...

Thanks Duncan and John! That makes sense. But why does the official
Python docs show an example that seems to imply that there is a "date"
type? See link below:

http://docs.python.org/lib/node349.html

I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
DD' format.

Thanks again,

Mike
 
D

Dennis Lee Bieber

Thanks Duncan and John! That makes sense. But why does the official
Python docs show an example that seems to imply that there is a "date"
type? See link below:

http://docs.python.org/lib/node349.html
Click to expand...

You missed two items... The open specified options to use either the
type /name/ declared in the CREATE TABLE or a type name attached to the
field names in a select -- and those are used to /call/ a data converter
function that has to be registered. Key phrases:

"""
There are default adapters for the date and datetime types in the
datetime module. They will be sent as ISO dates/ISO timestamps to
SQLite.

The default converters are registered under the name "date" for
datetime.date and under the name "timestamp" for datetime.datetime.
"""

"""
con = sqlite3.connect(":memory:",
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
"""
Note the "detect_types" specification. Also take care, if your
database was populated by some other application that used that
mm/dd/yyyy format, you may have problems as the converters above specity
/sending/ ISO format to the database from Python datetime objects, and
probably expecting to convert them back on input.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
T

Tim Roberts

Thanks Duncan and John! That makes sense. But why does the official
Python docs show an example that seems to imply that there is a "date"
type? See link below:
Click to expand...

You can certainly create fields of type "date" in sqlite, but sqlite
doesn't understand any types. **ALL** types in sqlite are stored and
compared as strings. You could declare your "date" field as type "frog"
and it would work exactly the same.

C:\tmp>sqlite x.db
SQLite version 2.8.6
Enter ".help" for instructions
sqlite> create table xyz (
...> xxx frog
...> );
sqlite> insert into xyz values (123);
sqlite> select * from xyz;
123
sqlite>

Note that the example in the web page you quoted takes a Python variable of
type datetime.date and converts it to a string, which produces
"2007-12-12". Also note that the conversion on the OTHER end has to use
the special sqlite adapter syntax ('select current_date as "d [date]"...').

In my opinion, it can be argued that the inclusion of sqlite in the Python
standard library was a mistake. It is a great product, and I've used it
many times in my own Python apps, but it has a number of unexpected
idiosyncracies. When you download and install it yourself, you can
evaluate the idiosyncracies and decide whether they are acceptable, but
when its in the standard library, you don't expect to go through that.
I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
DD' format.
Click to expand...

Another possible solution is to use a real database.
 
K

kyosohma

(e-mail address removed) wrote:



Another possible solution is to use a real database.
Click to expand...

I am using a "real" database: MS SQL Server 2000. Unfortunately, my
program will also need to run in an offsite location that cannot
connect to that server right now. Thus the need to use sqlite or some
flat-file format.

Thanks for the advice.

Mike

 
K

kyosohma

You missed two items... The open specified options to use either the
type /name/ declared in the CREATE TABLE or a type name attached to the
field names in a select -- and those are used to /call/ a data converter
function that has to be registered. Key phrases:

"""
There are default adapters for the date and datetime types in the
datetime module. They will be sent as ISO dates/ISO timestamps to
SQLite.

The default converters are registered under the name "date" for
datetime.date and under the name "timestamp" for datetime.datetime.
"""

"""
con = sqlite3.connect(":memory:",
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
"""
Note the "detect_types" specification. Also take care, if your
database was populated by some other application that used that
mm/dd/yyyy format, you may have problems as the converters above specity
/sending/ ISO format to the database from Python datetime objects, and
probably expecting to convert them back on input.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
Click to expand...

Well, that makes sense. I read the parts you mentioned earlier, but I
guess I just wasn't getting my head around the concepts.

Thanks for clearing that up.

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Connected SQLite to my java program but information are not submitted 2
mysql-ruby won't connect 6
need help with a cart I inherited, need to increase number of total characters allowed 3
XSL : Slow 0
[QUIZ] Fuzzy Time (#99) - Results 1
My own handy Pocket Reference notes 15
extension_pack 0
SINGAPORE PRIVATE CONDO / APT FOR SALE / Singapore New Upcoming Residential Projects 1

Members online

Total: 41 (members: 2, guests: 39)
Robots: 440

Forum statistics

Threads
473,770
Messages
2,569,583
Members
45,075
Latest member
MakersCBDBloodSupport

Latest Threads

Top