MySQL full-text searches and SQLAlchemy; the present and a proposed future
MySQL supports an array of full-text search features recommended for performing advanced textual queries on fields of
TEXT types. For a variety of great reasons Python is our language of choice and there’s no better Pythonic interface to a relational database than SQLAlchemy.
SQLAlchemy is feature-packed and rock-solid. Unfortunately, it does not completely support all of what MySQL has to offer in the way of full-text searches.
The following article provides a brief introduction to full-text searching in MySQL, how—and to what extent—you can use it in SQLAlchemy
1.0.13 as well as our proposal for feature-complete full-text searching in SQLAlchemy and working with the core team to merge it back into the core code base.
Working with full-text indexes
Full-text indexes are created on one or a group of fields. The exact same set of fields must be used in
AGAINST syntax to perform full-text queries.
Indexes can be created as part of the initial table definition:
CREATE TABLE customer ( customer_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(200), physical_address_street TEXT(), FULLTEXT name_index (name) ) ENGINE=InnoDB;
Or on an existing table:
CREATE FULLTEXT INDEX `name_index` ON customer(name); /* or across multiple fields */ CREATE FULLTEXT INDEX `fulltext_index` ON customer(name, physical_address_street);
Queries performed using
AGAINST are able to make use of the full-text index. They can be run in three different modes:
- Natural Language
- Natural Language with Query Expansion
- Boolean Mode
MySQL’s documentation covers querying in great detail. A basic
BOOLEAN mode query would look as follows:
SELECT customer_id FROM customer WHERE MATCH(name) AGAINST('anomaly+ soft*' IN BOOLEAN MODE); /* or across multiple fields */ SELECT customer_id FROM customer WHERE MATCH(name, physical_address_street) AGAINST('anomaly+ soft*' IN BOOLEAN MODE);
Full-text searching via SQLAlchemy
At present SQLAlchemy (as at version
1.0.13) has implemented fairly basic support for full-text searching. The query interface is limited to single fields and queries are preset to run in
customers = session.query(Customer).filter(Customer.name.match("anomaly")).all()
Running queries against multiple fields or other modes is only possible by rolling your own
ClauseElement. There’s an extensive thread on StackOverflow where Mike Bayer, the author of SQLAlchemy, has outlined a detailed solution.
There’s also no support for creating full-text indexes via the
declarative_base. The best option is to hook onto the
after_create event and create the index via the
event.listen( Customer.__table__, "after_create", DDL("CREATE FULLTEXT INDEX `name` ON %(table)s(name)"))
While investigating full-text support in SQLAlchemy, I had a brief discussion on the mailing list. As a result we’ve decided to take on the job of completely implementing MySQL full-text support in SQLAlchemy. We plan to support:
- Creation of full-text indexes via the
- Multiple fields across multiple indexes
- Querying multiple fields in
NATURAL LANGUAGEmode with
You can follow the development on our forked repository on Github.