PostgreSQL: COPY FROM SQL to specific columns

Question

I have been trying to figure out how to format a COPY FROM SQL statement for most of the morning and I need help.

I am trying to import data from an ASCII text file to a table in my Postgres database. I am thinking it does not like how I am specifying the input ASCII file. I have tried both of the file paths with no luck:

file = os.path.normpath(os.path.join('c:\\','Users','dan','Desktop','New_Folder','Sept_2014','R01761','R01761_tex.asc'))
file = r'C:\Users\dan\Desktop\New_folder\Sept_2014\R01761\R01761_tex.asc'

Here is my script I am using to access my database:

import psycopg2

try:
    conn = psycopg2.connect("dbname='reach_4a' user='root' host='localhost' port='9000' password='myPassword'")
    tblname = "sept_2014""
    file = r"C:\Users\dan\Desktop\New_folder\Sept_2014\R01761\R01761_tex.asc"
    #file = os.path.normpath(os.path.join('c:\\','Users','dan','Desktop','New_Folder','Sept_2014','R01761','R01761_tex.asc'))
    cur = conn.cursor()
    sql = "COPY %s (easting,northing,texture) FROM %s DELIMITERS ' ';" % (tblname,file) 
    cur.execute(sql)
    conn.commit()
except:
    print "I am unable to connect to the database"

#Close Database
try:
    conn.close()
    print 'Database connection destroyed'
except:
    print "I cant close the database"

When I step through my script in a python console, I get the following error when i try and run the cur.execute(sql) line:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-34-c26e11f8fb81> in <module>()
----> 1 cur.execute(sql)

ProgrammingError: syntax error at or near "c"
LINE 1: COPY sept_2014 (easting,northing,texture) FROM c:\Users\dan\...
                                                       ^

Am I properly substituting my strings to my SQL statement?

alecxe · Accepted Answer · 2016-02-04 21:28:31Z

2

You should put the path into quotes:

"COPY %s (easting,northing,texture) FROM '%s' DELIMITERS ' ';"

I would also think about parameterizing the query with the file path:

sql = "COPY {field} (easting,northing,texture) FROM %s DELIMITERS ' ';".format(field=tblname)
cur.execute(sql, (file, ))

Note that in this case the database driver would be responsible for putting quotes around a string value. And, as a bonus, you are now preventing SQL injection attacks.

Note that we have to insert the field into the query via the string formatting. Make sure you know where tblname is coming from and you either trust the source, or validate/escape/sanitize it before using in the query.

answered Feb 4, 2016 at 21:28

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dubbbdan Over a year ago

When I try to implement the parameterizing the query with the file path method I get the following error:

psycopg2.OperationalError: could not open file "C:\Users\dan\Desktop\New_folder\Sept_2014\R01761\R01761_tex.asc" for reading: No such file or directory

. But os.path.isfile(file) returns true.

alecxe Over a year ago

@dubbbdan well, are you getting the same error if you just put the quotes around the placeholder (the first suggestion in the answer)? Thanks.

alecxe Over a year ago

@dubbbdan okay, at least it is not related to the answer and a follow-up problem. What if you check it with os.path.exists(file)?

dubbbdan Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

PostgreSQL: COPY FROM SQL to specific columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related