import json
import pymysql

# Load JSON data
data = []
with open('PATH', 'r', encoding='utf8') as f: 
    data = [item for item in json.load(f)]

# Connect to the MariaDB database
connection = pymysql.connect(
    host='{{YOUT_HOST}}',
    port={{YOUR_PORT}},
    user='{{YOUR_USERNAME}}',
    password='{{YOUR_PASSWORD}}',
    db='{{YOUR_DB_NAME}',
    charset='utf8',
    use_unicode=True # Default: True
)

try:
    # Create a cursor
    cursor = connection.cursor()

    # Iterate over JSON data and insert into the database
    for item in data:
        query = \\
            f'INSERT INTO content (\\
                company_name, \\
                company_image_url, \\
                job_title, \\
                job_poster, \\
                job_description, \\
                job_category, \\
                job_location, \\
                company_type, \\
                employment_type, \\
                experience_level, \\
                salary, \\
                start_date, \\
                end_date \\
            ) VALUES (\\
                \\'{item["company_name"]}\\',\\
                \\'{item["company_image_url"]}\\',\\
                \\'{item["title"]}\\',\\
                \\'{item["poster_url"]}\\',\\
                \\'{item["content"]}\\',\\
                \\'{item["job"]}\\',\\
                \\'{item["location"]}\\',\\
                \\'{item["company_scale"]}\\',\\
                {item["regular"]},\\
                {item["experienced_years"]},\\
                {item["salary"]},\\
                \\'{item["start_date"]}\\',\\
                \\'{item["end_date"]}\\'\\
            );'
        rows = cursor.execute(query=query)

        if rows == 0:
            print(f'rows: {rows}, not inserted: {item}')
        else:
            print(f'rows: {rows}, inserted: {item}')

    # Commit the changes
    connection.commit()
except Exception as e:
    print(e)
finally:
    # Close the connection
    connection.close()