On Fri, Aug 3, 2018 at 11:18 AM, Jürg Rast <jue...@gmail.com> 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.
>
>
>
> 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 <jue...@gmail.com> 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 <jue...@gmail.com> 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 sqlalchemy+...@googlegroups.com.
>> >>> To post to this group, send email to sqlal...@googlegroups.com.
>> >>> 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 sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
