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)?
--
Best regards,
Maksim Kasimov
mailto: ka*****@i.com.ua

[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)

iD8DBQFCbmUbJd01MZaTXX0RApXIAKCEBuvx7QyvqmbNZWaSa6 mBgPT96QCgmLOn
Xfskg0r60jNYB4qen2NA89Y=
=hWkf
-----END PGP SIGNATURE-----

[Mike Meyer]

Jeff Epler <je****@unpythonic.net> writes:

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
--
Mike Meyer <mw*@mired.org> http://www.mired.org/home/mwm/
Independent WWW/Perforce/FreeBSD/Unix consultant, email for more information.

[Ola Natvig]

Maksim Kasimov wrote:

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.
--
--------------------------------------
Ola Natvig <ol********@infosense.no>
infoSense AS / development

[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);

import MySQLdb
db = MySQLdb.connect("localhost", "login", "password", "dbname")
c = db.cursor()
query_param = { .... '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


"Mike Meyer" <mw*@mired.org> ???????/???????? ? ???????? ?????????:
news:86************@guru.mired.org... Jeff Epler <je****@unpythonic.net> writes:

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
--
Mike Meyer <mw*@mired.org> http://www.mired.org/home/mwm/
Independent WWW/Perforce/FreeBSD/Unix consultant, email for more

information.

[Maksim Kasimov]

.... but not in the case when integers are in a dictionary (please, try to
execute your example by yourself first)
"Ola Natvig" <ol********@infosense.no> wrote:
news:kv************@pluto.i.infosense.no...

Maksim Kasimov wrote:

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.
--
--------------------------------------
Ola Natvig <ol********@infosense.no>
infoSense AS / development

[Peter Otten]

Maxim Kasimov wrote:

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);

import MySQLdb
db = MySQLdb.connect("localhost", "login", "password", "dbname")
c = db.cursor()
query_param = { ... '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

[Dennis Lee Bieber]

On Wed, 27 Apr 2005 08:44:36 +0200, Ola Natvig <ol********@infosense.no>
declaimed the following in comp.lang.python:

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

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

[Dennis Lee Bieber]

On Wed, 27 Apr 2005 09:57:29 +0300, "Maxim Kasimov" <ka*****@i.com.ua>
declaimed the following in comp.lang.python:

sql = "INSERT INTO %s (%s) values (%s)" % (table, ", ".join(keys), ", ".join(["?"] * len(keys)) )
And what do the multiple ? represent?

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

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%.

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

_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]).

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

[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

--
Best regards,
Maksim Kasimov
mailto: ka*****@i.com.ua

[Jeff Epler]

On Tue, Apr 26, 2005 at 09:59:29PM -0500, Mike Meyer wrote:

Jeff Epler <je****@unpythonic.net> writes:

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]

On 2005-04-27, Dennis Lee Bieber <wl*****@ix.netcom.com> wrote:

On Wed, 27 Apr 2005 08:44:36 +0200, Ola Natvig <ol********@infosense.no>
declaimed the following in comp.lang.python:

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

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