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

Improve this question
12
  • I'm not sure the sinqle quotes in your copy_cmd are correct. Commented Jul 14, 2022 at 19:14
  • typo mistake corrected in stackoverflow Commented Jul 14, 2022 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). Commented Jul 14, 2022 at 19:24
  • 1
    There are some examples here of using sqlalchemy though: using-sqlalchemy-to-load-csv-file-into-a-database Commented Jul 14, 2022 at 19:26
  • 2
    See psycopg2 copy_from. Commented Jul 14, 2022 at 19:36
Related questions
0
psycopg: can't copy from csv to postgresql with python, no results
4
Error when importing CSV to postgres with python and psycopg2
1
Copy command postgres append to csv file
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.