stored procedure call from sqlalchemy not commit

Question

I have tested my SP in MySQL and it works fine. I was able to insert new entry with it. I try to call it from flask with alchemy and it does run, but insert is not made into the table although it appears to execute the right commands.

My SP checks if there is an existing entry if yes then return 0, if no then insert the entry and return 1. When I send a new query from backend, I got 1 as return value but insert is not made in the table, When I send the same query, the return value is still 1. When I send an existing query that the table holds, the return value is 0.

I have other routes with the same db.connect() and it does fetch information. I read other posts about calling SP with the same execute function to run raw sql. From the doc it does seem execute doesn't require extra commit command to confirm the transaction.

So why can't I insert from the flask server?

This is the backend function

def add_book(info):
    try:
        connection = db.connect()
        title = info['bookTitle']
        url = info['bookUrl']
        isbn = info['isbn']
        author = info['author']
        #print("title: " + title + " url: "+ url + " isbn: "+ str(isbn) + " author"+ str(author))
        query = 'CALL add_book("{}", "{}", {}, {});'.format(title, url, isbn, author)
        #print(query)
        query_results = connection.execute(query)
        connection.close()
        query_results = [x for x in query_results]
        result = query_results[0][0]
    except Exception as err:
        print(type(err))
        print(err.args)
    return result

This is the table to insert

CREATE TABLE `book` (
  `isbn` int(11) DEFAULT NULL,
  `review_count` int(11) DEFAULT NULL,
  `language_code` varchar(10) DEFAULT NULL,
  `avg_rating` int(11) DEFAULT NULL,
  `description_text` text,
  `formt` varchar(30) DEFAULT NULL,
  `link` varchar(200) DEFAULT NULL,
  `authors` int(11) DEFAULT NULL,
  `publisher` varchar(30) DEFAULT NULL,
  `num_pages` int(11) DEFAULT NULL,
  `publication_month` int(11) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `image_url` varchar(200) DEFAULT NULL,
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `ratings_count` int(11) DEFAULT NULL,
  `work_id` int(11) DEFAULT NULL,
  `title` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`book_id`),
  KEY `authors` (`authors`),
  CONSTRAINT `book_ibfk_2` FOREIGN KEY (`authors`) REFERENCES `author` (`author_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36485537 DEFAULT CHARSET=utf8;

This is the SP

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `add_book`(
        IN titleIn VARCHAR(200), urlIn VARCHAR(200), isbnIn INT, authorIn INT)
BEGIN
DECLARE addSucess INT;

DECLARE EXIT HANDLER FOR sqlexception
BEGIN
    GET diagnostics CONDITION 1
    @p1 = returned_sqlstate, @p2 = message_text;
    SELECT @pa1, @p2;
ROLLBACK;
END;

DECLARE exit handler for sqlwarning
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 as RETURNED_SQLSTATE  , @p2 as MESSAGE_TEXT;
ROLLBACK;
END;

    IF EXISTS (SELECT 1 FROM book WHERE title = titleIn) THEN
        SET addSucess = 0;
    ELSE 
        INSERT INTO book (authors, title, url, book_id)
            VALUES (authorIn, titleIn, urlIn, null);
        SET addSucess = 1;
    END IF;
    SELECT addSucess;
END$$
DELIMITER ;

My user permission from show grants for current_user

[('GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOC ... (73 characters truncated) ... OW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION',), ('GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,ROLE_ADMIN,SET_USER_ID,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION',), ('REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON `mysql`.* FROM `root`@`%`',), ('REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON `sys`.* FROM `root`@`%`',), ('GRANT INSERT ON `mysql`.`general_log` TO `root`@`%`',), ('GRANT INSERT ON `mysql`.`slow_log` TO `root`@`%`',), ('GRANT `cloudsqlsuperuser`@`%` TO `root`@`%`',)]

Answer 1

I solved it with the Session api instead. If someone is reading, pls tell me a better way of passing the params and parse the return result

def add_book(info):
    title = info['bookTitle']
    url = info['bookUrl']
    isbn = info['isbn']
    author = info['author']
    with Session(db) as session:
        session.begin()
        try:
            query = 'CALL insert_book("{}", "{}", {}, {});'.format(title, url, isbn, author)
            result = session.execute(text(query)).all()
        except:
            session.rollback()
            raise
        else:
            session.commit()