How to load the CSV file data to MySQL database table using Python...

Below is the Python code to insert the data from the CSV file
- CSV file is located under the "D:\Files\" folder
- Destination database name is "Test"
- This code will truncate the destination table ("Test.people_info") and load the data from the csv file to it.

import pandas as pd
import pyodbc

# Import CSV
data = pd.read_csv(r'D:\Files\People.csv') 
data.head()

# Connect to MySQL Server
conn = pyodbc.connect('Driver=MySQL ODBC 8.0 ANSI Driver;'
                        'Server=localhost;'
                        'Database=Test;'
                        'Trusted_Connection=yes;'
                        'UID=root;'
                        'PASSWORD=YourPassword;')
cursor = conn.cursor()

cursor.execute('TRUNCATE TABLE Test.people_info')

for i, row in data.iterrows():
    sql = 'INSERT INTO Test.people_info (Name, Country, Age) VALUES (?,?,?)'
    cursor.execute(sql, tuple(row))
conn.commit();

Hope this will be helpful...