CODEWITHSUNDEEP
postgresqlpsycopg2
smartestblonde
smartestblonde

Reputation: 51

\COPY TO Postgres statement working in bash but not with python psycopg2

I am trying to use psycopg2 in my script to export data from a Postgres database to a file.

I can successfully run the following from my terminal and it works, no problem: 


psql -h myhost -p myport -U myuser -d mydbname -c "\COPY ( SELECT member_id FROM member_reward_transaction LIMIT 5) TO ~/Desktop/testexport.txt (FORMAT csv, DELIMITER '|', HEADER 0)"

I could presumably call the above using subprocess, but I would like to know why the following is not working for me: 

import configparser

config = configparser.ConfigParser()

config.read('config/qa_config.ini')

dbname=config['postgres-rewards']['db_name']
host=config['postgres-rewards']['host']
port=config['postgres-rewards']['port']
user=config['postgres-rewards']['user']
password=config['postgres-rewards']['password']

con = psycopg2.connect(database=dbname,user=user,password=password,host=host,port=port)
cur = con.cursor()
f = open('exports/test_export.csv')
cur.copy_to(f, 'member_reward_transaction', columns=('member_id', 'sponsor_id'), sep=",")
con.commit()
con.close()

The error when I run the script: 

  File "tests2.py", line 17, in <module>
    cur.copy_to(f, 'member_reward_transaction', columns=('member_id', 'sponsor_id'), sep=",")
psycopg2.errors.WrongObjectType: cannot copy from partitioned table "member_reward_transaction"
HINT:  Try the COPY (SELECT ...) TO variant.

using Python 3.6.5, PostgreSQL 11.5

Upvotes: 0

Views: 1630

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246433

Like the error message says, you have to use

COPY (SELECT ... FROM partitioned_table) TO STDOUT;

if you want to use a partitioned table.

Your psql command does that, but psycopg2's copy_to uses plain old

COPY partitioned_table TO STDOUT;

which doesn't work.

Use copy_expert which allows you to submit your own COPY statement.

Upvotes: 1

Related Questions