Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inserting dir/filename to mysql from Python

Posted on 2014-03-31
15
Medium Priority
?
1,152 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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 if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Suggested Courses

610 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