CODEWITHSUNDEEP
pythonpython-3.xpostgresqlcsv
Oscar Gonzalez
Oscar Gonzalez

Reputation: 148

Why only insert 1000 elements?

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?

Upvotes: 0

Views: 109

Answers (1)

Rockybilly
Rockybilly

Reputation: 4520

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.

Upvotes: 1

Related Questions