Grant Jordan Grant Jordan - 1 year ago 72
JSON Question

Populating SQLite table with JSON data, getting: sqlite3.OperationalError: near "x": syntax error

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:

"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&B offering a gourmet breakfast & 6 rooms with private baths & traditional decor."},

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

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

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()

And I also tried writing the query like this:

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

Answer Source

Your current problem is the missing quotes around the string values in the query.

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

query = """
        (?, ?, ?)""".format(table)

c.execute(query, (detail['id'], detail['name'], detail['description']))

Note that the table name cannot be parameterized - 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download