I have a simple Item class mapped to table automatically reflected
from the PostgreSQL database as follows:
t_item_master = Table('item_master', engine, autoload = True)
class Item(object):
... pass
assign_mapper(Item, t_item_master)
Now I'd like to get all items whose name match a certain pattern:
items=Item.select(Item.c.item_name.like('%TOTO%'))
This works fine. But since the 'LIKE' operator it generated is not
case insensitive so I would like to use the postgres 'ILIKE' operator
instead. In SQLAlchemy there is no 'ilike' function, therefore I tried
literal select text as follows:
items=Item.select("item_name ilike '%toto%'")
This doesn't work and generated the traceback:
Traceback (most recent call last):
File "<input>", line 1, in ?
File "build\bdist.win32\egg\sqlalchemy\mapping\mapper.py", line 280, in select
File "build\bdist.win32\egg\sqlalchemy\mapping\query.py", line 132, in select
File "build\bdist.win32\egg\sqlalchemy\mapping\query.py", line 136,
in select_whereclause
File "build\bdist.win32\egg\sqlalchemy\mapping\query.py", line 212,
in _select_statement
File "build\bdist.win32\egg\sqlalchemy\sql.py", line 472, in execute
File "build\bdist.win32\egg\sqlalchemy\sql.py", line 377, in execute
File "build\bdist.win32\egg\sqlalchemy\sql.py", line 354, in execute
File "build\bdist.win32\egg\sqlalchemy\engine.py", line 648, in
execute_compiled
File "build\bdist.win32\egg\sqlalchemy\engine.py", line 643, in proxy
File "build\bdist.win32\egg\sqlalchemy\engine.py", line 694, in execute
File "build\bdist.win32\egg\sqlalchemy\engine.py", line 714, in _execute
SQLError: (TypeError) unindexable object "SELECT
item_master.create_date AS item_master_create_date,
item_master.item_status AS item_master_item_status,
item_master.brand_id AS item_master_brand_id, item_master.shop_id AS
item_master_shop_id, item_master.update_date AS
item_master_update_date, item_master.main_image_id AS
item_master_main_image_id, item_master.item_name AS
item_master_item_name, item_master.specification AS
item_master_specification, item_master.country_id AS
item_master_country_id, item_master.unit_id AS item_master_unit_id,
item_master.purchase_price AS item_master_purchase_price,
item_master.item_group_id AS item_master_item_group_id,
item_master.model_no AS item_master_model_no, item_master.description
AS item_master_description, item_master.series_id AS
item_master_series_id, item_master.disp_order AS
item_master_disp_order, item_master.item_id AS item_master_item_id,
item_master.province_id AS item_master_province_id,
item_master.sales_price AS item_master_sales_price, item_master.vat_id
AS item_master_vat_id, item_master.item_type AS item_master_item_type,
item_master.psupplier_id AS item_master_psupplier_id \nFROM
item_master \nWHERE item_name ilike '%toto%'" {}
I first suspected that I can not put a where condition that way as
argument to select function, but the following works fine:
items=Item.select("sales_price > 1000")
What's wrong with my attempt to do the select and how to do it
correctly? I am using SQLAlchemy version 0.1.6.
Thanks for helping.
--
Hong Yuan
大管家网上建材超市
装修装潢建材一站式购物
http://www.homemaster.cn
