Populating SQLite table with JSON data, getting: sqlite3.OperationalError: near "x": syntax error
<p>I have a SQLite database with four tables named restaurants, bars, attractions, and lodging. Each table has 3 columns named id, name, and description. I am trying to populate the database with data from a JSON file that looks like this:</p>

<pre><code>{
  "restaurants": [
    {"id": "ChIJ8xR18JUn5IgRfwJJByM-quU", "name": "Columbia", "description": "Traditional Spanish restaurant, a branch of a long-standing local chain dating back to 1905."},
  ],
  "bars": [
    {"id": "ChIJ8aLBaJYn5IgR60p2CS_RHIw", "name": "Harrys", "description": "Chain outpost serving up Creole dishes in a leafy courtyard or on a balcony overlooking the bay."},
  ],
  "attractions": [
    {"id": "ChIJvRwErpUn5IgRaFNPl9Lv0eY", "name": "Flagler", "description": "Flagler College was founded in 1968. Formerly one of Henry Flagler's hotels, the college is allegedly home to many spirits. Tours are offered"},
  ],
  "lodging": [
    {"id": "ChIJz8NmD5Yn5IgRfgnWL-djaSM", "name": "Hemingway", "description": "Cottage-style B&amp;B offering a gourmet breakfast &amp; 6 rooms with private baths &amp; traditional decor."},
  ]
}
</code></pre>

<p>Whenever the script tries to execute the query, I get <code>sqlite3.OperationalError: near "x": syntax error</code> where x is a random word from one of the descriptions. An example error looks like this: <code>sqlite3.OperationalError: near "Spanish": syntax error</code>. The word is not always Spanish but it is always a word from one of the descriptions.</p>

<p>I have tried a couple different methods but always get the same result, here is one method I have tried:</p>

<pre><code>import sqlite3
import json

places = json.load(open('locations.json'))
db = sqlite3.connect('data.db')

for place, data in places.items():
    table = place
    for detail in data:
        query = 'INSERT OR IGNORE INTO ' + place + ' VALUES (?, ?, ?), (' \
                + detail['id'] + ',' + detail['name'] + ',' + detail['description'] + ')'
        c = db.cursor()
        c.execute(query)
        c.close()
</code></pre>

<p>And I also tried writing the query like this:</p>

<pre><code>query = 'INSERT OR IGNORE INTO {} VALUES ({}, {}, {})'\
          .format(table, detail['id'], detail['name'], detail['description'])
</code></pre>

<p>Your current problem is the <em>missing quotes</em> around the string values in the query.</p>

<p>You need to <em>properly parameterize your query</em> letting the database driver worry about the type conversions, putting quotes properly and escaping the parameters:</p>

<pre><code>query = """
    INSERT OR IGNORE INTO 
        {} 
    VALUES 
        (?, ?, ?)""".format(table)

c.execute(query, (detail['id'], detail['name'], detail['description']))
</code></pre>

<p>Note that the <a href="http://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite">table name cannot be parameterized</a> - we have to use string formatting to insert it into the query - make sure the table name is coming from a source you trust or/and properly validate it.</p>

python sql json python-3.x sqlite3