Inserting dir/filename to mysql from Python

I'm attempting to insert a full directory string including file name into a mysql db from Python, and it strips the back slashes out.  Is there a way to keep the source formatting the same when importing to mysql?  Below is a snippet of my code.

for root, dirs, files in os.walk("L:\Preservation\", topdown=False):
    for name in files:
        sql = "INSERT INTO dir(path, file) VALUES('" + os.path.join(root, name) + "')"
        cursor.execute(sql)
        cnx.commit()
    #    print(os.path.join(root, name))
    #for name in dirs:
    #    print(os.path.join(root, name))
LVL 1
SarahDaisy8Asked:
Who is Participating?
 
Walter RitzelConnect With a Mentor Senior Software EngineerCommented:
@pepr: by the path she has selected, it is clear that she uses python distribution on windows. I still have a doubt if it is Python 2.7.X or 3.3.X, because of print() function. In any way, I got her script, and made it work on Python 2.7.X with that exactly string. See below and see the log file attached showing the insert command:
from __future__ import print_function
import sys
import os
import datetime
import time
import labio.configWrapper
import labio.argParseWrapper
import labio.logWrapper
import labio.dbWrapper


def main():
    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
    print("Loading Configuration...")

    fileConfig = labio.configWrapper.load_configuration()

    if fileConfig.isLoaded:
        logging = labio.logWrapper.return_logging(fileConfig.log)
        logging.info('Starting process...')        
        db = labio.dbWrapper.dbSQLiteWrapper(fileConfig.database)
        
        if db.isDatabaseNew():
            db.executeCommand('CREATE TABLE dirold(path text)')
            db.executeCommand('CREATE TABLE dir(path text)')
## start - code in question 
        for root, dirs, files in os.walk("c:\Users\Walter\SVN\Mestrado", topdown=False):
            for name in files:
                fname = os.path.join(root, name)
                fnameT = fname.replace('\\', '\\\\')
                print(fnameT)
                sql = "INSERT INTO dir(path) VALUES('%s')" % fnameT
                logging.info("Inserting file: %s ..." % sql)        
                db.executeCommand(sql)
                db.commit()
##end code in question
        logging.info('Ending process...')        
    else:
        print("Configuration not loaded. Please create the app.config file.")

    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))


#################################################################################################
# 
# Script Start
# 
#################################################################################################
if __name__ == '__main__':
    main()

Open in new window

log-20140401195251.log
0
 
peprCommented:
The first problem is with "L:\Preservation\" Does it really  compile without syntax errors? The reason is that \" should not be treated as the end of the literal. All backlashes must be doubled, or you can use the raw string literal prefix like r"L:\Preservation\". You can also use normal slashes instead of the doubled backlashes.
Another problem is that  your INSERT INTO dir(path, file) prescribes insertion of two values, but the VALUES part include only one .
0
 
peprCommented:
Another guess  is that backlashes are interpreted also when executing the sql string. Then you should double the backlashes like fname = fname.replace(r'\', r'\\') where fname is the VALUES part content.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SarahDaisy8Author Commented:
Yes, the code compiles and inserts into the table with no problems.  However, it strips all of the backslashes from the directory, and in some instances, it inserts weird symbols where the slashes were.  I will try the fname.replace and let you know how that goes.
0
 
SarahDaisy8Author Commented:
I tried using fname = fname.replace(r'\', r'\\') but I received an error, so I modified the code using double slashes as you can see below.  However, it's still not working.  If I print the value of fname after the replace it still only shows a single slash, so I may still have the syntax wrong.

for root, dirs, files in os.walk("L:\Preservation", topdown=False):
    for name in files:
        fname = os.path.join(root, name)
        fname.replace('\\', '\\\\')
        print(fname)
        sql = "INSERT INTO dir(path) VALUES('" + fname + "')"
        cursor.execute(sql)
        cnx.commit()

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
try this:
for root, dirs, files in os.walk("c:\work", topdown=False):
	for name in files:
		fname = os.path.join(root, name)
                fnameT = fname.replace('\\', '\\\\')
                print(fnameT)
                sql = "INSERT INTO dir(path) VALUES('" + fnameT + "')"
                cursor.execute(sql)
                cnx.commit()

Open in new window

I think your problem is just that you dont have saved in a variable the result of the replace
0
 
SarahDaisy8Author Commented:
When I assign the replace value to a variable and then attempt to print that variable, both the print and INSERT have 0 results.
0
 
Walter RitzelSenior Software EngineerCommented:
Sorry, but can you please post your current script?
0
 
SarahDaisy8Author Commented:
import os
import mysql.connector

def main():
    pass

if __name__ == '__main__':
    main()

cnx = mysql.connector.connect()
cursor = cnx.cursor()

for root, dirs, files in os.walk("L:\Preservation", topdown=False):
    for name in files:
        fname = os.path.join(root, name)
        Tname = fname.replace('\\', '\\\\')
        print(Tname)
        sql = "INSERT INTO dir(path) VALUES('" + (Tname) + "')"
        cursor.execute(sql)
        cnx.commit()
    #    print(os.path.join(root, name))
    #for name in dirs:
    #    print(os.path.join(root, name))

cnx.close()

Open in new window

0
 
peprCommented:
Try to double the backlash in os.walk("L:\Preservation",...
Add the print that shows what names are processed.
Print that the sql variable to see the generated command.
0
 
Walter RitzelSenior Software EngineerCommented:
No, this is not needed.

just try to replace this line on your script:
sql = "INSERT INTO dir(path) VALUES('" + (Tname) + "')"

Open in new window


by this:
sql = "INSERT INTO dir(path) VALUES('%s')" % Tname

Open in new window


Other thing is to check if on the database exists a table called dir that have a field called path and no other required (NOT NULL) field, otherwise this insert could fail. In fact looking at your script, there is no reference to the mysql connection string, which I assume you have removed for security purposes. If not, you may want to check and put one on the connect command.
0
 
peprCommented:
@Walter: If there is no print result then Tname was never assigned, and it does not  depend on working with the database. My guess is that L:\Pxxx in the string literal was interpreted as L:Pxxx, and the walk has nothing to walk through.

Changing the sql = line would not change anything. It should produce the same result.
0
 
peprCommented:
@Walter: OK. I may be wrong. Not being at the computer, I did not check how \P is interpreted. Anyway, not doubling backlashes in paths is wrong as it works only in special cases -- unless the raw string literal is used. (By the way, git is better than svn :-)
@Sara: When the script produces no print result, does it show any error message?
0
 
Walter RitzelSenior Software EngineerCommented:
@pepr  and @Sara: here is a self sufficient version: the \P is not a problem, but does not harm to do as you mention, @pepr.

from __future__ import print_function
import sys
import os
import datetime
import time
import sqlite3

def main():
    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
    print('Collecting content on db')

    db = sqlite3.connect('db.db')
    cursor = db.cursor()

    cursor.execute("DROP TABLE IF EXISTS dir")
    cursor.execute("CREATE TABLE dir(path text)")
    db.commit()

    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
    
    for root, dirs, files in os.walk(r"c:\Projects", topdown=False):
        for name in files:
            fname = os.path.join(root, name)
            fnameT = fname.replace('\\', '\\\\')
            try:
                sql = u"INSERT INTO dir(path) VALUES('%s')" % fnameT.encode('utf-8')
                cursor.execute(sql)
            except Exception as e:
                print('Error on string:')
                print(sql)

    db.commit()

    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
    print('Listing content from db')
    
    cursor.execute("SELECT * FROM dir")

    rows = cursor.fetchall()

    for row in rows:
        print(row[0].decode('utf-8'))

    print(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
        
#################################################################################################
# 
# Script Start
# 
#################################################################################################
if __name__ == '__main__':
    main()

Open in new window

0
 
SarahDaisy8Author Commented:
That worked, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.