Import Large CSV File into MySQL using Python

Question

I'm trying to import one column of a large CSV file into MySQL using python 3.7. This is being done as a test run to import the rest of the columns.

For now, I can't even get the one column into the database. I was hoping to find some help.

I have setup a database with one table and only one field for the test data:

mysql> use aws_bill
Database changed

mysql> show tables;
+--------------------+
| Tables_in_aws_bill |
+--------------------+
| billing_info       |
+--------------------+

mysql> desc billing_info;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| RecordId | int(11) | NO   |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

When I run my code:

mydb = mysql.connector.connect(user='xxxx', password='xxxxx',
                            host='xxxxx',
                            database='aws_bill')
cursor = mydb.cursor()
try:
    with open(source) as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        sql = "INSERT INTO billing_info (RecordId) VALUES (%s)"
        for row in csv_reader:
            row = (', '.join(row))
            print(row)
            cursor.execute(sql, row)
except:
    mydb.rollback()
finally:
    mydb.close()

Only ONE line of the CSV column gets printed out:

python3 .\aws_billing.py
200176595756546201775238333

And nothing makes it into the database:

mysql> select RecordId from billing_info;
Empty set (0.00 sec)

If I comment out the sql insert statement: cursor.execute(sql, row)

Then all of the lines of the CSV print out:

203528424494971448426778962
203529863341009197771806423
203529974021473640029260511
203530250722634745672445063
203525214761502622966710100
203525122527782254417348410
203529365278919207614044035
...continues to the end of the file

But none of the data makes it into the database, of course. Because the SQL line is commented out. At least all of the lines of the CSV are printing out now, however, putting them into the database would be good!

Why is this happening? How can I get all the lines of the CSV into the database?

`except: mydb.rollback()` is bad practice, as even if a variable isn't declared, you enter the exception. What's the exception you're expecting? I suggest removing the try/except block see what happens — Jean-François Fabre, Apr 28 '19 at 17:40
I think you just need `cursor.execute(sql, row)` without converting `row` to string. just remove `row = (', '.join(row))` — Jean-François Fabre, Apr 28 '19 at 17:42

score 1 · Accepted Answer · answered Apr 28 '19 at 17:52

1

You can do it like this:

Change this line sql = "INSERT INTO billing_info (InvoiceId) VALUES (%s)" to

sql = "INSERT INTO billing_info (InvoiceId) VALUES {}"

And this one: cursor.execute(sql, row) to cursor.execute(sql.format(row))

answered Apr 28 '19 at 17:52

Klemen Koleša

446
3
6

The problem was that the value was too big for even a BIGINT type. So I converted the table to a string and now the data gets imported to the database as intended. Thanks for your valuable help! That got me most of the way there! – bluethundr Apr 28 '19 at 19:30

Import Large CSV File into MySQL using Python

1 Answers1