Re: formating query with empty parameter
In article mailman.717.1243258005.8015.python-l...@python.org,
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)
How do you handle correct SQL escaping?
--
Aahz (a...@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
--
http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
In article mailman.892.1243603377.8015.python-l...@python.org,
Tim Chase python.l...@tim.thechases.com wrote:
Aahz wrote:
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)
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 (a...@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
--
http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
On May 25, 2:15 pm, Diez B. Roggisch de...@nospam.web.de 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
On May 25, 2:25 pm, Pet petshm...@googlemail.com wrote: On May 25, 2:15 pm, Diez B. Roggisch de...@nospam.web.de 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! -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
On May 25, 2:50 pm, Peter Otten __pete...@web.de 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
On May 25, 3:26 pm, Tim Chase python.l...@tim.thechases.com 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: formating query with empty parameter
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
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
--
http://mail.python.org/mailman/listinfo/python-list
