CODEWITHSUNDEEP
sqlpython-3.xinserthanapython-sql
lAkShMipythonlearner
lAkShMipythonlearner

Reputation: 99

How to get final inserted sql records count using python

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

Number of Records inserted successfully: 1

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!

Upvotes: -1

Views: 32

Answers (2)

lAkShMipythonlearner
lAkShMipythonlearner

Reputation: 99

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!

Upvotes: -1

Jim Macaulay
Jim Macaulay

Reputation: 5155

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'

Upvotes: 0

Related Questions