insert_voters.py

import pandas as pd
import mysql.connector

# Replace 'your_file.xlsx' with the path to your Excel file
file_path = '07_محافظة_الكرك.xlsx'

df = pd.read_excel(file_path, skiprows=9)
data_dict = df.to_dict(orient='records')

db = mysql.connector.connect(
    port='3306',
    host='127.0.0.1',
    user='root',
    password='root',
    database='voters_db',
)
mycursor = db.cursor()
mycursor.execute('CREATE SCHEMA `voters_db` ;')
mycursor.execute('DROP TABLE `voters_db`.`voters`;')
mycursor.execute('''CREATE TABLE `voters_db`.`voters` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `gender` VARCHAR(255) NULL,
  `place_od_residence_in_id` VARCHAR(255) NULL,
  `actual_place_of_residence` VARCHAR(255) NULL,
  `election_area_id` VARCHAR(255) NULL,
  PRIMARY KEY (`id`));''')

for voter_data_dict in data_dict:
    mycursor.execute('INSERT INTO voters (name , gender , actual_place_of_residence) VALUES (%s,%s,%s)' ,(
        voter_data_dict['اسم الناخب'],
        voter_data_dict['الجنس'],
        voter_data_dict['مكان الاقامة']
    ))
db.commit()