formating query with empty parameter

[someone]

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]

someone wrote:

You need to pass

None

then as that parameter.

Diez

[Pet]

Hi,

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

>

[Pet]

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

Thanks again!

[Peter Otten]

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]

>>>> 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]

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

[Pet]

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

[Tim Chase]

>> 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]

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]

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

[Aahz]

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]

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]

In article <mailman.892.1243603...@python.org>,

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