MySQL defining full text indexes and using MATCH AGAINST, SQLAlchemy way

[Dev Mukherjee]

Hi everyone,

Is there a way of defining a MySQL FullText index via SQLAlchemy. We use the ORM layer to define all of our tables and use the create_all method provided by calling declarative_base to create our schema; and it would be nice to store it as part of the model definition.

I have found many Stackoverflow posts on partial solutions to this and was wondering if there's a SQLAlchemy way? e.g using a function generator.

Any pointers would be appreciated.

Thanks for your time.

[Mike Bayer]

most expedient way would be to use CREATE INDEX and specify it within DDL():

ddl = DDL("CREATE FULLTEXT INDEX some_idx ON table(colname)")

you can then just invoke it:


conn.execute(ddl)


or add it as a table event


event.listen(table, "after_create", ddl)

we can of course add an option to the MySQL dialect to allow
"mysql_fulltext=True" to Index as well.

On 05/05/2016 02:09 AM, Dev Mukherjee wrote:
> Hi everyone,
>
> Is there a way of defining a MySQL FullText index via SQLAlchemy. We use

> the ORM layer to define all of our tables and use the /create_all/
> method provided by calling /declarative_base /to create our schema; and

> it would be nice to store it as part of the model definition.
>
> I have found many Stackoverflow posts on partial solutions to this and
> was wondering if there's a SQLAlchemy way? e.g using a function generator.
>
> Any pointers would be appreciated.
>
> Thanks for your time.
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Dev Mukherjee]

On Thu, May 5, 2016 at 11:54 PM, Mike Bayer <mik...@zzzcomputing.com> wrote:

most expedient way would be to use CREATE INDEX and specify it within DDL():

ddl = DDL("CREATE FULLTEXT INDEX some_idx ON table(colname)")

you can then just invoke it:


conn.execute(ddl)

Ah OK. That will ensure that we don't miss out on creating the indexes.

I did find your post on Stackoverflow answering the same question

http://stackoverflow.com/questions/14971619/proper-use-of-mysql-full-text-search-with-sqlalchemy

Been querying like so

session.query(myapp.models.Customer).filter(text("MATCH(name) AGAINST (\"[:value]\" IN BOOLEAN MODE)", bindparams=[bindparam("value", ["+Anomaly", "+soft"])])).all()

Can I make this better or this should get me out of trouble for now?

 

or add it as a table event


event.listen(table, "after_create", ddl)

we can of course add an option to the MySQL dialect to allow "mysql_fulltext=True" to Index as well.

Would love to see that added to SQLAlchemy :-)

Be possible for you to provide a wrapper for the MATCH and AGAINST functions as well?

Thanks heaps for sparing your time.

[Mike Bayer]

On 05/06/2016 02:31 AM, Dev Mukherjee wrote:
> On Thu, May 5, 2016 at 11:54 PM, Mike Bayer <mik...@zzzcomputing.com
> <mailto:mik...@zzzcomputing.com>> wrote:
>
> most expedient way would be to use CREATE INDEX and specify it
> within DDL():
>
> ddl = DDL("CREATE FULLTEXT INDEX some_idx ON table(colname)")
>
> you can then just invoke it:
>
>
> conn.execute(ddl)
>
>
> Ah OK. That will ensure that we don't miss out on creating the indexes.
>
> I did find your post on Stackoverflow answering the same question
> http://stackoverflow.com/questions/14971619/proper-use-of-mysql-full-text-search-with-sqlalchemy
>
> Been querying like so
>
> session.query(myapp.models.Customer).filter(text("MATCH(name) AGAINST
> (\"[:value]\" IN BOOLEAN MODE)", bindparams=[bindparam("value",
> ["+Anomaly", "+soft"])])).all()
>
> Can I make this better or this should get me out of trouble for now?

That array value is odd, I'm not sure what that does, if I'm
understanding correctly you can use our own match() operator and just
pass that as one string:

session.query(Customer).filter(Customer.name.match("+Anomaly +soft"))

I can't find in MySQL's docs what the brackets here mean but you can add
those to the literal match value. I've updated the SO answer to include
that for a single column, just use match().

>
>
> or add it as a table event
>
>
> event.listen(table, "after_create", ddl)
>
> we can of course add an option to the MySQL dialect to allow
> "mysql_fulltext=True" to Index as well.
>
>
>
> Would love to see that added to SQLAlchemy :-)
>
> Be possible for you to provide a wrapper for the MATCH and AGAINST
> functions as well?
>
> Thanks heaps for sparing your time.
>

[Dev Mukherjee]

On Sat, May 7, 2016 at 12:48 AM, Mike Bayer <mik...@zzzcomputing.com> wrote:

On 05/06/2016 02:31 AM, Dev Mukherjee wrote:

On Thu, May 5, 2016 at 11:54 PM, Mike Bayer <mik...@zzzcomputing.com
<mailto:mik...@zzzcomputing.com>> wrote:

    most expedient way would be to use CREATE INDEX and specify it
    within DDL():

    ddl = DDL("CREATE FULLTEXT INDEX some_idx ON table(colname)")

    you can then just invoke it:


    conn.execute(ddl)


Ah OK. That will ensure that we don't miss out on creating the indexes.

I did find your post on Stackoverflow answering the same question
http://stackoverflow.com/questions/14971619/proper-use-of-mysql-full-text-search-with-sqlalchemy

Been querying like so

session.query(myapp.models.Customer).filter(text("MATCH(name) AGAINST
(\"[:value]\" IN BOOLEAN MODE)", bindparams=[bindparam("value",
["+Anomaly", "+soft"])])).all()

Can I make this better or this should get me out of trouble for now?

That array value is odd, I'm not sure what that does, if I'm understanding correctly you can use our own match() operator and just pass that as one string:

session.query(Customer).filter(Customer.name.match("+Anomaly +soft"))

I swear I looked around for a match operator before I asked :-

 

I can't find in MySQL's docs what the brackets here mean but you can add those to the literal match value.  I've updated the SO answer to include that for a single column, just use match().

If I create the full text and include multiple fields in the index

CREATE FULLTEXT INDEX `keyword_search` ON 

customer(name,physical_address_street,physical_address_suburb,

physical_address_state,physical_address_post_code);

I can only execute a search with the same set of fields

SELECT * FROM customer WHERE 

MATCH(name,physical_address_street,physical_address_suburb,physical_address_state,

physical_address_post_code) AGAINST ("+Anomaly" IN BOOLEAN MODE);

If I try this in SQLAlchemy

session.query(Customer).filter(Customer.name.match("+Anomaly"), Customer.physical_address_street.match("+Anomaly"),Customer.physical_address_suburb.match("+Anomaly"),Customer.physical_address_state.match("+Anomaly"),Customer.physical_address_post_code.match("+Anomaly")).all()

MySQL complains about no matching column list for Full text search.

Is is possible to express multiple fields in the match operator? Or should I not be creating a full text index amongst multiple fields?

PS for my use case I want to search against all the fields.

[Mike Bayer]

On Saturday, May 7, 2016, Dev Mukherjee <dev...@gmail.com> wrote:

If I create the full text and include multiple fields in the index

CREATE FULLTEXT INDEX `keyword_search` ON 

customer(name,physical_address_street,physical_address_suburb,

physical_address_state,physical_address_post_code);

I can only execute a search with the same set of fields

SELECT * FROM customer WHERE 

MATCH(name,physical_address_street,physical_address_suburb,physical_address_state,

physical_address_post_code) AGAINST ("+Anomaly" IN BOOLEAN MODE);

If I try this in SQLAlchemy

session.query(Customer).filter(Customer.name.match("+Anomaly"), Customer.physical_address_street.match("+Anomaly"),Customer.physical_address_suburb.match("+Anomaly"),Customer.physical_address_state.match("+Anomaly"),Customer.physical_address_post_code.match("+Anomaly")).all()

MySQL complains about no matching column list for Full text search.

Is is possible to express multiple fields in the match operator? Or should I not be creating a full text index amongst multiple fields?

Right that *is* the use case that the stack overflow answer is meant for.   I can accept pull requests that add a new mysql.match() object for this purpose.  But it might be nice if it integrates with the FULLTEXT DDL part of it as well.   This is all very simple to implement it's just coming up with a great API that supports all the edge cases that has to be done carefully.

 

PS for my use case I want to search against all the fields.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Dev Mukherjee]

On Sun, May 8, 2016 at 12:35 AM, Mike Bayer <mik...@zzzcomputing.com> wrote:

On Saturday, May 7, 2016, Dev Mukherjee <dev...@gmail.com> wrote:

If I create the full text and include multiple fields in the index

CREATE FULLTEXT INDEX `keyword_search` ON 

customer(name,physical_address_street,physical_address_suburb,

physical_address_state,physical_address_post_code);

I can only execute a search with the same set of fields

SELECT * FROM customer WHERE 

MATCH(name,physical_address_street,physical_address_suburb,physical_address_state,

physical_address_post_code) AGAINST ("+Anomaly" IN BOOLEAN MODE);

If I try this in SQLAlchemy

session.query(Customer).filter(Customer.name.match("+Anomaly"), Customer.physical_address_street.match("+Anomaly"),Customer.physical_address_suburb.match("+Anomaly"),Customer.physical_address_state.match("+Anomaly"),Customer.physical_address_post_code.match("+Anomaly")).all()

MySQL complains about no matching column list for Full text search.

Is is possible to express multiple fields in the match operator? Or should I not be creating a full text index amongst multiple fields?

Right that *is* the use case that the stack overflow answer is meant for.   I can accept pull requests that add a new mysql.match() object for this purpose.  But it might be nice if it integrates with the FULLTEXT DDL part of it as well.   This is all very simple to implement it's just coming up with a great API that supports all the edge cases that has to be done carefully.

 

I should have been clearer in my response :-) I was double checking if the existing operator supported that edge case. We will fork the repo and implement the functions (along with DDL support). 

[Dev]

Follow up to my own response and intent to implement this. Meng Zhuo already has a project that implements this function

https://github.com/mengzhuo/sqlalchemy-fulltext-search

Working through this https://github.com/mengzhuo/sqlalchemy-fulltext-search/issues/9 but other than that it works really well.

Also it's possibly best served to stay as an add-on as opposed to make it back into SQLAlchemy. Mike is probably a better judge of that :-)