pymysql.err.ProgrammingError 1064 in simple multiline SQL query for mariadb

Question

I have tried everything and keep getting this error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near
'INSERT INTO tabSingles (doctype, field, value) VALUES ('Bank Reconciliation', 'a' at line 2")

Expanded query (after python format expansion):

SELECT value INTO @var FROM tabSingles WHERE doctype = 'Bank Reconciliation' AND field = 'bank_account';
INSERT INTO tabSingles (doctype, field, value) VALUES ('Bank Reconciliation', 'account', @var);
DELETE FROM tabSingles WHERE doctype = 'Bank Reconciliation' AND field = 'bank_account';

Can anyone see the problem? Is there some issue with multi-line queries? I have tried the individual lines on the mariadb command line and they appear to work as expected. I've also tried both frappe.db.sql and multisql (thought it meant multiline sql but doesn't). If I comment line 2 out, it also errors on line 3. Sorry to disturb but I've been staring at this for hours and cannot figure it out!

EDIT: The obvious answer is this, but I'd still like to know why it doesn't like the original query:

UPDATE tabSingles SET field='{new_name}' WHERE doctype='{doctype}' AND field='{old_name}';

Can you post an example of the python code that isn't working? FWIW, I would consider this 3 single-line queries, not a multi-line query. — totalhack
– totalhack, Commented Jan 22, 2020 at 0:41
@totalhack I could but I don't think it will help will it? The query in the post is a single string printed from python after format expansion as sent to the db. — racitup
– racitup, Commented Jan 22, 2020 at 2:59

Georg Richter · Accepted Answer · 2020-01-22 10:30:47Z

3

For security reasons (mainly SQL injection) MariaDB (and MySQL) servers don't support the execution of multiple SQL statements by default.

For supporting multiple statements execution the client needs to send COM_SET_OPTION command and MYSQL_OPTION_MULTI_STATEMENTS_ON flag to the server, which is not supported by PyMySQL.

answered Jan 22, 2020 at 10:30

Georg Richter

7,6742 gold badges19 silver badges17 bronze badges

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

Comments

Rick James · Accepted Answer · 2020-01-26 17:27:27Z

-1

Do not try to run more than one statement in a call.

Do use BEGIN and COMMIT.

Do use FOR UPDATE.

You need 5 separate commands:

BEGIN;
SELECT ... FOR UPDATE;  -- to keep other connections from messing with the row(s).
UPDATE ...;
DELETE ...
COMMIT;   -- do all of the above "atomically"

answered Jan 26, 2020 at 17:27

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

pymysql.err.ProgrammingError 1064 in simple multiline SQL query for mariadb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related