473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

sqlite weirdness

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
Dec 12 '07 #1
3 1555
ky******@gmail.com wrote:
>
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:
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
sqlitecreate table xyz (
... xxx frog
...);
sqliteinsert into xyz values (123);
sqliteselect * 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.
Another possible solution is to use a real database.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Dec 13 '07 #2
On Dec 13, 1:54 am, Tim Roberts <t...@probo.comwrote:
kyoso...@gmail.com wrote:
<snipped>
I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
DD' format.

Another possible solution is to use a real database.
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

--
Tim Roberts, t...@probo.com
Providenza & Boekelheide, Inc.
Dec 13 '07 #3
On Dec 13, 12:12 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On Wed, 12 Dec 2007 16:02:35 -0800 (PST), kyoso...@gmail.com declaimed
the following in comp.lang.python:
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

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
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/
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
Dec 13 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: David Thielen | last post by:
Hi; I am creating png files in my ASP .NET app. When I am running under Windows 2003/IIS 6, the file is not given the security permissions it should have. It does not have any permission for...
12
by: John Salerno | last post by:
I've been looking around and reading, and I have a few more questions about SQLite in particular, as it relates to Python. 1. What is the current module to use for sqlite? sqlite3? or is that not...
4
by: Jim Carlock | last post by:
I added the following lines to PHP.INI. extension=php_pdo.dll extension=php_pdo_sqlite.dll extension=php_sqlite.dll specifically in that order. I noticed the extensions getting loaded are...
10
by: Luigi | last post by:
Hello all! I'm a newbie in PHP. I have written a short script that tries to update a SQLite database with the user data. It is pretty simple, something like this: <?php $sqlite =...
9
by: Gilles Ganault | last post by:
Hello I was looking for a lighter web server than Apache, and installed Lighttpd on CentOS through yum. It works fine, but I now need to use SQLite from a PHP script. I seem to understand that...
8
by: Gilles Ganault | last post by:
Hello I need to compile PHP5 with SQLite statically and without PDO. I don't need DB-independence, so it's fine using sqlite_*() functions instead of PDO. 1. I've downloaded, compiled, and...
3
by: Daniel Fetchinson | last post by:
Does Python 2.5.2's embedded SQLite support full text searching? Sqlite itself is not distributed with python. Only a python db api compliant wrapper is part of the python stdlib and as such it...
0
by: Joe Goldthwaite | last post by:
Thanks Guilherme. That helped. I guess I was thinking that pysqlite would automatically come with some version of sqlite. The fact that it doesn't is what was causing me to get the strange...
20
by: timotoole | last post by:
Hi all, On a (sun) webserver that I use, there is python 2.5.1 installed. I'd like to use sqlite3 with this, however sqlite3 is not installed on the webserver. If I were able to compile sqlite...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.