pydev to postgres issue with code

Question

I am trying to send data from python using pydev to postgresql. I am finding more than one error and could really need help. Many thanks! One of the error says:

psycopg2.InterfaceError: cursor already closed.

Any help would be appreciated!

Here is my code to create the table

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import csv
from itertools import count
path = r'C:\Users\sammy\Downloads\E0.csv'
with open(path, "r") as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    firstline = 1
    con = None
    con = psycopg2.connect("host='localhost' dbname='football' user='postgres' password='XXX'")   
    cur = con.cursor()
    con.commit()
    cur.execute("DROP TABLE testtest1234")
    cur.execute("CREATE TABLE testtest1234 (HY INTEGER)")
    for row in readCSV:
        if firstline:
            firstline=0
            continue 
        new_data = row[19]
        try:
            cur.execute("INSERT INTO testtest1234 values ("+new_data+")")
            cur.execute("SELECT * FROM testtest1234;")
        except psycopg2.DatabaseError as e:
            if con:
                con.rollback() 
            print ("Error %s % e", e)
            sys.exit(1) 
        finally:   
            if con:
                con.close()
            print (new_data)

print(" ".join(row))
out=open("new_data.csv", "w")
output = csv.writer(out)

for row in new_data:
    output.writerow(row)

out.close()

Here is my code to insert in the table

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import csv
from itertools import count
path = r'C:\Users\sammy\Downloads\E0.csv'
with open(path, "r") as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    con = None
    con = psycopg2.connect("host='localhost' dbname='football' user='postgres' password='XXX'")   
    cur = con.cursor()
    con.commit()
    for row in readCSV:
        new_data = row[19]
        print (new_data)


try:
    cur.execute("INSERT INTO testtest1234 values ("+new_data+")")

except psycopg2.DatabaseError as e:
    if con:
        con.rollback() 
    print ("Error %s % e", e)
    sys.exit(1) 
finally:   
    if con:
        con.close()

print(" ".join(row))
out=open("new_data.csv", "w")
output = csv.writer(out)

for row in new_data:
    output.writerow(row)

    out.close()

Answer 1

You are closing the connection inside the loop, so only the first one row is inserted.

for row in readCSV:
    try:
         ...
    except psycopg2.DatabaseError as e:
         ...
    finally:   
        if con:
            con.close()