Cannot save path to database (Python/Windows)

Question

I'm making a program that is suppoused to move files and save their new paths in a database. I do, however, have great problems with putting together and executing the SQL-query that inserts them into the database.

This is the program:

#logspc.py
import os
import sqlite3 as lite
import sys
import time

def getspc(path):
    dirs = os.listdir(path)
    spcfiles = []
    for filename in dirs:
        (shortname, extension) = os.path.splitext(filename)
        if extension == '.spc':
            spcfiles.append(filename)
    return spcfiles

src=os.path.normpath(r'C:\users\python\nonpython')
dest=os.path.normpath(r'C:\users\python\target')
files=getspc(src)
con = lite.connect('spcbase.db')
cur=con.cursor()
for mfile in files:
    oldpath=os.path.normpath(os.path.join(src,mfile))
    newpath=os.path.normpath(os.path.join(dest,mfile))
    os.rename(oldpath,newpath)
    query="INSERT INTO spectra VALUES ('" + newpath + "',SELECT date('now'))"
    print query
    cur.execute(query)
con.close()

It crashes on the line "cur.execute(query)", rendering this error:

Traceback (most recent call last):
  File "C:/Users/Python/logspc2.py", line 27, in <module>
    cur.execute(query)
OperationalError: near "SELECT": syntax error

The value of the query-variable as printed is

INSERT INTO spectra VALUES ('C:\users\python\target\Bertil.spc',SELECT date('now'))

Where do I go from here?

wouldn't simply date('now') work?

njzk2
– njzk2

2014-04-08 13:30:44 +00:00
Commented Apr 8, 2014 at 13:30 — njzk2
– njzk2, Commented Apr 8, 2014 at 13:30

Larry Lustig · Accepted Answer · 2014-04-08 14:53:03Z

3

The SQLite syntax for obtaining the current date is CURRENT_DATE (CURRENT_TIME and CURRENT_TIMESTAMP are also available):

 INSERT INTO spectra VALUES ('C:\users\python\target\Bertil.spc', CURRENT_DATE)

In addition, it would be good practice to get out of the habit of building SQL statements as strings and instead to use parameterized queries:

 cur.execute(
     'INSERT INTO spectra (PathCol, DateCol) VALUES (?, CURRENT_DATE)',
     [newpath]
 )

This makes your program "safer" because no one can sneak SQL into the statement by injecting it into one of the values you use to build your string. While that's not really a problem with your particular program* (since the newpath value isn't supplied by a user), it will also help you in the case where the filename contains characters that might confuse SQLite's string parser.

Finally, another good practice is to include the names of the columns into which you're INSERTing values in the INSERT statement. This makes your code less fragile -- without the column names, if the table is ever redefined with a different number of columns or different column order, then your code will break. By explicitly listing the column names your code can survive many common table redefinition scenarios.

* ^{_{Actually, it's an interesting question -- does there exist a method on any file systems for performing SQL injection via path and file names? I'm thinking it might actually be possible!}}

edited Apr 8, 2014 at 14:53

answered Apr 8, 2014 at 13:30

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2536262 Over a year ago

Writing like this gets me a syntax error. 'INTO' is marked as wrong.

user2536262 Over a year ago

It seems it should be written with citation marks: cur.execute( "INSERT INTO spectra VALUES (?, CURRENT_DATE)", [newpath] ) This gives no error, but it doesn't seem to save anything to the database either.

Larry Lustig Over a year ago

You're right -- I left the quote characters off my original post. I've updated to fix that problem, and also to include an additional suggestion about explicitly naming columns in the INSERT statement.

Community · Accepted Answer · 2017-05-23 10:27:56Z

Had the same issue and found the solution at:

http://www.experts-exchange.com/questions/28401790/Inserting-dir-filename-to-mysql-from-Python.html

for my program I recursive search in a Microsoft drive, fpath is defined with os.path in for loop.

print fpath returns -> C:\myfolder\myfile.zip

I figured it would INSERT INTO exactly as it printed.

fpath2 = fpath.replace('\\','\\\\')
SQL = 'INSERT INTO all_zip_files(fpath_zip,status) VALUES("%s", "0");' % (fpath2)

I also found that I need to define root dir in a special way:

PATH = r"c:\" #fails
PATH = r"c:" #works however execute(SQL) fails becaue path becomes c:myfolder\myfile.zip after using fpath.replace('\\',\\\\')
PATH = os.path.normpath("c:/") #WORKS with fpath.replace('\\','\\\\') to create desired windows path for my db

This worked for me however a "better" solution is presented here:

Passing a folder location as an SQL parameter in python causes an error

Collectives™ on Stack Overflow

Cannot save path to database (Python/Windows)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related