title: Convert JSON to SQLite in Python - How to map json keys to database columns properly?
body: <p>I want to convert a JSON file I created to a SQLite database.</p>
  
  <p>My intention is to decide later which data container and entry point is best, json (data entry via text editor) or SQLite (data entry via spreadsheet-like GUIs like SQLiteStudio).</p>
  
  <p>My json file is like this (containing traffic data from some crossroads in my city):</p>
  
  <pre><code>...
  "2011-12-17 16:00": {
      "local": "Av. Protásio Alves; esquina Ramiro Barcelos",
      "coord": "-30.036916,-51.208093",
      "sentido": "bairro-centro",
      "veiculos": "automotores",
      "modalidade": "semaforo 50-15",
      "regime": "típico",
      "pistas": "2+c",
      "medicoes": [
          [32, 50],
          [40, 50],
          [29, 50],
          [32, 50],
          [35, 50]
          ]
      },
  "2011-12-19 08:38": {
      "local": "R. Fernandes Vieira; esquina Protásio Alves",
      "coord": "-30.035535,-51.211079",
      "sentido": "único",
      "veiculos": "automotores",
      "modalidade": "semáforo 30-70",
      "regime": "típico",
      "pistas": "3",
      "medicoes": [
          [23, 30],
          [32, 30],
          [33, 30],
          [32, 30]
          ]
      }
  ...
  </code></pre>
  
  <p>And I have created nice database with a one-to-many relation with these lines of Python code:</p>
  
  <pre><code>import sqlite3
  
  db = sqlite3.connect("fluxos.sqlite")
  c = db.cursor()
  
  c.execute('''create table medicoes
           (timestamp text primary key,
            local text,
            coord text,
            sentido text,
            veiculos text,
            modalidade text,
            pistas text)''')
  
  c.execute('''create table valores
           (id integer primary key,
            quantidade integer,
            tempo integer,
            foreign key (id) references medicoes(timestamp))''')
  </code></pre>
  
  <p>BUT the problem is, when I was preparing to insert the rows with actual data with something like <code>c.execute("insert into medicoes values(?,?,?,?,?,?,?)" % keys)</code>, I realized that, since the dict loaded from the JSON file has no special order, it does not map properly to the column order of the database.</p>
  
  <p>So, I ask: "which strategy/method should I use to programmatically read the keys from each "block" in the JSON file (in this case, "local", "coord", "sentido", "veiculos", "modalidade", "regime", "pistas" e "medicoes"), create the database with the columns in that same order, and then insert the rows with the proper values"?</p>
  
  <p>I have a fair experience with Python, but am just beginning with SQL, so I would like to have some counseling about good practices, and not necessarily a ready recipe.</p>