mysql在where后接select,【已解决】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

好像直接用

cursor.execute(sqlStr)

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

去试试

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

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

参考：

$name = mysql_get_var("SELECT name from people where email = ‘roger@freekrai.net’");

去改为：

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

EnumValueDictTableName, categoryCellValue)

结果：

结果是1，不是我要的 0

mysql return select result

mysql 返回查看记录结果

难道是此处pymsql的

executeResult = cursor.execute(sqlStr)

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

此处好像始终返回1

pymysql return mysql result

原来还要再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"]

效果：