存储过程适用于 MySQL 工作台，但不适用于 Python [英] Stored procedure works on MySQL workbench, but not in python

问题描述

我正在测试一个数据库，其中有 3 个表，1 号存储用户信息，2 号存储不同城市的纬度和经度，第三个表存储用户的位置及其与最近的城市.第 3 个表具有来自其他两个表的主键的复合键.我在 mysql 工作台中编写了一个存储过程，它接收 5 个变量，然后将数据插入到第三个表中，这是使用一些随机变量的代码:

I'm testing a database where i have 3 tables, number 1 is storing user information, number 2 is storing the latitude and longitude for different cities, and there's a 3rd table storing the location of a user and it's distance to the closest city. The 3rd table has a composite key from primary keys of the other two tables. I have written a stored procedure in mysql workbench that takes in 5 variables then it inserts data into the 3rd table, here's the code using some random variables:

SET @lat55 = 36.8451995849609;
SET @lng55 = 54.428798675537;
SET @city55 = 'Rasht';
SET @state55 = 'Gilan';
SET @user_id55 = 70440675;


DELIMITER //  
DROP PROCEDURE IF EXISTS do1 //
CREATE PROCEDURE do1 (IN id INT, IN state VARCHAR(25), IN city VARCHAR(25),             
IN lat DECIMAL(15,13),IN lng DECIMAL(15,12))
BEGIN

`  `DECLARE dmini DECIMAL(5,1);
    DECLARE lmini INT;

    SELECT location_id, ST_Distance_Sphere(POINT(lng,lat), geopoint)/1000 AS 
    distance INTO lmini, dmini FROM locations ORDER BY distance ASC LIMIT 1;

    INSERT INTO user_has_loc (user_id, location_id, distance, state, city, 
    lat, lng) VALUES (id, lmini, dmini, state, city, lat, lng);
END;
//

CALL do1(@user_id55,@state55,@city55,@lat55,@lng55);

它工作得很好并插入到表中，但是当我尝试使用 python 中的相同变量调用相同的过程时，它什么也不做:

it works just fine and inserts into the table, but when i try to call the same procedure with the same variables from python, it does nothing:

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="123456",
        database="v21")
    c = mydb.cursor()

    lat = 36.8451995849609
    lng = 54.428798675537
    city = "Rasht"
    state = "Gilan"
    user = 70440675

    arg = (user, state, city, lat, lng)

    c.callproc('do1', arg)

except mysql.connector.Error as err:
    print("Something went wrong: {}".format(err))

这是当我使用

c.execute("CALL do1({},{},{},{},{})".format(user, state, city, lat, lng))

它给出了另一个错误，提示'字段列表'中的未知列'Gilan'".有趣的部分是，如果我手动将相同的数据插入表中，那么当我尝试从 python 调用该过程时，它会引发错误:键 'PRIMARY' 的重复条目 '70440675-883'".所以它似乎试图插入表中，但它没有保存它！有人可以告诉我我在这里做错了什么吗?

it gives another Error saying "Unknown column 'Gilan' in 'field list'". The interesting part is that if i insert the same data into the table manually, then when i try to call the procedure from python, it raises the error : "Duplicate entry '70440675-883' for key 'PRIMARY'". so it seems like it tries to insert into the table, but it doesn't save it !! Can somebody please tell me what am i doing wrong here??

推荐答案

Answer ----->> 所以最后我能够让它工作，诀窍是在我调用程序后添加 mydb.commit()来自蟒蛇

Answer ----->> so finally i was able to make it work, the trick was to add mydb.commit() after i call the procedure from python

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="123456",
        database="v21")
    c = mydb.cursor()

    lat = 36.8451995849609
    lng = 54.428798675537
    city = "Rasht"
    state = "Gilan"
    user = 70440675

    arg = (user, state, city, lat, lng)

    c.callproc('do1', arg)
    mydb.commit()

except mysql.connector.Error as err:
    print("Something went wrong: {}".format(err))

文档中没有提到我们需要使用 .commit() 使用 .callproc() 方法，尤其是当所有更新和插入都在数据库端完成时.但是，它在 .commit 中提到() 文档说明

it's not mentioned in the documentation that we need to use .commit() after using .callproc() method, especially when all the updating and inserting is being done on the database side. But, it is mentioned in .commit() documentation that

由于默认情况下连接器/Python 不会自动提交，因此在每次修改使用事务存储引擎的表的数据的事务之后调用此方法很重要.

Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

所以我的猜测是因为该过程试图操作数据库上的数据并且它是从 python 客户端调用的，所以我们需要在使用 .callproc() 之后使用 .commit().

So my guess is since the procedure tries to manipulate data on the database and it's called from the python client, we need to use .commit() after using .callproc().

这篇关于存储过程适用于 MySQL 工作台，但不适用于 Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！