mariadb python - executemany using SELECT

Question

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!

Answer 1

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.

Answer 2

Thanks for your first question on stackoverflow which identified a bug in MariaDB Server.

Here is a simple script to reproduce the problem:

CREATE TABLE t1 (a int);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1),(2);

Python:

>>> cursor.executemany("INSERT INTO t1 VALUES \
    (SELECT a FROM t2 WHERE a=?))", [(1,),(2,)])
>>> cursor.execute("SELECT a FROM t1")
>>> cursor.fetchall()
[(1,), (1,)]

I have filed an issue in MariaDB Bug tracking system.

As a workaround, I would suggest reading the country table once into an array (according to Wikipedia there are 195 different countries) and use these values instead of a subquery.

e.g.

countries= {}
cursor.execute("SELECT country, id FROM countries")
for row in cursor:
  countries[row[0]]= row[1]

and then in executemany

cursor.executemany("INSERT INTO region (region,id_country) values ('sounth', ?)", [(countries["fra"],) (countries["ger"],)])