69 lines
2.6 KiB
Python
69 lines
2.6 KiB
Python
import csv
|
|
import mysql.connector
|
|
|
|
def get_connection_details():
|
|
details = {}
|
|
with open('setting.txt', 'r') as f:
|
|
for line in f:
|
|
key, value = line.strip().split(':', 1)
|
|
details[key.strip()] = value.strip()
|
|
return details
|
|
|
|
def create_and_populate_table():
|
|
try:
|
|
config = get_connection_details()
|
|
conn = mysql.connector.connect(
|
|
host=config.get('Host'),
|
|
port=config.get('Port'),
|
|
user=config.get('Username'),
|
|
password=config.get('Password'),
|
|
database=config.get('Database'),
|
|
charset='utf8mb4' # Changed to utf8mb4 for better Chinese support
|
|
)
|
|
cursor = conn.cursor()
|
|
|
|
# Create table (if it doesn't exist, or if it was truncated)
|
|
# Explicitly setting CHARACTER SET and COLLATE for proper Chinese display
|
|
create_table_query = """
|
|
CREATE TABLE IF NOT EXISTS extension_data (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`plant` VARCHAR(255),
|
|
`department` VARCHAR(255),
|
|
`name` VARCHAR(255),
|
|
`position` VARCHAR(255),
|
|
`extension` VARCHAR(255)
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
|
|
"""
|
|
cursor.execute(create_table_query)
|
|
|
|
# Read from CSV and insert into table
|
|
with open('files/FINAL_整併後分機表.csv', 'r', encoding='cp950') as csvfile:
|
|
reader = csv.reader(csvfile)
|
|
next(reader) # Skip header row
|
|
for row in reader:
|
|
# Ensure row has at least 5 columns (plant, department, name, position, extension)
|
|
if len(row) >= 5:
|
|
insert_query = """
|
|
INSERT INTO extension_data (plant, department, name, position, extension)
|
|
VALUES (%s, %s, %s, %s, %s)
|
|
"""
|
|
# Map CSV columns to database columns
|
|
# CSV: [unit, department, name, position, extension, source_file, sheet]
|
|
# DB: [plant, department, name, position, extension]
|
|
data_to_insert = (row[0], row[1], row[2], row[3], row[4])
|
|
cursor.execute(insert_query, data_to_insert)
|
|
|
|
conn.commit()
|
|
print("Table 'extension_data' populated successfully with 'plant' data.")
|
|
|
|
except FileNotFoundError:
|
|
print("Error: The CSV file was not found.")
|
|
except Exception as e:
|
|
print(f"An error occurred: {e}")
|
|
finally:
|
|
if 'conn' in locals() and conn.is_connected():
|
|
cursor.close()
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
create_and_populate_table() |