[Tutor] sqlite query problem

[BOBÁK Szabolcs]

Dear List Members,

  I need help in a mystic problem (I hope just for me) with python and
sqlite3.

Running enviroment:
OS: Windows 2003 ENG 64bit, Windows XP HUN 32bit
Python: ActiveState Python 2.6.4 32bit (It's a must because of PyWin
extension in the future and I didn't have any issue with it on 64bit Win)

It's a little program wich collects the data of files on the given partition
or in given directories (and in the future should watch/monitoring the
changes).
It stores the data in sqlite3 database via python. It works quite well.
My problem is with the query. I see that I am a little bit confused with
passing variables to queries.
Mostly it works but when I want to query the files which last modification
date is lesser than a given date it not works.
The strange is when I do the same via command line (prebuilt sqlite3 windows
binary: 3.6.22) it works perfectly.

The table structure:
sql_command_create = 'create table '+sql_tablename+' (dirname text, fullpath
text, size_in_byte integer, creationdate integer, lastmoddate integer,
lastaccdate integer, archivalhato text)'    # the archivalhato field is a
remain too
sql_cursor.execute(sql_command_create)

Insert command:
sql_command_insert = 'insert into '+sql_tablename+' values (?, ?, ?, ?, ?,
?, ?)'
sql_cursor.execute(sql_command_insert, (sql_dirname, sql_fullpath, sql_size,
sql_creationdate, sql_lastmoddate, sql_lastaccdate, sql_archive_field))
sql_conn.commit()

These were my workarounds to pass variable values to queries. I just wonder
if there were better solution, but these work well.

The query:
sql_tablename_orig = 'pyfilestat_drive_e_2010_01_27_16_48_31'
sql_conn = sqlite3.connect(sql_file)
sql_cursor = sql_conn.cursor()

sql_command_stat = 'SELECT COUNT(*) FROM '+sql_tablename_orig
sql_cursor.execute(sql_command_stat)
print 'Az osszes sor szama: '+str(sql_cursor.fetchone())  #prints the number
of rows

This also works, but this not:
sql_command_stat = 'SELECT COUNT(lastmoddate) FROM
'+sql_tablename_orig+'WHERE lastmoddate < '+str(lastmod_date1)
sql_cursor.execute(sql_command_stat)

This was my original try, but tried various in various formula.
sql_command_stat = 'SELECT COUNT(lastmoddate) FROM
'+sql_tablename_orig+'WHERE lastmoddate < %d'
sql_cursor.execute(sql_command_stat, %lastmod_date1)

sql_command_stat = 'SELECT COUNT(lastmoddate) FROM
'+sql_tablename_orig+'WHERE lastmoddate < (?)'
sql_cursor.execute(sql_command_stat, (lastmod_date1))

But always the same error message:
sqlite3.OperationalError: near "<": syntax error
File "C:\python\stat.py", line 42, in <module>
sql_cursor.execute(sql_command_stat)

>From the three attempt I concluded that it's the same if I pass the variable
value as a string or an integer (maybe I am wrong).
Anyway it wasn't clear which is the best (securest) way. I thought that the
first and third.
Maybe the form of the date is not the best, but the program is an update of
text file based version, so it's a remain.

And when I try this query from command line, it works.

Thank you for your help in advance!

P.S. Excuse me for my bad English.
-- 
Bobák Szabolcs
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20100129/9754b5cb/attachment.htm>