xlsx파일 업로드 하여 DB로 가져오는법

<?php exec("python3 ./_xlsx.py.php  -f /tmp/myfile.xls  -d myDbName ", $output) ;

import mysql.connector
import pandas as pd
import getopt
import sys

def main(argv):
    inputfile = ''
    dbname = ''
    try:
        opts, args = getopt.getopt(argv, "hf:d:", ["file=", "dbname="])
    except getopt.GetoptError:
        print('_xlsx.py -f <inputfile> -d <dbname>')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print('_xlsx.py -f <inputfile> -d <dbname>')
            sys.exit()
        elif opt in ("-f", "--file"):
            inputfile = arg
        elif opt in ("-d", "--dbname"):
            dbname = arg
    print('Input file is', inputfile)
    print('DB name is', dbname)
    
    # Connect to the database
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='apmsetup',
        database=dbname
    ) 
    cursor = conn.cursor()

    # Read Excel file and create table
    table_name = inputfile.split(".")[0]
    df = pd.read_excel(inputfile, sheet_name=0)

    # Create the table with columns
    columns = ', '.join(['`{}` varchar(100) not null default \'\' '.format(col) for col in df.columns]) 
    create_table_query = 'CREATE TABLE if not exists  `{}` (idx int(11) AUTO_INCREMENT PRIMARY KEY, {}) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;'.format(table_name, columns)
    cursor.execute(create_table_query)
    result = cursor.fetchall()
    print("Create table result = ", result)

    cursor.execute("truncate {}".format(table_name))

    colIns = ', '.join(['`{}` '.format(col) for col in df.columns])
    # Use for loop to insert values into the table for each row
    for index, row in df.iterrows():
        values = ', '.join([f"'{str(value).replace('nan', '')}'" for value in row.values]) 
        sql = f"INSERT INTO `{table_name}` ({colIns}) VALUES ({values});"
        cursor.execute(sql)
        result = cursor.fetchall()
        print("Insert result = ", result) 

    # Commit the changes
    conn.commit()

    # Close the cursor and connection
    cursor.close()
    conn.close()

if __name__ == "__main__":
    main(sys.argv[1:])

python3 _xlsx.py -f 파일경로/파일이름.xlsx -d 데이터베이스이름 

-f와 -d 옵션 뒤에 각각 파일 경로 및 이름과 데이터베이스 이름을 입력하시면 됩니다.