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
_______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
