1

I am trying (and failing) to copy data from a .csv file into a postgres table using psycopg3. It was working perfectly using psycopg2 using the following code:

tabname= 'rd2'
fname2='new_data.csv'
new_data.to_csv(fname2, index=None)

with open(fname2,'r') as y:
    next(y)
    cur.copy_from(y,tabname, sep=',')
    conn.commit()

I have read the documentation for psycopg3: Psycopg3 Copy and Psycopg3 COPY_TO and COPY_FROM and am completely stumped. the latest iteration (of many) that I've tried is:

with open(fname, 'r') as f:
     with cur.copy("copy rd2 from stdin") as copy: 
          data=next(f)                         
          copy.write_row(data)
          conn.commit()

which gives the following error:

        
Traceback (most recent call last):

  Cell In[44], line 2
    with cur.copy("copy rd2 from stdin") as copy:

  File ~\Anaconda3\envs\spyder-env\Lib\contextlib.py:137 in __enter__
    return next(self.gen)

  File ~\Anaconda3\envs\spyder-env\Lib\site-packages\psycopg\cursor.py:903 in copy
    raise ex.with_traceback(None)

InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

I'm using Spyder 5 IDE and python 3.11

matt cooper
  • 101
  • 1
  • 8

1 Answers1

0

If your data is in a dataframe already, you can try:

with io.StringIO() as sourceStream:
    new_data.to_csv(sourceStream, sep="\t", header=False, index=False)
    sourceStream.seek(0)
    cur.copy_from(sourceStream, tabname)
    cur.commit()
JonSG
  • 10,542
  • 2
  • 25
  • 36