0

I'm using a Jupyter notebook for working with some database with postgresql I have the following instances: 

import pandas as pd
import (other packages)
conn_string= % I can't show this, but this is ok
conn = psycopg2.connect(conn_string)
cursor=conn.cursor
query= """ copy (select col1,col2 from Table where col3=a_parameter
           and col4=b_parameter) to '/tmp/test.csv' with csv """
pd.read_sql(query,conn)

But I got this error:

**ProgrammingError: syntax error at or near "("
LINE 1: COPY (select col1,col2 from Table where col3...**
             ^

Why the copy sentence has an error? I am using Postresql 8.0.2

L F
  • 548
  • 1
  • 7
  • 22
  • 1
    Your ancient version [did not support](https://www.postgresql.org/docs/8.0/static/sql-copy.html) a select statement in the `COPY` command. _If_ you are using an outdated version, you should also read the manual for that version, not for newer versions. You should upgrade to a supported and maintained version **now**. –  Jun 07 '18 at 21:23
  • 1
    Please upgrade your Postgres. We have 10 now and it's been many years since version 8. – Kamil Gosciminski Jun 07 '18 at 21:24
  • I'm working with Jupyter Notebook + Python 3.4+ psycopg2 (which allows me to do postgresql querys). How can I upgrade? Is there any option to export data to csv? – L F Jun 07 '18 at 21:26
  • https://www.postgresql.org/docs/10/static/upgrading.html – Nick Barnes Jun 07 '18 at 23:02

2 Answers2

1

Something like this:

import csv
            my_file_csv =  my_folder + "\Report_Trip_Day_" + my_opr + "_" + my_local_database + ".csv"


            out = csv.writer(open(my_file_csv, "w", newline=''), delimiter=',', quoting=csv.QUOTE_ALL)
            out.writerow(colnames)
            for row in my_xls_report_table:
                out.writerow(row)
Slumdog
  • 470
  • 2
  • 4
0

You can make this:

query= """ copy (select col1,col2 from Table where col3=a_parameter
       and col4=b_parameter) """

df=pd.read_sql(query,con=conn)
df.to_csv("name.csv",sep=",")
L F
  • 548
  • 1
  • 7
  • 22