时间戳

首页 > 解决方案 > 来自 sqlalchemy 的存储过程调用未提交

python - 来自 sqlalchemy 的存储过程调用未提交

问题描述

我已经在 MySQL 中测试了我的 SP，它工作正常。我能够用它插入新条目。我尝试使用炼金术从烧瓶中调用它并且它确实运行了，但是尽管它似乎执行了正确的命令，但它并没有插入到表中。

我的 SP 检查是否存在现有条目，如果是则返回 0，如果否则插入条目并返回 1。当我从后端发送新查询时，我得到 1 作为返回值，但未在表中插入，当我发送相同的查询，返回值仍然是 1。当我发送表保存的现有查询时，返回值是 0。

我有其他相同的路线，db.connect()它确实获取信息。我阅读了有关使用相同execute函数调用 SP 以运行原始 sql 的其他帖子。从文档来看，它似乎execute不需要额外的commit命令来确认交易。

那么为什么我不能从烧瓶服务器插入呢？

这是后台功能

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

这是要插入的表

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;

这是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 ;

我的用户许可来自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`@`%`',)]

标签： pythonmysqlsqlalchemy

解决方案

我用Sessionapi解决了它。如果有人正在阅读，请告诉我传递参数并解析返回结果的更好方法

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()

推荐阅读