How to copy a csv file to postgresql using the copy command?

Question

I wrote the following script for uploading a csv file to postgresql databse.

import psycopg2
import keys
con = psycopg2.connect(
            host = keys.keys['host'],
            database = keys.keys['database'],
            user = keys.keys['user'],
            password = keys.keys['password'])

#cursor 
cur = con.cursor()

#execute query
#Already created ___#cur.execute("CREATE TABLE accounts (user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP)")

cur.execute("""\COPY "MyData" FROM 'C:\FILES\TestData.csv' DELIMITER ',' CSV HEADER;""")

#commit the transcation 
con.commit()

#close the cursor
cur.close()

#close the connection
con.close()

But it returned the following error:-

SyntaxError: syntax error at or near "\"
LINE 1:  \COPY "MyData" FROM 'C:\FILES\TestData.csv' DELIMITER ',' C...

I'm not a root user, so I could not directly use the COPY command.

Answer 1

Well.

You can use psycopg2's copy_from -> https://www.psycopg.org/docs/cursor.html#cursor.copy_from

So your code would look something like:

import psycopg2
import keys
con = psycopg2.connect(
            host = keys.keys['host'],
            database = keys.keys['database'],
            user = keys.keys['user'],
            password = keys.keys['password'])

#cursor 
cur = con.cursor()

#execute query
#Already created ___#cur.execute("CREATE TABLE accounts (user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP)")


with open('C:\\Files\\TestData.csv', 'r') as acc:
    next(acc) # This will skip the header
    cur.copy_from(acc, 'accounts', sep=',')

#commit the transcation 
con.commit()

#close the cursor
cur.close()

#close the connection
con.close()

Hope this answers your question.