On May 8, 2013, at 12:33 AM, Michal Nowikowski <godf...@gmail.com> 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+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.
>
>
--
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.
