I am not a developer or PostgreSQL DB admin, so this may be basic questions.
Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13
I'm using a python script to ingest external data, create a CSV file and copy that into Postgres 10. I keep getting the following error: Psycopg2.ProgrammingError: syntax error at or near "VALUES"
I have a two part question - 1) I can not see the syntax error in the following sql statement
def insert_csv_data(sqlstmt): with get_conn('pg') as db: cur = db.cursor() sqlcopy = "COPY irwin (fire_id,name,type,acres,date_time,state,county,admin_unit,land_cat,commander,perc_cntnd,cont_date,gacc,lat,long,geom,updated,imo) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,ST_SetSRID(ST_MakePoint(%s, %s),4326)%s,%s) FROM STIN DELIMITER ',' CSV HEADER" with open(csv_file, 'r') as f: #next(f)# Skipping the first line header row cur.copy_expert(sqlcopy, f, size=8000) db.commit() cur.close()
And 2) Once that is resolved I'm expecting to get an error about the geometry column in postgres. If someone would also peek at the code snippets and let me know if anything jumps out I would SO APPRECIATE IT!
This snippet pulls the external data in order but I don't think I've coded this correctly to pull the lat/long into the geom field.
# Lat 15 - double if not attributes['InitialLatitude'] is None: lat = str(attributes['InitialLatitude']).replace('\n', '') else: lat = '0' #Long 16 - double if not attributes['InitialLongitude'] is None: long = str(attributes['InitialLongitude']).replace('\n', '') else: long = '0' # geom is not defined - script is dumping the geometry into the IMO field geom = str(attributes['InitialLatitude']) + ' ' + str(attributes['InitialLongitude'])
I added a Geom header to the csv data. Please help - thanks!