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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

# 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:password@127.0.0.1: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:

1 2 3

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

# 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:password@127.0.0.1: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