from getpass import getpass
from mysql.connector import connect, Error
import csv

# NOTE! This file does not work outside the author's laptop as the database is localised on his device.
# To test this externally, download MySQL onto your own device and modify the 'try with connect' variables below.
# Author: Md Md Farid

# Add compiled scraped data to a pre-created MySQL database.
with open('sampleCategories.csv', 'r') as sampleCat:
    catReader = csv.reader(sampleCat)
    next(catReader)
    catId = 1
    tuplesList = []
    for category in catReader:
        stringCategory = str(category)
        sampletuple = (catId, stringCategory)
        tuplesList.append(sampletuple)
        catId += 1

# Connect to the database.
# Change host and database name if using this file outside the author's laptop.
try:
    with connect(
        host="localhost",
        user=input("Enter username! "),
        password=getpass("Enter password! "),
        database="samples",
        ) as connection:
        # Update database table.
            use_db_query = "USE samples"
            for eachAppend in tuplesList:
                insert_db_query = 'INSERT INTO sample1 VALUES {}'.format(eachAppend)
                commit_db_query = "COMMIT"
                with connection.cursor() as cursor:
                    cursor.execute(use_db_query)
                    cursor.execute(insert_db_query)
                    cursor.execute(commit_db_query)
except Error as e:
    print(e)