import mysql.connector
import json
import os

# Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="Eimaipolykala1",
    database="twitter_db"
)
cursor = conn.cursor()

# Path to the directory containing JSON files
json_dir = r'C:\Users\User\Desktop\DBL Data Challenge\data'


# Loop through JSON files in the directory
for filename in os.listdir(json_dir):
    data_agg = []
    if filename.endswith('.json'):
        with open(os.path.join(json_dir, filename), 'r') as file:
            for line in file:
                try :
                # Load each line as JSON

                    data = json.loads(line)
                    data_agg.append(data)
                except JSONDecodeError as e:
                    print(f"Error in {filename}: {e}")
            
            
        for item in data_agg:
            # Insert data into MySQL table
            query = "INSERT INTO raw_data_proto (data, filename) VALUES (%s, %s)"
            values = (json.dumps(item), filename)
            cursor.execute(query, values)

# Commit changes and close connections
conn.commit()
cursor.close()
conn.close()