JSON to SQLite - code works, but how to improve?
Posted: Tue Apr 24, 2018 6:08 am
Hi. I need to read a JSON file (example below) into an SQLite table. The code below seems to work, but I suspect that there will be easier ways that I could do this. The "Insert Into" statement in particular, scare the **** out of me. I'm only working with 2 fields, and I've already gained a few additional grey hairs trying to get the quotes right I'd be interested to hear how more experienced LCers would have handled the same problem, as I've got to do a bunch of JSON to SQLite coding over the next few weeks. Thanks in advance.
put specialFolderPath("desktop") & "/Processed_States.json" into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
Put "BEGIN TRANSACTION;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put ("INSERT into processedState(code, description) VALUES (" & quote & tArray["processed_states_list"]["Item"][loadRow]["code"] & quote & "," & quote & tArray["processed_states_list"]["Item"][loadRow]["desc"] & quote & ");") into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
End Repeat
End if
Put "COMMIT;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
=============================================================================
{
"processed_states_list": {
"item": [
{
"desc": "Whole",
"code": "WH"
},
{
"desc": "Good Hard",
"code": "GH"
},
{
"desc": "Good Toffee",
"code": "GT"
},
{
"desc": "Gooey",
"code": "GO"
},
{
"desc": "Less Gooey",
"code": "LG"
},
{
"desc": "Light Toffee",
"code": "LT"
},
{
"desc": "Sugar Free",
"code": "SF"
},
{
"desc": "Other",
"code": "OT"
}
]
}
}
put specialFolderPath("desktop") & "/Processed_States.json" into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
Put "BEGIN TRANSACTION;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put ("INSERT into processedState(code, description) VALUES (" & quote & tArray["processed_states_list"]["Item"][loadRow]["code"] & quote & "," & quote & tArray["processed_states_list"]["Item"][loadRow]["desc"] & quote & ");") into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
End Repeat
End if
Put "COMMIT;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
=============================================================================
{
"processed_states_list": {
"item": [
{
"desc": "Whole",
"code": "WH"
},
{
"desc": "Good Hard",
"code": "GH"
},
{
"desc": "Good Toffee",
"code": "GT"
},
{
"desc": "Gooey",
"code": "GO"
},
{
"desc": "Less Gooey",
"code": "LG"
},
{
"desc": "Light Toffee",
"code": "LT"
},
{
"desc": "Sugar Free",
"code": "SF"
},
{
"desc": "Other",
"code": "OT"
}
]
}
}