MySQL connection drops during pandas DataFrame upload with empty values

Data and Databases
1

I’m having trouble uploading CSV data to MySQL using pandas. The process works fine for most files, but fails when the CSV starts with blank cells in a specific column.

data = pd.read_csv(file_path, delimiter=',', encoding='utf-8', keep_default_na=False)

# Clean up the 'region' column by removing unwanted text
data['region'] = data['region'].str.rstrip('Co.DistrictDist')

# Create database connection and upload
db_url = 'mysql+mysqlconnector://user:pass@192.168.1.10:3306/testdb'
db_engine = create_engine(db_url, echo=False)
db_conn = db_engine.connect()
data.to_sql(name="locations", con=db_engine, if_exists='append', index=False)
db_conn.close()

The weird thing is that empty values later in the file don’t cause problems. Only when the first row has missing data in the region field does it break. I tried using keep_default_na=False but still get this connection error:

(mysql.connector.errors.OperationalError) 2055: Lost connection to MySQL server at '192.168.1.10:3306', system error: 10053 Connection was terminated by host

Any ideas what might be causing this?

2

sounds like a schema mismatch. when the first row’s empty, pandas probably sets that column as nullable but your mysql table expects something different. try adding method='multi' and chunksize=1000 to your to_sql call - should help avoid timeouts with messy data.

3

that’s strange! have you tried explicitly setting the dtype when reading the CSV? maybe using dtype={'region': 'object'} could help. sometimes pandas infers types that MySQL doesn’t like, especially with empty cells.

4

This happens because MySQL gets confused when the first row has empty values. Pandas can’t figure out the right data type, so it creates inconsistent column definitions that mess with the MySQL connector. I’ve seen this before - the connection drops when MySQL gets unexpected null values in columns it thinks should be strings. Fix it by preprocessing your region column first: data['region'] = data['region'].fillna(''). This fills empty values with empty strings, keeps your data types consistent, and stops MySQL from hitting those null patterns that cause timeouts.