MySQL Full-text Searches and SQLAlchemy; the Present and a Proposed Future

, a 8-minute piece by Dev Mukherjee Dev Mukherjee

MySQL supports an array of full-text search features recommended for performing advanced textual queries on fields of CHAR, VARCHAR and 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 MATCH and 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 MATCH and AGAINST are able to make use of the full-text index. They can be run in three different modes:

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 BOOLEAN mode.

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 DDL.

event.listen(
    Customer.__table__, 
    "after_create", 
    DDL("CREATE FULLTEXT INDEX `name` ON %(table)s(name)"))

Proposed Future

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:

You can follow the development on our forked repository on Github.

Next Up: a 6-minute piece by Dev Mukherjee Dev Mukherjee

Constructing Multipart MIME Messages for Sending Emails in Python

Read more