Re: passing list values to an executemany cursor

Am 19.08.2009, 12:00 Uhr, schrieb <[email protected]>:

Hi Peter,

it looks like you're hitting a common newbie problem when working with the  
Python DB-API: how to pass in variables. For a long time Python has made  
it very easy for variables to be passed in next to the SQL statement to be  
executed. The main reason for this is that it allows the database driver  
to handle the escaping and, thus, reduce the exposure to SQL injection.

> cur.execute("CREATE TABLE pmh ()")
> for i in frcs_names:
>     cur.execute("ALTER TABLE pmh ADD COLUMN "+i+" double precision")
> con.commit()
> insert_st='INSERT INTO pmh VALUES ('+frcs_o+')'
> cur.executemany(insert_st)
> con.commit()
> The result is as follows:
>  >>> {'pmh_chainsaw': 84.031250000000014, 'pmh_loaderb':
> 155.75707692307691, 'pmh_truck': 93.977430555555571, 'pmh_loaders':
> 126.94655384615383}
> Traceback (most recent call last):
>   File "/tmp/py4195FHt", line 38, in <module>
>     insert_st='INSERT INTO pmh VALUES ('+frcs_o+')'
> TypeError: cannot concatenate 'str' and 'list' objects
> It appears that the connection is working and the lists have been
> populated. I can check the db and see that the table has been created
> with the appropriate column names. Any hints on correctly passing the
> list falues from the 'frcs_o' list would be most gratefully recieved.


The pattern for this is

c.execute(statement, parameters) where the statement is always a string  
containing placeholders and the parameters always a tuple and always  
contains at least one comma)

Your code, however, only passes in statements as strings. As frcs_o (as an  
aside this variable name is not very helpful) is a list you are asking  
Python to add a string and a list together which isn't possible. While you  
could coerce the list into a tuple and make a string of this, this  
bypasses all the quoting and would be only a single statement.

Your database manipulation statement doesn't except any parameters so can  
be left as it is.

Your inserts could be written like this

insert_st = "INSERT INTO pmh (pmh_chainsaw) VALUES (%s)"

for value in frcs_o:
	cur.execute(insert_st, (value, ) )

I've rewritten statement to include an explicit relation variable.
Note the use of commas - "%s" is confusing here because it used for string  
substitution which is why support for other placeholders such as "?" is  
popular. The statement are the tuple of values are then both passed into  
the cursor's execute method

exceutemany() works exactly the same but delegates the iteration to the  
cursor object which expects an iterable of tuples and in this case the  
following should work:

c.executemany(insert_st, frcs_o)

... or at least give you an error message from the psycopg driver or the  
database.

Charlie
-- 
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg