CODEWITHSUNDEEP
pythonsql-serverpandasdataframepyodbc
Shazib Shabir
Shazib Shabir

Reputation: 158

Providing DataFrame values to .executemany for an UPDATE

I need to update excel data in MSSQL via python. So first I read excel file(Has only one row in it) and make necessary changes for datatypes:

import pyodbc 
import pandas as pd
df = pd.read_excel('test_new.xlsx')
first_col = df.pop('number')
df['number'] = first_col
df['sys_created_on'] = df['sys_created_on'].dt.strftime('%Y-%m-%d %H:%M:%S')
df['opened_at'] = df['opened_at'].dt.strftime('%Y-%m-%d %H:%M:%S')
df['resolved_at'] = df['resolved_at'].dt.strftime('%Y-%m-%d %H:%M:%S')
df['sys_updated_on'] = df['sys_updated_on'].dt.strftime('%Y-%m-%d %H:%M:%S')
df.fillna('', inplace=True)
records = df.to_records(index=False)
result = list(records)
result

The above statement gives result. The result is a list

[('Sha', 'po1301', 'Closed', 'TLS - Admin', 15585, 'Store Network', 'Switch', 'CATS0005587', '', 'mic.vin.com ', 'pis - opo', 'LEC NIN', '2020-08-11 23:56:03', '1 Pro 1 LLC', '', '', 'Switch', '', '', '', 'Phone', 'oshi3001', 'Resolved', '"Approved Tech\r\n1. troubleshooting.\r\n"\r\n\r\n\r\nIMPORTANT - TECH SHOULD CALL NOC ON ARRIVAL', 'No', False, '1 - High', '0-6 hours', False, 'mada gill', '7-ELEVEN STORE - 38578', '', '2020-08-11 23:56:03', 'john win', '1 - Critical', 6, '', 'Not Solved (Not Reproducible)', 'duplicate case, see notes', 59022, '2020-08-12 16:19:45', '', 'UNKNOWN', '3 - Low', 'Store is hard down', 1, 'Power Verification', 'svd', '1 - High', '2020-08-12 16:19:45', False, 'INC1090099')]

After this part, i create a SQL update statement:

conn = pyodbc.connect()
cursor = conn.cursor()
cursor.executemany("Update proj.[mid].[datahub] SET assigned_to = ?, [caller_id#user_name] = ?, state = ?, assignment_group = ?, business_duration = ?, business_service = ?, category = ?, u_category_structure = ?, u_resolution_ci = ?, resolved_by = ?, u_resolver_group = ?, u_service_provider = ?, sys_created_on = ?, caller_id = ?, u_caller_first_name = ?, u_caller_last_name = ?, u_vendor_category = ?, rfc = ?, closed_at = ?, closed_by = ?, contact_type = ?, sys_created_by = ?, u_customer_portal_state = ?, description = ?, u_first_call_resolution = ?, u_first_time_fix = ?, impact = ?, u_incident_age = ?, knowledge = ?, reopened_by = ?, location = ?, u_on_behalf_of = ?, opened_at = ?, opened_by = ?, priority = ?, reassignment_count = ?, u_resolution_method = ?, close_code = ?, close_notes = ?, calendar_stc = ?, resolved_at = ?, u_sd_esclation = ?, sla_due = ?, severity = ?, short_description = ?, reopen_count = ?, subcategory = ?, sys_updated_by = ?, urgency = ?, sys_updated_on = ?, [assigned_to#active] = ? WHERE number = ?", result)
cursor.commit()

It gives error: ProgrammingError: The second parameter to executemany must be a sequence, iterator, or generator.

It doesnt take result variable as list for some reasons, when it indeed is a list. If I print the statement inside cursor.executemany() and then execute that, it updates the data, but using the result variable which holds a list, it doesn't work.

Please help.

Upvotes: 3

Views: 1842

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

result = print(list(tuple(records)))

prints the results to the console, but the print function itself returns None. Instead, you should omit the records call and assign result like this ...

result = list(df.itertuples(index=False))

... then print it out if you so desire

print(result)

Edit: Complete working example

cnxn = pyodbc.connect("DSN=mssqlLocal64")
crsr = cnxn.cursor()

# set up test environment
crsr.execute("CREATE TABLE #tmp (txt varchar(10), id int primary key)")
crsr.execute(
    "INSERT INTO #tmp (txt, id) VALUES "
    "('old_foo', 1), ('old_bar', 2)"
)
print(crsr.execute("SELECT * FROM #tmp").fetchall())
"""console output:
[('old_foo', 1), ('old_bar', 2)]
"""

# test
df = pd.DataFrame([("new_foo", 1), ("new_bar", 2)], columns=["txt", "id"])
result = list(df.itertuples(index=False))
crsr.executemany("UPDATE #tmp SET txt = ? WHERE id = ?", result)
print(crsr.execute("SELECT * FROM #tmp").fetchall())
"""console output:
[('new_foo', 1), ('new_bar', 2)]
"""

Upvotes: 1

Related Questions