Обсуждение: 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
>