Data loading in mysql
Tue May 07 2024 22:26:28 GMT+0000 (Coordinated Universal Time)
Saved by
@madgakantara
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:
# Load each line as JSON
data = json.loads(line)
data_agg.append(data)
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()
content_copyCOPY
Comments