【已解决】pymysql中如何用select和where查询到对应的数据

希望通过：

SELECT * FROM `enum_value_dict` WHERE (`category`=’word_difficulty’ and `name`=’flyer’)

去查询到

中的flyer的value的值：3

mysql select query value

mysql return select value

MySQL – How to SELECT based on value of another SELECT – Stack Overflow

Mysql function returning a value from a query – Stack Overflow

Return Only One Variable from MySQL Query | CSS-Tricks

好像直接用

cursor.execute(sqlStr)

就可以返回查询到的对应row的数据？

去试试

‘SELECT * FROM `enum_value_dict` WHERE (`category`=\\’word_difficulty\\’ and `name`=\\’flyer\\’)’

返回1，并不是我要的单个record的值

参考：

https://css-tricks.com/snippets/php/return-only-one-variable-from-mysql-query/

$name = mysql_get_var("SELECT name from people where email = ‘[email protected]’");

去改为：

queryValueFromTypeSql = "SELECT `value` FROM `%s` WHERE (`category`=’word_type’ and `name`=’%s’)" % (

EnumValueDictTableName, categoryCellValue)

结果：

结果是1，不是我要的 0

mysql return select result

mysql – How to put a Query result in a return variable in SQL Function – Stack Overflow

How does MySQL return a result set from a stored procedure? – Database Administrators Stack Exchange

MySQL :: MySQL 5.5 Reference Manual :: 13.2.9 SELECT Syntax

mysql 返回查看记录结果

MySQL 查询数据 | 菜鸟教程

查询MYSQl数据表中的最后一条记录 – CSDN博客

MySQL中在查询结果集中得到记录行号的方法 – CSDN博客

MySQL使用count()来统计记录条数 – MySQL – 洪哥笔记

MySQL(三) 数据库表的查询操作【重要】 – 一杯凉茶 – 博客园

难道是此处pymsql的

executeResult = cursor.execute(sqlStr)

语句不能返回select出来的值？

此处好像始终返回1

pymysql return mysql result

python – pymysql fetchall() results as dictionary? – Stack Overflow

使用PyMySQL处理大结果集的方法 | 钟武的技术博客

原来还要再execute mysql后，去

result = cursor.fetchall()

才行。

去试试代码：

executeReturn = cursor.execute(sqlStr)

sqlResult = cursor.fetchall()

self.connection.commit()

就可以返回要的结果了：

另外，改回之前的select * 看看效果

# queryValueFromTypeSql = "SELECT `value` FROM `%s` WHERE (`category`=’word_type’ and `name`=’%s’)" % (EnumValueDictTableName, categoryCellValue)

queryAllFromTypeSql = "SELECT * FROM `%s` WHERE (`category`=’word_type’ and `name`=’%s’)" % (

EnumValueDictTableName, categoryCellValue)

结果返回了dict的list：

可以获取想要的任何字段了

【总结】

此处，python中pymsql时，想要返回查询的符合条件的record记录的结果的话，需要：

除了execute还要fetchall（或fetchone），才能返回结果

cursor = self.connection.cursor()

logging.debug("cursor=%s", cursor)

try:

    executeReturn = cursor.execute(sqlStr)

    sqlResult = cursor.fetchall()

    self.connection.commit()

    logging.debug("+++ Ok to execute sql %s for %s -> return=%s, result=%s", sqlStr, actionDescription, executeReturn, sqlResult)

    executeOk = True

    errDict["code"] = 0

    errDict["message"] = "OK"

except pymysql.Error as err:

    errStr = str(err)

而想要返回的是某个字段的值，还是相应的record的dict对象

则分别是：

select some_filed from where xxx

select * from where xxx

比如：

queryValueFromTypeSql = "SELECT `value` FROM `%s` WHERE (`category`=’word_type’ and `name`=’%s’)" % (EnumValueDictTableName, categoryCellValue)

"SELECT * FROM `%s` WHERE (`category`=’word_type’ and `name`=’%s’)" % (

EnumValueDictTableName, categoryCellValue)

即可返回单个的值，或整个dict对象的list。

相关完整代码是：

def executeSql(self, sqlStr, actionDescription=""):

    logging.debug("executeSql: sqlStr=%s, actionDescription=%s", sqlStr, actionDescription)

    executeOk = False

    resultDict = {

        "code": 0,

        "message": "Unknown Mysql Error",

        "data": None

    }

    if self.connection is None:

        logging.error("Please connect mysql first before execute mysql %s for %s", sqlStr, actionDescription)

        executeOk = False

        resultDict["code"] = 10000

        resultDict["message"] = "Mysql not connected"

        return executeOk, resultDict

    cursor = self.connection.cursor()

    logging.debug("cursor=%s", cursor)

    try:

        executeReturn = cursor.execute(sqlStr)

        sqlResult = cursor.fetchall()

        self.connection.commit()

        logging.debug("+++ Ok to execute sql %s for %s -> return=%s, result=%s", sqlStr, actionDescription, executeReturn, sqlResult)

        executeOk = True

        resultDict["code"] = 0

        resultDict["message"] = "OK"

        resultDict["data"] = sqlResult

    except pymysql.Error as err:

        errStr = str(err)

        # logging.error("!!! %s when execute sql: %s for %s", errStr, sqlStr, actionDescription)

        logging.debug("!!! %s when execute sql: %s for %s", errStr, sqlStr, actionDescription)

        self.connection.rollback()

        executeOk = False

        foundErrorCode, errorCode, errorMessage = self.extractMysqlErrorCodeMessage(errStr)

        if foundErrorCode:

            resultDict["code"] = errorCode

            resultDict["message"] = errorMessage

    return executeOk, resultDict

调用，获取返回值：

getWordDifficultySql = "SELECT * FROM `%s` WHERE (`category`=’word_difficulty’ and `name`=’%s’)" %\

                  (EnumValueDictTableName, difficultyCellValue)

logging.info("getWordDifficultySql=%s", getWordDifficultySql)

searchDifficultyOk, resultDict = connection.executeSql(getWordDifficultySql)

logging.info("%s -> %s, %s", getWordDifficultySql, searchDifficultyOk, resultDict)

if not searchDifficultyOk:

    logging.error("Invalid word difficulty %s for row [%d] -> %s", difficultyCellValue, wsCurRowNum, resultDict)

    continue

wordDifficulty = resultDict["data"][0]["value"]

效果：

转载请注明：在路上 » 【已解决】pymysql中如何用select和where查询到对应的数据