How to get final inserted sql records count using python

Question

I have script where i can read data and did transformation and inserted to database using python

But transformed data is inserting loop wise so when i try to get cursor.rowcount it showing every single insert not final count

Below is the script which i tried

   try:
   
    conn = dbapi.connect(
    address="sapb101.sap.shome.net",
    port=30015,
    user="***",
    password="***",
    databasename='B1Q'
  
    )

    print("Succesful Connection to Hana Dev")
except:
    print("Unsuccesful Connection to Hana Dev")    

cur = conn.cursor ()
try:
    del_query = '''
        delete from "SAPTOM"."EL.ps.Tech::tbl.IBDbounrend_PY" where "LoadDt" = to_char(now(), 'YYYYMMDD')
        '''
    cur.execute(del_query)
    print('number of rows deleted', cur.rowcount)
except Exception as e:
    print("Error deleting query:", e)
query = '''
#select query
'''

cur.execute(query)
data = cur.fetchall()
number_rows = f"Total number of rows in table:  {len(data)}\n"
print(number_rows# 65 rows
for row in data:
    LoadDt = row[0]
    #print(LoadDt)
    FiscalWeek = row[1]
    InboundCaseVol = row[2]
    OutboundCaseVol6WCH =row[3]
    OutboundCaseVol6WWC =row[4]
    #print(LoadDt,FiscalWeek,InboundCaseVol,OutboundCaseVol6WCH,OutboundCaseVol6WWC)
   

    
    try:
        
        tgt_query = '''INSERT INTO"SAPTOM"."EL.ps.Tech::tbl.IBDbounrend_PY"("LoadDt","FiscalWeek","InboundCaseVol","OutboundCaseVol6WCH","OutboundCaseVol6WWC") VALUES (?,?,?,?,?) '''
        val = (LoadDt,FiscalWeek,InboundCaseVol,OutboundCaseVol6WCH,OutboundCaseVol6WWC)
        cur.execute(tgt_query, val)
        conn.commit()
        #print(cur.rowcount)
        number_rows_inser = f'Number of Records inserted successfully: {cur.rowcount}\n'

    except Exception as e:
        print("Error inserting data:", e)

When i try to print cur.rowcount it is giving me every time 1

Below is output

Number of Records inserted successfully: 1

But i want output like below:

Number of Records inserted successfully: 65

Please suggest your ideas here help me out!

Jim Macaulay · Accepted Answer · 2024-02-01 10:52:22Z

0

You can use enumerate function to get the index of your iteration and get the length of your iteration, this will get you the total number of rows inserted

count = None
for index, row in enumerate(data):
    count = index

    number_rows_inser = f'Number of Records inserted successfully: len(count)\n'

answered Feb 1, 2024 at 10:52

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lAkShMipythonlearner · Accepted Answer · 2024-02-01 11:04:56Z

-1

I just got my solution simple

I changed insert query and i used list comprehension so got final got

below is answer

 List = [character for character in data] 
try:
    cur.executemany('''INSERT INTO "STOM"."EL.pps.Tech::tbl.IBOFcstd_PY"("LoadDt","FiscalWeek","InboundCaseVol","OutboundCaseVol6WCH","OutboundCaseVol6WWC") VALUES (?,?,?,?,?) ''', List )
    conn.commit()
    number_rows_inser = f'Number of Records inserted successfully: {cur.rowcount}\n'
    print(number_rows_inser)
except Exception as e:
    print("Error inserting data:", e)

Thanks for looking into this question and answer!

answered Feb 1, 2024 at 11:04

lAkShMipythonlearner

9511 bronze badges

Collectives™ on Stack Overflow

How to get final inserted sql records count using python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related