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.