时间戳

首页 > 解决方案 > 如果我使用 mysql 用户变量，python 中的 mysql 不会返回结果

python - 如果我使用 mysql 用户变量，python 中的 mysql 不会返回结果

问题描述

我有一个有点复杂的 mysql 查询。我在 DBEAVER 和 mysql 工作台中测试了查询，并得到了正确的结果。但是当我在 python 中执行查询时，我没有得到任何结果。我用 mysql 和 pymysql 试过了。我在单行或多行中尝试过。但仍然只是空的结果。

这是我的代码：

def read_all_locationpairs(version):
sqlstring=f"""
SELECT DISTINCT
from_location_id, to_location_id
FROM
    (SELECT            
        @last_trip_id AS from_trip_id,
            @last_trip_id:=trip_id AS to_trip_id,
            @last_location_id AS from_location_id,
            @last_location_id:=CONCAT_WS(',',location_id,stop) AS to_location_id,
            trip_id
    FROM
        bconn.timetable
    WHERE
        version_id = {version}
    ORDER BY trip_id , start , order_nr) AS trips
WHERE
    from_trip_id = to_trip_id
ORDER BY from_location_id , to_location_id limit 5;
""".replace("\n","")
# Same request in a single line with fix version_id
#sqlstring="SELECT DISTINCT from_location_id, to_location_id FROM (SELECT @last_trip_id AS from_trip_id, @last_trip_id:=trip_id AS to_trip_id, @last_location_id AS from_location_id, @last_location_id:=CONCAT_WS(',',location_id,stop) AS to_location_id, trip_id FROM bconn.timetable        WHERE   version_id = 38 ORDER BY trip_id , start , order_nr) AS trips    WHERE from_trip_id = to_trip_id    ORDER BY from_location_id , to_location_id"
mycursor = bconndb.cursor()
mycursor.execute(sqlstring)
all_locations=mycursor.fetchall()
print(all_locations)
mycursor.close()

标签： pythonmysqlvariables

解决方案

我在额外的执行中初始化了变量，而不是我工作正常。

 cursor = connection.cursor()
cursor.execute("SET @last_trip_id := null;")    
cursor.execute("SET @last_location_id := null;")    
cursor.execute(f"""
SELECT DISTINCT
    from_location_id, to_location_id
    FROM
        (SELECT            
            @last_trip_id AS from_trip_id,
                @last_trip_id:=trip_id AS to_trip_id,
                @last_location_id AS from_location_id,
                @last_location_id:=CONCAT_WS(',',location_id,stop) AS to_location_id,
                trip_id
        FROM
            bconn.timetable
        WHERE
            version_id = 38
        ORDER BY trip_id , start , order_nr) AS trips
    WHERE
        from_trip_id = to_trip_id
    ORDER BY from_location_id , to_location_id
""")    
all_locations=cursor.fetchall()
print(all_locations)
cursor.close()

推荐阅读