Python -- Inserting Record from For Loop using MySQLDB

Question

I have done the tutorials but I am far from an accomplished python hacker.

I am trying to do the following using MySQLdb:

loop through a list of files from a directory
generate a new file name for these files based on function parameters
insert a database record with the new filename
move the file to the new directory using the new filename.

I have items 1,2 and 4 working but can't get the database insert working correctly. There is not an error but nothing is inserted into the table. I can login and access the database through a command prompt, and I can insert a record into the table directly.

I am using python 2.75, mySQL 5.6.13, windows 7 64bit, and MySQL_python-1.2.4-py2.7-win32 installed using easy_install.

def moveFile(rPath, dPath, dbID):
import fnmatch
import os
import MySQLdb as mysql
import sys
conn = mysql.connect(host='localhost', user='*******', passwd='*******', db='*******')
pattern = '*.pdf'
inc = 0
for root, dirs, files in os.walk(rootPath):
    for filename in fnmatch.filter(files, pattern):
        dire = root.find(rootPath) + len(rootPath)
        dest = destPath + root[dire:]
        fname = dbID + "_" + str(inc) + ".pdf"
        # print fname
        # print os.path.join(root, filename),  os.path.join(dest, fname)
        # os.renames(os.path.join(root, filename), os.path.join(dest, fname))
        x = conn.cursor()
        x.execute("INSERT INTO documents(documentname) VALUES (fname)")
        inc += 1

return 'Files Count: ', inc

I am sure I need to commit somewhere in the code but my attempts have produced no error but also no results.

Thanks for reading my question land I will try all suggestions promptly.

falsetru · Accepted Answer · 2013-08-04 14:58:47Z

2

Replace x.execute(...) line as follow:

    x.execute("INSERT INTO documents(documentname) VALUES (%s)", (fname,))

And commit at the end.

conn.commit()

answered Aug 4, 2013 at 14:58

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

2 Comments

Michael BW Over a year ago

Okay, I replaced the execute statement but I am not entirely sure where the commit statement should go. I placed it at the end of the for filename in fnmatch.filter(files, pattern): loop but no records were inserted into the database. Thanks for all your help

Michael BW Over a year ago

Well, I am an idiot. I forgot to put the parens at the end of the commit statement. I added those and the inserts worked. Thanks for all your help again.

BartoszKP · Accepted Answer · 2013-08-04 15:00:33Z

1

Yes, definitely unless you commit the transaction you won't see any results in the DB. Do this after you've executed all the inserts.

answered Aug 4, 2013 at 15:00

BartoszKP

36k15 gold badges109 silver badges135 bronze badges

Collectives™ on Stack Overflow

Python -- Inserting Record from For Loop using MySQLDB

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related