公共代码

from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, JSON

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer(), primary_key=True)
    message = Column(JSON)

字符串

MySQL

JSON查询语句就是这样清晰漂亮。

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test', pool_recycle=3600)  # MySQL
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

person = Person(message={'name': 'Bob'})  # Insert a record
session.add(person)
session.commit()

query = session.query(Person.id).filter(Person.message['name'] == 'Bob')  # MySQL will work, clear and beautiful
print(query[0].id)  # 1
print(query)
# SELECT person.id AS person_id 
# FROM person 
# WHERE JSON_EXTRACT(person.message, %(message_1)s) = %(param_1)s

型

SQLite

它使用了不必要的json_quote()，结果不清楚。我想用JSON_QUOTE关闭sqlite JSON查询。
我该怎么解决这个问题？

engine = create_engine('sqlite:///:memory:')  # SQLite
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

person = Person(message={'name': 'Bob'})  # Insert a record
session.add(person)
session.commit()

query = session.query(Person.id).filter(Person.message['name'] == func.json_quote('Bob'))  # SQLite will work, but need to use json_quote
print(query[0].id)  # 1
print(query)
# SELECT person.id AS person_id 
# FROM person 
# WHERE JSON_QUOTE(JSON_EXTRACT(person.message, ?)) = json_quote(?)

型