Skip to content
Advertisement

Create dynamically insert into table in Python

i am trying to read from some db2 schema all the tables and for those tables to get their columns so i can create dynamically insert into statement My data is stored in a list in this format:

list =
    [
      {'tableName1': ['col1','col2','col3',...]},
      {'tableName2': ['col1','col2','col3','col4',...]},
      ...
    ]

where tableName is the name of the table in my schema. So far i read from db catalog all tables and their columns and insert them into a list, but i don’t know how to continue with iteration and create insert statement. SQL statement will be:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1

In my case column1,column2,… are in the list and table name is also in the list. Table names are stored in the list, but table where we insert will have different prefix

Advertisement

Answer

You could have divided this problem in smaller parts and then try to form query string. Here I have done it for two tables. You can iterate for other tables according to your needs.

list1 =[
    {'tableName1': ['col1','col2','col3']},
    {'tableName2': ['col1','col2','col3','col4']}
]

dict1 = list1[0]
dict2 = list1[1]

table_name_1 = list(dict1.keys())[0]
table_name_2 = list(dict2.keys())[0]

table1_columns = tuple(dict1[table_name_1])
table2_columns = tuple(dict2[table_name_2])

query_string = f"INSERT INTO {table_name_2} ({', '.join(table2_columns)}) " 
               f"SELECT {', '.join(table1_columns)} FROM {table_name_1};"

print(query_string)
Advertisement