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

Question

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()
        c.execute(query)
        c.close()

And I also tried writing the query like this:

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

score 4 · Accepted Answer · edited May 23 '17 at 12:06

4

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 = """
    INSERT OR IGNORE INTO 
        {} 
    VALUES 
        (?, ?, ?)""".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.

edited May 23 '17 at 12:06

Community

1
1

answered Sep 25 '16 at 13:26

alecxe

462,703
120
1,088
1,195

Thank you for your quick response. I was pretty sure that was the problem from what I read, unfortunately, when I try your method I get `c.execute(query, (detail['id'], detail['name'], detail['description'])) sqlite3.IntegrityError: datatype mismatch` – Grant Jordan Sep 25 '16 at 13:38
@GrantJordan ah, okay, what happens if you specify the column name explicitly after the table name, e.g. `INSERT OR IGNORE INTO {} (COLUMN1, COLUMN2, COLUMN3) VALUES (?, ?, ?)` – alecxe Sep 25 '16 at 13:40
OK, the datatype mismatch error was because I made the id column an Integer instead of Text and my id's are alphanumeric. Thank you for your help, the script runs now with no errors! – Grant Jordan Sep 25 '16 at 13:58

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

1 Answers1