How do I skipkeys on json file read to python dictionary?

[BrandonKastning]

Here is a sample block of my code:

# Source: JSON - Courtlistener.com : https://www.courtlistener.com/api/rest/v3/opinions/4706734/

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

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

Updated (Might be onto something here - my next step to pass my new variables to MariaDB):

CODE:

# Source: https://pythonbasics.org/read-json-file/
# Source JSON: 4706734.json

import json
# Read File
with open('4706734.json', 'r') as opinion:
    data=opinion.read()
# Parse File
obj = json.loads(data)


# Example from Source - Show Values
#print("id: " + str(obj['id']))
#print("resource_uri: " + str(obj['resource_uri']))


# Show Only Values (No Key Names)

# Assign JSON Dictionary Item to a Python Variable

pvar_resource_uri = str(obj['resource_uri'])
pvar_id = str(obj['id'])
pvar_absolute_url = str(obj['absolute_url'])
pvar_cluster = str(obj['cluster'])
pvar_date_created = str(obj['date_created'])
pvar_date_modified = str(obj['date_modified'])
pvar_per_curiam = str(obj['per_curiam'])
pvar_type = str(obj['type'])
pvar_sha1 = str(obj['sha1'])
pvar_page_count = str(obj['page_count'])
pvar_download_url = str(obj['download_url'])
pvar_local_path = str(obj['local_path'])
pvar_plain_text = str(obj['plain_text'])
pvar_html_with_citations = str(obj['html_with_citations'])
pvar_extracted_by_ocr = str(obj['extracted_by_ocr'])
pvar_opinions_cited = str(obj['opinions_cited'])


input("Press Enter to Continue")

print(pvar_opinions_cited)

I need to learn how to do two things:

1) How do I skipkeys json key "joined_by"
2) How do I properly store an array of URL's from within my JSON file surrounded by [] brackets that choke when null on "joined_by" (joined_by) key is a null [] bracket array and 1064 mysql errors me using SQLAlchemy. And "opinions_cited" has a list of URL's wrapped in a [ ] bracket array and I don't know how to properly store those into MySQL/MariaDB.

Using Text & LongText currently (2 columns) key / value (each row is a key in 1 column and the value of that key in a second column). I can share the full code also.

Full code so far is found here:

https://dpaste.com/CX5BGP6Y8 (My program ends at "input" when I go into the 11 Column Version; once I figure out how to do my 2 Column Version).

Any pointers would be great! Thank you. I will be continuing to scour the internet for answers!

Best Regards,

Brandon Kastning

[BrandonKastning]

Hey Everyone! I solved this with the following (Mapped out Everything Minus the Nulled Values and Nulled Bracket Arrays):

Dragon Breath [F01].py:

# Source: JSON Data Sets: Courtlistener.com - https://www.courtlistener.com/api/bulk-info/
# Source: Courtlistener.com - created by Michael Lissner - Executive Director - Free Law Project
# 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/

# Python Learning Sources:
# 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/
# Source: https://www.geeksforgeeks.org/inserting-null-as-default-in-sqlalchemy/
# Source: https://stackoverflow.com/questions/61684135/how-to-represent-longtext-in-sqlalchemy
# Source: https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime
# Source: https://stackoverflow.com/questions/983354/how-to-make-a-python-script-wait-for-a-pressed-key
# Source: https://www.programcreek.com/python/example/71942/json.items
# Source: https://pythonbasics.org/read-json-file/

# Sharpen Your Sword Ministries
# http://sharpenyoursword.org by Brandon Kastning (Veritas Gladius)
# https://github.com/SharpenYourSword/

# Dragon Breath [F.01]
# irc.libera.chat | KnowledgeShark 
# JSON File(s) to MariaDB Table(s) Python 3 Loader
# Source JSON: 4706734.json (Largest Washington State Supreme Court Opinion in the Courtlistener.com Washington Supreme Court Dataset)
# Start Date: 03/06/2022
# Finish Date: 03/07/2022 @ Approx. 19:41

# Maria DB Database Name: EXODUS_CL_JSON_WASH_SUPREME
# Maria DB Table Name: 4706734_16_Column
# Schema: utf8mb4_unicode_ci
# id / INT / 11 / AUTO_INCREMENT / PRIMARY_KEY
# courtlistener_resource_uri / TEXT / NULL 
# courtlistener_id / TEXT / NULL
# courtlistener_absolute_url / TEXT / NULL
# courtlistener_cluster / TEXT / NULL
# courtlistener_date_created / TEXT / NULL
# courtlistener_date_modified / TEXT / NULL
# courtlistener_per_curiam / TEXT / NULL
# courtlistener_type / TEXT / NULL
# courtlistener_sha1 / TEXT / NULL
# courtlistener_page_count / TEXT / NULL
# courtlistener_download_url / TEXT / NULL
# courtlistener_local_path / TEXT / NULL
# courtlistener_plain_text / TEXT / NULL
# courtlistener_html_with_citations / TEXT / NULL
# courtlistener_extracted_by_ocr / TEXT / NULL
# courtlistener_opinions_cited / TEXT / NULL
# exodus_courtlistener_opinion_entry_timestamp / TIMESTAMP / CURRENT_TIMESTAMP

print("Welcome to Dragon Breath [F.01]... Starting All Systems...")
input("Press ENTER when ready to Start The Payload!")

print("Importing all Requried Python3 Module Libraries...")
import json
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint
from sqlalchemy import Index
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import insert
from sqlalchemy.sql import select
from sqlalchemy.sql import func
from sqlalchemy import cast
from sqlalchemy import and_, or_, not_
from sqlalchemy import update, delete
from sqlalchemy import text
print("Importing Python3 Module Libraries Required for This Python Program Successful!")

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")
print("Setting up Connection to MariaDB: localhost Successful!")

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

print ("Mapping Remote MariaDB Table Schematics (16 Columns) to Dragon Breath [F.01]...")
Base = declarative_base()

class JudicialOpinion16Columns(Base):
    __tablename__ = '4706734_16_Column'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    courtlistener_resource_uri = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_id = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_absolute_url = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_cluster = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_date_created = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_date_modified = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_per_curiam = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_type = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_sha1 = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_page_count = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_download_url = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_local_path = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_plain_text = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_html_with_citations = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_extracted_by_ocr = sqlalchemy.Column(sqlalchemy.Text)
    courtlistener_opinions_cited = sqlalchemy.Column(sqlalchemy.Text)
#    exodus_courtlistener_opinion_entry_timestamp = sqlalchemy.Column(sqlalchemy.DateTime)
print("Dragon Breath [F.01] Successfully Defined Remote MariaDB & Table Schematics! (16 Columns)")

# NEW READ/PARSE (WORKED ON 1 INSERT - ALL 16 COLUMNS):

# Read File:
with open('4706734.json', 'r') as currentopinion:
    data=currentopinion.read()
# Parse File
obj = json.loads(data)

# Show Only Keys (No Value Names)
# Assign JSON Key Name to a Python Variable (16)
pvar_key_resource_uri = ("resource_uri")
pvar_key_id = ("id")
pvar_key_absolute_url = ("absolute_url")
pvar_key_cluster = ("cluster")
pvar_key_date_created = ("date_created")
pvar_key_date_modified = ("date_modified")
pvar_key_per_curiam = ("per_curiam")
pvar_key_type = ("type")
pvar_key_sha1 = ("sha1")
pvar_key_page_count = ("page_count")
pvar_key_download_url = ("download_url")
pvar_key_local_path = ("local_path")
pvar_key_plain_text = ("plain_text")
pvar_key_html_with_citations = ("html_with_citations")
pvar_key_extracted_by_ocr = ("extracted_by_ocr")
pvar_key_opinions_cited = ("opinions_cited")

# Show Only Values (No Key Names)
# Assign JSON Key Value to a Python Variable (16)
pvar_value_resource_uri = str(obj['resource_uri'])
pvar_value_id = str(obj['id'])
pvar_value_absolute_url = str(obj['absolute_url'])
pvar_value_cluster = str(obj['cluster'])
pvar_value_date_created = str(obj['date_created'])
pvar_value_date_modified = str(obj['date_modified'])
pvar_value_per_curiam = str(obj['per_curiam'])
pvar_value_type = str(obj['type'])
pvar_value_sha1 = str(obj['sha1'])
pvar_value_page_count = str(obj['page_count'])
pvar_value_download_url = str(obj['download_url'])
pvar_value_local_path = str(obj['local_path'])
pvar_value_plain_text = str(obj['plain_text'])
pvar_value_html_with_citations = str(obj['html_with_citations'])
pvar_value_extracted_by_ocr = str(obj['extracted_by_ocr'])
pvar_value_opinions_cited = str(obj['opinions_cited'])

# Working 1 Column True Value Insert -> MariaDB - Successful!
#KeyPair_16_Hit_Combo = JudicialOpinion16Columns(courtlistener_resource_uri=pvar_value_resource_uri)
#Session.add(KeyPair_16_Hit_Combo)
#Session.commit()
#print("Successfully inserted 2 Columns into MariaDB")
#input("End of Program | Check MariaDB | Press ENTER")

# Working 2 Column True Value Insert -> MariaDB - Successful!
#KeyPair_16_Hit_Combo = JudicialOpinion16Columns(courtlistener_resource_uri=pvar_value_resource_uri, courtlistener_id=pvar_value_id)
#Session.add(KeyPair_16_Hit_Combo)
#Session.commit()
#print("Successfully inserted 2 Columns into MariaDB")
#input("End of Program | Check MariaDB | Press ENTER to Exit Python3")

KeyPair_16_Hit_Combo = JudicialOpinion16Columns(courtlistener_resource_uri=pvar_value_resource_uri, courtlistener_id=pvar_value_id, courtlistener_absolute_url=pvar_value_absolute_url, courtlistener_cluster=pvar_value_cluster, courtlistener_date_created=pvar_value_date_created, courtlistener_date_modified=pvar_value_date_modified, courtlistener_per_curiam=pvar_value_per_curiam, courtlistener_type=pvar_value_type, courtlistener_sha1=pvar_value_sha1, courtlistener_page_count=pvar_value_page_count, courtlistener_download_url=pvar_value_download_url, courtlistener_local_path=pvar_value_local_path, courtlistener_plain_text=pvar_value_plain_text, courtlistener_html_with_citations=pvar_value_html_with_citations, courtlistener_extracted_by_ocr=pvar_value_extracted_by_ocr, courtlistener_opinions_cited=pvar_value_opinions_cited)
Session.add(KeyPair_16_Hit_Combo)
Session.commit()
print("Dragon Breath [F.01] Successfully Inserted All 16 Key/Value Pairs to MariaDB SQLAlchemy Mapped Columns into MariaDB 10.5.12")
input("Dragon Breath [F.01] by KnowledgeShark - End of Program | Check MariaDB for Results! | Press ENTER to Exit Python3")
input("Visit irc.libera.chat - #python #sqlalchemy #mysql - For The Greatest Minds on The Planet - Open Source EVERYTHING!")
print("“And oppress not the widow, nor the fatherless, the stranger, nor the poor; and ...” - Zechariah 7:10 (KJV)")
print("Sharpen Your Sword Ministries | #JesusChristofNazareth #LetHISPeopleGo")

Thank you everyone for this forum!

Best Regards,

Brandon Kastning

[snippsat]

I think your are doing much more job that needed.
You can/should serialize that data Dictionary/JSON to the database.
Then you put whole Dictionary/JSON into the database and not singe variables.
Just a example of this.

import MySQLdb
import json

db = MySQLdb.connect(...)
cursor = db.cursor()

dic = {"resource_uri": "https://www.courtlistener.com/api/rest/v3/opinions/4706734/","id": 4706734,}
sql = "INSERT INTO someting(address, data) VALUES (%s, %s)"

cursor.execute(sql, ("Home 123", json.dumps(dic)))
cursor.commit()

The when take out of database serialize back to original form.

dic = json.loads(str_from_db)

[BrandonKastning]

snippsat,

Thank you very much for this!

Best Regards,

Brandon Kastning

I think your are doing much more job that needed.
You can/should serialize that data Dictionary/JSON to the database.
Then you put whole Dictionary/JSON into the database and not singe variables.
Just a example of this.

import MySQLdb
import json

db = MySQLdb.connect(...)
cursor = db.cursor()

dic = {"resource_uri": "https://www.courtlistener.com/api/rest/v3/opinions/4706734/","id": 4706734,}
sql = "INSERT INTO someting(address, data) VALUES (%s, %s)"

cursor.execute(sql, ("Home 123", json.dumps(dic)))
cursor.commit()

The when take out of database serialize back to original form.

dic = json.loads(str_from_db)