[sqlalchemy] Re: Error while using CAST
Hi King,
Thanks for the reply,I tried giving
table.update().values(empno = cast(table.c.empno,Integer)).execute()
but still the same the same error.
OperationalError: (OperationalError) (1292, "Truncated incorrect
INTEGER value: 'testing'") 'UPDATE test.mytable SET
`empno`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
Regards
Pravin B
On Sep 14, 8:38 pm, "King Simon-NFHD78"
wrote:
> Does this work instead:
>
> table.update().values(empno = cast(table.c.empno,Integer)).execute()
>
> ie. a bare 'empno' inside your cast expression is just referring to a python
> variable 'empno', which you've probably set to the value 'testing' at some
> other point in your code. You need the column object table.c.empno instead
>
> Hope that helps,
>
> Simon
>
> pravin battula wrote
>
>
>
>
>
>
>
>
>
> > Mike,
>
> > when i execute the below sql statement directly in the database using
> > sqlyog,it works fine but when tried with sqlalchemy it didn't.
> > update mytable set EmpMaster = cast(empno as UNSIGNED INTEGER)
>
> > On Sep 14, 8:23 pm, pravin battula wrote:
> > > Hi Mike,
>
> > > I'm using Mysql 5.0 backend
>
> > > On Sep 14, 8:20 pm, Mike Conley wrote:
>
> > > > Don't know what database you are using, but this looks like you
> > are trying
> > > > to cast the string 'testing' to an integer and the database
> > engine says you
> > > > can't do that.
>
> > > > --
> > > > Mike Conley
>
> > > > On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
> > wrote:
>
> > > > > Sorry for the spelling mistake.It shows an error as below.
> > > > > OperationalError: (OperationalError) (1292, "Truncated
> > incorrect
> > > > > INTEGER value: 'testing'") 'UPDATE test.mytable SET
> > > > > `newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
>
> > > > > On Sep 14, 6:48 pm, pravin battula
> > wrote:
> > > > > > Hi,
>
> > > > > > I'm using cast to update values in a table by issuing
> > following
> > > > > > command.
>
> > > > > > table.update().values(empno = cast(empno,Integer)).execute().
>
> > > > > > Where as empno is an string field,i'm trying to convert the
> > data from
> > > > > > empno column from string to integer and then issuing the
> > below command
> > > > > > to alter the data type of the column by issuing following
> > command.
> > > > > > alter_column(table.c.empno,type=Integer).
>
> > > > > > It shows an error as
> > > > > > OperationalError: (OperationalError) (1292, "Truncated
> > incorrect
> > > > > > INTEGER value: '1d'") 'UPDATE test.mytable SET
> > > > > > `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
>
> > > > > > Please do the needful
>
> > > > > --
> > > > > You received this message because you are subscribed to the
> > Google Groups
> > > > > "sqlalchemy" group.
> > > > > To post to this group, send email to
> > sqlalchemy@googlegroups.com.
> > > > > To unsubscribe from this group, send email to
> > > > > sqlalchemy+unsubscr...@googlegroups.com.
> > > > > For more options, visit this group at
> > > > >http://groups.google.com/group/sqlalchemy?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: Error while using CAST
Does this work instead:
table.update().values(empno = cast(table.c.empno,Integer)).execute()
ie. a bare 'empno' inside your cast expression is just referring to a python
variable 'empno', which you've probably set to the value 'testing' at some
other point in your code. You need the column object table.c.empno instead
Hope that helps,
Simon
pravin battula wrote
>
> Mike,
>
> when i execute the below sql statement directly in the database using
> sqlyog,it works fine but when tried with sqlalchemy it didn't.
> update mytable set EmpMaster = cast(empno as UNSIGNED INTEGER)
>
> On Sep 14, 8:23 pm, pravin battula wrote:
> > Hi Mike,
> >
> > I'm using Mysql 5.0 backend
> >
> > On Sep 14, 8:20 pm, Mike Conley wrote:
> >
> >
> >
> >
> >
> >
> >
> > > Don't know what database you are using, but this looks like you
> are trying
> > > to cast the string 'testing' to an integer and the database
> engine says you
> > > can't do that.
> >
> > > --
> > > Mike Conley
> >
> > > On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
> wrote:
> >
> > > > Sorry for the spelling mistake.It shows an error as below.
> > > > OperationalError: (OperationalError) (1292, "Truncated
> incorrect
> > > > INTEGER value: 'testing'") 'UPDATE test.mytable SET
> > > > `newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
> >
> > > > On Sep 14, 6:48 pm, pravin battula
> wrote:
> > > > > Hi,
> >
> > > > > I'm using cast to update values in a table by issuing
> following
> > > > > command.
> >
> > > > > table.update().values(empno = cast(empno,Integer)).execute().
> >
> > > > > Where as empno is an string field,i'm trying to convert the
> data from
> > > > > empno column from string to integer and then issuing the
> below command
> > > > > to alter the data type of the column by issuing following
> command.
> > > > > alter_column(table.c.empno,type=Integer).
> >
> > > > > It shows an error as
> > > > > OperationalError: (OperationalError) (1292, "Truncated
> incorrect
> > > > > INTEGER value: '1d'") 'UPDATE test.mytable SET
> > > > > `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
> >
> > > > > Please do the needful
> >
> > > > --
> > > > You received this message because you are subscribed to the
> Google Groups
> > > > "sqlalchemy" group.
> > > > To post to this group, send email to
> sqlalchemy@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > > > sqlalchemy+unsubscr...@googlegroups.com.
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Multiple rows returning with uselist=False under load.
On Sep 14, 2011, at 11:22 AM, Justin Levine wrote: > Howdy -- > > We're running load tests against our Pylons application, which uses > SQLAlchemy to hit an Oracle DB. Under load we're getting the following > errors: > > [Tue Sep 13 12:10:45 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ > site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows > returned with uselist=False for eagerly-loaded attribute 'entity' > [Tue Sep 13 12:10:45 2011] [error] populator(state, dict_, row) > [Tue Sep 13 12:48:46 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ > site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows > returned with uselist=False for eagerly-loaded attribute 'entity' > [Tue Sep 13 12:48:46 2011] [error] populator(state, dict_, row) > [Tue Sep 13 13:16:49 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ > site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows > returned with uselist=False for eagerly-loaded attribute 'entity' > [Tue Sep 13 13:16:49 2011] [error] populator(state, dict_, row) > > As said in the error message, I assume it's returning multiple rows > for what should be a 1-to-1 relationship, but I'm not sure why this is > only happening under load. It's happening exclusively with one of our > entities. the error means you have a statement like : select a.*, b.* from a join b on a.id=b.a_id where uselist=False means that there should be only one row in "b" that has "a_id" on it.If a second row comes in, that's the warning. You might want to ensure that the statement being emitted here isn't capable of returning more than one "b" for each "a", and also that your database has referential constraints, if possible, which prevent it as well, for example in the simple case here, "b.a_id" would get a UNIQUE constraint on it so that only one "b" could belong to an "a" at a time. Another thing would be to turn these warnings into errors using the Python warnings filter.Combined with SQL logging, you'd get a full stack trace and just prior to that the offending SQL statement. As for why load is the factor here, it's possible that it's not actually a causative factor, just one which correlates to your app accessing some particular data that's not in the expected state. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Error while using CAST
Mike,
when i execute the below sql statement directly in the database using
sqlyog,it works fine but when tried with sqlalchemy it didn't.
update mytable set EmpMaster = cast(empno as UNSIGNED INTEGER)
On Sep 14, 8:23 pm, pravin battula wrote:
> Hi Mike,
>
> I'm using Mysql 5.0 backend
>
> On Sep 14, 8:20 pm, Mike Conley wrote:
>
>
>
>
>
>
>
> > Don't know what database you are using, but this looks like you are trying
> > to cast the string 'testing' to an integer and the database engine says you
> > can't do that.
>
> > --
> > Mike Conley
>
> > On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
> > wrote:
>
> > > Sorry for the spelling mistake.It shows an error as below.
> > > OperationalError: (OperationalError) (1292, "Truncated incorrect
> > > INTEGER value: 'testing'") 'UPDATE test.mytable SET
> > > `newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
>
> > > On Sep 14, 6:48 pm, pravin battula wrote:
> > > > Hi,
>
> > > > I'm using cast to update values in a table by issuing following
> > > > command.
>
> > > > table.update().values(empno = cast(empno,Integer)).execute().
>
> > > > Where as empno is an string field,i'm trying to convert the data from
> > > > empno column from string to integer and then issuing the below command
> > > > to alter the data type of the column by issuing following command.
> > > > alter_column(table.c.empno,type=Integer).
>
> > > > It shows an error as
> > > > OperationalError: (OperationalError) (1292, "Truncated incorrect
> > > > INTEGER value: '1d'") 'UPDATE test.mytable SET
> > > > `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
>
> > > > Please do the needful
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "sqlalchemy" group.
> > > To post to this group, send email to sqlalchemy@googlegroups.com.
> > > To unsubscribe from this group, send email to
> > > sqlalchemy+unsubscr...@googlegroups.com.
> > > For more options, visit this group at
> > >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Multiple rows returning with uselist=False under load.
Howdy -- We're running load tests against our Pylons application, which uses SQLAlchemy to hit an Oracle DB. Under load we're getting the following errors: [Tue Sep 13 12:10:45 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute 'entity' [Tue Sep 13 12:10:45 2011] [error] populator(state, dict_, row) [Tue Sep 13 12:48:46 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute 'entity' [Tue Sep 13 12:48:46 2011] [error] populator(state, dict_, row) [Tue Sep 13 13:16:49 2011] [error] /opt/wgen-3p/python26/lib/python2.6/ site-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute 'entity' [Tue Sep 13 13:16:49 2011] [error] populator(state, dict_, row) As said in the error message, I assume it's returning multiple rows for what should be a 1-to-1 relationship, but I'm not sure why this is only happening under load. It's happening exclusively with one of our entities. Any help? Thanks, - Justin -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Error while using CAST
Hi Mike,
I'm using Mysql 5.0 backend
On Sep 14, 8:20 pm, Mike Conley wrote:
> Don't know what database you are using, but this looks like you are trying
> to cast the string 'testing' to an integer and the database engine says you
> can't do that.
>
> --
> Mike Conley
>
> On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
> wrote:
>
>
>
>
>
>
>
> > Sorry for the spelling mistake.It shows an error as below.
> > OperationalError: (OperationalError) (1292, "Truncated incorrect
> > INTEGER value: 'testing'") 'UPDATE test.mytable SET
> > `newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
>
> > On Sep 14, 6:48 pm, pravin battula wrote:
> > > Hi,
>
> > > I'm using cast to update values in a table by issuing following
> > > command.
>
> > > table.update().values(empno = cast(empno,Integer)).execute().
>
> > > Where as empno is an string field,i'm trying to convert the data from
> > > empno column from string to integer and then issuing the below command
> > > to alter the data type of the column by issuing following command.
> > > alter_column(table.c.empno,type=Integer).
>
> > > It shows an error as
> > > OperationalError: (OperationalError) (1292, "Truncated incorrect
> > > INTEGER value: '1d'") 'UPDATE test.mytable SET
> > > `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
>
> > > Please do the needful
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Error while using CAST
Don't know what database you are using, but this looks like you are trying
to cast the string 'testing' to an integer and the database engine says you
can't do that.
--
Mike Conley
On Wed, Sep 14, 2011 at 9:51 AM, pravin battula wrote:
> Sorry for the spelling mistake.It shows an error as below.
> OperationalError: (OperationalError) (1292, "Truncated incorrect
> INTEGER value: 'testing'") 'UPDATE test.mytable SET
> `newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
>
> On Sep 14, 6:48 pm, pravin battula wrote:
> > Hi,
> >
> > I'm using cast to update values in a table by issuing following
> > command.
> >
> > table.update().values(empno = cast(empno,Integer)).execute().
> >
> > Where as empno is an string field,i'm trying to convert the data from
> > empno column from string to integer and then issuing the below command
> > to alter the data type of the column by issuing following command.
> > alter_column(table.c.empno,type=Integer).
> >
> > It shows an error as
> > OperationalError: (OperationalError) (1292, "Truncated incorrect
> > INTEGER value: '1d'") 'UPDATE test.mytable SET
> > `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
> >
> > Please do the needful
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Error while using CAST
Sorry for the spelling mistake.It shows an error as below.
OperationalError: (OperationalError) (1292, "Truncated incorrect
INTEGER value: 'testing'") 'UPDATE test.mytable SET
`newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
On Sep 14, 6:48 pm, pravin battula wrote:
> Hi,
>
> I'm using cast to update values in a table by issuing following
> command.
>
> table.update().values(empno = cast(empno,Integer)).execute().
>
> Where as empno is an string field,i'm trying to convert the data from
> empno column from string to integer and then issuing the below command
> to alter the data type of the column by issuing following command.
> alter_column(table.c.empno,type=Integer).
>
> It shows an error as
> OperationalError: (OperationalError) (1292, "Truncated incorrect
> INTEGER value: '1d'") 'UPDATE test.mytable SET
> `newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
>
> Please do the needful
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Error while using CAST
Hi,
I'm using cast to update values in a table by issuing following
command.
table.update().values(empno = cast(empno,Integer)).execute().
Where as empno is an string field,i'm trying to convert the data from
empno column from string to integer and then issuing the below command
to alter the data type of the column by issuing following command.
alter_column(table.c.empno,type=Integer).
It shows an error as
OperationalError: (OperationalError) (1292, "Truncated incorrect
INTEGER value: '1d'") 'UPDATE test.mytable SET
`newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
Please do the needful
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] deferrable constraints
On 09/14/2011 12:25 PM, Wichert Akkerman wrote:
Constraints marked as deferrable result in a syntax error when using
SQLite. Is this deliberate, or a bug in the sqlite dialect?
As a workaround I figured I could use events to only add deferrable
constraint variants on PostgreSQL and use the non-deferrable version on
other database. That resulted in this code:
_generic_constraints = [UniqueConstraint('article_id', 'colour, 'size')]
_ deferrable_variant_constraints = [
UniqueConstraint('article_id', 'uuid', deferrable=True,
initially='DEFERRED'),
]
class Article(BaseObject):
__table_args = (_generic_constraints[0], _deferrable_constraints[0], {})
def deferrable_supported(ddl, target, bind, **kw):
"""Check if deferrable constraints are supported.
This function can be used as a callable for
:ref:`execute_if` to
only run DDL statements on databases that support deferrable constraints.
"""
return bind.dialect == 'postgresql'
def deferrable_not_supported(ddl, target, bind, **kw):
"""Check if deferrable constraints are not supported.
This function can be used as a callable for
:ref:`execute_if` to
only run DDL statements on databases that do not support deferrable
constraints.
"""
return not deferrable_supported(ddl, target, bind, **kw)
for constraint in _generic_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_not_supported))
for constraint in _deferrable_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_supported))
But this fails as well since SQLite does not support ALTER TABLE .. ADD
CONSTRAINT. Is there another way to create deferrable constraints only
on databases that support it?
Wichert.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] deferrable constraints
Constraints marked as deferrable result in a syntax error when using SQLite. Is this deliberate, or a bug in the sqlite dialect? Regards, Wichert. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
