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"
}
]
}
}
JSON to SQLite - code works, but how to improve?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: JSON to SQLite - code works, but how to improve?
Hi,
A bonus for the more adventurous ones among us - I just recognized that my more advanced kwote() version (status: development, "may contain hazardous ingredients") could be used in the formatValues() function, too. Have fun testing!
Have fun!
- SQLite does the BEGIN/ COMMIT for you, so you can usually skip this. You're using it here correctly to prohibit single BEGIN/ COMMITs for each record, but:
. - Why use a repeat for each record, instead of feeding the whole data into the table at once? Should be much faster.
For table-like data see my example below. Adapting to arrays should be a minor problem.
. - I found that creating the VALUES part of a SQL action statement is quite likely to introduce "bgus". So I wrote me a function to do it for me. Start with a button:
The (slightly reduced) function that does the job:
Code: Select all
on mouseUp put "Whole" & tab & "WH" & tab & "1,1" & CR & \ "Good Hard" & tab & "GH" & tab & "2,2" & CR & \ "Good Toffee" & tab & "GT" into myData -- myData is a tab-delimited table containing the data for our VALUES put "S" & tab & "S" & tab & "P" into myFormat -- myFormat is a tab-delimited line containing a descriptor entry for each field that needs formatting put formatValues(myData,myFormat) -- formatValues takes all this and mingles it into a correct VALUE form end mouseUp
Code: Select all
function formatValues what, theFormat -- what: tab-del multi-line data -- theFormat: also tab-del, 1 entry per field (can be empty) on 1 line -- returns a nice VALUES construct including the final semicolon if theFormat is empty then put "1" into theFormat -- dummy for 1-value data set itemdel to tab repeat for each line L in what put "(" into myLine repeat with i = 1 to the number of items of theFormat switch char 1 of item i of theFormat case "S" -- single kwote put Kwote(item i of L, "'") & comma after myLine break case "P" -- number with (comma -> points) if item i of L is empty then put "0.00" & comma after myLine else put com2Pt(item i of L) & comma after myLine end if break case "C" -- number with (points -> comma) if item i of L is empty then put "0,00" & comma after myLine else put com2Pt(item i of L, true) & comma after myLine end if break default -- no change put item i of L & comma after myLine end switch end repeat delete last char of myLine put ")," after myLine put myLine & CR after myVar end repeat delete last char of myVar delete last char of myVar put ";" after myVar return myVar end formatValues
Code: Select all
function Com2Pt what, Reverse
if Reverse is not true then
replace comma with "." in what
else
replace "." with comma in what
end if
return what
end Com2Pt
function kwote what, theChar
if theChar is empty then put quote into theChar
return theChar & what & theChar
end kwote
Code: Select all
function kwote what, theChar, ToEscape
-- kwote("What)" -> "What"
-- kwote("What","#") -> #What#
-- kwote("What","1()") -> (What)
-- kwote("What","2[()]!") -> [(What)]!
if ToEscape is not true then put false into ToEscape
if theChar is empty then -- Default
put quote into MyChar
put quote into My2Char
else -- theChar not empty:
if len(theChar) >1 then -- special mode for different inserts
if char 1 of thechar is a number then -- a len() is given for multi insert
put char 1 of thechar into MyLen
put char 2 to MyLen+1 of theChar into MyChar -- the chunk specified comes before
put char (MyLen + 2) to len(theChar) of theChar into My2Char -- the remaining stuff behind
else if char 1 of theChar = "-" then -- we need to reduce something
put char 2 of thechar into MyLen
delete char 1 to MyLen of what
delete char (MyLen * -1) to -1 of what
return what
else -- standard for () or such
put char 1 of theChar into MyChar
put char 2 of theChar into My2Char
-- note: if theChar doesn't start with a number, anything but char 1 to 2 is ignored!
end if
else -- a 1-char theChar:
put theChar into MyChar
put theChar into My2Char
end if
end if
if ToEscape then --- dangerous chars in strings may be escaped!
put "34,39" into MyNums -- " '
repeat for each item X in MyNums
replace numtochar(X) with "\" & numtochar(X) in what
end repeat
end if
return MyChar & what & My2Char
end kwote
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
Re: JSON to SQLite - code works, but how to improve?
After taking your advice, what I've ended up with is the below -
put specialFolderPath("desktop") & "/" & inputName into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Put "INSERT into processedState(code, description) VALUES" into ssSQLCode
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put "(" after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["code"], "") & comma after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["desc"], "") & ")," after ssSQLCode
End Repeat
delete last char of ssSQLCode
revExecuteSQL the dbID of this stack, ssSQLCode
This seems to work fine, and certainly generates less "did I get the quotes right" fear
Thanks for your help.
put specialFolderPath("desktop") & "/" & inputName into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Put "INSERT into processedState(code, description) VALUES" into ssSQLCode
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put "(" after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["code"], "") & comma after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["desc"], "") & ")," after ssSQLCode
End Repeat
delete last char of ssSQLCode
revExecuteSQL the dbID of this stack, ssSQLCode
This seems to work fine, and certainly generates less "did I get the quotes right" fear
Thanks for your help.