CODEWITHSUNDEEP
pythonsqlitedictionary
emza0114
emza0114

Reputation: 69

python sqlite adding rows to a sql data base where the row is given as a dictionary

I'm new to using sqlite and I'm trying to write a class in Python which will handle all logging for a program that I'm writing.

The class receives a dictionary whose keys are the names of the columns of the database and values are the entries for that row. Now the dictionary may not have entries for all columns so I want to just log those entries that exist and set some default value if no entry can be found for these columns. The function I have at the moment in my class is something like this:

def AddRow(self, Row, Header):
    keys = ''
    values = ''

    for key in Header:
        if keys and values:
            keys += ','
            values += ','

        try:
            keys += string(key)
            values += string(Row[key])
        except:
            keys += string(key)
            values += '0.0'
    print keys
    print values
    self.c.execute("INSERT INTO {tn} ({k}) VALUES ({v})".format(tn=self.Table_Name, k=keys, v=values))

Firstly it doesn't compile with the error "sqlite3.OperationalError: near ")": syntax error"

secondly the way Im approaching this seems very clumsy is there a better/easier way then to populate a string first before executing?

Upvotes: 2

Views: 73

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1124858

Do not generate the SQL from values. Use SQL parameters instead:

def AddRow(self, Row, Header):
    columns = ', '.join(Header)
    params = ', '.join([':{}'.format(k) for k in Header])
    sql = "INSERT INTO {} ({}) VALUES ({})".format(self.Table_Name, columns, params)
    Row = dict(dict.fromkeys(Header, 0.0), **Row)
    self.c.execute(sql, Row)

The sqlit3 adapter supports two styles of SQL parameters; positional (?) and named (:name); using named parameters lets you use a dictionary as the parameter source.

The above method generates the column names and named parameters for each of the columns, then makes sure that there are values for all columns by generating a new dictionary with all keys set to 0.0, then overriding any keys with those found in Row.

By using named parameters you get several benefits:

  • Protection against SQL injection attacks
  • Automatic quoting appropriate to the value
  • Re-use of already parsed SQL queries

A quick demo session to illustrate what the above produces:

>>> Row = {'foo': 42, 'bar': 81}
>>> Header = ['foo', 'bar', 'baz']
>>> columns = ', '.join(Header)
>>> params = ', '.join([':{}'.format(k) for k in Header])
>>> "INSERT INTO {} ({}) VALUES ({})".format('demo_tablename', columns, params)
'INSERT INTO demo_tablename (foo, bar, baz) VALUES (:foo, :bar, :baz)'
>>> dict(dict.fromkeys(Header, 0.0), **Row)
{'bar': 81, 'foo': 42, 'baz': 0.0}

Upvotes: 2

Related Questions