Hi thanks for the quick reply - this works just fine. The whitespace appears to be okay. It also helped solving the next issue of calling functions with method-like dot-notation like in: https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stastext-geography-data-type?view=sql-server-ver15
Am Dienstag, 26. November 2019 17:33:21 UTC+1 schrieb Mike Bayer: > > > > On Tue, Nov 26, 2019, at 10:45 AM, Sebastian Eckweiler wrote: > > Hi there, > > I'm trying to do with Microsoft SQL Server what this snippet: > > https://docs.sqlalchemy.org/en/13/core/custom_types.html#types-sql-value-processing > does for PostGIS. > > Unfortunately in MSSQL "ST_GeomFromText" does not exist in the default > function namespace, but is a static method of the geography type and is > expected to be called as > > geography::STGeomFromText > > (see: > https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stgeomfromtext-geography-data-type?view=sql-server-ver15 > ) > > I tried several things in "bind_expression" but didn't manage to get > sqlalchemy to compile anything into "geography::STGeomFromText". > When using "func.geography.STGeomFromText" this is ends up in SQL with a > plain "." as a separation. > All attempts at injecting the double colons literally failed because the > prefix is then wrapped in quotes. > > Am I missing something here or do I have to (e.g.) use a custom dialect > for that? > > > the double colon does not appear to be necessarily a "separator" but > various forum posts and whatnot seem to suggest it is some kind of > namespace qualifier, it seems to be referred towards heavily in SQL Server > 2000 and was somehow changed in 2005, but for this particular extension > their current documentation is still referring towards it. > > in the official docs for 2019 we see it called the "scope resolution > operator": > > > https://docs.microsoft.com/en-us/sql/t-sql/language-elements/scope-resolution-operator-transact-sql?view=sql-server-ver15 > > it's not really clear how this should be implemented in SQLAlchemy, as I'm > not really sure "double colon" outright replaces the dot for function > namespace qualifiers. > > If SQL Server can tolerate whitespace between the :: and the name, this > recipe will work for now: > > from sqlalchemy.sql.expression import UnaryExpression > from sqlalchemy.sql import operators > from sqlalchemy import func > > > def geometry(fn): > return UnaryExpression(fn, operator=operators.custom_op("geometry::")) > > > expr = geometry(func.STGeomFromText()) > > print(expr) > > > generates: > > geometry:: STGeomFromText() > > > if not then we need to build some custom @compiles for that right now. > > longer term I think we either need a new namespace added to the SQL Server > dialect or we need additional options on the func. namespace to simulate > this effect. > > > > > > > Thanks & cheers > Sebastian > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/37eb37ce-92e8-4eb4-b880-75aa86dfdbb7%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/37eb37ce-92e8-4eb4-b880-75aa86dfdbb7%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d6ca4a8e-64f4-4ef5-8e22-fd4219c36769%40googlegroups.com.
