Insert Values from dictionary into sqlite database

Question

I cannot get my head around it. I want to insert the values of a dictionary into a sqlite databse.

url = "https://api.flickr.com/services/rest/?method=flickr.photos.search&api_key=5f...1b&per_page=250&accuracy=1&has_geo=1&extras=geo,tags,views,description"
soup = BeautifulSoup(urlopen(url)) #soup it up
for data in soup.find_all('photo'): #parsing the data
    dict = { #filter the data, find_all creats dictionary KEY:VALUE
        "id_p": data.get('id'),
        "title_p": data.get('title'),
        "tags_p": data.get('tags'),
        "latitude_p": data.get('latitude'),
        "longitude_p": data.get('longitude'),
    }
    #print (dict)
    connector.execute("insert into DATAGERMANY values (?,?,?,?,?)", );
    connector.commit()

connector.close

My keys are id_p, title_p etc. and the values I retrieve through data.get.

However, I cannot insert them. When I try to write id, title, tags, latitude, longitude behind ...DATAGERMANY values (?,?,?,?,?)", ); I get NameError: name 'title' is not defined.

I tried it with dict.values and dict but then its saying table DATAGERMANY has 6 columns but 5 values were supplied.

Adding another ? gives me the error (with `dict.values): ValueError: parameters are of unsupported type

This is how I created the db and table.

#creating SQLite Database and Table
connector = sqlite3.connect("GERMANY.db") #create Database and Table, check if NOT NULL is a good idea
connector.execute('''CREATE TABLE DATAGERMANY
        (id_db INTEGER PRIMARY KEY AUTOINCREMENT,
        id_photo INTEGER NOT NULL,
        title TEXT,
        tags TEXT,
        latitude NUMERIC NOT NULL, 
        longitude NUMERIC NOT NULL);''')

The method should work even if there is no valueto fill in into the database... That can happen as well.

Answer 1

You can use named parameters and insert all rows at once using executemany().

As a bonus, you would get a good separation of html-parsing and data-pipelining logic:

data = [{"id_p": photo.get('id'),
         "title_p": photo.get('title'),
         "tags_p": photo.get('tags'),
         "latitude_p": photo.get('latitude'),
         "longitude_p": photo.get('longitude')} for photo in soup.find_all('photo')]
connector.executemany("""
    INSERT INTO
        DATAGERMANY
        (id_photo, title, tags, latitude, longitude)
    VALUES
        (:id_p, :title_p, :tags_p, :latitude_p, :longitude_p)""", data)

Also, don't forget to actually call the close() method:

connector.close()

FYI, the complete code:

import sqlite3
from urllib2 import urlopen
from bs4 import BeautifulSoup

url = "https://api.flickr.com/services/rest/?method=flickr.photos.search&api_key=5f...1b&per_page=250&accuracy=1&has_geo=1&extras=geo,tags,views,description"
soup = BeautifulSoup(urlopen(url))

connector = sqlite3.connect(":memory:")
cursor = connector.cursor()

cursor.execute('''CREATE TABLE DATAGERMANY
        (id_db INTEGER PRIMARY KEY AUTOINCREMENT,
        id_photo INTEGER NOT NULL,
        title TEXT,
        tags TEXT,
        latitude NUMERIC NOT NULL,
        longitude NUMERIC NOT NULL);''')

data = [{"id_p": photo.get('id'),
         "title_p": photo.get('title'),
         "tags_p": photo.get('tags'),
         "latitude_p": photo.get('latitude'),
         "longitude_p": photo.get('longitude')} for photo in soup.find_all('photo')]

cursor.executemany("""
    INSERT INTO
        DATAGERMANY
        (id_photo, title, tags, latitude, longitude)
    VALUES
        (:id_p, :title_p, :tags_p, :latitude_p, :longitude_p)""", data)

connector.commit()

cursor.close()
connector.close()

Answer 2

As written, your connector.execute() statement is missing the parameters argument.

It should be used like this:

connector.execute("insert into some_time values (?, ?)", ["question_mark_1", "question_mark_2"])

Unless you need the dictionary for later, I would actually use a list or tuple instead:

row = [
  data.get('id'),
  data.get('title'),
  data.get('tags'),
  data.get('latitude'),
  data.get('longitude'),
]

Then your insert statement becomes:

connector.execute("insert into DATAGERMANY values (NULL,?,?,?,?,?)", *row)

Why these changes?

• The NULL in the values (NULL, ...) is so the auto-incrementing primary key will work
• The list instead of the dictionary because order is important, and dictionaries don't preserve order
• The *row so the five-element row variable will be expanded (see here for details).
• Lastly, you shouldn't use dict as a variable name, since that's a built-in variable in Python.

Answer 3

If you're using Python 3.6 or above, you can do this for dicts:

dict_data = {
 'filename' : 'test.txt',
 'size' : '200'
}
table_name = 'test_table'
attrib_names = ", ".join(dict_data.keys())
attrib_values = ", ".join("?" * len(dict_data.keys()))
sql = f"INSERT INTO {table_name} ({attrib_names}) VALUES ({attrib_values})"
cursor.execute(sql, list(dict_data.values()))