formating query with empty parameter

1 view
Skip to first unread message

someone

unread,
May 25, 2009, 8:10:04 AM5/25/09
to
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

Diez B. Roggisch

unread,
May 25, 2009, 8:15:03 AM5/25/09
to
someone wrote:

You need to pass

None

then as that parameter.

Diez

Pet

unread,
May 25, 2009, 8:25:16 AM5/25/09
to

Hi,

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


>

Pet

unread,
May 25, 2009, 8:45:28 AM5/25/09
to

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

Thanks again!

Peter Otten

unread,
May 25, 2009, 8:50:32 AM5/25/09
to
Pet wrote:

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


Tim Chase

unread,
May 25, 2009, 9:26:29 AM5/25/09
to Pet, pytho...@python.org
>>>> 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


Pet

unread,
May 25, 2009, 10:53:06 AM5/25/09
to

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

Pet

unread,
May 25, 2009, 10:54:12 AM5/25/09
to

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

Tim Chase

unread,
May 25, 2009, 12:16:48 PM5/25/09
to Pet, pytho...@python.org
>> 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


Peter Otten

unread,
May 25, 2009, 12:45:26 PM5/25/09
to
Pet wrote:

> On May 25, 2:50 pm, Peter Otten <__pete...@web.de> 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

Pet

unread,
May 25, 2009, 1:17:44 PM5/25/09
to
On 25 Mai, 18:16, Tim Chase <python.l...@tim.thechases.com> 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

Message has been deleted

Aahz

unread,
May 29, 2009, 8:44:14 AM5/29/09
to
In article <mailman.717.1243258...@python.org>,

Tim Chase <pytho...@tim.thechases.com> 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 (aa...@pythoncraft.com) <*> http://www.pythoncraft.com/

my-python-code-runs-5x-faster-this-month-thanks-to-dumping-$2K-
on-a-new-machine-ly y'rs - tim

Tim Chase

unread,
May 29, 2009, 9:22:44 AM5/29/09
to Aahz, pytho...@python.org
Aahz wrote:
> Tim Chase <pytho...@tim.thechases.com> 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


Aahz

unread,
May 29, 2009, 11:23:00 AM5/29/09
to
In article <mailman.892.1243603...@python.org>,

Right, that's what I get for reading code early in the morning.

Reply all
Reply to author
Forward
0 new messages