Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problems with my loop
#1
I am using pandas to read data from SQL with some specific chunksize and loading it into postgres.
I am reading a files that contain the script to insert data into table.
I am having problem with my loop because it's not going to the next line. it comes back and gets the same data and with that I get the error:

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "vendor_pkey"
this is my code bellow (not whole code)

Could anyone explain me what is wrong here?


    try:
        connection = psycopg2.connect(
                database="my_database",
                user="user",
                password="my_password",
                host="my_host",
                port="port")

        try:
            with open('{0}{1}.sql'.format('path_sql', 'tables', 'r', errors='ignore') as sql:
                files = sql.read()
        except FileNotFoundError:
            raise
        except Exception as error:
        	raise error

        ind = 0
        for df in pd.read_sql(query, con, chunksize=10000):

            # create a cursor
            cur = connection.cursor()

            for i in df:

            	# execute a statement
            	i = eval(f'f"""{files}"""')
            	cur.execute(files)

            ind = ind + 1

        cur.close()

    except (Exception, psycopg2.DatabaseError) as error:
    	  connection.rollback()
    	  raise error

    finally:
    	  if connection is not None:
            connection.commit()
            connection.close()

    return "End loads"
I do appreciate for any help
Reply
#2
When posting errors, include the entire error trace. Not that it would help much here with all the exception handling. Strip all of that out until you get things working. The error trace will provide information useful for understanding what went wrong.

I would be interested in seeing the code that generated you error. The code you post has a lot of errors that prevent it from reaching the for loop. Really, the code doesn't make any sense. What is query and con for this call?
for df in pd.read_sql(query, con, chunksize=10000):
Are they defined elsewhere? As is they are two name errors.

What is going on here?
with open('{0}{1}.sql'.format('path_sql', 'tables', 'r', errors='ignore') as sql:
This converts to:
with open('path_sqltables.sql) as sql:
Why are you using the format when you already know the name? What did you expect to happen with "r" or "errors='ignore'"? Were those supposed to be arguments in the open call? Sorry, they were in the format call.

Is the posted code part of a function? Why is there a return?
return "End loads"
What is the purpose of "ind"? The value is never used for anything.

Does the error happen here?
i = eval(f'f"""{files}"""')
or here?
cur.execute(files)
If so, it might be because nothing changes "files" between iterations of the for loop.

And the for loop is odd. Why are you doing this:
        for i in df:
<snip>
            i = eval(f'f"""{files}"""')
Are you expecting i to somehow change the eval() statement? i = eval() changes the value referenced by i, but it does not change the value previously referenced by i. If you are expecting this "i = eval(f'f"""{files}"""')" to change something in df, that isn't going to happen.
While your at it, fix the indentation. The standard is 4 spaces per level. Yours is sometimes that and sometimes double that. Be consistent.
Reply
#3
The error happen this line:
cur.execute(files)
Here I read the files that have the script to insert into table that are stored in a .sql files:
        for i in df:
<snip>
            i = eval(f'f"""{files}"""')
the commands i run is of "insert into table (folder1, folder2) values ( value1, value2) " so it executes a block (chuncksize), then inserts a row, then it has to go to the next row to insert the next record, but this is not happening. The commands is not taken the next line. It's like a loop in the same line, so for it Iam having duplicate key value violates unique constraint

Any idea how to fix it?
Reply
#4
Why do you think df affects this?
eval(f'f"""{files}"""')
df does not change "files", and df is not mentioned in the eval command. How is reading a new block supposed to change anything?

What is the value of "files"?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  sports Stats > table output loop problems paulfearn100 3 2,448 Jul-22-2020, 03:21 AM
Last Post: c_rutherford
  Loop problems Kristenl2784 0 1,318 Jul-09-2020, 03:57 PM
Last Post: Kristenl2784
  “While” Loop Problems Ryan_teresi 3 2,823 Jun-27-2020, 06:43 PM
Last Post: tanv321
  Loop problems yuvalsaias 1 2,551 Feb-04-2017, 04:02 PM
Last Post: ichabod801

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020