In pypyodbc, How to query a table for its distinct values, truncate old table and append the distinct values to the empty table

Question

I have a table that for whatever reason has some dupes after a process runs. Each row should be a distinct report. I'm trying to do this with a for look on the cursor object after doing the .fetchall method and it is working somewhat. But it only inserts one row.

import pypyodbc

conn_str = ('Driver={SQL Server};Server=*****************;Trusted_Connection=yes;')

con = pypyodbc.connect(conn_str)
cur = con.cursor()
cur.execute("SELECT DISTINCT TDate, Report, Records, Status from Elig_Own.DST_Report_Status_Test")
rows = cur.fetchall()
for row in rows:
        TDate = row[0]  # first item in the tuple iterable
        Report = row[1] # Second
        Records = row[2] # third
        Status = row[3]  # fourth
        cur.execute("truncate table Elig_own.DST_Report_Status_Test")
        cur.execute('''INSERT into Elig_Own.DST_Report_Status_Test (TDate, Report, Records, Status) 
                        VALUES (?, ?, ?, ?)''',(TDate, Report, Records, Status))      

con.commit()
con.close()

If I just print "row" within the for loop I get the list of tuples with all the fields and values I need for my table but I'm not sure how to pass these back into the empty table, all of the rows, not just one. Why am I getting only one?

score 0 · Answer 1 · answered Oct 07 '19 at 14:44

You are truncating (emptying) the table each time you iterate through your for loop. Therefore, when the loop finishes, your table will only contain the last row you inserted. You probably want to execute the TRUNCATE TABLE just once, before you enter the loop.

In pypyodbc, How to query a table for its distinct values, truncate old table and append the distinct values to the empty table

1 Answers1