Update Table in IBM DB2 Cloud from Python notebook

Question

I need to update the column in IBM DB2 cloud with values stored in dataframe from Python Notebook. I am able to connect to the DB2 from Python Notebook. Now I need update the one column of table in DB2 Cloud with the values stored in data frame. Below is my code, Problem is i have 100 records in the df same has to be updated in table but with this code 10000 records are updated in the table which means 100*100. Looking for support

tuple_of_tuples = tuple([tuple(x) for x in df.values])
load_db2_sql = "UPDATE schema.tablename SET Columnname = ?"
stmt = ibm_db.prepare(conn, load_db2_sql)
ibm_db.execute_many(stmt, tuple_of_tuples)

user_7923625 · Answer

After a few failed attempts I came up with a different way of thinking that solved the problem you are facing.

Simple answer: using pandas read_sql() method read your table into a dataframe, and replace the table column values received with your new values and update by using a command to_sql().

Example:

Assuming below is your SQL table, named dummy_test with schema named TEST_SCHEMA

# library versions
pandas 1.0.5
sqlachemy 1.3.17

A    B     C
  1   10   100
  2   20   200
  3   30   300
  4   40   400
  5   50   500
  6   60   600
  7   70   700
  8   80   800
  9   90   900
 10  100  1000

read this table into pandas dataframe

# define execution engine using sqlalchemy create_engine() 
# method with your db params

engine = sqlalchemy.create_engine('ibm_db_sa://{user}:{pwd}@{host}:{port}/{db};SECURITY=SSL'.format(
    user=params['username'],
    pwd=params['password'],
    host=params['hostname'],
    port=params['port'],
    db=params['database']
))
db_table = pd.read_sql('SELECT * FROM TEST_SCHEMA.dummy_test', engine)

# show table output
db_table
    a   b   c
0   1   10  100
1   2   20  200
2   3   30  300
3   4   40  400
4   5   50  500
5   6   60  600
6   7   70  700
7   8   80  800
8   9   90  900
9   10  100 1000

now assuming that the dataframe which values you want to use is named df_with_values_to_update and the column name is A contains the following values you want to update

df_with_values_to_update
    A
0   10
1   11
2   12
3   13
4   14
5   15
6   16
7   17
8   18
9   19

replace db_table column 'A' values with df_with_values_to_update column 'A' values

db_table['a'] = df_with_values_to_update['A']

write back to the db2 with to_sql() method

db_table.to_sql('dummy_test', engine, schema='TEST_SCHEMA', if_exists='replace', index=False)

You can query again the db2 from step2 to confirm the values has been replaced.

Update Table in IBM DB2 Cloud from Python notebook

1 Answers