Sqlalchemy: How to use a database function that does not accept arguments?

[mkapi]

I have a use case that uses Oracle 12c SYSTIMESTAMP and applies label to the resulting expression.
Note that this function is used without parentheses. It can also be used with parentheses(not explicitly documented), and in that case it requires a precision argument which I am not aware of in my code base.

One solution for this use case is to use func.SYSTIMESTAMP(). But this function compiles into SYSTIMESTAMP() which is different than what I want.

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-30088: datetime/interval precision is out of range
[SQL: SELECT SYSTIMESTAMP() AS "system timestamp"
FROM dual]

Another solution would be using text("SYSTIMESTAMP"). This works fine until i need to label it where it fails (since TextClause can not be labeled).

Traceback (most recent call last):
  File "/home/mkapi/Desktop/oracle-systimestamp-question.py", line 6, in <module>
    engine.execute(select([text("SYSTIMESTAMP").label("system timestamp")]).select_from(text("dual"))).fetchall()
AttributeError: 'TextClause' object has no attribute 'label'

Below is the minimal example I can provide:

from sqlalchemy import create_engine, func, select, text

engine = create_engine("oracle+cx_oracle://user:pass@host:port/db", echo=True)
conn = engine.connect()

engine.execute(select([func.SYSTIMESTAMP().label("system timestamp")]).select_from(text("dual"))).fetchall()
engine.execute(select([text("SYSTIMESTAMP").label("system timestamp")]).select_from(text("dual"))).fetchall()

Have a nice day!

[CaselIT]

You can use literal_column for this.

[mkapi]

That works. Thanks.

Here is the working version for the people coming here in the future:

from sqlalchemy import create_engine, literal_column, select, text

engine = create_engine("oracle+cx_oracle://user:pass@host:port/db", echo=True)
conn = engine.connect()

engine.execute(select([literal_column("SYSTIMESTAMP").label("system timestamp")]).select_from(text("dual"))).fetchall()

[CaselIT]

@zzzeek maybe we could improve documentation in func to specify that to use a "function" without parentheses literal_column could be used, and in lliteral_column to indicate that it can be used to represents a function called without ()?

[zzzeek]

there is a feature in func which allows some functions to be rendered without the parenthesis as this is sometimes required. for example func.current_timestamp():

>>> from sqlalchemy import func
>>> print(func.current_timestamp())
CURRENT_TIMESTAMP

the way this works right now is that the compiler has a list of function names for which this logic applies. The Oracle dialect could be given a new rule to compile names like "SYSTIMESTAMP" in the appropriate fashion automatically. I would just want to make it an exuahstive list to start with rather than just adding one or two names. There are also some other Oracle functions that have been requested have special compilation functions including #1758 .