?
Solved

Inserting dir/filename to mysql from Python

Posted on 2014-03-31
15
Medium Priority
?
1,092 Views
Last Modified: 2014-04-02
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))
0
Comment
Question by:SarahDaisy8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 5
15 Comments
 
LVL 29

Expert Comment

by:pepr
ID: 39968141
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
 
LVL 29

Expert Comment

by:pepr
ID: 39968192
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
 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39969784
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39969858
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
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39970437
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
 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39970534
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
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39970655
Sorry, but can you please post your current script?
0
 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39970714
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
 
LVL 29

Expert Comment

by:pepr
ID: 39970832
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
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39970849
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
 
LVL 29

Expert Comment

by:pepr
ID: 39971195
@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
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 2000 total points
ID: 39971798
@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
 
LVL 29

Expert Comment

by:pepr
ID: 39971935
@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
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39972766
@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
 
LVL 1

Author Closing Comment

by:SarahDaisy8
ID: 39972774
That worked, thanks!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question