JSON to SQLite - code works, but how to improve?

[KimD]

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"
}
]
}
}

[AxWald]

Hi,

1. 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:
   .
2. 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.
   .
3. 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:

   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

   The (slightly reduced) function that does the job:

   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

Just see that there's a few more functions used in here. These they are:

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

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!

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

Have fun!

[KimD]

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.