I am struggling with basic json library + sql alchemy w/ mariadb-connector json ->sql

[BrandonKastning]

I am struggling with basic json library + sql alchemy w/ mariadb-connector json ->sql

# Source: Courtlistener.com - Washington Supreme Court (Largest JSON in Dataset) - 4706734.json
# Source: JSON - Courtlistener.com : https://www.courtlistener.com/api/rest/v3/opinions/4706734/
# Source: https://pynative.com/python-json-load-and-loads-to-parse-json/
# Source: https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-1/

# Very Simple Loader

# Part A: Parse key/value data from loaded .json file : 4706734.json to a Python Dictionary Called "developer"
# Part B: Python Dictionary to MariaDB
# Part C: Loop through a directory of .JSON folders for each Jurisdictional Dataset
# Goal is to Eventually have a mirrored Table built with identical column names as keys in the .JSON file
# Initital Goal was to bring key name to -> MariaDB Column: ckey and the same key's value in JSON to -> MariaDB Column: cvalue

import json
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

print("Starting Basic JSON Loader to MariaDB using SQLAlchemy & MariaDB-Connector...")
# Define the MariaDB engine using MariaDB Connector/Python

print("Setting up Connection to MariaDB: localhost...")
engine = sqlalchemy.create_engine("mysql+pymysql://username:[email protected]:3306/DatabaseName?charset=utf8mb4")


# Start an instance of MariaDB-Connector
print("Connecting to MariaDB: localhost...")
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
Session = Session()

# Mapping MariaDB Remote Database & Table Structure with SQLAlchemy
print("Connected Successfully to MariaDB: localhost!")
print ("Defining Remote MariaDB & Table Schematics...")
Base = declarative_base()

class JudicialOpinion(Base):
    __tablename__ = 'Constitutional_Case_Law_CL_WASH_SUPREME'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    ckey = sqlalchemy.Column(sqlalchemy.Text, default=False)
    cvalue = sqlalchemy.Column(sqlalchemy.Text, default=False)
#    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
#    first_name = sqlalchemy.Column(sqlalchemy.String(length=100))
#    last_name = sqlalchemy.Column(sqlalchemy.String(length=100))
#    active = sqlalchemy.Column(sqlalchemy.Boolean, default=True)

print("Successfully Defined Remote MariaDB & Table Schematics!")

# Read JSON File & Convert to a Python Dictionary

print("Started Reading JSON file")
with open("4706734.json", "r") as read_file:
    print("Converting JSON encoded data into Python dictionary")
    developer = json.load(read_file)

print("Successfully read 4706734.json to a Python Dictionary called: 'developer'")


# Define Contents of Python Dictionary "developer" for use with SQLAlchemy & MariaDB-Connector

print("Define Contents of Python Dictionary which now contains the JSON file loaded...")
for key, value in developer.items():
         ikey = key
         ivalue = value
print ("Successfully assigned Python Dictionary Item Variables!")
#    print("Decoded JSON Data From File & Now Attempting to Send to MariaDB")
#    for key, value in developer.items():

#        print(key, ":", value)
#    print("Done reading json file")


#    print("Parsed JSON Data From File")
#    for key, value in developer.items():
#        print(key, ":", value)
#    print("Done reading json file")


# INSERT to MariaDB from Python Dictionary

#newEmployee = Employee(firstname=”Rob”, lastname=”Hedgpeth”)

# Attempting to Add / Insert keys and values from JSON to column "ckey" & "cvalue" from key / value from JSON library
print("Starting Attempt at Inserting JSON keys/values to MariaDB columns: ckey & cvalue...") 
# Errors with JSON Data | newJudicialOpinion = JudicialOpinion(ckey=ikey,cvalue=ivalue) 
newJudicialOpinion = JudicialOpinion(ckey="ikey", cvalue="ivalue")
Session.add(newJudicialOpinion)
Session.commit()
print("Script Ran Successfully!")

My output succeeds with the full code running. However; the row that is inserted is "ikey" in column: "ckey" and "ivalue" in column: "cvalue"

I was hoping to extract each json files keys and put the name of the key in column : "ckey' and the paired value to "ivalue" as my first script.

My second one was to learn how to mirror the key names in the json file to create a SQLAlchemy table using the same names and then inserting the json paired value data to the columns.

I believe "LONGTEXT" is required for the amount of data in the court opinion key pair values from the JSON file.

my Table is as follows:

id / INT / 11 / Auto_Increment Primary Key
ckey / TEXT / default null
cvalue / LONGTEXT / default null

UPDATE:

I was able to change some things around (placing the insert in the "for" block statement -- unsure if this is called a function)

It worked, however it did not pass the actual values of each of the rows (which was the amount for the json file) -- Progress!

New code:

# Source: Courtlistener.com - Washington Supreme Court (Largest JSON in Dataset) - 4706734.json
# Source: JSON - Courtlistener.com : https://www.courtlistener.com/api/rest/v3/opinions/4706734/
# Source: https://pynative.com/python-json-load-and-loads-to-parse-json/
# Source: https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-1/

# Very Simple Loader

# Part A: Parse key/value data from loaded .json file : 4706734.json to a Python Dictionary Called "developer"
# Part B: Python Dictionary to MariaDB
# Part C: Loop through a directory of .JSON folders for each Jurisdictional Dataset
# Goal is to Eventually have a mirrored Table built with identical column names as keys in the .JSON file
# Initital Goal was to bring key name to -> MariaDB Column: ckey and the same key's value in JSON to -> MariaDB Column: cvalue

import json
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

print("Starting Basic JSON Loader to MariaDB using SQLAlchemy & MariaDB-Connector...")
# Define the MariaDB engine using MariaDB Connector/Python

print("Setting up Connection to MariaDB: localhost...")
engine = sqlalchemy.create_engine("mysql+pymysql://username:[email protected]:3306/DatabaseName?charset=utf8mb4")


# Start an instance of MariaDB-Connector
print("Connecting to MariaDB: localhost...")
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
Session = Session()

# Mapping MariaDB Remote Database & Table Structure with SQLAlchemy
print("Connected Successfully to MariaDB: localhost!")
print ("Defining Remote MariaDB & Table Schematics...")
Base = declarative_base()

class JudicialOpinion(Base):
    __tablename__ = 'Constitutional_Case_Law_CL_WASH_SUPREME'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    ckey = sqlalchemy.Column(sqlalchemy.Text, default=False)
    cvalue = sqlalchemy.Column(sqlalchemy.Text, default=False)
#    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
#    first_name = sqlalchemy.Column(sqlalchemy.String(length=100))
#    last_name = sqlalchemy.Column(sqlalchemy.String(length=100))
#    active = sqlalchemy.Column(sqlalchemy.Boolean, default=True)

print("Successfully Defined Remote MariaDB & Table Schematics!")

# Read JSON File & Convert to a Python Dictionary

print("Started Reading JSON file")
with open("4706734.json", "r") as read_file:
    print("Converting JSON encoded data into Python dictionary")
    developer = json.load(read_file)

print("Successfully read 4706734.json to a Python Dictionary called: 'developer'")


# Define Contents of Python Dictionary "developer" for use with SQLAlchemy & MariaDB-Connector

print("Define Contents of Python Dictionary which now contains the JSON file loaded...")
for key, value in developer.items():
         ikey = key
         ivalue = value
         newJudicialOpinion = JudicialOpinion(ckey="ikey", cvalue="ivalue")
         Session.add(newJudicialOpinion)
         Session.commit()
#print("Script Ran Successfully!")
#print ("Successfully assigned Python Dictionary Item Variables!")
#    print("Decoded JSON Data From File & Now Attempting to Send to MariaDB")
#    for key, value in developer.items():

#        print(key, ":", value)
#    print("Done reading json file")


#    print("Parsed JSON Data From File")
#    for key, value in developer.items():
#        print(key, ":", value)
#    print("Done reading json file")


# INSERT to MariaDB from Python Dictionary

#newEmployee = Employee(firstname=”Rob”, lastname=”Hedgpeth”)

# Attempting to Add / Insert keys and values from JSON to column "ckey" & "cvalue" from key / value from JSON library
#print("Starting Attempt at Inserting JSON keys/values to MariaDB columns: ckey & cvalue...") 
# Errors with JSON Data | newJudicialOpinion = JudicialOpinion(ckey=ikey,cvalue=ivalue) 
#newJudicialOpinion = JudicialOpinion(ckey="ikey", cvalue="ivalue")
#Session.add(newJudicialOpinion)
#Session.commit()
print("Script Ran Successfully!")

Here is my updated code (again) -- getting some help from someone here

My current updated code is : https://dpaste.com/A2F6U8WGK
Any pointers would be greatly appreciated! Thank you everyone for this forum!

The problem currently is I am getting a 1064 error which can be seen here : https://dpaste.com/8W96JSMUV for Syntax when it comes to row 6 which has a json load key/value of "[]" ; I would batch remove if I didn't need to:

a) learn how to handle brackets with SQLAlchemy & Python
b) last row has an array of URLs in 1 field that is wrapped in "[]" (So I will encounter this again); the entire dataset I am working with is setup this way.

Any pointers would be helpful! Thank you!

Best Regards,

Brandon