6

I have a python module which copy data from a table to a file.Im using postgresql as database server. COPY is the command is to be used to do the above action.

However in a blog (http://grokbase.com/t/postgresql/pgsql-general/058tagtped/about-error-must-be-superuser-to-copy-to-or-from-a-file) it states that, You can use \copy in 'psql' on the client side, but you have to be a superuser to do COPY on the server side, for security reasons. So I used \copy command. When I try to execute the below method, it results in error as

psycopg2.ProgrammingError: syntax error at or near "\" LINE 1: \copy

I can't find why its throwing error. can someone help me out?

def process():
     query="\copy %s TO %s"%('test_table', 'test_file.txt')

     @env.with_transaction()
     def do_execute(db):
         cursor = db.cursor()
         cursor.execute(query)

do_execute is a database wrapper, which creates connection and executes the query.

Darknight
  • 1,132
  • 2
  • 13
  • 31
  • 3
    `\copy` is a command only recognized by the `psql` command line tool. It is *not* valid SQL. The command line tool most likely implements the command using several SQL constructs to load data then export it to a text file. – Martijn Pieters Aug 08 '13 at 10:07
  • And for that valid SQL - you can find it at http://www.postgresql.org/docs/8.2/static/sql-copy.html – Jon Clements Aug 08 '13 at 10:08
  • The same page states: *If you are not using 'psql' and your client library supports it, you can use 'COPY FROM stdin' and pass the data across the client connection.* – Martijn Pieters Aug 08 '13 at 10:08
  • possible duplicate of [Can not "COPY FROM" with Postgres & Python](http://stackoverflow.com/questions/13868492/can-not-copy-from-with-postgres-python) – Martijn Pieters Aug 08 '13 at 10:11
  • @MartijnPieters If I replace the line query="\copy %s TO %s"%('test_table', 'test_file.txt') with query='pg_dump table_name > %s'%(txt_file), again I'm facing the same error. – Darknight Aug 08 '13 at 11:39
  • Where do you see `pg_dump` being used in the dupe I linked you to? That's another command-line tool, **not** a SQL statement. – Martijn Pieters Aug 08 '13 at 11:41
  • You can use one of the copy methods (`.copy_to` in your case): http://initd.org/psycopg/docs/cursor.html#cursor.copy_from – freakish Aug 08 '13 at 11:52
  • @freakish I have tried that method also. But still facing the same error. – Darknight Aug 08 '13 at 11:58
  • @PSivachandran How exactly are you using it? – freakish Aug 08 '13 at 12:00
  • @MartijnPieters I have tried copy_to, pg_dump, COPY methods to copy table data to a file. But Ended up with same error. can you please tel me a method to do the above action. I have tried googling, but results ended up with error. – Darknight Aug 08 '13 at 12:01
  • @freakish f = open('filename', 'w') query="copy_to(f, 'tablename')". Then the query value will be passed as an argument to the method mentioned above – Darknight Aug 08 '13 at 12:06
  • @PSivachandran No, use cursor in Python: `cursor.copy_to(f, 'tablename')` (the docs are for the driver) – freakish Aug 08 '13 at 12:08

1 Answers1

6

\ is an escape in Python strings, so your string contains the escape \c. However \c is an invalid escape in Python, and Python leaves invalid escapes unchanged, so "\copy" is just \copy. (Thus @tiziano's answer is misleading).

>>> print "\c"
\c

The real problem is that \copy is a psql command, not a server side PostgreSQL command. You can't use it with a client other than psql. You must instead use the psycopg2 support for COPY to do it via your client driver.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778