Im trying to input many rows to a table in a mariaDB. For doing this i want to use executemany() to increase speed. The inserted row is dependent on another table, which is found with SELECT. I have found statements that SELECT doent work in a executemany(). Are there other ways to sole this problem?
import mariadb
connection = mariadb.connect(host=HOST,port=PORT,user=USER,password=PASSWORD,database=DATABASE)
cursor = connection.cursor()
query="""INSERT INTO [db].[table1] ([col1], [col2] ,[col3])
VALUES ((SELECT [colX] from [db].[table2] WHERE [colY]=? and
[colZ]=(SELECT [colM] from [db].[table3] WHERE [colN]=?)),?,?)
ON DUPLICATE KEY UPDATE
[col2]= ?,
[col3] =?;"""
values=[input_tuplets]
When running the code i get the same value for [col1] (the SELECT-statement) which corresponds to the values from the from the first tuplet.
If SELECT doent work in a executemany() are there another workaround for what im trying to do? Thx alot!
I think that reading out the tables needed, doing the search in python, use exeutemany() to insert all data. It will require 2 more queries (to read to tables) but will be OK when it comes to calculation time.