Raw SQL works, using flask-mysql get syntax error [duplicate]

Question

This question already has answers here:

Suggested way to run multiple sql statements in python? (9 answers)

Closed 4 years ago.

I am building an application with Flask/mysql. I have a raw SQL script needed to seed the database. If I enter a SQL shell and run the command, it works without issues. When I run it from Flask, I am getting an error:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE IF EXISTS board_symbol; \n DROP TABLE IF EXISTS symbol; \n ' at line 2")
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

My flask application is very simple, two files. The first, app.py:

from flask import Flask 
from db import create_db, seed_db 
app = Flask(__name__)
db = create_db(app)
conn = db.connect() 
seed_db(conn)
@app.route("/")
def index():
 return "Hello world!"

The second, db.py:

from flaskext.mysql import MySQL 
def create_db(app):
 app.config["MYSQL_DATABASE_USER"] = "root" 
 app.config["MYSQL_DATABASE_PASSWORD"] = "mysqluser" 
 app.config["MYSQL_DATABASE_DB"] = "fault_tree" 
 app.config["MYSQL_DATABASE_HOST"] = "localhost" 
 mysql = MySQL()
 mysql.init_app(app)
 return mysql 
def seed_db(conn):
 try:
 cursor = conn.cursor()
 cursor.execute("""
 DROP TABLE IF EXISTS symbol_connection; 
 DROP TABLE IF EXISTS board_symbol; 
 DROP TABLE IF EXISTS symbol; 
 DROP TABLE IF EXISTS board; 
 DROP TABLE IF EXISTS symbol_type; 
 CREATE TABLE IF NOT EXISTS board ( 
 id INTEGER NOT NULL AUTO_INCREMENT, 
 name VARCHAR(100),
 description VARCHAR(500),
 PRIMARY KEY(id)
 );
 CREATE TABLE IF NOT EXISTS symbol_type (
 id INTEGER NOT NULL AUTO_INCREMENT, 
 type VARCHAR(30),
 PRIMARY KEY(id)
 );
 CREATE TABLE IF NOT EXISTS symbol (
 id INTEGER NOT NULL AUTO_INCREMENT, 
 name VARCHAR(100),
 description VARCHAR(200),
 type INT,
 child_board INT,
 PRIMARY KEY(id),
 FOREIGN KEY(type) REFERENCES symbol_type(id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(child_board) REFERENCES board(id) ON DELETE CASCADE ON UPDATE CASCADE
 );
 CREATE TABLE IF NOT EXISTS board_symbol (
 board_id INTEGER,
 symbol_id INTEGER,
 FOREIGN KEY(board_id) REFERENCES board(id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(symbol_id) REFERENCES symbol(id) ON DELETE CASCADE ON UPDATE CASCADE,
 PRIMARY KEY(board_id, symbol_id)
 );
 CREATE TABLE IF NOT EXISTS symbol_connection (
 board_id INTEGER NOT NULL, 
 start_symbol INT NOT NULL, 
 destination_symbol INT NOT NULL, 
 PRIMARY KEY(start_symbol, destination_symbol),
 FOREIGN KEY(board_id) REFERENCES board(id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(start_symbol) REFERENCES symbol(id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(destination_symbol) REFERENCES symbol(id) ON DELETE CASCADE ON UPDATE CASCADE
 );
 INSERT INTO board(name, description) VALUES('test_board', 'test_board');
 INSERT INTO symbol_type(type) VALUES('Event/basic');
 INSERT INTO symbol_type(type) VALUES('Event/conditioning');
 INSERT INTO symbol_type(type) VALUES('Event/intermediate');
 INSERT INTO symbol_type(type) VALUES('Event/remote basic');
 INSERT INTO symbol_type(type) VALUES('Event/underdeveloped');
 INSERT INTO symbol_type(type) VALUES('Gate/and');
 INSERT INTO symbol_type(type) VALUES('Gate/or');
 INSERT INTO symbol_type(type) VALUES('Gate/priority and');
 INSERT INTO symbol_type(type) VALUES('Gate/priority or');
 INSERT INTO symbol_type(type) VALUES('Gate/exclusive or');
 INSERT INTO symbol_type(type) VALUES('Transfer');
 INSERT INTO symbol(name, description, type) VALUES('Test event', 'test basic event', 1);
 INSERT INTO symbol(name, description, type) VALUES('Test and gate', 'test and gate', 6);
 INSERT INTO symbol(name, description, type) VALUES('Test intermediate event', 'test int event', 3);
 INSERT INTO symbol(name, description, type) VALUES('Test or gate', 'test or gate', 7);
 INSERT INTO board_symbol(board_id, symbol_id) VALUES(1, 1);
 INSERT INTO board_symbol(board_id, symbol_id) VALUES(1, 2);
 INSERT INTO board_symbol(board_id, symbol_id) VALUES(1, 3);
 INSERT INTO board_symbol(board_id, symbol_id) VALUES(1, 4);
 INSERT INTO symbol_connection(board_id, start_symbol, destination_symbol) VALUES(1, 1, 2);
 INSERT INTO symbol_connection(board_id, start_symbol, destination_symbol) VALUES(1, 1, 3);
 INSERT INTO symbol_connection(board_id, start_symbol, destination_symbol) VALUES(1, 3, 4);
 SELECT * FROM symbol_connection;
 SELECT * FROM symbol_type;
 SELECT * FROM board_symbol;
 SELECT * FROM symbol;
 SELECT * FROM board;
 """)
 data = cursor.fetchall()
 print(data)
 except Exception as e: 
 print(e)
 # raise Exception("Problem initializing MySQL database - check that fault_tree database exists and user root has access")

This is set up using a local database named fault_tree, with creds as listed in db.py if helpful for troubleshooting.

Answer 1

By default, you can only do one SQL statement per invocation of cursor.execute().

To run multiple SQL statements separated by semicolons, use cursor.execute("""...""", multi=True)

See https://instructobit.com/tutorial/130/Executing-multi-query-.sql-files-using-MySQL-connector-in-Python for a tutorial.