python - 使用 sqlalchemy 进行区分大小写的查询

Question

我需要使用 sqlalchemy 使一些查询对 mysql 数据库区分大小写如何使用 sqlalchemy 使查询区分大小写。提前致谢。

Answer 1

您需要指定列的排序规则区分大小写。
SQLAlchemy 对 MySQL 方言的支持 allows several column-types to accept a collation kwarg .
下面是一个示例，说明如何在创建表时指定列的排序规则。

import sqlalchemy as sa
sa.__version__
# -> '0.7.7'
engine = sa.create_engine('mysql+mysqldb://uname:pword@localhost/your_db', 
                          pool_recycle=3600)
metadata = sa.MetaData()
some_table = sa.Table('some_table', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('some_column', 
              # specify case-sensitive collation
              sa.dialects.mysql.VARCHAR(100, collation='utf8_bin')), 
)
metadata.create_all(engine)
conn = engine.connect()
conn.execute('''INSERT INTO some_table (some_column) 
                VALUES (%s);''', ('foo',))
conn.execute('''INSERT INTO some_table (some_column) 
                VALUES (%s);''', ('FOO',))
conn.execute('SELECT * FROM some_table;').fetchall()
# -> [(1L, 'foo'), (2L, 'FOO')]
conn.execute('''SELECT * 
                FROM some_table 
                WHERE some_column LIKE %s;''', ('foo%',)).fetchall()
# -> [(1L, 'foo')]
conn.execute('''SELECT * 
                FROM some_table 
                WHERE some_column LIKE %s;''', ('FOO%',)).fetchall()
# -> [(2L, 'FOO')]