python equivalent of php implode

[Maksim Kasimov]

in php-scripts, to insert data to database, i'm doing like this:

...
$query_param = array(
'field0' => 1,
'field1' => 3,
'field2' => $var2,
'field3' => $var3,
);
...

$sql = "INSERT INTO $table (".implode(", ", array_keys($query_param)).") VALUES ('".implode("','", $query_param)."')";

how it can be done, using python (elegantly, without cycles)?

 

[Jeff Epler]

It looks like php's implode(sep, seq) is like sep.join(seq) in Python.

But this is a lousy way to write database queries. You should use the
Python's DB-API interface's execute(statement, parameters) instead.
Assuming that paramstyle is 'qmark', I think it ends up looking
something like this:

items = query_param.items()
keys = [item[0] for item in items]
values = [item[1] for item in items]

# If the query parameters or the table are under
# user control you must take care to validate them
assert table in permitted_tables
for k in query_param.keys():
assert k in permitted_keys

sql = "INSERT INTO %s (%s) values %s" % (
table, ", ".join(keys),
", ".join(["?"] * len(keys))
)
conn.execute(sql, values)

now you don't have to worry that you get the quoting of the values
absolutely right, since db-api does it for you.

Jeff

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCbmUbJd01MZaTXX0RApXIAKCEBuvx7QyvqmbNZWaSa6mBgPT96QCgmLOn
Xfskg0r60jNYB4qen2NA89Y=
=hWkf
-----END PGP SIGNATURE-----

 

[Mike Meyer]

items = query_param.items()
keys = [item[0] for item in items]
values = [item[1] for item in items]

Is there some reason not to do:

keys = query_params.keys()
values = query_params.values()

That would seem to be a lot more obvious as to what was going on.

Thanks,
<mike

 

[Ola Natvig]

in php-scripts, to insert data to database, i'm doing like this:

...
$query_param = array(
'field0' => 1,
'field1' => 3,
'field2' => $var2,
'field3' => $var3,
);
...

$sql = "INSERT INTO $table (".implode(", ",
array_keys($query_param)).") VALUES ('".implode("','", $query_param)."')";

how it can be done, using python (elegantly, without cycles)?

sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, ','.params.keys()),
','.join(param.values()))

should do the trick.

 

[Maxim Kasimov]

i'm tying to run example, and then get a traceback. am i something missed?

mysql> create table tmp_tmp (id int not null auto_increment primary key, sd
varchar(255) not null default '', si int not null default 1);
.... 'sd' : 'somedata',
.... 'si' : 2,
.... }

table = 'tmp_tmp'
keys = query_param.keys()
values = query_param.values()
sql = "INSERT INTO %s (%s) values (%s)" % (table, ", ".join(keys), ", ".join(["?"] * len(keys)) )
c.execute(sql, values)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/local/lib/python2.2/site-packages/MySQLdb/cursors.py", line 95,
in execute
return self._execute(query, args)
File "/usr/local/lib/python2.2/site-packages/MySQLdb/cursors.py", line
108, in _execute
self.errorhandler(self, ProgrammingError, m.args[0])
File "/usr/local/lib/python2.2/site-packages/MySQLdb/connections.py", line
33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: not all arguments converted

items = query_param.items()
keys = [item[0] for item in items]
values = [item[1] for item in items]

Is there some reason not to do:

keys = query_params.keys()
values = query_params.values()

That would seem to be a lot more obvious as to what was going on.

Thanks,
<mike

information.

 

[Maksim Kasimov]

.... but not in the case when integers are in a dictionary (please, try to
execute your example by yourself first)

 

[Peter Otten]

i'm tying to run example, and then get a traceback. am i something missed?

mysql> create table tmp_tmp (id int not null auto_increment primary key,
sd varchar(255) not null default '', si int not null default 1);
... 'sd' : 'somedata',
... 'si' : 2,
... }

table = 'tmp_tmp'
keys = query_param.keys()
values = query_param.values()
sql = "INSERT INTO %s (%s) values (%s)" % (table, ", ".join(keys), ", ".join(["?"] * len(keys)) )
c.execute(sql, values)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/local/lib/python2.2/site-packages/MySQLdb/cursors.py", line
95,
in execute
return self._execute(query, args)
File "/usr/local/lib/python2.2/site-packages/MySQLdb/cursors.py", line
108, in _execute
self.errorhandler(self, ProgrammingError, m.args[0])
File "/usr/local/lib/python2.2/site-packages/MySQLdb/connections.py",
line
33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: not all arguments converted

Try another paramstyle (see http://python.org/peps/pep-0249.html), e. g.

.... ",".join(["%s"] * len(keys)) ...

instead of

.... ",".join(["?"] * len(keys)) ...

Peter

 

[Maksim Kasimov]

done. thanks

Try another paramstyle (see http://python.org/peps/pep-0249.html), e. g.

.... ",".join(["%s"] * len(keys)) ...

instead of

.... ",".join(["?"] * len(keys)) ...

Peter

 

[Dennis Lee Bieber]

sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, ','.params.keys()),
','.join(param.values()))

That also violates the DB-API recommendations that the
..execute() method should be used to do parameter substitution -- to
ensure proper quoting of odd data...

--

 

[Dennis Lee Bieber]

".join(["?"] * len(keys)) )

And what do the multiple ? represent?

As I read the documentation,
http://sourceforge.net/docman/display_doc.php?docid=26238&group_id=22307,
MySQLdb uses "format", not "qmark" placeholders. That means you should
probably be using "%s" instead of "?" in that second join()

_mysql_exceptions.ProgrammingError: not all arguments converted

Well, if MySQLdb wants %s for argument processing, and you
supplied ?, then none of your supplied values are being converted...
Proper error message for the condition.

I'd normally test my statements before responding -- but I
haven't gotten everything installed on my new computer yet (I think I've
got four databases, but none running... MySQL, MaxDB, Firebird, ignore
JET/Access, SQL Server [SQLExpress]).

--

 

[Jeff Epler]

items = query_param.items()
keys = [item[0] for item in items]
values = [item[1] for item in items]

Is there some reason not to do:

keys = query_params.keys()
values = query_params.values()

That would seem to be a lot more obvious as to what was going on.

I was afraid that .keys() and .values() might not "match up" (so that
the i'th key maps to the i'th value in query_param). Now that I've
glanced at the documentation, I see that this *is* guaranteed[1], and
I should have written the code you proposed.

Jeff
[1] http://docs.python.org/lib/typesmapping.html note 3

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD4DBQFCb5TuJd01MZaTXX0RAntjAJ9P/mkn0I89OAGuZg+/p0eD2I1yggCYyXyf
WPsG6NFMvqaM5ZbeViyI3g==
=PfBJ
-----END PGP SIGNATURE-----

 

[Dave Cook]

That also violates the DB-API recommendations that the
.execute() method should be used to do parameter substitution -- to
ensure proper quoting of odd data...

I would think this would be OK:

keys = params.keys()
columnList = ", ".join(keys)
valueList = ["%%(%s)s" % key for keys]
sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, columnList, valueList)
cursor.execute(sql, params)

Though you would probably want to go further and filter out keys that don't
belong in the table, something like:

keys = [key for key in tableColumns if key in params]

Dave Cook