se6p98DL

1import csv, os
2
3from szkolenie_python_podstawy.constants import PROJECT_PATH
4
5from mysql.connector import connect, CMySQLConnection
6from mysql.connector.cursor_cext import CMySQLCursor
7
8szkolenie_wroclaw_db: CMySQLConnection = connect(
9    host='localhost',
10    port='3306',
11    user='root',
12    password='test1test'
13)
14
15db_cursor: CMySQLCursor = szkolenie_wroclaw_db.cursor()
16db_cursor.execute('USE uczelnia')
17
18statement_create_table_pattern = """create table if not exists przedmioty
19(
20    {} varchar(12)  null,
21    {} varchar(30)  null,
22    {} timestamp    null,
23    {} varchar(30)  null,
24    {} varchar(1)   null,
25    {} varchar(64)  null,
26    {} int          null,
27    {} varchar(150) null,
28    {} varchar(10)  null,
29    {} varchar(100) null
30)
31"""
32
33statement_pattern = """INSERT INTO uczelnia.przedmioty (CDYD_KOD, PRZ_KOD, UTW_DATA, TPRO_KOD, UCZESTNICY, GUID, PRAC_ID, NAZWA, JED_ORG_KOD, NAME) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', {}, '{}', '{}', '{}')"""
34
35with open(os.path.join(PROJECT_PATH, 'przed_cykli.csv'), mode='r', encoding='windows-1250') as przedmioty_file:
36    reader = csv.DictReader(przedmioty_file, delimiter=',')
37    statement_create_table_pattern = statement_create_table_pattern.format(
38        *reader.fieldnames
39    )
40    db_cursor.execute(statement_create_table_pattern)
41    szkolenie_wroclaw_db.commit()
42    for row in reader:
43        for field in ['NAME', 'NAZWA']:
44            if '\'' in row[field]:
45                row[field] = row[field].replace('\'', '\'\'')
46        statement = statement_pattern.format(*tuple(row.values()))
47        db_cursor.execute(statement)
48    szkolenie_wroclaw_db.commit()