On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
> >> However, due to database locking issues, I need to do a bunch of
> >> inserts in one transaction or batch. Thus, I store them in a simple
> >> queue. Therefore, the julianday('now') won't work because all of my
> >> batch inserts will have the same date and time. And that doesn't
> >> work very well. ;)
>
>
> >From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>
> >You will have to deal with this in any case. E.g. on Windows the system
> >timer resolution is 15ms by default. You can insert quite a few records
> >in 15 ms.
> >It is unrealistic to expect that every record could be inserted with a
> >unique timestamp. Find some other way to ensure uniqueness (e.g. just
> >assign a sequential number to each).
>
>
>
> Thanks for your response. However, my batch inserts and the uniqueness of
> my timestamps aren't the issue. I guess I didn't explain my situation well
> enough. Let me try again.
>
>
>
> I take readings every X seconds which gives me plenty of uniqueness for each
> reading. I save the SQL statements and then insert them in small batches.
> However, from reading this newsgroup I've learned that the correct way to
> put dates into SQLite is as I described before:
>
>
> - create table my_table(date_stuff real);
>
>
> - insert into my_table values(julianday('now'));
>
>
> In my batch loop, I can't use julianday("now") - I need the timestamp
> to reflect when I took the reading.
>
>
>
> If the right way to put datetime in the dbase is the julianday('now')
> format, I need to be able to create and capture that format in python.
>
>
>
> What is the julianday("now") equivalent in python? I can't find a simple,
> straight-forward answer to this question.
I have no idea what the julianday("now") equivalent in Python is, but
why not let SQLite do the work? Here is how I would do it with Perl...
(mix of pseudo-code and Perl ahead)
# prepare statement
$sth = $dbh->prepare(qq{
INSERT INTO table (somecol, timestamp)
VALUES (?, julianday("now"))
});
open transaction...
# loop through your readings
$sth->execute($somecol);
end transaction...
$dbh->commit;
or throw error...
The above works for me.
Keep in mind though... Igor's caveat about not having enough
resolution for timing might apply. So, you might have to use some kind
of high resolution timer. CPAN has one us Perl aficionados...
conveniently, it is called Time::HiRes
<http://search.cpan.org/~jhi/Time-HiRes-1.9715/HiRes.pm>
I am sure you have one for your Python world as well. If you decide to
use something like that, then you will have to modify your code
accordingly.
>
>
>
> Thank you,
>
> Chris
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
