Skip to content
Advertisement

UPDATE SQLite database from Python not working as expected

I want to perform an UPDATE? on a SQLite database from Python (2.7). This is the table`

CREATE TABLE IF NOT EXISTS "foo" (
    "country_code"  TEXT,
    "country_name"  TEXT,
    "continent_code"    TEXT,
    "area"  INTEGER
);

and this is how I want to insert rows

connection = sqlite3.connect('foo.db')
cursor = connection.cursor()
urlCountriesContinents = 'https://raw.githubusercontent.com/Stophface/geojson-places/master/data/continents/continents.json'
countriesContinents = requests.get(urlCountriesContinents)
countriesContinents = countriesContinents.json()

for continent in countriesContinents:
    continentCode = continent['continent_code']
    for countryCode in continent['countries']:
        cursor.execute('UPDATE country SET country_code = ?, country_name = ?, continent_code = ?', (countryCode, 'Foo', continentCode))

connection.commit()
connection.close()

However, when I do a SELECT * FROM foo it the result set is zero rows.. There is not data in there. Why?

Advertisement

Answer

It sounds like you want to insert here, not update:

for continent in countriesContinents:
    continentCode = continent['continent_code']
    for countryCode in continent['countries']:
        sql = '''INSERT INTO country (country_code, country_name, continent_code)
                 VALUES (?, ?, ?)'''
        cursor.execute(sql, (countryCode, 'Foo', continentCode))

Note that there is a cursor#executeMany() function which you also might be able to use here, to avoid one/both of the above loops.

Advertisement