On Fri, Aug 3, 2018 at 11:34 AM, Mike Bayer <[email protected]> wrote: > On Fri, Aug 3, 2018 at 11:18 AM, Jürg Rast <[email protected]> wrote: >> Thank you very much! >> >> I tried version 1 with hybrid properties which look something like this: >> >> class Quetschmass(Base): >> __tablename__ = 'MyTable' >> >> >> ID = Column(sa.Integer, primary_key=True) >> serial_number = Column(types.NCHAR) >> >> @hybrid_property >> def SN(self): >> return int(self.serial_number ) >> >> >> @SN.setter >> def SN(self, value): >> self.serial_number = str(value) >> >> >> @SN.expression >> def SN(self): >> return sa.func.CONVERT(sa.literal_column('INT'), self.serial_number >> ) >> >> Which seems to work as expected for most cases: >> >> obj= MyTable(SN=13) >> session.add(obj) >> session.commit() >> >> # Resulting SQL: >> # INSERT INTO [MyTable] ([Head_SN]) OUTPUT inserted.[ID] VALUES (?) >> >> objects = session.query(MyTable).filter(MyTable.SN < 100) >> >> # Resulting SQL: >> # SELECT [MyTable].[ID] AS [MyTable_ID], [MyTable].[serial_number] AS >> [MyTable_serial_number] >> # FROM [MyTable] >> # WHERE CONVERT(INT, [MyTable].[serial_number]) < ? >> >> session.query(Quetschmass).filter(Quetschmass.SN == >> 13).delete(synchronize_session='fetch') >> session.commit() >> >> # Resulting SQL: >> # SELECT [MyTable].[ID] AS [MyTable_ID] >> # FROM [MyTable] >> # WHERE CONVERT(INT, [MyTable].[serial_number]) = ? >> # >> # DELETE FROM [MyTable] WHERE CONVERT(INT, [MyTable].[serial_number]) = ? >> >> >> The only question is about the delete(synchronize_session='fetch'). Why is >> this required? > > > It seems like you're talking about the "fetch" and that would mean the > default of "evaluate" didn't work so I'd assume this was hitting you > in the expression evaluator. If you don't want that SELECT there, I > would instead say "synchronize_session=False". The expression > evaluator is attempting to locate in the current Session objects that > meet your criteria, and for that it is attempting to evaluate your > criteria in Python. It doesn't know what to do with the CONVERT > function so it falis.
Sorry I hit send by accident. Was also going to say, the evaluator is not currently extensible, unfortunately. issue: https://bitbucket.org/zzzeek/sqlalchemy/issues/3162/extensibility-for-orm-evaluator refers to this being possible, and I've added this use case as a comment. > >> >> >> >> Am Freitag, 3. August 2018 15:35:40 UTC+2 schrieb Mike Bayer: >>> >>> On Fri, Aug 3, 2018 at 5:29 AM, Jürg Rast <[email protected]> wrote: >>> > bind_expression() seems to be applied on the wrong side of the where >>> > clause. >>> > For example, my bind_expression function looks like this: >>> > >>> > def bind_expression(self, val): >>> > return sa.func.CONVERT(sa.literal_column('INT'), val) >>> > >>> > If I use this in a query like >>> > >>> > engine.execute(table.select(table.c.serial_number > >>> > 500).limit(1)).fetchone()) >>> > >>> > The following SQL is executed: >>> > >>> > SELECT TOP 1 [MyTable].[ID], CONVERT(INT, [MyTable].[serial_number ]) AS >>> > [serial_number ] >>> > FROM [MyTable] >>> > WHERE [MyTable].[serial_number ] > CONVERT(INT, ?) >>> > >>> > So, the CONVERT is applied to the value (in my case 500) and not to the >>> > colum ([MyTable].[serial_number] in my case). I expected something like >>> > >>> > SELECT TOP 1 [MyTable].[ID], CONVERT(INT, [MyTable].[serial_number ]) AS >>> > [serial_number ] >>> > FROM [MyTable] >>> > WHERE CONVERT(INT, [MyTable].[serial_number ]) > ? >>> > >>> > Is this also possible? >>> >>> the bind/column/result methods of types are only used for the link >>> between Python values and SQL code. They don't apply to SQL elements >>> that are within a statement that don't link to a Python value being >>> passed in such as a column. >>> >>> Options for having a particular column always have a CONVERT function >>> around it no matter where it is include: >>> >>> 1. when you place the column in your query, don't use the variable >>> that refers to the column (e.g. MyModel.column), use the "convert" >>> function version of it (eg. MyModel.converted_column). Options for >>> this with the ORM include using column_property() as well as hybrids. >>> By far the simplest way to do this. >>> >>> 2. create a custom type that also overrides comparison methods, such >>> that the column expression part of it receives the CONVERT. This is >>> a fairly intricate way to travel in this case since you need that >>> function probably on a lot of comparisons (__lt__, __le__, __eq__, >>> etc) and there may be some edge cases that make certain things more >>> challenging when doing this (e.g. the conversion happens when you >>> don't want it to, kinds of situations). An introduction is at >>> >>> http://docs.sqlalchemy.org/en/latest/core/custom_types.html#redefining-and-creating-new-operators. >>> >>> 3. apply statement processing using a visitor. this would be even >>> more complicated than #2 so probably not worth it. >>> >>> >>> > >>> > >>> > Am Dienstag, 31. Juli 2018 18:59:39 UTC+2 schrieb Mike Bayer: >>> >> >>> >> A WHERE clause is going to be binding your parameter into the SQL so >>> >> you >>> >> need to use the bind_expression() method as well, in a manner very >>> >> similar >>> >> to how you are using column_expression(). >>> >> >>> >> On Tue, Jul 31, 2018, 12:45 PM Jürg Rast <[email protected]> wrote: >>> >>> >>> >>> I have to work with a old database which stores almost all information >>> >>> as >>> >>> strings (VARCHAR, NCHAR). To create a usable interface, I tought of >>> >>> creating >>> >>> a UserDefinedType or a TypeDecorator to convert the values which are >>> >>> fed to >>> >>> the database or loaded from the database. >>> >>> >>> >>> Both versions seem to work when used like this: >>> >>> >>> >>> class IntegerString(types.UserDefinedType): >>> >>> >>> >>> >>> >>> def get_col_spec(self): >>> >>> return 'NCHAR' >>> >>> >>> >>> >>> >>> def column_expression(self, col): >>> >>> return sa.func.CONVERT(sa.literal_column('INT'), col) >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> class TestType(types.TypeDecorator): >>> >>> >>> >>> >>> >>> impl = types.NCHAR >>> >>> >>> >>> >>> >>> def process_bind_param(self, value, dialect): >>> >>> return str(value) >>> >>> >>> >>> >>> >>> def process_result_value(self, value, dialect): >>> >>> return int(value) >>> >>> >>> >>> >>> >>> Where the IntergerString works at the database level and the TestType >>> >>> works at the application level. However, what is missing is type >>> >>> conversion >>> >>> if the column is used in Where Clauses. Is there a method to apply the >>> >>> type >>> >>> conversion at the database level for where clauses? >>> >>> >>> >>> >>> >>> -- >>> >>> 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 post to this group, send email to [email protected]. >>> >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> >>> For more options, visit https://groups.google.com/d/optout. >>> > >>> > -- >>> > 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 post to this group, send email to [email protected]. >>> > Visit this group at https://groups.google.com/group/sqlalchemy. >>> > For more options, visit https://groups.google.com/d/optout. >> >> -- >> 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 post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
