CODEWITHSUNDEEP
pythonpostgresqletlpsycopg2
blabla
blabla

Reputation: 303

Print a postgresql table to standard output in python

I have a table in postgresql named mytable and I need to print the contents of this table from a python application to stdout.

I'm currently doing the following:

        conn = psycopg2.connect("dbname=postgres user=postgres password=psswd")
        cur = conn.cursor() 
        cur.copy_to(sys.stdout,'mytable',sep = '\t')

However, I get some "\N" when its printed in between some columns. I believe the reason why this happens is because somewhere during the print process, the line exceeds and goes to the next line in the psql terminal and so these \N s show up.

Output:

E0307   1       M       400     Ethan   UTDallas        12.98580404     \N      50.79403657     1
E0307   1       M       400     Lucas   Baylor  15.18511175     \N      56.87285183     3
E0307   1       M       400     Jackson Baylor  13.64228411     \N      56.87285183     3
E0307   1       M       400     Jacob   Baylor  13.19878974     \N      56.87285183     3
E0307   1       M       400     Samuel  Baylor  14.84666623     \N      56.87285183     3

My question is the following:

  1. How do I get rid of these \N in output? Is there an alternative way of printing a table? I'm trying to avoid ways in which I have to execute an entire "SELECT * FROM my_table" query. Something that just uses the name of the table to be printed.

  2. Also, how do I get the table headers while printing out? I tried the following:

    cur.execute("COPY mytable TO STDOUT with csv header")

I get this error message:

ProgrammingError: can't execute COPY TO: use the copy_to() method instead

Also, I'm not sure if this is the best way. But something I tried to do :)

Upvotes: 11

Views: 13889

Answers (3)

Aslan Varoqua
Aslan Varoqua

Reputation: 191

As stated earlier by Neto: cur.copy_expert("sql statement", sys.stdout) will work. To use copy_to you need to pass null param.

Try this if you choose the copy_to method (set null value - see docs). Print column names first.

header = [i[0] for i in cur.description
print header
cur.copy_to(sys.stdout, 'table', sep='\t', null='\N')

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

That \N is the default textual representation of a null value. It can be changed with the null parameter of copy_to

To have the headers in the output use copy_expert

copy = "copy mytable to stdout with csv header delimiter '\t' null 'NULL'"
cursor.copy_expert(copy, sys.stdout)

Upvotes: 3

Max Power
Max Power

Reputation: 8996

don't have a postgress table handy to test this but does this work for you?

import psycopg2 as pg
import pandas as pd
import pandas.io.sql as psql

connection = pg.connect("dbname=postgres user=postgres password=psswd")
#my_table   = pd.read_sql_table('table_name', connection)
my_table    = pd.read_sql('select * from my-table-name', connection)
another_attempt= psql.read_sql("SELECT * FROM my-table-name", connection)

print(my_table)

# OR
print(another_attempt)

Upvotes: 7

Related Questions