Solved

Inserting dir/filename to mysql from Python

Posted on 2014-03-31
15
988 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

730 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