formating query with empty parameter

Discussion in 'Python' started by someone, May 25, 2009.

  1. someone

    someone Guest

    Hello!

    if one of parameter in values is empty, I'm getting
    TypeError: not enough arguments for format string

    But how to handle such situation? It is ok for DB, that some of values
    are empty.



    def __insert(self, data):
    query = """
    BEGIN;
    INSERT INTO table
    (a, b, c, d, e, f, g)
    VALUES
    (%s, %s, %s, %s, %s, %s, %s);
    COMMIT;
    """
    values = [
    data['a'],
    data['b'],
    data['c'],
    data['d'],
    data['e'],
    data['f'],
    data['g']
    ]
    self.db.execute(query, *values)



    Thanks Pet
     
    someone, May 25, 2009
    #1
    1. Advertising

  2. someone wrote:

    > Hello!
    >
    > if one of parameter in values is empty, I'm getting
    > TypeError: not enough arguments for format string
    >
    > But how to handle such situation? It is ok for DB, that some of values
    > are empty.
    >
    >
    >
    > def __insert(self, data):
    > query = """
    > BEGIN;
    > INSERT INTO table
    > (a, b, c, d, e, f, g)
    > VALUES
    > (%s, %s, %s, %s, %s, %s, %s);
    > COMMIT;
    > """
    > values = [
    > data['a'],
    > data['b'],
    > data['c'],
    > data['d'],
    > data['e'],
    > data['f'],
    > data['g']
    > ]
    > self.db.execute(query, *values)


    You need to pass

    None

    then as that parameter.

    Diez
     
    Diez B. Roggisch, May 25, 2009
    #2
    1. Advertising

  3. someone

    Pet Guest

    On May 25, 2:15 pm, "Diez B. Roggisch" <> wrote:
    > someone wrote:
    > > Hello!

    >
    > > if one of parameter in values is empty, I'm getting
    > > TypeError: not enough arguments for format string

    >
    > > But how to handle such situation? It is ok for DB, that some of values
    > > are empty.

    >
    > > def __insert(self, data):
    > >         query = """
    > >             BEGIN;
    > >                 INSERT INTO table
    > >                     (a,  b,  c,  d,  e,  f,  g)
    > >                     VALUES
    > >                     (%s, %s, %s, %s, %s, %s, %s);
    > >             COMMIT;
    > >             """
    > >         values = [
    > >             data['a'],
    > >             data['b'],
    > >             data['c'],
    > >             data['d'],
    > >             data['e'],
    > >             data['f'],
    > >             data['g']
    > >             ]
    > >         self.db.execute(query, *values)

    >
    > You need to pass
    >
    > None


    Hi,

    thanks for reply.
    Unfortunately, it doesn't work. Still getting TypeError: not enough
    arguments for format string


    >
    > then as that parameter.
    >
    > Diez
     
    Pet, May 25, 2009
    #3
  4. someone

    Pet Guest

    On May 25, 2:25 pm, Pet <> wrote:
    > On May 25, 2:15 pm, "Diez B. Roggisch" <> wrote:
    >
    >
    >
    >
    >
    > > someone wrote:
    > > > Hello!

    >
    > > > if one of parameter in values is empty, I'm getting
    > > > TypeError: not enough arguments for format string

    >
    > > > But how to handle such situation? It is ok for DB, that some of values
    > > > are empty.

    >
    > > > def __insert(self, data):
    > > >         query = """
    > > >             BEGIN;
    > > >                 INSERT INTO table
    > > >                     (a,  b,  c,  d,  e,  f,  g)
    > > >                     VALUES
    > > >                     (%s, %s, %s, %s, %s, %s, %s);
    > > >             COMMIT;
    > > >             """
    > > >         values = [
    > > >             data['a'],
    > > >             data['b'],
    > > >             data['c'],
    > > >             data['d'],
    > > >             data['e'],
    > > >             data['f'],
    > > >             data['g']
    > > >             ]
    > > >         self.db.execute(query, *values)

    >
    > > You need to pass

    >
    > > None

    >
    > Hi,
    >
    > thanks for reply.
    > Unfortunately, it doesn't work. Still getting TypeError: not enough
    > arguments for format string
    >
    >
    >
    >
    >
    > > then as that parameter.

    >
    > > Diez


    Sorry, for previous quick post. Actually it works now, I've missed
    some other parameter in list

    Thanks again!
     
    Pet, May 25, 2009
    #4
  5. someone

    Peter Otten Guest

    Pet wrote:

    > > someone wrote:
    > > > Hello!

    > >
    > > > if one of parameter in values is empty, I'm getting
    > > > TypeError: not enough arguments for format string

    > >
    > > > But how to handle such situation? It is ok for DB, that some of values
    > > > are empty.

    > >
    > > > def __insert(self, data):
    > > > query = """
    > > > BEGIN;
    > > > INSERT INTO table
    > > > (a, b, c, d, e, f, g)
    > > > VALUES
    > > > (%s, %s, %s, %s, %s, %s, %s);
    > > > COMMIT;
    > > > """
    > > > values = [
    > > > data['a'],
    > > > data['b'],
    > > > data['c'],
    > > > data['d'],
    > > > data['e'],
    > > > data['f'],
    > > > data['g']
    > > > ]
    > > > self.db.execute(query, *values)

    > >
    > > You need to pass
    > >
    > > None

    >
    > Hi,
    >
    > thanks for reply.
    > Unfortunately, it doesn't work. Still getting TypeError: not enough
    > arguments for format string
    >


    The code you posted doesn't match that error message. You have to invoke
    cursor.execute() as

    cursor.execute(query, values) # correct

    , not

    cursor.execute(query, *values) # wrong

    or

    cursor.execute(query % values) # wrong

    The length of values must match the number of "%s" occurences in the sql
    query, but as Diez indicated you may pass None for every field that allows a
    NULL value in the table.

    Peter
     
    Peter Otten, May 25, 2009
    #5
  6. someone

    Tim Chase Guest

    >>>> if one of parameter in values is empty, I'm getting
    >>>> TypeError: not enough arguments for format string
    >>>> But how to handle such situation? It is ok for DB, that some of values
    >>>> are empty.
    >>>> def __insert(self, data):
    >>>> query = """
    >>>> BEGIN;
    >>>> INSERT INTO table
    >>>> (a, b, c, d, e, f, g)
    >>>> VALUES
    >>>> (%s, %s, %s, %s, %s, %s, %s);
    >>>> COMMIT;
    >>>> """
    >>>> values = [
    >>>> data['a'],
    >>>> data['b'],
    >>>> data['c'],
    >>>> data['d'],
    >>>> data['e'],
    >>>> data['f'],
    >>>> data['g']
    >>>> ]
    >>>> self.db.execute(query, *values)

    >
    > Sorry, for previous quick post. Actually it works now, I've missed
    > some other parameter in list


    To stave off this problem, I often use:

    values = [
    data['a'],
    data['b'],
    data['c'],
    data['d'],
    data['e'],
    data['f'],
    data['g'],
    ]
    params = ', '.join('%s' for _ in values)
    query = """
    BEGIN;
    INSERT INTO table
    (a,b,c,d,e,f,g)
    VALUES (%s);
    COMMIT;
    """ % params
    self.db.execute(query, values)

    If the indexes are named the same as the fieldnames, or you have
    a mapping of them, I tend to use something like

    field_map = {
    # dictionary_index: database_fieldname
    # data['a'] -> table.f1
    'a': 'f1',
    'b': 'f2',
    'c': 'f3',
    # ...
    }
    name_value_pairs = (
    (data[k], v)
    for k,v
    in fieldmap.iteritems())
    values, fieldnames = zip(*name_value_pairs)
    # may want to do fieldname escaping here:
    fieldname_string = ', '.join(fieldnames)
    params = ', '.join('%s' for _ in ordering)

    query = """
    BEGIN;
    INSERT INTO table (%s) VALUES (%s);
    COMMIT;
    """ % (fieldname_string, params)
    self.db.execute(query, values)

    -tkc
     
    Tim Chase, May 25, 2009
    #6
  7. someone

    Pet Guest

    On May 25, 2:50 pm, Peter Otten <> wrote:
    > Pet wrote:
    > > > someone wrote:
    > > > > Hello!

    >
    > > > > if one of parameter in values is empty, I'm getting
    > > > > TypeError: not enough arguments for format string

    >
    > > > > But how to handle such situation? It is ok for DB, that some of values
    > > > > are empty.

    >
    > > > > def __insert(self, data):
    > > > >         query = """
    > > > >             BEGIN;
    > > > >                 INSERT INTO table
    > > > >                     (a,  b,  c,  d,  e,  f,  g)
    > > > >                     VALUES
    > > > >                     (%s, %s, %s, %s, %s, %s, %s);
    > > > >             COMMIT;
    > > > >             """
    > > > >         values = [
    > > > >             data['a'],
    > > > >             data['b'],
    > > > >             data['c'],
    > > > >             data['d'],
    > > > >             data['e'],
    > > > >             data['f'],
    > > > >             data['g']
    > > > >             ]
    > > > >         self.db.execute(query, *values)

    >
    > > > You need to pass

    >
    > > > None

    >
    > > Hi,

    >
    > > thanks for reply.
    > > Unfortunately, it doesn't work. Still getting TypeError: not enough
    > > arguments for format string

    >
    > The code you posted doesn't match that error message. You have to invoke
    > cursor.execute() as
    >
    > cursor.execute(query, values) # correct
    >
    > , not
    >
    > cursor.execute(query, *values) # wrong


    as far as I know it is not wrong, at least for pyPgSQL it takes values
    and escapes properly preventing sql injections

    >
    > or
    >
    > cursor.execute(query % values) # wrong
    >
    > The length of values must match the number of "%s" occurences in the sql
    > query, but as Diez indicated you may pass None for every field that allows a
    > NULL value in the table.
    >
    > Peter
     
    Pet, May 25, 2009
    #7
  8. someone

    Pet Guest

    On May 25, 3:26 pm, Tim Chase <> wrote:
    > >>>> if one of parameter in values is empty, I'm getting
    > >>>> TypeError: not enough arguments for format string
    > >>>> But how to handle such situation? It is ok for DB, that some of values
    > >>>> are empty.
    > >>>> def __insert(self, data):
    > >>>>         query = """
    > >>>>             BEGIN;
    > >>>>                 INSERT INTO table
    > >>>>                     (a,  b,  c,  d,  e,  f,  g)
    > >>>>                     VALUES
    > >>>>                     (%s, %s, %s, %s, %s, %s, %s);
    > >>>>             COMMIT;
    > >>>>             """
    > >>>>         values = [
    > >>>>             data['a'],
    > >>>>             data['b'],
    > >>>>             data['c'],
    > >>>>             data['d'],
    > >>>>             data['e'],
    > >>>>             data['f'],
    > >>>>             data['g']
    > >>>>             ]
    > >>>>         self.db.execute(query, *values)

    >
    > > Sorry, for previous quick post. Actually it works now, I've missed
    > > some other parameter in list

    >
    > To stave off this problem, I often use:
    >
    >    values = [
    >     data['a'],
    >     data['b'],
    >     data['c'],
    >     data['d'],
    >     data['e'],
    >     data['f'],
    >     data['g'],
    >     ]
    >    params = ', '.join('%s' for _ in values)
    >    query = """
    >      BEGIN;
    >        INSERT INTO table
    >          (a,b,c,d,e,f,g)
    >        VALUES (%s);
    >      COMMIT;
    >      """ % params
    >    self.db.execute(query, values)
    >


    Why do you pass values to execute() if you already have your query
    formatted?

    > If the indexes are named the same as the fieldnames, or you have
    > a mapping of them, I tend to use something like
    >
    >    field_map = {
    >      # dictionary_index: database_fieldname
    >      # data['a'] -> table.f1
    >      'a': 'f1',
    >      'b': 'f2',
    >      'c': 'f3',
    >      # ...
    >      }
    >    name_value_pairs = (
    >      (data[k], v)
    >      for k,v
    >      in fieldmap.iteritems())
    >    values, fieldnames = zip(*name_value_pairs)
    >    # may want to do fieldname escaping here:
    >    fieldname_string = ', '.join(fieldnames)
    >    params = ', '.join('%s' for _ in ordering)
    >
    >    query = """
    >      BEGIN;
    >        INSERT INTO table (%s) VALUES (%s);
    >      COMMIT;
    >      """ % (fieldname_string, params)
    >    self.db.execute(query, values)
    >
    > -tkc
     
    Pet, May 25, 2009
    #8
  9. someone

    Tim Chase Guest

    >> To stave off this problem, I often use:
    >>
    >> values = [
    >> data['a'],
    >> data['b'],
    >> data['c'],
    >> data['d'],
    >> data['e'],
    >> data['f'],
    >> data['g'],
    >> ]
    >> params = ', '.join('%s' for _ in values)
    >> query = """
    >> BEGIN;
    >> INSERT INTO table
    >> (a,b,c,d,e,f,g)
    >> VALUES (%s);
    >> COMMIT;
    >> """ % params
    >> self.db.execute(query, values)
    >>

    >
    > Why do you pass values to execute() if you already have your query
    > formatted?


    The "params" might be better named "placeholders". So after the

    query = "..." % params

    the query looks like your original (go ahead and print "query" to
    see), only the number of placeholders ("%s") is guaranteed to
    match the number of values you pass in during the execute() call.
    The second iteration I gave goes one step further to ensure
    that the "(a,b,c,d,e,f,g)" portion also matches in count to the
    number of values and place-holders to be used.

    Once you have a SQL query that matches what you plan to pass
    (based on your initial data-structure: a list/tuple or a
    dictionary), then you call execute(query, values) to have the
    database then associate the parameter-placeholders ("%s") with
    the corresponding value from "values".

    -tkc
     
    Tim Chase, May 25, 2009
    #9
  10. someone

    Peter Otten Guest

    Pet wrote:

    > On May 25, 2:50 pm, Peter Otten <> wrote:


    >> cursor.execute(query, *values) # wrong

    >
    > as far as I know it is not wrong, at least for pyPgSQL it takes values
    > and escapes properly preventing sql injections


    If so replace "# wrong" with "# superfluous" ;)

    Peter
     
    Peter Otten, May 25, 2009
    #10
  11. someone

    Pet Guest

    On 25 Mai, 18:16, Tim Chase <> wrote:
    > >> To stave off this problem, I often use:

    >
    > >>    values = [
    > >>     data['a'],
    > >>     data['b'],
    > >>     data['c'],
    > >>     data['d'],
    > >>     data['e'],
    > >>     data['f'],
    > >>     data['g'],
    > >>     ]
    > >>    params = ', '.join('%s' for _ in values)
    > >>    query = """
    > >>      BEGIN;
    > >>        INSERT INTO table
    > >>          (a,b,c,d,e,f,g)
    > >>        VALUES (%s);
    > >>      COMMIT;
    > >>      """ % params
    > >>    self.db.execute(query, values)

    >
    > > Why do you pass values to execute() if you already have your query
    > > formatted?

    >
    > The "params" might be better named "placeholders".  So after the


    O, thanks for clarification, I've completely missed the point of
    params = ', '.join
    >
    >     query = "..." % params
    >
    > the query looks like your original (go ahead and print "query" to
    > see), only the number of placeholders ("%s") is guaranteed to
    > match the number of values you pass in during the execute() call.
    >   The second iteration I gave goes one step further to ensure
    > that the "(a,b,c,d,e,f,g)" portion also matches in count to the
    > number of values and place-holders to be used.
    >
    > Once you have a SQL query that matches what you plan to pass
    > (based on your initial data-structure:  a list/tuple or a
    > dictionary), then you call execute(query, values) to have the
    > database then associate the parameter-placeholders ("%s") with
    > the corresponding value from "values".
    >
    > -tkc
     
    Pet, May 25, 2009
    #11
  12. On Mon, 25 May 2009 07:53:06 -0700 (PDT), Pet <>
    declaimed the following in gmane.comp.python.general:


    > as far as I know it is not wrong, at least for pyPgSQL it takes values
    > and escapes properly preventing sql injections
    >

    It is incorrect if it is supposed to be DB-API compatible. DB-API
    ..execute() specifies two arguments period: the parameterized query, and
    a single list/tuple containing all the values for the statement.

    Using the * means Python is unpacking the value list/tuple and
    passing a whole slew of arguments. All other DB-API compliant modules
    should then give you your "insufficient" arguments problems because they
    are trying to fit the first item of your list to all the placeholders
    and running out of items... (Especially MySQLdb, which uses the Python %
    interpolation internally, after escaping each item).

    >>> def places(fmt, args, *overflow):

    .... print "fmt: %r\nargs: %r" % (fmt, args)
    .... print "substituted: %r" % (fmt % args)
    ....
    >>> f3 = "three places: %s, %s, %s"
    >>> f1 = "one place: %s"
    >>> v3 = ("Me", "myself", "I")
    >>> places(f1, v3)

    fmt: 'one place: %s'
    args: ('Me', 'myself', 'I')
    Traceback (most recent call last):
    File "<interactive input>", line 1, in <module>
    File "<interactive input>", line 3, in places
    TypeError: not all arguments converted during string formatting
    >>> places(f1, *v3)

    fmt: 'one place: %s'
    args: 'Me'
    substituted: 'one place: Me'
    >>> places(f3, v3)

    fmt: 'three places: %s, %s, %s'
    args: ('Me', 'myself', 'I')
    substituted: 'three places: Me, myself, I'
    >>> places(f3, *v3)

    fmt: 'three places: %s, %s, %s'
    args: 'Me'
    Traceback (most recent call last):
    File "<interactive input>", line 1, in <module>
    File "<interactive input>", line 3, in places
    TypeError: not enough arguments for format string
    >>>


    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, May 25, 2009
    #12
  13. someone

    Aahz Guest

    In article <>,
    Tim Chase <> wrote:
    >
    >To stave off this problem, I often use:
    >
    > values = [
    > data['a'],
    > data['b'],
    > data['c'],
    > data['d'],
    > data['e'],
    > data['f'],
    > data['g'],
    > ]
    > params = ', '.join('%s' for _ in values)
    > query = """
    > BEGIN;
    > INSERT INTO table
    > (a,b,c,d,e,f,g)
    > VALUES (%s);
    > COMMIT;
    > """ % params
    > self.db.execute(query, values)


    How do you handle correct SQL escaping?
    --
    Aahz () <*> http://www.pythoncraft.com/

    my-python-code-runs-5x-faster-this-month-thanks-to-dumping-$2K-
    on-a-new-machine-ly y'rs - tim
     
    Aahz, May 29, 2009
    #13
  14. someone

    Tim Chase Guest

    Aahz wrote:
    > Tim Chase <> wrote:
    >> To stave off this problem, I often use:
    >>
    >> values = [
    >> data['a'],
    >> data['b'],
    >> data['c'],
    >> data['d'],
    >> data['e'],
    >> data['f'],
    >> data['g'],
    >> ]
    >> params = ', '.join('%s' for _ in values)
    >> query = """
    >> BEGIN;
    >> INSERT INTO table
    >> (a,b,c,d,e,f,g)
    >> VALUES (%s);
    >> COMMIT;
    >> """ % params
    >> self.db.execute(query, values)

    >
    > How do you handle correct SQL escaping?


    If you dump "query", you see that "params" (possibly a better
    name would be "place_holders") is merely a list of "%s, %s, %s,
    ...., %s" allowing the "execute(query, ***values***)" to properly
    escape the values. The aim is to ensure that
    "count(placeholders) == len(values)" which the OP mentioned was
    the problem.

    My second round of code (in my initial post) ensures that

    the number of items in the column definition (in this case the
    "a,b,c,d,e,f,g")
    is the same as
    the number of placeholders
    is the same as the number of values.

    The column-names should be code-controlled, and thus I don't
    worry about sql escaping them (my own dumb fault here), whereas
    the values may come from an untrusted source and need to be
    escaped. So the code I use often has a dictionary of

    mapping = {
    "tablefield1": uservalue1,
    "tablefield2": uservalue2,
    ...
    }

    which I can then easily add/remove columns in a single place if I
    need, rather than remembering to adjust the query in two places
    (the table-fieldnames and add the extra placeholder) AND the
    building of the "values" parameter. It also makes it harder to
    mis-sequence them, accidentally making the table-fieldnames
    "a,b,c" and the values list "a,c,b" (which, when I have 20 or so
    fields being updated has happened to me on more than one occasion)

    -tkc
     
    Tim Chase, May 29, 2009
    #14
  15. someone

    Aahz Guest

    In article <>,
    Tim Chase <> wrote:
    >Aahz wrote:
    >> Tim Chase <> wrote:
    >>> To stave off this problem, I often use:
    >>>
    >>> values = [
    >>> data['a'],
    >>> data['b'],
    >>> data['c'],
    >>> data['d'],
    >>> data['e'],
    >>> data['f'],
    >>> data['g'],
    >>> ]
    >>> params = ', '.join('%s' for _ in values)
    >>> query = """
    >>> BEGIN;
    >>> INSERT INTO table
    >>> (a,b,c,d,e,f,g)
    >>> VALUES (%s);
    >>> COMMIT;
    >>> """ % params
    >>> self.db.execute(query, values)

    >>
    >> How do you handle correct SQL escaping?

    >
    >If you dump "query", you see that "params" (possibly a better
    >name would be "place_holders") is merely a list of "%s, %s, %s,
    >..., %s" allowing the "execute(query, ***values***)" to properly
    >escape the values. The aim is to ensure that
    >"count(placeholders) == len(values)" which the OP mentioned was
    >the problem.


    Right, that's what I get for reading code early in the morning.
    --
    Aahz () <*> http://www.pythoncraft.com/

    my-python-code-runs-5x-faster-this-month-thanks-to-dumping-$2K-
    on-a-new-machine-ly y'rs - tim
     
    Aahz, May 29, 2009
    #15
    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. John

    empty/non-empty element

    John, Jul 15, 2003, in forum: XML
    Replies:
    1
    Views:
    1,039
    Klaus Johannes Rusch
    Jul 16, 2003
  2. Lukas
    Replies:
    3
    Views:
    820
    spiff
    Nov 10, 2005
  3. Marcia Hon

    Check if a directory is empty and empty it

    Marcia Hon, Feb 8, 2004, in forum: C Programming
    Replies:
    8
    Views:
    582
    Dave Thompson
    Feb 14, 2004
  4. Brian Roberts

    empty lists vs empty generators

    Brian Roberts, May 3, 2005, in forum: Python
    Replies:
    12
    Views:
    665
    Jeremy Bowers
    May 4, 2005
  5. someone
    Replies:
    8
    Views:
    325
    someon
    Mar 23, 2009
Loading...

Share This Page