Python: SQLite insert row

Question

I have more than 100 rows of data and it would be really messy to have a line for insert table for every row. So I am trying to somehow quicken how I insert rows into my sqlite database. So I came up with the following:

def insert_item(obj):
    with db:
        c.execute("INSERT INTO Database VALUES (\
                   :First, :Last, :Item1, :Item2)",
                  {'First': obj.First,
                   'Last': obj.Last,
                   'Item1': obj.Item1,
                   'Item2': obj.Item2})

obj_1 = ('John', 'Doe', 4, 5)
obj_2 = ('Jane', 'Doe', 6, 8)
...
obj_106 = ('Johnathan', 'Doe', 9, 1)

d = {}
for x in range(1, 107):
    row = (d["obj_{0}".format(x)])
    insert_item(row)

However, it returned with this error:

 row = (d["obj_{0}".format(x)])
KeyError: 'obj_1'

Not sure what went wrong, the string is returning the correct value that I want but the function is not working.

Thank you

Isn't the dictionary d empty at the point in your code where you're trying to lookup 'obj_1'? — rwp
– rwp, Commented Apr 8, 2018 at 5:09

Alan Hoover · Accepted Answer · 2018-04-08 05:47:17Z

1

You define a bunch of tuples, then an empty dict then try to access a value in said empty dict. What you want to do is to load those tuples into that dict (or a list) to start.

d={obj_1 : ('John', 'Doe', 4, 5),
   obj_2 : ('Jane', 'Doe', 6, 8),
   ...
   obj_106 : ('Johnathan', 'Doe', 9, 1)}

for x in range(1, 107):
    row = (d["obj_{0}".format(x)])
    insert_item(row)

Or, even better

d = [('John', 'Doe', 4, 5),
     ('Jane', 'Doe', 6, 8),
      ...
     ('Johnathan', 'Doe', 9, 1)]

for row in d:
    insert_item(row)

You really should read up on how to use a dict

answered Apr 8, 2018 at 5:47

Alan Hoover

1,4502 gold badges9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shubhitgarg · Accepted Answer · 2018-04-08 07:39:32Z

1

Use executemany for Bulk inserts. Insert all the objects as tuples in list eg.

My_list = [('John', 'Doe', 4, 5),('Jane', 'Doe', 6, 8).....]

c.executemany("INSERT INTO Database ('First,'Last','item1,'item2') VALUES (?,?,?,?)",(My_list))

answered Apr 8, 2018 at 7:39

Shubhitgarg

5846 silver badges20 bronze badges

Comments

Anonymousy · Accepted Answer · 2018-04-08 08:23:08Z

I found a way to loop through the INSERT INTO with locals()

 def insert_item(obj):
    with db:
        c.execute("INSERT INTO Database VALUES (\
                   :First, :Last, :Item1, :Item2)",
                  {'First': obj.First,
                   'Last': obj.Last,
                   'Item1': obj.Item1,
                   'Item2': obj.Item2})

obj_1 = ('John', 'Doe', 4, 5)
obj_2 = ('Jane', 'Doe', 6, 8)
...
obj_106 = ('Johnathan', 'Doe', 9, 1)

for num in range(1, 107):
        row = locals()["obj_" + str(num)]
        insert_item(row)

This works perfectly, just that you need to manually key in the range.

Collectives™ on Stack Overflow

Python: SQLite insert row

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related