Thanks,
SET sql_mode=ANSI_QUOTES;
solved my problem.
Regards,
Godfryd
On Wednesday, May 8, 2013 4:26:35 PM UTC+2, Michael Bayer wrote:
>
>
> On May 8, 2013, at 12:33 AM, Michal Nowikowski
> <god...@gmail.com<javascript:>>
> wrote:
>
> Hello,
>
> I'm using PostgreSQL and MySQL databases and unfortunately my table names
> are CamelCase.
> I have a problems with quoting in raw queries that it works for both
> dialects.
>
> 1. following query works in MySQL and fails in PostgreSQL:
> s = sqlalchemy.text('select max(version) from DatabaseSchema;')
> db_ver = conn.execute(s).fetchone()[0]
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation
> "databaseschema" does not exist
> LINE 1: select max(version) from DatabaseSchema;
>
> 2. this query works in PostgreSQL and fails in MySQL:
> s = sqlalchemy.text('select max(version) from "DatabaseSchema";')
> db_ver = conn.execute(s).fetchone()[0]
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have an
> error in your SQL syntax; check the manual that corresponds to your MySQL
> server version for the right syntax to use near \'"DatabaseSchema"\' at
> line 1') 'select max(version) from "DatabaseSchema";' ()
>
> 3. this one fails in both but I would expect that is portable and should
> work in both dialects:
> s = sqlalchemy.text('select max(version) from :db;')
> db_ver = conn.execute(s, db="DatabaseSchema").fetchone()[0]
>
> MySQL error:
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an
> error in your SQL syntax; check the manual that corresponds to your MySQL
> server version for the right syntax to use near ''DatabaseSchema'' at line
> 1") 'select max(version) from %s;' ('DatabaseSchema',)
>
> PostgreSQL error:
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
> near "'DatabaseSchema'"
> LINE 1: select max(version) from 'DatabaseSchema';
> ^
> 'select max(version) from %(db)s;' {'db': 'DatabaseSchema'}
>
> I have to use raw queries.
> And the question is: how to write such queries to have it working in both
> dialects?
> My version of python-sqlalchemy is 0.7.9-1.
>
>
>
> your MySQL database probably doesn't like the quotes because by default it
> uses ` as the quoting character. MySQL has an option "ansi_quotes" which
> allows regular " to be used (
> http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_ansi_quotes)
>
> , if you first emit SET sql_mode=ANSI_QUOTES; on your MySQL connection.
> I seem to recall that MySQL-python can do this for you too if you send
> "sql_mode" to it, which with create_engine looks like create_engine("
> mysql://...?sql_mode=ANSI_QUOTES").
>
>
>
>
>
>
> Thanks in advance,
> Godfryd
>
> --
> 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.
