CODEWITHSUNDEEP
pythonsqlite
Adam
Adam

Reputation: 41

List of filenames to sqlite with Python

I'm trying to populate an sqlite3 database with a list of filenames from a directory using Python. I'm not sure why this is not working:

conn = sqlite3.connect( "databasefile.db" )
cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS filenames (filename TEXT)')
for root, dirnames, filenames in os.walk(directory):
    for filename in filenames:
        if filename.endswith(".ext"):
            cur.executemany('INSERT INTO filenames (filename) VALUES (?)', (filename))
            conn.commit()

I'd like it to produce this in the database file:

   Column
1  1st.ext
2  2nd.ext
3  3rd.ext

But instead it results in this:

   Column
1  1
2  r
3  s
4  t
5  .
6  e
7  x
8  t
9  2
10 n
11 d
12 .

etc.

Could anyone help me understand where I'm going wrong?

EDIT: Thank you, I didn't realize iterating over a string gives the individual characters. Added the comma to make 'filename' a tuple and resolved.

Upvotes: 3

Views: 1996

Answers (4)

Williams
Williams

Reputation: 4338

Try:

   if filename.endswith(".ext"):
       cur.executemany('INSERT INTO filenames (filename) VALUES (?)', filename)

Your problem is definitely that you are iterating over a string somewhere.

In a normal python IDE you can replicate this as follows:

>>> s = "my string"
>>> for x in s: print x
... 
m
y

s
t
r
i
n
g
>>>

Upvotes: 1

Michael0x2a
Michael0x2a

Reputation: 64218

I believe it's because executemany expects a list of items to execute. In your script, you have the line:

cur.executemany('INSERT INTO filenames (filename) VALUES (?)', (filename))

This simplifies down to:

cur.executemany('INSERT INTO filenames (filename) VALUES (?)', filename)

And then executemany interprets your filename as a list of single characters.

To fix it, you could turn your expression into a tuple:

cur.executemany('INSERT INTO filenames (filename) VALUES (?)', (filename,))

...or just do it all at once:

conn = sqlite3.connect( "databasefile.db" )
cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS filenames (filename TEXT)')
for root, dirnames, filenames in os.walk(directory):
    allowedFilenames = [f for f in filenames if f.endswith(".ext")]
    cur.executemany('INSERT INTO filenames (filename) VALUES (?)', allowedFilenames)
    conn.commit()

Upvotes: 1

Srikar Appalaraju
Srikar Appalaraju

Reputation: 73668

I think your filename variable isn't a tuple, i think sqlite is considering it a string. To make a tuple, don't forget to put a trailing coma:

(filename,)

Remember, it's commas that make a tuple, not brackets!

Upvotes: 2

BenTrofatter
BenTrofatter

Reputation: 2158

First off, you don't need to use executemany, since you're not actually doing more than one insert at a time. Secondly, change (filename) to (filename,). Both strings and tuples support the iterator protocol, and without that comma, the executemany function is interpreting the variable filename as the thing to iterate over. When you iterate over a string, you get individual characters.

The other thing you thing you might do is as follows:

for root, dirnames, filenames in os.walk(directory):
    cur.executemany("INSERT INTO filenames (filename) VALUES (?)",
                    [(filename,) for filename in filenames if filename.endswith(".ext")])
    conn.commit()

Now you're actually taking advantage of executemany, and generally, fewer jaunts off to your database is better.

Upvotes: 3

Related Questions