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.
When I execute, is this handled seamlessly by the engine? Or do I need to explicitly compile to the mysql dialect before executing? Randy On Wednesday, April 17, 2013 3:28:08 PM UTC-4, Michael Bayer wrote: > > "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), '%%') > > > > On Apr 17, 2013, at 2:28 PM, Randy Shults <randy.c...@gmail.com<javascript:>> > wrote: > > 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 > > > -- > 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 <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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.