I have a folder of 1000 JSON files that I need to iterate through and insert into a certain field, without writing over the string data that already exists in the other fields.
For context, I am making an ArcGIS .stylx file, which has one field that is JSON and six others that are plain text. The JSON field defines the appearance of a cartographic symbol, while the text fields provide metadata like the name of the symbol, its category, primary key, etc.
I have already tried to iterate through the directory and re-create each row of the table entirely, with the JSON as one of the arguments. Doing it this way, the contents of the field are JSON, according to DB4S; however, something is clearly wrong with the data, because when I try to access the DB, it will crash if I try to preview the symbol that the JSON is supposed to define. The problem is only with the JSON because viewing the metadata, which is dictated by the plain text fields, presents no problems. This makes me think that DB4S thinks the content is JSON, but it's actually being stored as a string, because the same thing happens when I explicitly insert it as a string.
I abandoned the above effort and am now trying to insert just the JSON in only the one field, without remaking the others. Here is my code thusfar:
import json import sqlite3 import os
directory = "C:\\Users\\debruhld\\Documents\\polycolors_bin\\"
db = "C:\\Users\\debruhld\\Documents\\FGDC_polycolors2.stylx"
conn = sqlite3.connect(db)
cur = conn.cursor()
count = 0
for file in os.listdir(directory):
count = count + 1
with open((directory+file), 'r') as j:
## (I'm only attempting to load in the first 10 files while I'm trying to figure out why this isn't working)
if count < 10:
contents = str(json.loads(j.read()))
## ITEMS is the table I'm accessing, CONTENT is the field I'm trying to fill
cur.execute('INSERT INTO ITEMS(CONTENT) VALUES(?)', [json.dumps(contents)])
conn.commit()
conn.close()This code throws no errors, but when I view the table in DB4S, the content field is empty. Thus, predictably, the .stylx file still crashes.
How can I insert my JSON into only one field without altering the other fields, while maintaining its status as a JSON object?
