'Table doesn't exist' with creation using execute() function with 'multi=True' parameter

Question

MySQL 5.6.

I don't understand why if I use multi=True parameter table is not created. I don't find any information about it in documentation. If I separate all queries it works, but I don't want to separate them.

from mysql import connector as connector

conn = connector.connect(
    user='root',
    password='root',
    host='127.0.0.1',
    database='ra'
)
conn.autocommit = True
cursor = conn.cursor(dictionary=True, buffered=True)

sql = """
DROP TABLE IF EXISTS tab01;
CREATE TABLE tab01 (nzu DECIMAL(5,0) PRIMARY KEY,
                    nbr DECIMAL(3,0) DEFAULT NULL,
                    nland DECIMAL(5,0) DEFAULT NULL,
                    nlandtype DECIMAL(5,0) DEFAULT NULL,
                    nzuarea DECIMAL(12,1) DEFAULT NULL,
                    nzuareaos DECIMAL(12,1) DEFAULT NULL) ENGINE = MyISAM;
INSERT INTO tab01 (SELECT zu.nzu, zu.nbr, zu.nland,
                    IF(ISNULL(lands.nlandtype),0,lands.nlandtype) AS nlandtype,
                    0.0 AS nzuarea, 0.0 AS nzuareaos
                    FROM (SELECT * FROM zu WHERE nhoz = '6204000001') AS zu
                    LEFT JOIN lands AS lands ON zu.nland=lands.nland
                    ORDER BY nzu);
"""
cursor.execute(sql, multi=True) 
cursor.execute('SELECT * FROM tab01') 
# mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'ra.tab01' doesn't exist
result = cursor.fetchone()

Well, according to the doc it should work - but this is MySQL and you have DDL stuff in your query, so all bets are off. May I ask why you "don't want" to "separate" your queries ? — bruno desthuilliers
– bruno desthuilliers, Commented Jan 13, 2020 at 13:08
@bruno-desthuilliers, What is DLL stuff? I don't want to separate them because of speed, I found that multi queries works much more faster (I tested same query as above). — neurocker
– neurocker, Commented Jan 13, 2020 at 13:15
DDL : "Data Definition Language" => (schema definition / alterations). MySQL doesn't know how to wrap DDL in a transaction, and that's the cause of quite a few issues. This being said, I think mitkosoft's link might well be the answer to your question (and if yes, please mention it in a comment so we can close your question as a duplicate). — bruno desthuilliers
– bruno desthuilliers, Commented Jan 13, 2020 at 13:41
Oh and yes : "I found that multi queries works much more faster (I tested same query as above)" => well, if it does nothing, it's indeed going to be "much faster" xD — bruno desthuilliers
– bruno desthuilliers, Commented Jan 13, 2020 at 13:44

mitkosoft · Accepted Answer · 2020-01-13 22:03:10Z

1

Are you sure your CREATE works? I believe ENGINE option has to be at the end of CREATE statement:

CREATE TABLE tab01 (
 nzu DECIMAL(5,0) PRIMARY KEY,
 nbr DECIMAL(3,0) DEFAULT NULL,
 nland DECIMAL(5,0) DEFAULT NULL,
 nlandtype DECIMAL(5,0) DEFAULT NULL,
 nzuarea DECIMAL(12,1) DEFAULT NULL,
 nzuareaos DECIMAL(12,1) DEFAULT NULL
) ENGINE = MyISAM;

Also this statement:

cursor.execute(sql, multi=True)

creates an iterator over the results. It looks like it's lazy (i.e., it executes SQL statements only as needed). You're never asking for the results for the second statement, so it is only executing the first one. Try:

for _ in cursor.execute(sql, multi=True): pass

edited Jan 13, 2020 at 22:03

answered Jan 13, 2020 at 12:28

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

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

2 Comments

mitkosoft Over a year ago

@pythoff, I have revised my answer, pls check.

neurocker Over a year ago

It looks like it even not execute the first one (if I delete DROP query, it will lead to the same error), but with your for loop it works, thanks.

Collectives™ on Stack Overflow

'Table doesn't exist' with creation using execute() function with 'multi=True' parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related