73 lines
2.6 KiB
Python
73 lines
2.6 KiB
Python
import mysql.connector
|
|
import csv
|
|
from datetime import datetime
|
|
|
|
# Database connection details
|
|
DB_CONFIG = {
|
|
'host': 'mysql.theaken.com',
|
|
'port': 33306,
|
|
'database': 'db_A024',
|
|
'user': 'A024',
|
|
'password': 'p1D37ddnIJCN'
|
|
}
|
|
|
|
CSV_FILE = 'D:\\_PANJIT\\_Trae\\Lydia_practice\\ver1_ext\\files\\FINAL_整併後分機表_converted.csv'
|
|
TABLE_NAME = 'EXT'
|
|
|
|
def create_table_and_import_data():
|
|
try:
|
|
conn = mysql.connector.connect(**DB_CONFIG)
|
|
cursor = conn.cursor()
|
|
|
|
# Drop table if it exists (for clean run, remove in production)
|
|
cursor.execute(f"DROP TABLE IF EXISTS {TABLE_NAME}")
|
|
print(f"Dropped table {TABLE_NAME} if it existed.")
|
|
|
|
# Create table
|
|
create_table_sql = f"""
|
|
CREATE TABLE {TABLE_NAME} (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
plant VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
department VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
csv_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
position VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
extension VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
createdate DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
"""
|
|
cursor.execute(create_table_sql)
|
|
print(f"Table {TABLE_NAME} created successfully.")
|
|
|
|
# Import data from CSV
|
|
with open(CSV_FILE, 'r', encoding='Big5', errors='ignore') as f:
|
|
reader = csv.reader(f)
|
|
next(reader) # Skip header row
|
|
|
|
insert_sql = f"""
|
|
INSERT INTO {TABLE_NAME} (plant, department, csv_id, position, extension)
|
|
VALUES (%s, %s, %s, %s, %s)
|
|
"""
|
|
|
|
for row in reader:
|
|
# Ensure row has enough columns, handle potential empty strings
|
|
if len(row) >= 5:
|
|
# MySQL Connector/Python expects a tuple for execute()
|
|
data = (row[0], row[1], row[2], row[3], row[4])
|
|
cursor.execute(insert_sql, data)
|
|
else:
|
|
print(f"Skipping malformed row: {row}")
|
|
|
|
conn.commit()
|
|
print(f"Data imported successfully into {TABLE_NAME}.")
|
|
|
|
except mysql.connector.Error as err:
|
|
print(f"Error: {err}")
|
|
finally:
|
|
if 'conn' in locals() and conn.is_connected():
|
|
cursor.close()
|
|
conn.close()
|
|
print("MySQL connection closed.")
|
|
|
|
if __name__ == "__main__":
|
|
create_table_and_import_data()
|