python - 无法让 SQLAlchemy func.lower 与 MySQL 一起使用

Question

我在语法上犯了一些愚蠢的错误，但不知道是什么。

我尝试使用sqlalchemyfunc通过将数据库字段和变量都转为小写来比较它们，但输出为空，就好像条件不存在一样不满足。

如果我不使用 func 并传递静态变量，同样的事情会起作用。

代码:

question = "Hey"
q1 = QuestionsAndAnswers.query.filter(func.lower(QuestionsAndAnswers.question) == func.lower(question)).all()
q2 = QuestionsAndAnswers.query.filter(QuestionsAndAnswers.question == "Hey").all()
print "q1", q1
print "q2", q2

输出:

q1 []
q2 [<intercom_bot.models.QuestionsAndAnswers object at 0x7f1e2c7add50>]

数据库:

+----+-----------------+--------------------------------------------------+------------+
| id | question        | answer                                           | created_at |
+----+-----------------+--------------------------------------------------+------------+
|  1 | Hi              | Hey, Here I am and here you are. How can I help? | NULL       |
|  2 | Hello           | Hey, Here I am and here you are. How can I help? | NULL       |
|  3 | Helo            | Hey, Here I am and here you are. How can I help? | NULL       |
|  4 | Heelo           | Hey, Here I am and here you are. How can I help? | NULL       |
|  5 | Hell            | Hey, Here I am and here you are. How can I help? | NULL       |
|  6 | Hallo           | Hey, Here I am and here you are. How can I help? | NULL       |
|  7 | Hey             | Hey, Here I am and here you are. How can I help? | NULL       |
|  8 | He              | Hey, Here I am and here you are. How can I help? | NULL       |
|  9 | Ho              | Hey, Here I am and here you are. How can I help? | NULL       |
| 10 | I need help     | Hey, Here I am and here you are. How can I help? | NULL       |
| 11 | Help            | Hey, Here I am and here you are. How can I help? | NULL       |
| 12 | can you help me | Hey, Here I am and here you are. How can I help? | NULL       |
| 13 | Greetings       | Hey, Here I am and here you are. How can I help? | NULL       |
+----+-----------------+--------------------------------------------------+------------+

PS:

print QuestionsAndAnswers.query.filter(func.lower(QuestionsAndAnswers.question) == func.lower(question))

给出了这个:

 SELECT questions_and_answers.id AS questions_and_answers_id, questions_and_answers.question AS questions_and_answers_question, questions_and_answers.answer AS questions_and_answers_answer, questions_and_answers.created_at AS questions_and_answers_created_at 
FROM questions_and_answers 
WHERE lower(questions_and_answers.question) = lower(:lower_1)

PPS:这是模型。

class QuestionsAndAnswers(Base):
    """docstring for QuestionsAndAnswers"""
    __tablename__ = 'questions_and_answers'
    id = Column(BIGINT, primary_key=True)
    question = Column(MEDIUMBLOB, nullable=False)
    answer = Column(MEDIUMBLOB, nullable=False)
    created_at = Column(DATETIME, nullable=True,
                        default=datetime.datetime.now())

Answer 1

问题在于 MySQL 函数 LOWER() 和 UPPER() cannot handle binary string types并仅返回未修改的二进制字符串。

作为有问题的专栏

question = Column(MEDIUMBLOB, nullable=False)

是二进制字符串类型，函数应用

func.lower(QuestionsAndAnswers.question)

是一个无操作，将按原样返回二进制字符串。这意味着比较将在“Hey”和“hey”之间进行，并且谓词将不匹配。

正确的解决方法是更改​​模型、表和数据以使用正确的文本类型，例如 Unicode和 UnicodeText ，但一个快速的解决方案是添加强制转换:

from sqlalchemy import Unicode, cast

func.lower(cast(QuestionsAndAnswers.question, Unicode))