I want to use INSERT INTO statement in MySQL but my data is loaded like this (.csv):
id, name, age
1, John, 40
2, Mary, 23
3, Alex, 34
I’m using Python to prepare the load files and I’m struggling to get the formatting right. The trouble is I need to start rows with ‘(‘ and end them with ‘),’ and ALSO I need strings (inserted in mysql as VARCHAR) in quotes. I tried using df.to_csv(quoting = csv.QUOTE_NONNUMERIC()). That only adds the quotation marks to all non-integers, but I still need to add the delimiters to separate every row. The best I could achieve is adding these (so the df is right), but when I save as csv, the delimiters themselves (not being numeric) get wrapped in quotes.
How can I best format the df to create my mysql load files?
Format required (mysql website):
INSERT INTO tbl_name (a,b,c)
VALUES(1,2,3), (4,5,6), (7,8,9);
3
Use the csv module to parse the CSV file. Then use cursor.executemany() to insert all the CSV rows into the table in bulk.
import csv
with open("filename.csv") as f:
next(f) # skip header row
cf = csv.reader(f, skipinitialspace=True)
cursor.executemany('INSERT INTO tbl_name (id, name, age) VALUES %s', cf)
The prepared statement will handle all the necessary quoting. See How to use variables in SQL statement in Python?