mysql dialect and "contains"

[Randy Shults]

Can't seem to find any reference to anyone else having this issue, so it must be something I'm doing incorrectly. In Mysql, the contains operator doesnt seem to be translated properly by the dialect. 

Docs state the following:

    contains(other, **kwargs)¶

    Implement the ‘contains’ operator.

    In a column context, produces the clause LIKE '%<other>%'

But I'm getting the following:

    >print table.c.attribute.contains("test")

    ciq_user_event.attribute LIKE '%%' || :attribute_1 || '%%'

It should be something more like this ('||' is not a concat operator in mysql):

    ciq_user_event.attribute LIKE CONCAT('%', :attribute_1, '%') 

rather than .contains('test'), I can obviously do .like(func.concat("%","test","%")). 

But I'd really appreciate anyone reaching out and explaining what I'm missing with the .contains operator in mysql. 

sqlalchemy 0.8.0

For completeness:

ubuntu 12.04

python 2.7.3

mysqlconnector 1.0.9

AWS RDS, MySQL 5.5.27

Thanks!

Randy

(Actual code snippet below):

__________________________________________________________

engine = sqlalchemy.create_engine('mysql+mysqlconnector://%s:%s@%s/%s' % (config["user"], config["pass"], config["host"], config["db"]),connect_args = {"sql_mode": config["mode"],'client_flags': [ClientFlag.SSL],'ssl_ca': config["ssl_loc"],'ssl_cert': None,'ssl_key': None})

metadata = sqlalchemy.MetaData()

metadata.reflect(bind=engine)

conn = engine.connect() 

table = sqlalchemy.Table('ciq_user_event',metadata)   

select = sqlalchemy.select([table.c.partner_id]).where(table.c.attribute.contains("test"))

print select

[Michael Bayer]

"concat" is a function that's specific to MySQL.  So if you create an expression against contains() without any specification that MySQL is in use, you get a LIKE.

When the construct is compiled against the MySQL dialect, same as if it is invoked by an engine that's associated with MySQL, you get concat().

from sqlalchemy.sql import table, column

from sqlalchemy.dialects import mysql

t = table('t', column('x'))

print t.c.x.contains("test")

print t.c.x.contains("test").compile(dialect=mysql.dialect())

t.x LIKE '%%' || :x_1 || '%%'

t.x LIKE concat(concat('%%', %s), '%%')

--
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.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Bayer]

On Apr 17, 2013, at 3:37 PM, Randy Shults <randy.c...@gmail.com> wrote:

> Awesome thank you.
>
> So even though the Table object is attached to the metadata that was bound to the engine that uses a mysql connection, I have to explicitly set the dialect and compile when printing out the literal sql.

OK well here the answer is "sort of". *Some* SQL constructs, essentially those that are "executable", will compile themselves according to the dialect they're associated with; basically select(), insert(), update(), delete():

from sqlalchemy import Table, Column, MetaData, create_engine, String, select

m = MetaData()
t = Table('t', m, Column('x', String))
m.bind = create_engine("mysql://")
print select([t.c.x.contains("test")])

but just the column expression, since the expression isn't by itself "executable" it doesn't spend the time searching for an Engine:

print t.c.x.contains("test")


So if you're only dealing with execute(), you should be OK. But this is why I've really de-emphasized "bound metadata" overall, just makes things more confusing.

[Randy Shults]

Thank you. That makes sense. 

--

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

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/ZhksMS_1DCU/unsubscribe?hl=en.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.