0

I want SQLAlchemy to generate the following SQL code:

SELECT t171 AS "3Harm" FROM production

I've been playing around with something similar to this SQLAlchemy ORM snippet:

session.query(Production.t171.label('3harm'))

The problem here is that this doesn't properly quote "3harm" in the generated SQL. Instead of "3harm" this generates the unquoted 3harm, which is invalid because it starts with a numerical character and therefore raises the following Oracle exception:

ORA-00923: FROM keyword not found where expected

I can get this to work by capitalizing any character in the alias name:

session.query(Production.t171.label('3Harm'))

But I would still prefer to use all lowercase column names since the rest of my program is standardized for all lowercase. Any idea how to force quote the lowercase version?

Vijchti
  • 526
  • 6
  • 19
  • 1
    I can confirm that `oracle` dialect has a problem and is not quoting your label, but it will quote properly one with space (like `'3 Harm'`). In fact, there is a ticket on sqlalchemy for this: [Issue #2138 - oracle bind names that start with numbers](https://bitbucket.org/zzzeek/sqlalchemy/issue/2138/oracle-bind-names-that-start-with-numbers). Please vote for the issue to get its priority up. – van Feb 28 '14 at 09:38

2 Answers2

1

Found the solution while looking for something else.

Any column can be forced to use quotes with column.quote = True.

So for the original example:

column = Production.t171.label('3harm')
column.quote = True
session.query(column)

Success!

Vijchti
  • 526
  • 6
  • 19
0

The SQL you want to generate isn't valid; rather than this:

SELECT t171 AS '3Harm' FROM production

... you need the identifier to be enclosed in double quotes, not single quotes:

SELECT t171 AS "3Harm" FROM production

So it looks like you should be able to do this:

session.query(Production.t171.label('"3harm"'))

or maybe:

session.query(Production.t171.label("3harm"))

But I don't use SQLAlchemy and I don't have any way to check if either is valid; you might need to escape the double quotes in the first one, for instance, though from this perhaps the second is more likely to work... and I don't understand why 3Harm would work unquoted.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks for the response, Alex. Those were typos and have been fixed. Using `'"3harm"'` generates `"""3harm"""` in the generated SQL code, which causes problems of its own. `"3harm"`, meanwhile, is identical to `'3harm'` in Python; it's evaluated as a string either way and the generated SQL is still invalid. – Vijchti Feb 28 '14 at 16:27