File to import csv-file into postgres

readuluru.py

#!/usr/bin/python3

# This script can read a csv-file exported from radassist and insert data into a postgis table to be used by the qgisSpectre plugin
# 
# schema.sql creates a appropriate table in a postgis enabled postgresql database
#
# Copyright Morten Sickel, 2019

import sys
import psycopg2

filename=sys.argv[1]
mission=sys.argv[2]
dsn = "dbname=ositest password=my_secret_password" # Must be set to be able to connect to database
conn = psycopg2.connect(dsn)
cur=conn.cursor()
insert= "insert into measure (latitude,longitude,altitude,acqtime,flightdosevd1,flightdosevd2,specvd1,specvd2,laseralt,radalt,pressure,temperature,linenumber,filename,mission) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

def lst2pgarr(alist):
    return '{' + ','.join(alist) + '}'

f = open(filename, "r",encoding='latin-1')
header=True
idxs=[]
for idx,line in enumerate(f):
  line=line.rstrip("\n")
  data=(line.split(';')) 
  if(header):
    idxs.append(data.index("spc_ch001"))
    idxs.append(data.index("spc_ch512"))
    print(idxs)
    for k,v in enumerate(data[0:idxs[0]]):
        print("{} => {}".format(k,v))
    header=False
  else:
    data=[x.replace(',','.') for x in data]  
    vd1=lst2pgarr(data[idxs[0]:idxs[1]])
    
    insdata=[data[18],data[19],data[10],data[5],data[42],data[43],vd1,None,data[41],None,data[46],data[47],data[1],filename,mission]
    insdata=[x if x !='' else None for x in insdata]
    
    print(insdata)
    cur.execute(insert,insdata)

cur.execute("update measure set geom=ST_SetSRID(ST_MakePoint(Longitude, Latitude),4326) where geom is null")

conn.commit()
conn.close()