Thread: how to pass in bind variables with cursor.copy_expert?
I need to extract some rows from a table as a CSV file, based on a query sort of like: copy ( select * from blah where x = %(x)s ) but I don't see how to pass in a dictionary with values for the query! Is this not allowed? What am I missing? Thanks in advance. Matt -- W. Matthew Wilson matt@tplus1.com http://tplus1.com
On 21/02/2014 17:27, W. Matthew Wilson wrote: > I need to extract some rows from a table as a CSV file, based on a > query sort of like: > > copy ( > select * > from blah > where x = %(x)s > ) > > but I don't see how to pass in a dictionary with values for the query! You can use cursor.mogrify() to build the query and then pass it to cursor.copy_export(). See the documentation for the details. federico -- Federico Di Gregorio federico.digregorio@dndg.it Di Nunzio & Di Gregorio srl http://dndg.it Everything will be OK at the end. If it's not OK, it's not the end. -- Unknown
On 02/21/2014 08:47 AM, Federico Di Gregorio wrote: > On 21/02/2014 17:27, W. Matthew Wilson wrote: >> I need to extract some rows from a table as a CSV file, based on a >> query sort of like: >> >> copy ( >> select * >> from blah >> where x = %(x)s >> ) >> >> but I don't see how to pass in a dictionary with values for the query! Would something like this work, or am I missing something?: sql = """COPY (SELECT * FROM cell_per WHERE cell_per = %(cp)s) TO '/home/postgres/cp.csv'""" cur.execute(sql,{"cp": 18}) > > You can use cursor.mogrify() to build the query and then pass it to > cursor.copy_export(). See the documentation for the details. > > federico >
On 02/21/2014 08:27 AM, W. Matthew Wilson wrote: > I need to extract some rows from a table as a CSV file, based on a > query sort of like: > > copy ( > select * > from blah > where x = %(x)s > ) > > but I don't see how to pass in a dictionary with values for the query! > > Is this not allowed? > > What am I missing? Now I see what I was missing. I did not read the subject fully and see you wanted to use copy_expert. In that situation, I do as Federico suggested and build the full query first then pass it to copy_expert. > > Thanks in advance. > > Matt >