I want to insert records in batches in DB using executemany() using python. I have a below code
def find_data(db_string):
json_str = '[\"{\"NAME\":\"Aman\",\"EMAIL\":\"[email protected]\",\"SALARY\":785674}\",\"{\"NAME\":\"Rahul\",\"EMAIL\":\"[email protected]\",\"SALARY\":65489}\", \"{\"NAME\":\"Vijay\",\"EMAIL\":\"[email protected]\",\"SALARY\":1254798}\"]'
formatted_json = json_str.replace('\','').replace('"{','{').replace('}"','}')
json_data = json.loads(formatted_json)
try:
bulk_operations=[]
with concurrent.futures.ThreadPoolExecutor(new_worker=10) as executor:
futures_task = []
for json_row in json_data:
futures_task.append(executor.submit(insert_data,json_row, bulk_operations))
concurrent.futures.wait(futures_task)
if bulk_operations:
insert_query = "Insert into Employee (name, email, salary, createdTs) values(?,?,?,?)"
conn = ibm_db_dbi.connect(db_string)
cur = conn.cursor()
cur.executemany(insert_query, bulk_operations)
expect Exception as e:
print("Error")
def insert_data(json_row, bulk_operations):
try:
approach #1:
name = str(json_row["NAME"])
email = str(json_row["EMAIL"])
salary = json_row["SALARY"]
current_time = datetime.now()
values = f"('{name}','{email}',{salary},'{current_time}')"
bulk_operations.append(values)
approach #2:
name1 = json_row["NAME"]
email1 = json_row["EMAIL"]
salary1 = json_row["SALARY"]
current_time = datetime.now()
record = [(name1,email1,salary1, current_time)]
bulk_operations.append(record)
expect Exception as e:
print("Error")
When running this I am getting below error :
[MainThread] ERROR ibm_db_dbi::DatabaseError: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
Can anyone help me on this please?