Python, easyGUI and sqlite3: Using python to read files in one folder or multiple folder, then adding the folder names and filenames to database

Question

I am new in python and am trying to add folder names and text files in those folders to the database. The problem is that i don't know how to add the "textfiles" and "opendirectory" to the database. Please look at this code and help me. Thanks

#!/usr/bin/python

from easygui import *
import sys, glob, os, sqlite3


msgbox("Please choose your folder ","Welcome to MTT", ok_button ="Choose")

opendirectory = diropenbox("Welcome", "MTT",None)


con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute('DROP TABLE IF EXISTS folder')
cur.execute('DROP TABLE IF EXISTS file')
cur.execute('CREATE TABLE folder( folderid INTEGER PRIMARY KEY, foldername   VARCHAR(120))')
cur.execute('CREATE TABLE file( fileid INTEGER PRIMARY KEY, folderid INTEGER, dataname VARCHAR(120), FOREIGN KEY(folderid) REFERENCES foldername(folderid))')
con.commit()


def main():

    for dirpath,dirnames,filenames in os.walk(opendirectory):

        for textfiles in filenames:

            print textfiles
            print opendirectory
            cur.execute ('INSERT INTO folder (folderid, foldername) VALUES (null,opendirector)')
            cur.execute('INSERT INTO file(fileid, dataname) VALUES(null,textfiles)')
            cur.execute('SELECT * FROM folder')
            print cur.fetchall()


main()

print 'success'

Sébastien · Accepted Answer · 2011-11-21T16:05:15.470

2

Assuming that you want to add all the filenames to the database (without considering their relative path inside opendirectory), here is a way to correct your queries.

cur.execute ("INSERT INTO folder (foldername) VALUES (?);", (opendirectory))
cur.execute("INSERT INTO file (dataname) VALUES(?);", (textfiles))

NB: this won't be sufficient to make the logical link in database between a file and the opendirectory it was found in.

Now, let's say you want to store in DB the path of the parent folder of the file in addition to its filename: Just add a column parent_folder in your file table, and use an insert query like this (I changed the variable names to make them easier to understand):

for dirpath, dirsInDirpath, filesInDirPath in os.walk(opendirectory):
    for myfile in filesInDirPath:   
        cur.execute("INSERT INTO file (dataname, parent_folder) VALUES(?, ?);", (myfile, dirpath))

edited Nov 21 '11 at 16:05

answered Nov 21 '11 at 10:05

Sébastien

13,831
10
55
70

Hi, thanks for your answer. I am struggling with adding the relative path inside "opendirectory" to the database, too. Do you have any idea? – Ricky Nov 21 '11 at 13:34
My last edit should clear your doubts about the usage of `os.walk()` – Sébastien Nov 21 '11 at 14:49
I'd give you a +1, but you have a database injection vulnerability. [See here](http://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) for more info. – Spencer Rathbun Nov 21 '11 at 14:53
@SpencerRathbun You're right, I changed the queries to the safe format. As I currently only work on personal "offline" projects where security is not an issue, I find it more convenient to use the more common "string formatting" way. Besides I was actually disappointed by the fact that the "safe placeholders" way is not supported for DDL queries (e.g. `create table ?`) - or maybe I missed something – Sébastien Nov 21 '11 at 16:13
@Sebastien If you need more control over your queries, look into [sqlalchemy](http://www.sqlalchemy.org/). Using safe variable insertion is an important habit for anyone using sql, especially when we don't know exactly where it's going to be used, that's all. – Spencer Rathbun Nov 21 '11 at 16:47

Python, easyGUI and sqlite3: Using python to read files in one folder or multiple folder, then adding the folder names and filenames to database

1 Answers1