import csv, os

from szkolenie_python_podstawy.constants import PROJECT_PATH

from mysql.connector import connect, CMySQLConnection
from mysql.connector.cursor_cext import CMySQLCursor

szkolenie_wroclaw_db: CMySQLConnection = connect(
    host='localhost',
    port='3306',
    user='root',
    password='test1test'
)

db_cursor: CMySQLCursor = szkolenie_wroclaw_db.cursor()
db_cursor.execute('USE uczelnia')

statement_pattern = """INSERT INTO uczelnia.przedmioty (CDYD_KOD, PRZ_KOD, UTW_DATA, TPRO_KOD, UCZESTNICY, GUID, PRAC_ID, NAZWA, JED_ORG_KOD, NAME) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', {}, '{}', '{}', '{}')"""

with open(os.path.join(PROJECT_PATH, 'przed_cykli.csv'), mode='r', encoding='windows-1250') as przedmioty_file:
    reader = csv.DictReader(przedmioty_file, delimiter=',')
    for i, row in enumerate(reader):
        for field in ['NAME', 'NAZWA']:
            if '\'' in row[field]:
                row[field] = row[field].replace('\'', '\'\'')
        if i == 2122:
            print()
        vals = tuple(row.values())
        statement = statement_pattern.format(*vals)
        db_cursor.execute(statement)
    szkolenie_wroclaw_db.commit()