Incorrect number of bindings supplied -error-CodePudding

I end up always with "Incorrect number of bindings supplied" error of SQlite3. I tried (dataset), (dataset,),[dataset].

import requests
import json
import datetime
import sqlite3

#Get tbe Data
url = 'https://opensky-network.org//api/flights/departure?airport=EDDF&begin=1517227200&end=1517230800'
content = requests.get(url).content
dataset = json.loads(content)
#print (dataset)

#Create Table in Sqlite3
try:
    db = sqlite3.connect('Flights')
    cursor = db.cursor()
    cursor.execute('''create table flights(icao24 VARCHAR(50), firstSeen VARCHAR(50), estDepartureAirport VARCHAR(50), lastSeen VARCHAR(50), estArrivalAirport VARCHAR(50), callsign VARCHAR(50), estDepartureAirportHorizDistance VARCHAR(50), estDepartureAirportVertDistance VARCHAR(50), estArrivalAirportHorizDistance VARCHAR(50), estArrivalAirportVertDistance VARCHAR(50), departureAirportCandidatesCount VARCHAR(50), arrivalAirportCandidatesCount VARCHAR(50))''')
except Exception as E:
    print('Error:', E)
else:
    print ('Table created')

#Insert Date to the Table
try:
    cursor.executemany('insert into flights(icao24, firstSeen, estDepartureAirport, lastSeen, estArrivalAirport, callsign, estDepartureAirportHorizDistance, estDepartureAirportVertDistance, estArrivalAirportHorizDistance, estArrivalAirportVertDistance, departureAirportCandidatesCount, arrivalAirportCandidatesCount) values (?,?,?,?,?,?,?,?,?,?,?,?)', (dataset,))
except Exception as E:
    print('Error:', E)
else:
    db.commit()
    print ('Data inserted')

CodePudding user response：

The problem was that you were trying to insert a list of dictionaries into your database instead of inserting each dictionary separately and then using an update statement after that to put them together again. The following code should work for you now. It uses the same structure as your original code but inserts each dictionary one at a time instead of trying to do it all in one go.

# Import required modules

import requests

import json

from bs4 import BeautifulSoup

# Get the data

url = 'https://opensky-network.org//api/flights/departure?airport=EDDF&begin=1517227200&end=1517230800'

content = requests.get(url).content

dataset = json.loads(content)

# Create connection

conn = sqlite3.connect("Flights")

# Insert the data

try:

cursor = conn.cursor()

cursor.execute('''create table flights(icao24 VARCHAR(50), firstSeen VARCHAR(50), estDepartureAirport VARCHAR(50), lastSeen VARCHAR(50), estArrivalAirport VARCHAR(50), callsign VARCHAR(50), estDepartureAirportHorizDistance VARCHAR(50), estDepartureAirportVertDistance VARCHAR(50), estArrivalAirportHorizDistance VARCHAR(50), estArrivalAirportVertDistance VARCHAR(50), departureAirportCandidatesCount VARCHAR(50), arrivalAirportCandidatesCount VARCHAR(50))''')

cursor.executemany('''insert into flights(icao24, firstSeen, estDepartureAirport, lastSeen, estArrivalAirport, callsign, estDepartureAirportHorizDistance, estDepartureAirportVertDistance, estArrivalAirportHorizDistance, estArrivalAirportVertDistance, 
departureAirportCandidatesCount, arrivalAirportCandidatesCount) values (?,?,?,?,?,?,?,?,?,?,?,?)''', (dataset,))

except Exception as E:

print('Error:', E)

else:

conn.commit()

print('Data inserted')

# Update the table to include the new information

try:

cursor = conn.cursor()

cursor.execute('''update flights set estDepartureAirport = ?, estDepartureAirportHorizDistance = ?, estDepartureAirportVertDistance = ?, estArrivalAirport = ?, estArrivalAirportHorizDistance = ?, estArrivalAirportVertDistance = ?, departureAirportCandidatesCount = ?, arrivalAirportCandidatesCount = ? where ICAO24 = ?''', (dataset.pop(0).get('estDepartureAirport'), dataset.pop(0).get('estDepartureAirportHorizDistance'), dataset.pop(0).get('estDepartureAirportVertDistance'), dataset.pop(0).get('estArrivalAirport'), dataset.pop(0).get('estArrivalAirportHorizDistance'), dataset.pop(0).get('estArrivalAirportVertDistance'), dataset.pop(0).get('departureAirportCandidatesCount'), dataset.pop(0).get('arrivalAirportCandidatesCount'), dataset.pop(0).get('ICAO24')))

except Exception as E:

print('Error:', E)

else:

conn.commit()

print('Data updated')

CodePudding user response：

dataset is a list of dictionaries. My assumption is that you want to insert the values from the dictionaries into the table. So, replacing (dataset,) with (tuple(record.values()) for record in dataset) should produce the desired result (worked here):

...

#Insert Date to the Table
try:
    cursor.executemany(
        '''
        insert into flights (
            icao24,
            firstSeen,
            estDepartureAirport,
            lastSeen,
            estArrivalAirport,
            callsign,
            estDepartureAirportHorizDistance,
            estDepartureAirportVertDistance,
            estArrivalAirportHorizDistance,
            estArrivalAirportVertDistance,
            departureAirportCandidatesCount,
            arrivalAirportCandidatesCount
        ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''',
        (tuple(record.values()) for record in dataset)
    )

...

(tuple(record.values()) for record in dataset) is an iterator (actually a generator, which is also an iterater) which is a suitable argument for .executemany():

... The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.

It seems to me that the dictionaries are sorted in the order you need for the insert. If that is not (always) the case you should modify the generator accordingly (fetching the values explicitly by keys in the desired order).