Using python to insert a large csv file into mysql table

Question

I have a workflow where I need to take a 500k row csv and import it into a mysql table. I have a python script that seems to be working, but no data is being saved into the actual table when I select it. I'm dropping and re-creating the table headers then trying to bulk insert the csv file, but it doesn't look like the data is going in. No errors are reported in the python console when running.

The script takes about 2 minutes to run which makes me think that it's doing something, but I don't get anything other than the column headers when I select * from the table itself.

My script looks roughly like:

import pandas as pd
import mysql.connector

dataframe.to_csv('import-data.csv', header=False, index=False)

DB_NAME = 'SCHEMA1'
TABLES = {}
TABLES['TableName'] = (
"CREATE TABLE `TableName` ("
"`x_1` varchar(10) NOT NULL,"
"`x_2` varchar(20) NOT NULL,"
"PRIMARY KEY (`x_1`)"
") ENGINE = InnoDB")

load = """
LOAD DATA LOCAL INFILE 'import-data.csv'
INTO TABLE SCHEMA1.TableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
"""

conn = mysql.connector.connect(
    host=writer_host,
    port=port,
    user=username,
    password=password,
    database=username,
    ssl_ca=cert_path
) 

cursor = conn.cursor(buffered=True)
cursor.execute("DROP TABLE IF EXISTS SCHEMA1.TableName")
cursor.execute(TABLES['TableName'])
cursor.execute(load)
cursor.close()
conn.close()

Answer 1

missing commit after executing your commands

cursor.commit()