#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Import .txt data into MySQL via Python


    Hi,

    I am currently undertaking an assignment for university. I've having quite a bit of trouble with this seemingly simple task, which is to import the data from 2 .txt files into 2 tables within an sql database via python.

    Here is what I've done so far. This subject is a core unit in my course, which is a double degree with business. I mention this merely to make you aware that programming is not my strong suit.

    Code:
    import MySQLdb
    
    def connect_to_database(user, password):
        return MySQLdb.connect(host='localhost', user='root', passwd='password')
    
    ###Load text files containing car details into variables
    car_details = file(car_details.txt)
    cars_for_sale = file(cars_for_sale.txt)
    
    cursor.execute('USE cars_db')
    
    #Define Car Details for implementation                           
    def load_cd_file():
                        Load_CD = ('LOAD DATA LOCAL INFILE 'car_details.txt'
                        INTO TABLE car_details
                        FIELDS TERMINATED BY '\t'
                        LINES TERMINATED BY '\r\n')
    
    #Define Cars for sale for implementation                         
    def load_cfs_file(cursor, *files):
                        Load_CFS = ('LOAD DATA LOCAL INFILE 'cars_for_sale.txt';'
                        INTO TABLE cars_for_sale
                        FIELDS TERMINATED BY '\t'
                        LINES TERMINATED BY '\r\n')
    
    cursor.execute(Load_CD, Load_CFS)
    
    #define data entry function
    def data_entry(cars_db):
    
    #Make a connection to the mySQL database
        load files(cursor, ('car_details.txt'), ('cars_for_sale.txt'))
    
    db.commit()
    cursor.close()
    data_entry()
    db.close()
    I'm confident that there are plenty of mistakes within, but I'd be very grateful for any help you can provide.

    Thanks,

    Peter
  2. #2
  3. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,895
    Rep Power
    481
    I don't feel like, sorry, reading the MySQLdb documents now. The concept of opening the database to get a cursor, then accessing the database throught the cursor looks correct.

    If you need to read the file car_details.txt, this statement opens the file for reading. Notice that the file name is a string.
    car_details = file('car_details.txt'
    This next statement reads the file into a list of one line per list item.
    lines = car_details.readlines()

    I don't think you need to open the file or read it---I'm unfamiliar with the sql statement
    LOAD DATA LOCAL INFILE
    but if the argument is a file name as a single quote string, and the whole statement is a python string, you could write it like this:
    Code:
    Load_CD = "LOAD DATA LOCAL INFILE 'car_details.txt' INTO TABLE car_details FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'"
    If you want a function to return a result other than None you need a return statement:
    Code:
    def load_cd_file():
        return "LOAD DATA LOCAL INFILE 'car_details.txt' INTO TABLE car_details FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'"
    If you want a function to assign to a global variable---another way to get data out of a function---you need a global statement.
    Code:
    def load_cd_file():
        global Load_CD
        Load_CD = "LOAD DATA LOCAL INFILE 'car_details.txt' INTO TABLE car_details FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'"
    Here,
    load files(cursor, ('car_details.txt'), ('cars_for_sale.txt'))
    looks clever. As you know, it is invalid python.

    You're a clever person. Reorganize to the extent possible putting the functions up top and the module's executable statements toward the bottom. It's easier to read

    definitions
    actions

    than

    def
    action
    def
    action
    def
    action

    although I find with python code there sometimes is a bit of that alternation.

    Dave
    Last edited by b49P23TIvg; October 7th, 2012 at 07:51 PM. Reason: misplaced ) removed
    [code]Code tags[/code] are essential for python code and Makefiles!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Reviewed Code


    Hi,

    I've reviewed my code and so far I've got the following.
    Code:
        import csv
            import MySQLdb 
    
            #connect to database
            mydb = MySQLdb.connect("localhost","root","0dy5seuS","cars_db" )
    
            #define the function
            def data_entry(cars_for_sale):
    
                #cursor creation
                cursor = mydb.cursor()
            #load the file 'cars_for_sale.txt' into the database under the table 'cars_for_sale'
                sql = """LOAD DATA LOCAL INFILE 'cars_for_sale.TXT'
                               INTO TABLE cars_for_sale
                               FIELDS TERMINATED BY '\t'
                               LINES TERMINATED BY '\r\n'"""
    
                #execute the sql function above
                cursor.execute(sql)
    
                #commit to the database
                mydb.commit()
    
                #call data_entry(cars_for_sale) function
                data_entry(cars_for_sale)
                cursor.close()
    In the testing element provided when I run it the program just sits there for a few seconds and then it says:
    Code:
    Trying:
        data_entry("cars_for_sale")
    Expecting:
        The number of rows inserted to cars_for_sale is 7049
    **********************************************************************
    File "__main__", line 4, in __main__
    Failed example:
        data_entry("cars_for_sale")
    Exception raised:
        Traceback (most recent call last):
          File "C:\Python27\lib\doctest.py", line 1289, in __run
            compileflags, 1) in test.globs
          File "<doctest __main__[0]>", line 1, in <module>
            data_entry("cars_for_sale")
          File "E:/Uni/104/Portfolio 2/MediumTask_DataStatistics/question/TEST2_data_statistics.py", line 270, in data_entry
            data_entry(cars_for_sale) *it repeats this line several hundred/thousand times"
    
      The following few lines are after the repeated error above. 
    
      File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 243, in cursor
        return (cursorclass or self.cursorclass)(self)
      File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 51, in __init__
        from weakref import proxy
    RuntimeError: maximum recursion depth exceeded while calling a Python object
    Again, if you can help me at all, i'd very much appreciate it.
  6. #4
  7. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,895
    Rep Power
    481
    Indentation---leading white space---is a critical part of python syntax. When I cut and paste into python the program you just presented, it gives IndentationError on the first line. The first line must be flush left. The second line also gives IndentationError because, since the first statement wasn't any of those that indicate a subordinate block of statements, it must have the same or less indentation than the preceding statement. Your program runs farther than that, therefor your post misrepresents it.

    None-the-less, it looks like you've equivalently written
    Code:
    def f(x):
        f(x)
    
    f('some argument')
    I think you can see that f is a function that would call itself ad infinitum unless some sort of finite resource were completely consumed. Recursive functions are possible in python. They need a means to terminate, and you did not intend to write a recursive function.
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo