1

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!

Improve this question
3
  • Can you please provide a short reproducible example with parameters and preferable not in MSSQL emulation mode? Commented Jan 27, 2023 at 19:30
  • Thx for your respond @GeorgRichter. Ill put a reproducable (silly) example below. The example works when its not in emulation mode. I also works a single query in emulation mode. The issue is when the command execute_many() is used. One way aound this would be to find another way of doing this without using select. Any ideas? Commented Jan 31, 2023 at 8:37
  • CREATE TABLE region (id INT(6) AUTO_INCREMENT PRIMARY KEY,id_country INT(6),region VARCHAR(30)) CREATE TABLE countries (id INT(6) AUTO_INCREMENT PRIMARY KEY, country VARCHAR(30)) insert into countries (country) values ("ger"),("fra") insert into region (region,id_country) values ("sounth",(select id from countries where country="ger")) Commented Jan 31, 2023 at 8:38

2 Answers 2

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
0

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"],)])
Improve this answer

1 Comment

thx for your comment Georg! And, also for filing the bug. The suggensted workaround is the one i tried to explain in my own comment, so thats good. Thx alot!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.