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?
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] <javascript:>>
> 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] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > 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.
