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 sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ac71dc78-1232-4d60-9726-a1a3e3500f8a%40www.fastmail.com.
