insert a dictionary into sql data base

Discussion in 'Python' started by David Bear, Dec 5, 2005.

  1. David Bear

    David Bear Guest

    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 --
    David Bear, Dec 5, 2005
    #1
    1. Advertising

  2. 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>
    Fredrik Lundh, Dec 5, 2005
    #2
    1. Advertising

  3. David Bear

    David Bear Guest

    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 --
    David Bear, Dec 5, 2005
    #3
  4. 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>
    Fredrik Lundh, Dec 5, 2005
    #4
  5. David Bear

    David Bear Guest

    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, Dec 5, 2005
    #5
  6. David Bear

    David Bear Guest

    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 --
    David Bear, Dec 6, 2005
    #6
  7. 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.
    Carsten Haese, Dec 6, 2005
    #7
  8. David Bear

    David Bear Guest

    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 --
    David Bear, Dec 6, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pratik Gupte
    Replies:
    1
    Views:
    622
  2. Harry Zoroc
    Replies:
    1
    Views:
    933
    Gregory Vaughan
    Jul 12, 2004
  3. Rod
    Replies:
    0
    Views:
    438
  4. Replies:
    7
    Views:
    567
  5. chen li
    Replies:
    6
    Views:
    117
    Giles Bowkett
    Jan 23, 2007
Loading...

Share This Page