Apr-28-2019, 05:26 PM
(This post was last modified: Apr-28-2019, 06:36 PM by bluethundr.)
Hello,
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:
Then all of the lines of the CSV print out:
Why is this happening? How can I get all the lines of the CSV into the database?
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 200176595756546201775238333And 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 fileBut 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?