How to insert multiple rows from a python nested dictionary to a sqlite db

Question

I read a yaml file and extract values into a python list. I want to dump the values into a sqlite db and the structure of the list is nested. I have attempted various methods of storing the data in my db, as seen below but they don't solve the problem.

When the lists of the format ('a','b','c') I am able to insert into the table without issues. But when I have a key-value pair, I run into issues.

Data structure: 
 [   {   'CPU': 2,
    'jobname': 'Job1',
    'memory': '4 GB',
    'problem': 'Prob1',
    'team': '1'},
{   'CPU': 4,
    'jobname': 'Job2',
    'memory': '256 GB',
    'problem': 'Prob3',
    'team': '3'},
{   'CPU': 5,
    'jobname': 'Job3',
    'memory': '8 GB',
    'problem': 'Prob5',
    'team': '1'}]

This is what I tried but it looks their data structure is slightly different: Python : How to insert a dictionary to a sqlite database?

# read data from the config file
def read_yaml(file):
    with open(file, "r") as stream:
        try:
            config = yaml.safe_load(stream)
        except yaml.YAMLError as exc:
            print(exc)
            print("\n")
    return config

    q = read_yaml("queue.yaml")

  # establish connection to sqlite database and save into db
 conn = sqlite3.connect('queues.db')
 c = conn.cursor()

 # create a sqlite3 database to store the dictionary values
 def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS queues(job TEXT, team 
       TEXT, problem TEXT, CPU INT, memory TEXT)")

 create_table()

 # insert data into the table
def dynamic_data_entry():
    for item in q:
        c.execute("INSERT INTO queues VALUES (?, ?, ?, ?, ?)", item)
    conn.commit()

dynamic_data_entry()

This is the output error : Traceback (most recent call last):

File "queue_info.py", line 50, in <module>
dynamic_data_entry()
File "queue_info.py", line 47, in dynamic_data_entry
c.execute("INSERT INTO queues VALUES (?, ?, ?, ?, ?)", item)
sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a 
 dictionary (which has only names).

score 2 · Accepted Answer · answered Dec 28 '18 at 18:56

2

sqlite3 supports dictionaries using the placeholder syntax :[key] in place of ?. You can also use executemany instead of for item in q:

c.executemany("INSERT INTO queues (job, team, problem, CPU, memory) VALUES
    (:jobname, :team, :problem, :CPU, :memory);", data)

answered Dec 28 '18 at 18:56

Reid Ballard

1,480
14
19

thank you! I may have a follow-up question about deeply nested dictionaries like Data structure: Data = [ { 'CPU': 2, 'jobname': '[Queue': Job1', 'Queue2': 'Job3'], 'memory': '4 GB', 'problem': 'Prob1', 'team': '1'}, How can I handle such a case within my sqlite insertion? – nlp Dec 28 '18 at 19:06
afaik you'll need to preprocess the dicts to normalize them into a flat list before inserting. – Reid Ballard Dec 28 '18 at 20:43

How to insert multiple rows from a python nested dictionary to a sqlite db

1 Answers1