Import .txt data into MySQL

[PeteB247]

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.

import MySQLdb

def connect_to_database(user, password):
    return MySQLdb.connect(host='localhost', user='root', passwd='0dy5seuS')


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

[TrustyTony]

Use " or """ quoting for strings containing single quotes.

[woooee]

Start with a tutorial on MySQL for Python, as your code does not contain enough to comment on. For starters, you don't ever call the function connect_to_database, and the function itself does not connect to any database. Start with opening a database correctly and inserting records, which is in every tutorial, then you can read the text file one record at at time, split it into fields, and insert the fields into the database in the proper place.

[PeteB247]

Hi,

I've reviewed my code and so far I've got the following.

    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:

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.

[TrustyTony]

You have infinite recursion at line 25, like the error message indicates

RuntimeError: maximum recursion depth exceeded while calling a Python object

[PeteB247]

I see, is there anyway I can stop this and have it write to the database?

[woooee]

Aready answered Here