FAQ
I have a dictionary that contains a row of data intended for a data base.

The dictionary keys are the field names. The values are the values to be
inserted.

I am looking for a good pythonic way of expressing this, but I have a
problem with the way lists are represented when converted to strings.

Lets say my dictionary is

data = {"fname": "todd", "lname": "Bush"}
fields = data.keys()
vals = []
for v in fields:
vals.append(data[v])

sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)

but fields and vals are represented as lists. So, then I need to strip the
[] from them, but then ... there must be an easier way.

Any advise?

--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --

Search Discussions

  • Fredrik Lundh at Dec 5, 2005 at 9:59 pm

    David Bear wrote:

    The dictionary keys are the field names. The values are the values to be
    inserted.

    I am looking for a good pythonic way of expressing this, but I have a
    problem with the way lists are represented when converted to strings.

    Lets say my dictionary is

    data = {"fname": "todd", "lname": "Bush"}
    fields = data.keys()
    vals = []
    for v in fields:
    vals.append(data[v])

    sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)

    but fields and vals are represented as lists. So, then I need to strip the
    [] from them, but then ... there must be an easier way.

    Any advise?
    1) use parameters to pass in the values (see
    http://www.python.org/peps/pep-0249.html )

    2) use parameters to pass in values

    3) use parameters to pass in values

    4) here's a simplified version of your code:

    data = {"fname": "todd", "lname": "Bush"}

    fields = data.keys()
    values = data.values()

    cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )

    (this assumes that your database uses %s for parameters; if it uses
    ? instead, replace "%%s" with "?". see the paramstyle documentation
    in the db-api docs (pep 249) for more info)

    </F>
  • David Bear at Dec 5, 2005 at 10:20 pm

    Fredrik Lundh wrote:

    cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )
    Thanks for the hint. However, I don't understand the syntax.

    I will be inserting in to postgresql 8.x. I assumed the entire string would
    be evaluated prior to being sent to the cursor. However, when I attempt to
    manual construct the sql insert statment above I get an error:
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    File "<stdin>", line 1
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    ^
    SyntaxError: invalid syntax


    --
    David Bear
    -- let me buy your intellectual property, I want to own your thoughts --
  • Fredrik Lundh at Dec 5, 2005 at 10:38 pm
    David Bear wrote
    Fredrik Lundh wrote:
    cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )
    Thanks for the hint. However, I don't understand the syntax.

    I will be inserting in to postgresql 8.x. I assumed the entire string would
    be evaluated prior to being sent to the cursor.
    Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
    FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
    but this is important. Parameter passing gives you simpler code, and
    fewer security holes.
    However, when I attempt to manual construct the sql insert statment
    above I get an error:
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    File "<stdin>", line 1
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    ^
    SyntaxError: invalid syntax
    DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
    formatting to insert the field names, but let the database layer deal with
    the values.

    If you want to do things in two steps, do the fields formatting first

    query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

    and pass the query and the values sequence to the database layer:

    cursor.execute(query, values)

    The database will take care of the rest.

    </F>
  • David Bear at Dec 5, 2005 at 11:32 pm

    Fredrik Lundh wrote:

    David Bear wrote
    Fredrik Lundh wrote:
    cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )
    Thanks for the hint. However, I don't understand the syntax.

    I will be inserting in to postgresql 8.x. I assumed the entire string
    would be evaluated prior to being sent to the cursor.
    Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
    FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
    but this is important. Parameter passing gives you simpler code, and
    fewer security holes.
    please, shout until I 'get it'... I don't mind. I just don't understand
    using the star in front of the values variable; it generates a syntax
    exception for me.
    However, when I attempt to manual construct the sql insert statment
    above I get an error:
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    File "<stdin>", line 1
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    ^
    SyntaxError: invalid syntax
    DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
    formatting to insert the field names, but let the database layer deal with
    the values.
    since I am so new to this, I didn't know the database layer would handle
    this for me. Is the evaluation of the fieldname done by the dbapi layer or
    by postgresql?
    If you want to do things in two steps, do the fields formatting first

    query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

    and pass the query and the values sequence to the database layer:

    cursor.execute(query, values)
    I found this info on the pgdb interface:

    http://www.pygresql.org/pg.html

    section 4.7 describes the insert method. It is passed the tablename and a
    dictionary. But it doesn't describe how it resolves fieldnames and their
    values. I assume the dictionary key MUST correspond to a named field.
    The database will take care of the rest.
    this is my trouble. I always think I need to do more -- but I can't seem to
    find good examples on the http://www.pygresql.org/pgdb.html website.

    Do know of any good examples?
    </F>
    --
    David Bear
    -- let me buy your intellectual property, I want to own your thoughts --
  • David Bear at Dec 6, 2005 at 1:00 am

    Fredrik Lundh wrote:

    David Bear wrote
    Fredrik Lundh wrote:
    cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )
    Thanks for the hint. However, I don't understand the syntax.

    I will be inserting in to postgresql 8.x. I assumed the entire string
    would be evaluated prior to being sent to the cursor.
    Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
    FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
    but this is important. Parameter passing gives you simpler code, and
    fewer security holes.
    However, when I attempt to manual construct the sql insert statment
    above I get an error:
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    File "<stdin>", line 1
    print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
    *values)
    ^
    SyntaxError: invalid syntax
    DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
    formatting to insert the field names, but let the database layer deal with
    the values.

    If you want to do things in two steps, do the fields formatting first

    query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

    and pass the query and the values sequence to the database layer:

    cursor.execute(query, values)

    The database will take care of the rest.

    </F>
    I think I'm missing some important documentation somewhere. Here's what I
    tried (using both % and $ signs):
    sql
    'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
    (%s);'
    sql2
    'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
    ($s);'
    values
    ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
    cursor.execute(sql1, values)
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    NameError: name 'sql1' is not defined
    cursor.execute(sql, values)
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
    self.executemany(operation, (params,))
    File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
    executemany
    raise OperationalError, "internal error in '%s': %s" % (sql,err)
    pg.OperationalError: internal error in 'INIT': not all arguments converted
    during string formatting

    I get the same error if using $ sign.

    When I look at the pygresql dbapi official site at
    http://www.pygresql.org/pgdb.html

    "this section needs to be written"...

    I would really appreciate some more examples on using pgdb (pygresql)


    --
    David Bear
    -- let me buy your intellectual property, I want to own your thoughts --
  • Carsten Haese at Dec 6, 2005 at 2:32 am
    On Mon, 05 Dec 2005 18:00:21 -0700, David Bear wrote
    Fredrik Lundh wrote:
    DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
    formatting to insert the field names, but let the database layer deal with
    the values.

    If you want to do things in two steps, do the fields formatting first

    query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

    and pass the query and the values sequence to the database layer:

    cursor.execute(query, values)

    The database will take care of the rest.

    </F>
    I think I'm missing some important documentation somewhere. Here's
    what I tried (using both % and $ signs):
    sql
    'INSERT INTO nic (addr_code,ip_address,property_control,mac_address)
    VALUES
    (%s);'
    sql2
    'INSERT INTO nic (addr_code,ip_address,property_control,mac_address)
    VALUES
    ($s);'
    values
    ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
    cursor.execute(sql1, values)
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    NameError: name 'sql1' is not defined
    cursor.execute(sql, values)
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
    self.executemany(operation, (params,))
    File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
    executemany
    raise OperationalError, "internal error in '%s': %s" % (sql,err)
    pg.OperationalError: internal error in 'INIT': not all arguments converted
    during string formatting

    I get the same error if using $ sign.

    When I look at the pygresql dbapi official site at
    http://www.pygresql.org/pgdb.html

    "this section needs to be written"...

    I would really appreciate some more examples on using pgdb (pygresql)
    It appears that Fredrik gave you good advice but bad example code. The example
    he gave you constructs an insert query with only one parameter placeholder.
    You'll need as many placeholders as the number of values that are inserted.

    The following example should work better:

    def insertDict(curs, tablename, data):
    fields = data.keys()
    values = data.values()
    placeholder = "%s"
    fieldlist = ",".join(fields)
    placeholderlist = ",".join([placeholder] * len(fields))
    query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
    placeholderlist)
    curs.execute(query, values)

    The main thing to note here is that we *are* using string formatting to build
    a query that's based on a variable table name and a variable column list, but
    we *are not* using string formatting to fill in the values.[*]

    On a somewhat related note, it's unfortunate that many database modules use %s
    as parameter placeholders, because it makes it too tempting to write bad code
    such as

    cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad, uses
    vulnerable and error-prone string formatting

    instead of

    cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good, uses
    parameters.

    [*] This code blindly trusts that the table name and dictionary keys don't
    contain SQL injection attacks. If the source of these is not completely
    trustworthy, the code needs to be hardened against such attacks. I'll leave
    that as an exercise for the reader.

    Hope this helps,

    Carsten.
  • David Bear at Dec 6, 2005 at 10:19 pm

    Carsten Haese wrote:

    The
    example he gave you constructs an insert query with only one parameter
    placeholder. You'll need as many placeholders as the number of values that
    are inserted.

    The following example should work better:

    def insertDict(curs, tablename, data):
    fields = data.keys()
    values = data.values()
    placeholder = "%s"
    fieldlist = ",".join(fields)
    placeholderlist = ",".join([placeholder] * len(fields))
    query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
    placeholderlist)
    curs.execute(query, values)

    The main thing to note here is that we *are* using string formatting to
    build a query that's based on a variable table name and a variable column
    list, but we *are not* using string formatting to fill in the values.[*]

    On a somewhat related note, it's unfortunate that many database modules
    use %s
    as parameter placeholders, because it makes it too tempting to write bad
    code
    such as

    cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad,
    uses vulnerable and error-prone string formatting

    instead of

    cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good,
    uses parameters.

    [*] This code blindly trusts that the table name and dictionary keys don't
    contain SQL injection attacks. If the source of these is not completely
    trustworthy, the code needs to be hardened against such attacks. I'll
    leave that as an exercise for the reader.

    Hope this helps,

    Carsten.
    Thank you very much for the greater explanation. Yes, I was not
    understanding that that %s in one instance was a python string format
    operator, and in another instance it was a placeholder sent to the dbapi
    objects (and I supposed on down into the data base cursor) for the
    parameters following the function call. BIG DIFFERENCE.

    --
    David Bear
    -- let me buy your intellectual property, I want to own your thoughts --

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedDec 5, '05 at 9:43p
activeDec 6, '05 at 10:19p
posts8
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase