Writes CPTs and cases to MySQL database from CSV

db-write.py

#!/usr/local/bin/python3

# pip3 install mysql-connector-python
import mysql.connector
import argparse
import csv

DB_USER = 'root'
DB_HOST = 'localhost'
DB_NAME = 'opkit'

parser = argparse.ArgumentParser(prog="db-write.py", description='Write CPT codes and cases to a MySQL database.')
# parser.add_argument('--password', help='Database password', required=True)
parser.add_argument('--cases-input', help='CSV file with cases to write', required=True)
parser.add_argument('--cpts-input', help='CSV file with CPT codes to write', required=True)
args = parser.parse_args()

# Read data from CSV
cases = []
with open(args.cases_input, newline='') as data:
    reader = csv.DictReader(data)
    for row in reader:
        cases.append(row)

cpts = []
with open(args.cpts_input, newline='') as data:
    reader = csv.DictReader(data)
    for row in reader:
        cpts.append(row)

# Write data to database
# db = mysql.connector.connect(user=DB_USER, password=args.password, host=DB_HOST, database=DB_NAME)
db = mysql.connector.connect(user=DB_USER, host=DB_HOST, database=DB_NAME)

print("Writing CPTs to database...")

count = 0
for cpt in cpts:
    cursor = db.cursor()

    statement = "INSERT INTO cpts (code) VALUES (%s)"
    values = (cpt['code'],)
    cursor.execute(statement, values)

    db.commit()
    count += 1

print(f'{count} record(s) inserted.')

print("Writing cases to database...")

count = 0
for case in cases:
    cursor = db.cursor()

    statement = "INSERT INTO cases (date, time, physician, patient, status) VALUES (%s, %s, %s, %s, %s)"
    values = (case['date'], case['time'], case['physician'], case['patient'], case['status'])
    cursor.execute(statement, values)

    db.commit()
    count += 1

print(f'{count} record(s) inserted.')

def fetch(table, id, conn):
    cursor = conn.cursor()
    cursor.execute(f'SELECT * FROM {table} WHERE id = {id}')
    return cursor.fetchone()

# Create association - WIP
for case in cases:
    case_id = fetch('cases', )
    case_id = 

    cursor = db.cursor()

    statement = "INSERT INTO cpts_cases (cpt_id, case_id) VALUES (%s, %s)"
    values = (cpt_id, case_id)
    cursor.execute(statement, values)

    statement = "UPDATE cases WHERE id = "

    db.commit()
    count += 1

db.close()