Solved

Inserting dir/filename to mysql from Python

Posted on 2014-03-31
15
874 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
  • 5
  • 5
  • 5
15 Comments
 
LVL 28

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 28

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

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 15

Expert Comment

by:Walter Ritzel
ID: 39970655
Sorry, but can you please post your current script?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 28

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 15

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 28

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 15

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 28

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 15

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Installing Python 2.7.3 version on Windows operating system For installing Python first we need to download Python's latest version from URL" www.python.org " You can also get information on Python scripting language from the above mentioned we…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now