formating query with empty parameter

S

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
 
D

Diez B. Roggisch

someone said:
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
 
P

Pet

someone said:
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
 
P

Pet

someone said:
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

Hi,

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




then as that parameter.

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

Thanks again!
 
P

Peter Otten

Pet said:
someone said:
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
 
T

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
 
P

Pet

Pet said:
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

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
 
P

Pet

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
 
T

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
 
P

Peter Otten

Pet said:
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
 
P

Pet

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
 
D

Dennis Lee Bieber

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).
.... print "fmt: %r\nargs: %r" % (fmt, args)
.... print "substituted: %r" % (fmt % args)
.... fmt: 'one place: %s'
args: ('Me', 'myself', 'I')
Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
fmt: 'one place: %s'
args: 'Me'
substituted: 'one place: Me'fmt: 'three places: %s, %s, %s'
args: ('Me', 'myself', 'I')
substituted: 'three places: Me, myself, I'fmt: 'three places: %s, %s, %s'
args: 'Me'
Traceback (most recent call last):
File "<interactive input>", line 1, in <module>

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
A

Aahz

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?
 
T

Tim Chase

Aahz said:
Tim Chase said:
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
 
A

Aahz

Aahz said:
Tim Chase said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top