CODEWITHSUNDEEP
pythonhana
Alex
Alex

Reputation: 55

Executemany() SQL-Update Statement with variables for column names

I am really struggling with updating many rows in python using SAP HANA as my database and PyHDB for establishing the interface between both applications. Its working when I "hardcode" the columns, but I need to dynamically switch the columns by defining them inside of an array for example.

I am able to update in a hardcoded way the necessary columns by performing the following SQL-query:

sql = """UPDATE "ARE"."EMPLOYEES" SET "LIKELIHOOD_NO" = %s, "LIKELIHOOD_YES"= %s, "CLASS" = %s WHERE "EmployeeNumber" = %s;"""
cursor.executemany(sql, list)
connection.commit()

What I want to achieve is the following scenario:

 dynamic_columns = ["LIKELIHOOD_NO", "LIKELIHOOD_Yes"]
 sql = """UPDATE "ARE"."EMPLOYEES" SET dynamic_column = %s, "LIKELIHOOD_YES" = %s, "CLASS" = %s WHERE "EmployeeNumber" = %s;"""
 cursor.executemany(sql, list)
 connection.commit()

I am always getting the error that the relevant column / columns could not be found, but I cant figure out a way to solve this.

Upvotes: 1

Views: 2686

Answers (1)

nosklo
nosklo

Reputation: 223122

You can use normal string interpolation (.format()) to add the dynamic column name. You can see in the code here that pyHDB supports "numeric" paramstyle:

for col in ['LIKELIHOOD_YES', 'LIKELIHOOD_NO']:
    sql = ('UPDATE "ARE"."EMPLOYEES" SET "{some_col}" = :1, "CLASS" = :2 '
           'WHERE "EmployeeNumber" = :3;').format(some_col=col)
    cursor.executemany(sql, list_of_tuples)

This code will run for both columns 'LIKELIHOOD_YES' and 'LIKELIHOOD_NO'. Adapt it as you need. It would work with a list of tuples like this:

list_of_tuples = [
    (value1, class_1, employee_no_1),
    (value2, class_2, employee_no_2),
    (value3, class_3, employee_no_3),
    (value4, class_4, employee_no_4),
]

The code in your question seems to be using the 'format' paramstyle instead, but that doesn't seem what pyHDB is using. See PEP 249 for more information on paramstyles.

Upvotes: 1

Related Questions