0

I am trying to use the function of copy_from in psycopy2 to insert data to my table named "ST_DAY_SRS",but how can I specified the schema named "NEW_CNYB"? I used the parmeter of option but it doesnot work ,please help me,thanks very much.

def action():
    conn = psycopg2.connect(host='****',port='45432',
                user='postgres',password='postgres',database='NEW_CNYB',options='-c search_path="NEW_CNYB"')

    df = pd.DataFrame(l, columns=['ID', 'DATE', 'SUNRISE', 'SUNSET'])
    ret=''
    values_list=[]

    for i in df.itertuples():
        values_list.append('\t'.join([i[1],i[2],i[3],i[4]]))
    for value in values_list:
        ret += value + '\n'
    print(ret)
    cur = conn.cursor()
    cur.copy_from(file=io.StringIO(ret),table="ST_DAY_SRS",
                 columns=("ID", "DATE", "SUNRISE", "SUNSET"))
    conn.commit()
houye
  • 1

1 Answers1

0

The way i would do it, is to insert the schema_name before the table name like this 

   # schema_name.table_name 
    cur.copy_from(file=io.StringIO(ret),table="ST_DAY_SRS",
                     columns=("ID", "DATE", "SUNRISE", "SUNSET"))

Another possibilities if that did not work out :

  1. Set the schema for all the queries. Check the replays on this link
  2. Create a role/user that belong to the schema instead with insert.

Good luck.

Solie
  • 1
  • 3