0

I'm running script in Jupyter notebook to upload csv file into Postgresql table. Jupyter notebook is on server1 and postgresql server is on another server server2.

psql client also installed on Jupyter machine(server1). Able to upload file using below psql command from server1

hari@idrtnjdhew1:~/data$ psql -h server2.zzz.com -p 5432 -d db_name -U hari1 -c '\COPY test_upload FROM 'file_name.csv' CSV HEADER'

But I have to write scripts in Jupyter notebooks to upload file into postgresql server table.

import psycopg2 as pg
import os
import sys

conn = psycopg2.connect(
    host="server2",
    database="db_name",
    user="hari1",
    password="xyz")
        
cursor = conn.cursor()

delete = """Truncate table test_upload"""

cursor.execute(delete)

copy_cmd = """
psql -c "\COPY (test_upload) FROM STDIN  CSV HEADER" < '\home\data\file_name.csv'
"""
cursor.execute(copy_cmd)

But above scripts is giving error SyntaxError: syntax error at or near "psql" LINE : psql -c "\COPY (test_upload) FROM STDIN...

Is there way to execute psql command using psycopg2

Hari
  • 299
  • 4
  • 12
  • I'm not sure the sinqle quotes in your copy_cmd are correct. – topsail Jul 14 '22 at 19:14
  • typo mistake corrected in stackoverflow – Hari Jul 14 '22 at 19:18
  • 1
    In general I think a cursor.execute() should take a sql command, not a psql command. To execute psql commands you probably just want to run your psql command as a shell command (with some kind of method in python to invoke a shell command). – topsail Jul 14 '22 at 19:24
  • 1
    There are some examples here of using sqlalchemy though: [using-sqlalchemy-to-load-csv-file-into-a-database](https://stackoverflow.com/questions/31394998/using-sqlalchemy-to-load-csv-file-into-a-database) – topsail Jul 14 '22 at 19:26
  • If all you have is a pandas hammer and this looks like a pandas nail then you can also use a pandas hammer: [how-can-i-load-a-csv-into-a-database-with-sqlalchemy](https://stackoverflow.com/questions/56165255/how-can-i-load-a-csv-into-a-database-with-sqlalchemy) - see answer from Martin Thoma – topsail Jul 14 '22 at 19:30
  • That's a shell command, not an SQL command. You can use `os.system` or `subprocess.run` to run that script. Personally, I think it's just about as easy to read the CSV file in Python and do `INSERT` commands. – Tim Roberts Jul 14 '22 at 19:33
  • 2
    See `psycopg2` [copy_from](https://www.psycopg.org/docs/cursor.html#cursor.copy_from). – Adrian Klaver Jul 14 '22 at 19:36
  • thanks @topsail for sharing post `using sqlalchemy`. since mine is bulk file, i took 2nd solution from the post. below is working for me `with open(csv_file_path, 'r') as f: cmd = 'COPY test_upload(col1, col2, col3) FROM STDIN WITH (FORMAT CSV, HEADER FALSE)' cursor.copy_expert(cmd, f) conn.commit()` – Hari Jul 14 '22 at 19:45
  • @AdrianKlaver earlier I tried copy_from() method, but it didn't for work as my file header. couldn't find argument for header=true – Hari Jul 14 '22 at 20:01
  • Then you need [copy_expert](https://www.psycopg.org/docs/cursor.html#cursor.copy_expert) or get rid of the header line in the file. – Adrian Klaver Jul 14 '22 at 20:23
  • @AdrianKlaver can't rid of header. Anyway, As mentioned above copy_expert resolved my issue. Thanks – Hari Jul 14 '22 at 20:38
  • Actually it easy enough `csv_file = open('\home\data\file_name.csv')` then `csv_file.readline()` then `copy_from(csv_file)` – Adrian Klaver Jul 14 '22 at 20:48

0 Answers0