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.
