파이이선 python3 -f /filepaht/filename -d DbName 같이 업션읽기

import sys
import getopt

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)

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

 

#!/usr/bin/env python3
# <?php  echo "PHP는정상". __FILE__;    /* PHP주석시작
  
#?> 

import mysql.connector
import pandas as pd
import sys
# Connect to the database
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='apmsetup',
    database='mydb'
) 
cursor = conn.cursor()

table_name = "vkdlf기"
df = pd.read_excel('C:\\Users\\yjm91\\aaa\\koex\\{}.xlsx'.format(table_name), 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)
print(create_table_query)
cursor.execute(create_table_query)
result = cursor.fetchall()
print("Cre reult=",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():
    ''' nan표시문제.
    #insert_query = 'INSERT INTO `{}` ({}) VALUES ({})'.format(
    #    table_name,
    #    ', '.join(df.columns),
    #    ', '.join(['\'{}\''.format(row[col]) for col in df.columns])
    )
    '''   
    values = ', '.join([f"'{str(value).replace('nan', '')}'" for value in row.values]) 
    sql = f"INSERT INTO `{table_name}` ({colIns}) VALUES ({values});"
    
    #print(sql) 
    cursor.execute(sql)
    result = cursor.fetchall()
    print("Ins reult=",result) 
     
# Commit the changes
conn.commit()

result = cursor.fetchall()
print("Ins reultㄸ=",result) 
# Close the cursor and connection
cursor.close()
conn.close()

# php로 실행시에도 소스 유출방지.
# 여기서PHP주석끝 <?php */  ?>