Re: [sqlalchemy] Decimals generated as strings in query
Dear Michael,
Thanks a lot for your reply.
In trying to narrow the problem as much as possible, I missed something
important in my example. I'm actually doing an UPDATE, not a SELECT. When I
wrote 'I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
filter expression failed' I meant that it didn't work in an update
scenario. In a select scenario it works ok. This is what I should have
wrote in my bug_test.py:
amount = decimal.Decimal('0.0001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + sqlalchemy.cast(amount,
sqlalchemy.Numeric(precision=16, scale=8)) = Balance.full_balance
)
values = {}
values[Balance.available_balance] = Balance.available_balance + amount
row_count = query.update(values)
print row_count, rows updated
and the error I get is 'sqlalchemy.exc.InvalidRequestError: Could not
evaluate current criteria in Python. Specify 'fetch' or False for the
synchronize_session parameter.'. This is not even getting to MySQL.
Thanks a lot
On Monday, April 13, 2015 at 2:46:14 PM UTC-3, Michael Bayer wrote:
On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
Dear all,
I have a table that has 2 numeric columns, and I'm writing a query that
performs some arithmetic on the filter clause between those columns and a
Decimal. The problem that I'm facing is that I don't get any results at
all. After a while I realized that the SQL statement getting generated is
dumping Decimals as strings, and when strings are involved in a numeric
expression they get converted to floats. So, my query is not returning
anything at all due to float representation limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
filter expression failed. Can anyone help me in getting a cast over a query
parameter to work in a filter expression ?
this is driver stuff. If you change the query to see what you're getting:
query = session.query(Balance.available_balance + amount,
Balance.full_balance)
you can see there's some floating point noise in there, not to mention
we're even getting the value back as a floating point:
Col (u'anon_1', u'balance_full_balance')
2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
(3.0004e-08, Decimal('3E-8'))
I'm getting the same result with MySQL-Python, PyMySQL, and
Mysql-connector. The issue is definitely in the drivers however, the code
below produces no result for all three drivers:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
SELECT balance.available_balance + %s AS anon_1,
balance.full_balance AS balance_full_balance FROM balance
WHERE balance.available_balance + %s = balance.full_balance,
(amount, amount))
print cursor.fetchall()
If I embed the number 000.1 as is into the query without using a
parameter, then it works, as it does on the command line.
Looking into PyMySQL since that's the driver I'm most familiar with, if we
send a Decimal it is doing this:
SELECT balance.available_balance + 1E-8 AS anon_1, balance.full_balance AS
balance_full_balance FROM balance WHERE balance.available_balance + 1E-8 =
balance.full_balance
What is interesting is that these values as passed in an INSERT *are*
working correctly.Which means really, this is a MySQL bug; I can prove
it at the command line.
First, we illustrate that scientific notation *is* accepted directly by
MySQL:
mysql insert into balance (full_balance, available_balance) values (3E-8,
2E-8);
Query OK, 1 row affected (0.00 sec)
values go in just fine (note I increased the scale in the table here,
hence the two trailing 0's):
mysql select * from balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
but in the WHERE clause, *it fails*:
mysql select * from balance where available_balance + 1E-8 =
full_balance;
Empty set (0.00 sec)
writing out the whole value, *it succeeds*:
mysql select * from balance where available_balance + 0.0001 =
full_balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
we can see that *MySQL itself is doing floating point*, so that's really
the bug here:
mysql select available_balance + 1E-8 from balance;
++
| available_balance + 1E-8 |
++
| 0.00030004 |
++
1 row in set (0.00 sec)
We can in fact
Re: [sqlalchemy] Decimals generated as strings in query
On 4/13/15 2:25 PM, Gabriel Becedillas wrote:
Dear Michael,
Thanks a lot for your reply.
In trying to narrow the problem as much as possible, I missed
something important in my example. I'm actually doing an UPDATE, not a
SELECT. When I wrote 'I tried casting my decimals using
sqlalcheme.cast(..., sqlalchemy.Numeric(precision=16, scale=8)) but
that didn't work because the filter expression failed' I meant that it
didn't work in an update scenario. In a select scenario it works ok.
This is what I should have wrote in my bug_test.py:
amount = decimal.Decimal('0.0001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + sqlalchemy.cast(amount,
sqlalchemy.Numeric(precision=16, scale=8)) = Balance.full_balance
)
values = {}
values[Balance.available_balance] = Balance.available_balance + amount
row_count = query.update(values)
print row_count, rows updated
and the error I get is 'sqlalchemy.exc.InvalidRequestError: Could not
evaluate current criteria in Python. Specify 'fetch' or False for the
synchronize_session parameter.'. This is not even getting to MySQL.
for query.update() you usually want to send synchronize_session=False.
also the cast() needs to be Decimal(), not Numeric(). Will not work
with Numeric().
Thanks a lot
On Monday, April 13, 2015 at 2:46:14 PM UTC-3, Michael Bayer wrote:
On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
Dear all,
I have a table that has 2 numeric columns, and I'm writing a
query that performs some arithmetic on the filter clause between
those columns and a Decimal. The problem that I'm facing is that
I don't get any results at all. After a while I realized that the
SQL statement getting generated is dumping Decimals as strings,
and when strings are involved in a numeric expression they get
converted to floats. So, my query is not returning anything at
all due to float representation limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work
because the filter expression failed. Can anyone help me in
getting a cast over a query parameter to work in a filter
expression ?
this is driver stuff. If you change the query to see what you're
getting:
query = session.query(Balance.available_balance + amount,
Balance.full_balance)
you can see there's some floating point noise in there, not to
mention we're even getting the value back as a floating point:
Col (u'anon_1', u'balance_full_balance')
2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
(3.0004e-08, Decimal('3E-8'))
I'm getting the same result with MySQL-Python, PyMySQL, and
Mysql-connector. The issue is definitely in the drivers however,
the code below produces no result for all three drivers:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
SELECT balance.available_balance + %s AS anon_1,
balance.full_balance AS balance_full_balance FROM balance
WHERE balance.available_balance + %s =
balance.full_balance,
(amount, amount))
print cursor.fetchall()
If I embed the number 000.1 as is into the query without using
a parameter, then it works, as it does on the command line.
Looking into PyMySQL since that's the driver I'm most familiar
with, if we send a Decimal it is doing this:
SELECT balance.available_balance + 1E-8 AS anon_1,
balance.full_balance AS balance_full_balance FROM balance WHERE
balance.available_balance + 1E-8 = balance.full_balance
What is interesting is that these values as passed in an INSERT
*are* working correctly.Which means really, this is a MySQL
bug; I can prove it at the command line.
First, we illustrate that scientific notation *is* accepted
directly by MySQL:
mysql insert into balance (full_balance, available_balance)
values (3E-8, 2E-8);
Query OK, 1 row affected (0.00 sec)
values go in just fine (note I increased the scale in the table
here, hence the two trailing 0's):
mysql select * from balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
but in the WHERE clause, *it fails*:
mysql select * from balance where available_balance + 1E-8 =
full_balance;
Empty set (0.00 sec)
writing out the whole value, *it succeeds*:
mysql select * from balance where available_balance + 0.0001
= full_balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 |
Re: [sqlalchemy] Decimals generated as strings in query
Thanks a lot Mike.
Although I did see the Specify 'fetch' or False for the
synchronize_session parameter in the error I associated that with a
connection-time parameter and I thought 'no.. that has nothing to do with
my problem'.
My bad.
Thanks a lot.
On Mon, Apr 13, 2015 at 3:31 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 4/13/15 2:25 PM, Gabriel Becedillas wrote:
Dear Michael,
Thanks a lot for your reply.
In trying to narrow the problem as much as possible, I missed something
important in my example. I'm actually doing an UPDATE, not a SELECT. When I
wrote 'I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
filter expression failed' I meant that it didn't work in an update
scenario. In a select scenario it works ok. This is what I should have
wrote in my bug_test.py:
amount = decimal.Decimal('0.0001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + sqlalchemy.cast(amount,
sqlalchemy.Numeric(precision=16, scale=8)) = Balance.full_balance
)
values = {}
values[Balance.available_balance] = Balance.available_balance + amount
row_count = query.update(values)
print row_count, rows updated
and the error I get is 'sqlalchemy.exc.InvalidRequestError: Could not
evaluate current criteria in Python. Specify 'fetch' or False for the
synchronize_session parameter.'. This is not even getting to MySQL.
for query.update() you usually want to send synchronize_session=False.
also the cast() needs to be Decimal(), not Numeric(). Will not work with
Numeric().
Thanks a lot
On Monday, April 13, 2015 at 2:46:14 PM UTC-3, Michael Bayer wrote:
On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
Dear all,
I have a table that has 2 numeric columns, and I'm writing a query that
performs some arithmetic on the filter clause between those columns and a
Decimal. The problem that I'm facing is that I don't get any results at
all. After a while I realized that the SQL statement getting generated is
dumping Decimals as strings, and when strings are involved in a numeric
expression they get converted to floats. So, my query is not returning
anything at all due to float representation limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because
the filter expression failed. Can anyone help me in getting a cast over a
query parameter to work in a filter expression ?
this is driver stuff. If you change the query to see what you're getting:
query = session.query(Balance.available_balance + amount,
Balance.full_balance)
you can see there's some floating point noise in there, not to mention
we're even getting the value back as a floating point:
Col (u'anon_1', u'balance_full_balance')
2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
(3.0004e-08, Decimal('3E-8'))
I'm getting the same result with MySQL-Python, PyMySQL, and
Mysql-connector. The issue is definitely in the drivers however, the code
below produces no result for all three drivers:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
SELECT balance.available_balance + %s AS anon_1,
balance.full_balance AS balance_full_balance FROM balance
WHERE balance.available_balance + %s = balance.full_balance,
(amount, amount))
print cursor.fetchall()
If I embed the number 000.1 as is into the query without using a
parameter, then it works, as it does on the command line.
Looking into PyMySQL since that's the driver I'm most familiar with, if
we send a Decimal it is doing this:
SELECT balance.available_balance + 1E-8 AS anon_1, balance.full_balance
AS balance_full_balance FROM balance WHERE balance.available_balance + 1E-8
= balance.full_balance
What is interesting is that these values as passed in an INSERT *are*
working correctly.Which means really, this is a MySQL bug; I can prove
it at the command line.
First, we illustrate that scientific notation *is* accepted directly by
MySQL:
mysql insert into balance (full_balance, available_balance) values
(3E-8, 2E-8);
Query OK, 1 row affected (0.00 sec)
values go in just fine (note I increased the scale in the table here,
hence the two trailing 0's):
mysql select * from balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
but in the WHERE clause, *it fails*:
mysql select * from balance where available_balance + 1E-8 =
full_balance;
Empty set (0.00 sec)
writing out the whole value, *it succeeds*:
mysql select * from balance where available_balance + 0.0001 =
full_balance;
++--+---+
|
Re: [sqlalchemy] Decimals generated as strings in query
On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
Dear all,
I have a table that has 2 numeric columns, and I'm writing a query
that performs some arithmetic on the filter clause between those
columns and a Decimal. The problem that I'm facing is that I don't get
any results at all. After a while I realized that the SQL statement
getting generated is dumping Decimals as strings, and when strings are
involved in a numeric expression they get converted to floats. So, my
query is not returning anything at all due to float representation
limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work
because the filter expression failed. Can anyone help me in getting a
cast over a query parameter to work in a filter expression ?
this is driver stuff. If you change the query to see what you're getting:
query = session.query(Balance.available_balance + amount,
Balance.full_balance)
you can see there's some floating point noise in there, not to mention
we're even getting the value back as a floating point:
Col (u'anon_1', u'balance_full_balance')
2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
(3.0004e-08, Decimal('3E-8'))
I'm getting the same result with MySQL-Python, PyMySQL, and
Mysql-connector. The issue is definitely in the drivers however, the
code below produces no result for all three drivers:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
SELECT balance.available_balance + %s AS anon_1,
balance.full_balance AS balance_full_balance FROM balance
WHERE balance.available_balance + %s = balance.full_balance,
(amount, amount))
print cursor.fetchall()
If I embed the number 000.1 as is into the query without using a
parameter, then it works, as it does on the command line.
Looking into PyMySQL since that's the driver I'm most familiar with, if
we send a Decimal it is doing this:
SELECT balance.available_balance + 1E-8 AS anon_1, balance.full_balance
AS balance_full_balance FROM balance WHERE balance.available_balance +
1E-8 = balance.full_balance
What is interesting is that these values as passed in an INSERT *are*
working correctly.Which means really, this is a MySQL bug; I can
prove it at the command line.
First, we illustrate that scientific notation *is* accepted directly by
MySQL:
mysql insert into balance (full_balance, available_balance) values
(3E-8, 2E-8);
Query OK, 1 row affected (0.00 sec)
values go in just fine (note I increased the scale in the table here,
hence the two trailing 0's):
mysql select * from balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
but in the WHERE clause, *it fails*:
mysql select * from balance where available_balance + 1E-8 = full_balance;
Empty set (0.00 sec)
writing out the whole value, *it succeeds*:
mysql select * from balance where available_balance + 0.0001 =
full_balance;
++--+---+
| id | full_balance | available_balance |
++--+---+
| 2 | 0.000300 | 0.000200 |
++--+---+
1 row in set (0.00 sec)
we can see that *MySQL itself is doing floating point*, so that's really
the bug here:
mysql select available_balance + 1E-8 from balance;
++
| available_balance + 1E-8 |
++
| 0.00030004 |
++
1 row in set (0.00 sec)
We can in fact make it work with a CAST. However! crazytown time. Even
though NUMERIC and DECIMAL are equivalent in MySQL, cast will *not*
accept NUMERIC (note SQLAlchemy only warns on these and only as of 1.0 I
think):
mysql select available_balance + CAST(1E-8 AS NUMERIC) from balance;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'NUMERIC) from balance' at line 1
But it *will* accept DECIMAL:
mysql select available_balance + CAST(1E-8 AS DECIMAL) from balance;
+---+
| available_balance + CAST(1E-8 AS DECIMAL) |
+---+
| 0.000200 |
+---+
1 row in set (0.00 sec)
So there's our answer:
from sqlalchemy import cast, DECIMAL
amount = decimal.Decimal('0.0001')
query = session.query(Balance.available_balance + cast(amount,
DECIMAL()), Balance.full_balance)
query = query.filter(
Balance.available_balance + cast(amount, DECIMAL()) =
Balance.full_balance
)
SELECT balance.available_balance + CAST(%s AS DECIMAL) AS anon_1,
Re: [sqlalchemy] polymorphic inheritance and unique constraints
On 4/13/15 4:59 PM, Richard Gerd Kuesters | Pollux Automation wrote:
well, this didn't work with upstream 1.0 - sorry, I was in another
project and couldn't test it myself.
you're not doing the same thing this user was doing in any case...
Traceback (most recent call last):
File database_test.py, line 46, in module
from plx.db.core import *
File ../src/plx/db/core.py, line 901, in module
UniqueConstraint(ContainerInstance.batch_id,
ContainerAggregation.container_descriptor_id,)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2464, in __init__
ColumnCollectionMixin.__init__(self, *columns,
_autoattach=_autoattach)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2393, in __init__
self._check_attach()
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2429, in _check_attach
table.description)
sqlalchemy.exc.ArgumentError: Column(s)
'container_aggregation.fk_container_descriptor_id' are not part of
table 'container_instance'.
I got sqlalchemy from git, today.
sqlalchemy.__version__
'1.0.0'
container_aggretation is a subclass of container_instance. I'm not
using concrete inheritance here, may this be the problem?
anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1.
cheers,
richard.
On 03/24/2015 08:49 PM, Michael Bayer wrote:
are these two separate constraints? I just looked and it seems like they are
distinct.
I just added a fix to 1.0 because someone was hacking around something similar
to this.
The easiest way to get these for the moment is just to create the
UniqueConstraint outside of the class definition.
class Foo(Base):
# …
class Bar(Foo):
# …
UniqueConstraint(Bar.x, Foo.y)
that way all the columns are set up, should just work.
Richard Gerd Kuesters | Polluxrich...@pollux.com.br wrote:
well, understanding better the docs for column conflicts, can i use a
declared_attr in a unique constraint? if yes, my problem is solved :)
On 03/24/2015 10:33 AM, Michael Bayer wrote:
Richard Gerd Kuesters | Pollux
rich...@pollux.com.br
wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique along
either types. is there a way to move this column to the parent and use a
constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts
.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things), this
works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is
in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is
in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
# since both child uses bar_id, why
Re: [sqlalchemy] polymorphic inheritance and unique constraints
well, this didn't work with upstream 1.0 - sorry, I was in another
project and couldn't test it myself.
Traceback (most recent call last):
File database_test.py, line 46, in module
from plx.db.core import *
File ../src/plx/db/core.py, line 901, in module
UniqueConstraint(ContainerInstance.batch_id,
ContainerAggregation.container_descriptor_id,)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2464, in __init__
ColumnCollectionMixin.__init__(self, *columns, _autoattach=_autoattach)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2393, in __init__
self._check_attach()
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2429, in _check_attach
table.description)
sqlalchemy.exc.ArgumentError: Column(s)
'container_aggregation.fk_container_descriptor_id' are not part of table
'container_instance'.
I got sqlalchemy from git, today.
sqlalchemy.__version__
'1.0.0'
container_aggretation is a subclass of container_instance. I'm not using
concrete inheritance here, may this be the problem?
anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1.
cheers,
richard.
On 03/24/2015 08:49 PM, Michael Bayer wrote:
are these two separate constraints? I just looked and it seems like they are
distinct.
I just added a fix to 1.0 because someone was hacking around something similar
to this.
The easiest way to get these for the moment is just to create the
UniqueConstraint outside of the class definition.
class Foo(Base):
# …
class Bar(Foo):
# …
UniqueConstraint(Bar.x, Foo.y)
that way all the columns are set up, should just work.
Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote:
well, understanding better the docs for column conflicts, can i use a
declared_attr in a unique constraint? if yes, my problem is solved :)
On 03/24/2015 10:33 AM, Michael Bayer wrote:
Richard Gerd Kuesters | Pollux
rich...@pollux.com.br
wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique along
either types. is there a way to move this column to the parent and use a
constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts
.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things), this
works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is
in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is
in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
# since both child uses bar_id, why not having it on the parent?
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class
Re: [sqlalchemy] GeoAlchemy2 mutation tracking on Geometry Column?
On 4/9/15 1:38 PM, joe meiring wrote: Is there some way to implement mutation tracking on a sqlalchemy2 Geometry (POLYGON) Column? Can I just wrap it in a MutableDict can I? I haven't worked with geoalchemy in many years, I'd advise just give it a try and/or dig into geoalchemy's source to see if that's feasible. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Decimals generated as strings in query
Dear all,
I have a table that has 2 numeric columns, and I'm writing a query that
performs some arithmetic on the filter clause between those columns and a
Decimal. The problem that I'm facing is that I don't get any results at
all. After a while I realized that the SQL statement getting generated is
dumping Decimals as strings, and when strings are involved in a numeric
expression they get converted to floats. So, my query is not returning
anything at all due to float representation limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
filter expression failed. Can anyone help me in getting a cast over a query
parameter to work in a filter expression ?
I'm attaching a sample to reproduce the issue.
Thanks a lot.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
import sqlalchemy
from sqlalchemy import orm
from sqlalchemy.ext import declarative
import decimal
Base = declarative.declarative_base()
engine = None
session_factory = None
class Balance(Base):
__tablename__ = balance
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
full_balance = sqlalchemy.Column(sqlalchemy.Numeric(precision=16, scale=8), nullable=False)
available_balance = sqlalchemy.Column(sqlalchemy.Numeric(precision=16, scale=8), nullable=False)
def initialize():
global engine
global session_factory
engine = sqlalchemy.create_engine(mysql+mysqldb://develuser:develpassword@localhost/bugtest, pool_recycle=3600)
session_factory = orm.sessionmaker()
session_factory.configure(bind=engine)
def create_schema():
Base.metadata.create_all(engine)
def main():
# Manually execute these 3 statements in MySQL:
# CREATE DATABASE bugtest;
# CREATE USER 'develuser'@'localhost' IDENTIFIED BY 'develpassword';
# GRANT ALL ON bugtest.* TO 'develuser'@'localhost';
initialize()
create_schema()
# Insert a new balance.
session = session_factory()
db_balance = Balance()
db_balance.full_balance = decimal.Decimal('0.0003')
db_balance.available_balance = decimal.Decimal('0.0002')
session.add(db_balance)
session.commit()
# Query.
amount = decimal.Decimal('0.0001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + amount = Balance.full_balance
)
print len(query.all()), should be 0
if __name__ == __main__:
main()
