Re: [pysqlite] variable number of fields updates

Gerhard Häring <[email protected]> Tue, 04 Nov 2008 16:07:29 +0100

Newsgroups	gmane.comp.python.db.pysqlite.user
Message-ID	<[email protected]>

Eric S. Johansson wrote:
> got a record with 21 fields.   have a faint inkling of when I'm going to insert
> versus update but that's another problem.  What I'm really concerned with is
> that I update a variable number of fields at different points in my workflow.
> With what I know about SQL, I have to create a separate unique and distinct SQL
> command for every different set of fields I'm updating.  This is seriously ugly
> not to mention the complexity and testing all of these combinations.  Is there
> any mechanism/tool/idiom available that let's me do something like specify a
> dictionary, and a list of fields to update and have it generate the right SQL

Short answer. Use an ORM - I recommend SQLAlchemy.

Long answer: you can also build your own, half-assed solution :-P

I've attached a sketch that should give you an idea.

-- Gerhard

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite

sketch.py (text/x-python, 1.2 KB)

import sqlite3

# sketch of helper functions to build a stupid mini-ORM

con = sqlite3.connect(":memory:")

FIELDNAMES = ["c" + str(i) for i in range(21)]

con.execute("create table test(id integer primary key, " + ",".join(FIELDNAMES) + ")")


def do_insert(con, table, d):
    columns = ",".join(d.keys())
    values = d.values()
    value_placeholders = ",".join(["?"] * len(values))
    sql = "insert into %(table)s (%(columns)s) values (%(value_placeholders)s)" % locals()

    cur = con.cursor()
    cur.execute(sql, values)
    return cur.lastrowid

def do_update(con, table, id_value, d):
    columns = ",".join(d.keys())
    values = d.values()
    values.append(id_value)
    set_clauses = ",".join(["%s=?" % colname for colname in d.keys()])
    sql = "update %(table)s set %(set_clauses)s where id=?" % locals()
    cur = con.cursor()
    cur.execute(sql, values)

d = {"c5" : 5, "c6": "bla", "c7": None}
id1 = do_insert(con, "test", d)

d = {"c5" : 55, "c6": "blabla", "c7": 13}
id2 = do_insert(con, "test", d)

d["c7"] = 14
do_update(con, "test", id2, d)

# test if id={id2}, c7 == 14 now
assert con.execute("select c7 from test where id=?", (id2,)).fetchone()[0] == 14