Why only insert 1000 elements?

Question

I want to insert 1 million of rows in my database with this code, but only insert 1000 and I don't know why.

I have 2 csv files with 1000 rows like this:

Katherina,Rasmus,82-965-3140,29/09/1962,krasmus8thetimescouk

import psycopg2
import csv
print("\n")


csv_file1=open('/home/oscarg/Downloads/base de datos/archivo1.csv', "r")
csv_file2=open('/home/oscarg/Downloads/base de datos/archivo2.csv', "r")

try:
    connection = psycopg2.connect(user = "oscar",
                                  password = "",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "challenge6_7")
    cursor = connection.cursor()
    csv_reader1 = csv.reader(csv_file1, delimiter=',')
    for row in csv_reader1:
        csv_reader2 = csv.reader(csv_file2, delimiter=',')
        contador=+1
        for row2 in csv_reader2:
            nombre=row[0]+" "+row2[0]
            apellido=row[1]+" "+row2[1]
            cedula_id=row[2]+row2[2]
            if not(contador%1000):
                fecha_nacimiento="'"+row[3]+"'"
            else:
                fecha_nacimiento="'"+row2[3]+"'"
            if not (contador%3):
                email=row[4]+"@hotmail.com"
            else:
                email=row2[4]+"@gmail.com"
            postgres_insert_query = " INSERT INTO cliente (nombre, apellido, cedula_id,fecha_nacimiento, cliente_email) VALUES (%s,%s, %s, %s,%s)"
            record_to_insert = (nombre, apellido, cedula_id, fecha_nacimiento, email)
            cursor.execute(postgres_insert_query, record_to_insert)
            connection.commit()
            if (contador==1000):
                contador=0

except (Exception, psycopg2.Error) as error :
    print(error.pgerror)

finally:
    #closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

csv_file1.close()
csv_file2.close()

Insert 1000 rows and then stop, it's a problem with my code, psycopg or my database?

No only 1000 and then stop – Oscar Gonzalez Jul 16 '19 at 02:10 — Oscar Gonzalez, Jul 16 '19 at 02:10

Rockybilly · Accepted Answer · 2019-07-16T02:17:29.963

It is possible that the reader pointer expires (End of File) for the second iteration of the second csv file, so nothing is being read.

You might want to store the rows in a list first, then iterate over them.

See: Python import csv to list

Edit: This is the issue. I made a little test myself.

import csv

csv_file1=open("a.csv", "r")
csv_file2=open("1.csv", "r")

csv_reader1 = csv.reader(csv_file1, delimiter=',')
for row in csv_reader1:
    csv_file2=open("1.csv", "r") # Removing this line makes the code run N times
                                 # Instead of N x N (a million in your example.)
    csv_reader2 = csv.reader(csv_file2, delimiter=',')
    for row2 in csv_reader2:
        print(row, row2)

I tested it by opening the file (not the reader), in the first loop. However opening the file again and again does not seem like the best practice. You should store it in a list if you don't have memory limitations.

Why only insert 1000 elements?

1 Answers1