CODEWITHSUNDEEP
pythonsqlite
FooBar
FooBar

Reputation: 16478

Sqlite thinks I lack bindings

In lack of any source on how to create an sqlite query in Python from a dictionary, I built my own:

    updates = ', '.join(["`"+field+"`" + '=:'+field for field in information.keys() if field != 'name'])
    where = ' WHERE name == :name'
    values = {':'+field: value for field, value in information.items()}

    query = 'UPDATE firms SET ' + updates + where
    c.execute(query, values)

However, I get 

sqlite3.ProgrammingError: You did not supply a value for binding 1.

This befuddles me, as I think I have supplied all I should have:

In[374]: query
Out[374]: 'UPDATE firms SET `founded`=:founded, `size`=:size, `headquarters`=:headquarters, `type`=:type, `revenue`=:revenue WHERE name == :name'
In[375]: information
Out[375]: 
{'founded': '1962',
 'headquarters': 'Bentonville, AR',
 'name': 'Walmart',
 'revenue': '$10+ billion (USD) per year',
 'size': '10000+ employees',
 'type': 'Company - Public (WMT)'}

Upvotes: 1

Views: 725

Answers (1)

Robᵩ
Robᵩ

Reputation: 168596

You don't need the : in the values keys. Try this:

values = {field: value for field, value in information.items()}

Or, more succintly:

values = information

Sample program:

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()

c.execute("create table firms (founded, hq, name, rev, size, type)")
c.execute("insert into firms ( name ) values (?) ",("bar", ))
conn.commit()

def update(information):
    updates = ', '.join(["`"+field+"`" + '=:'+field for field in information.keys() if field != 'name'])
    where = ' WHERE name == :name'
    values = information
    query = 'UPDATE firms SET ' + updates + where
    c.execute(query, values)
    conn.commit()

update(dict(name='bar', founded='1062', rev='1 MILLION DOLLARS!'))
print c.execute('select * from firms').fetchall()

Result:

[(u'1062', None, u'bar', u'1 MILLION DOLLARS!', None, None)]

Upvotes: 1

Related Questions