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 <[email protected]<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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
