Alexander
Pony uses single-table inheritance, when all subclasses share the same table as defined in base class. I'm wondering how you was able to have _table_ in Student at all, I thought it should lead to error during generate_mapping call
At this moment you need to define created_at, etc. in all classes separately, maybe later we can implement some kind of abstract class inheritance for this
Anonymous
Looking forward to your implement. Thanks.
Anonymous
Actually, it did raise an error. I want to make this question clear to you, so i kept the _table_ field.
Lucky
pony.orm.core.TransactionError: drop_all_tables() cannot be called inside of db_session
How can I leave the session?
Lucky
Ah, got it.
from pony.orm import db_session
db_session.__exit__()
db.drop_all_tables()
db_session.__enter__()
# resume code
Lucky
I try regernerating them with db.generate_mapping(create_tables=True)
Lucky
but get pony.orm.core.BindingError: Mapping was already generated
Lucky
All I want is to have it kill all tables and recreate them cleanly.
Nikolay
I try regernerating them with db.generate_mapping(create_tables=True)
db.create_tables()? It's what generate_mapping uses
Lucky
I got it working with
db.execute('TRUNCATE TABLE "' + '", "'.join(db.schema.tables) + '" CASCADE')
Lucky
db.create_tables()? It's what generate_mapping uses
Thanks, that actually did work.
db_session.__exit__()
db.drop_all_tables(with_all_data=True)
db.create_tables()
db_session.__enter__()
Lucky
text = orm.select(text for text in Text if text.created_at < old_rules_read['date']).order_by(orm.desc(Text.created_at)).limit(1).get()
AttributeError: 'QueryResult' object has no attribute 'get'
Lucky
How to I get a resut of a select(…)?
Nikolay
How to I get a resut of a select(…)?
.get should work. There's also .first and select(...)[:]
Lucky
This feels so dirty list(orm.select(…).order_by(…).limit(1))[0]
and definitly not pythonic.
Nikolay
It should, right?
Lucky
strange
Nikolay
strange
.limit(2) can also be written as [:2] so i guess it's treated as getting the query result same as [:]
Nikolay
.first() should be equivalent to .limit(1).get() except it will return None instead of raising if nothing is found
Lucky
Entity.get(foo="bar") does also return None.
Nikolay
I guess i'm confusing it with Entity[id]
Lucky
class Chat(db.Entity):
id = PrimaryKey(int, size=64)
…
states = Set(State)
texts = Set(Text)
class Text(db.Entity):
id = PrimaryKey(int, auto=True, unsigned=True)
text = Required(str)
chat = Required('Chat', column='chat_id')
…
created_at = Required(datetime)
updated_at = Required(datetime)
What is the best way to get from having a chat object (`chat = Chat.get(id=123)`)
to get the most recent text (`chat.texts`, the one with the hightest `chat.texts.updated_at`)?
orm.select(t for t in Text if t.chat == chat).order_by(orm.desc(Text.updated_at)).first()?
Lucky
I was playing around with chat.texts, which is a Set() but this seems to now allow limit or order_by
Lucky
I mean as I need only one element anyway I could use Set.__next__(), but am still missing the order_by
Alexander
chat.texts.select().order_by(lambda text: desc(text.updated_at)).first()
Alexander
Ah, got it.
from pony.orm import db_session
db_session.__exit__()
db.drop_all_tables()
db_session.__enter__()
# resume code
It looks incorrect. Why you were inside db_session before?
Lucky
It looks incorrect. Why you were inside db_session before?
Because I'm called from code which already has a session open.
Lucky
which is a library, pony_up
Lucky
technically it is under my control to change that code as it's my own library,
but changing that might break compatibility for other users.
Anonymous
@metaprogrammer hey Alexander! We talked briefly awhile back about PostgresSql table partitions. Is there a way (I assume not) to avoid Pony requiring a primary key? You can't use unique constraints or primary keys with table list partitioning because Postgres requires them to be part of the partition, which defeats the purpose of partitions by value.
Anonymous
I will likely have to review to raw sql for this =(
Jim
Hi,
I have the following code I want to reuse across entities but no related entities (no inheritance).
_bgColor = Optional(int, size=32, unsigned=True)
@property
def bgColor(self):
return QColor(self._bgColor) if self._bgColor else None
@bgColor.setter
def bgColor(self, value):
if isinstance(value, QColor):
self._bgColor = value.rgba()
elif isinstance(value, str):
self._bgColor = QColor(value).rgba()
elif isinstance(value, int):
self._bgColor = value
Jim
mixins implies many code for each new field
class ColorMixin:
def reader(self, value):
return QColor(value) if value else None
def setter(self, value):
if isinstance(value, QColor):
return = value.rgba()
elif isinstance(value, str):
return = QColor(value).rgba()
elif isinstance(value, int):
return = value
class MyClass(db.Entity, ColorMixin):
_bgColor = Optional(int, size=32, unsigned=True)
@property
def bgColor(self):
return self.reader(self._bgColor)
@bgColor.setter
def bgColor(self, value):
res = self.setter(value)
if res:
self._bgColor = res
do you see any shorter way to do it ? like create a custom field type ?
Alexander
You can move property into mixin (and probably inline reader/setter into the propery geter/setter):
```class ColorMixin:
def reader(self, value):
return QColor(value) if value else None
def setter(self, value):
if isinstance(value, QColor):
return = value.rgba()
elif isinstance(value, str):
return = QColor(value).rgba()
elif isinstance(value, int):
return = value
@property
def bgColor(self):
return self.reader(self._bgColor)
@bgColor.setter
def bgColor(self, value):
res = self.setter(value)
if res:
self._bgColor = res
class MyClass(db.Entity, ColorMixin):
_bgColor = Optional(int, size=32, unsigned=True)
```
Jim
and redefining the descriptor ?
class OptionalColor(Optional):
def __get__(self, obj, objtype):
value = super().__get__(obj, obtype)
return QColor(value) if value else Noneis it something that could possibly work ?
Alexander
I'm not sure it is a good idea. Inside a query you probably want to work with raw color value. because you can't make QColor instance inside SQL. It is better to have it as a seprate value that you cannot use inside a query, but can access when object is already loaded into Python
Jim
ok thank you alexander.
Nikolay
Now that I think about it I never understood how exactly volatile=True is different from optimistic=False on an attribute. If this causes pony to constantly refetch the children list then it's a bit more of an overkill that I thought, especially since in my case the single extra child created by raw sql is immediately fetched manually.
But it doesn't look like this makes pony do more queries than it already does. But then again I also don't know if it's supposed to and how to trigger that behavior.
Is there an observable difference between optimistic=False and volatile=True on a regular attribute? By the docs it seems that volatile=True is optimistic=False with some additional behavior
Alexander
Hi, sorry for the late answer. optimistic and volatile are indeed similar, and it was necessary for me to check the code to tell the difference. But they were created for different purposes.
In PonyORM each attribute of object instance has two flags associated with it: read bit (or rbit) and write bit (or wbit). They are called bits because internally they are combined to a corresponding bit mask (rbits/wbits) for specific object instance. The read bit is set to 1 if a user's code accessed the value of attribute received from the database. The write bit is set to 1 if the attribute value was set by the user's code.
So if attribute has rbit==0 and wbit==0 that mean that the attribute value is unknown to user.
The combination of rbit==1 and wbit==0 means that user have read the attribute and knows its value, but have not changed it.
rbit==0 and wbit==1 means that the attribute value was assigned by user without reading the previous value loaded from the database
rbit==1 and wbit==1 means that the user's code read the attribute value and then modified it.
Also each attribute keeps two values: val and dbval. val is the current value of the attribute, and dbval is the value that was read from the database.
By default Pony uses optimistic checks to provide data consistency. If some object was modified and it is necessary to perform update command, Pony constructs additional optimistic checks for all attributes with rbit==1 by adding a check that the attribute's database value is still equal to dbval. If the value in database is not equal to dbval, Pony throws an error and abort transaction, because the object state may be inconsistent.
Now back to you question.
optimistic=False for attribute means that the attribute will not be added to optimistic checks even if its rbit is equal to 1. The reasons for introducing this feature were: (1) in Oracle it is not possible to compare CLOB values, so for CLOB columns Pony set optimistic flag to False; (2) float values cannot be compared by equality operation, because there may be rounding errors (note that you can still set optimistic=True for float values, and in that case Pony will construct inexact check criteria with >= and <=)
volatile=True means that the attribute may change in database at any time. For volatile attributes Pony never set the rbit flag. So, after volatile attribute value was read by user, Pony forget about that fact and later, if the value in database changes, Pony thinks it is not a big deal. The reason for adding volatile attributes were database triggers. If an attribute may be changed in the database by trigger on insert or update, we can mark it as volatile and Pony will not be surprised by this change. Also Pony will not assume that the value that was written during INSERT is still the actual value.
Up to this moment optimistic and volatile could not be applied to Set attributes. In last commit I allowed Set attributes to be volatile. The meaning of volatile Set attribute is the following:
When Pony loads all items of specific Set attributes, it marks this attribute as fully_loaded. After that Pony does not try to load it again, and assumes that all items are already presents in memory. But if another query loads some new item which happen to belong to fully loaded collection, Pony consider it as a consistency problem, and assumes that some concurrent transaction modified our fully loaded collection. In this situation Pony throws an error "Phantom object appeared in a collection". This way Pony increased transaction isolation level from "read committed" to "serializable".
With this last change Set attributes which are marked as volatile does not consider it an error when some new collection item found in fully loaded collection. This way it's now possible to insert rows with raw SQL queries and don't encounter the error about phantom objects.
Looks again on this stuff, maybe we can combine optimistic=False and volatile=True somehow, but it may be hard to do correctly without breaking backward compatibility
#documentation #todo add to doc
Nikolay
Intuitively one would expect volatile=True to result in more database queries (mostly cause it looks like it should do something on top of what optimistic=false already does), and I'm glad it's not "a new query on every single attribute access"
Alexander
For non-Set attribute non-setting of rbit will not lead to additional queries, but will prevent adding this attribute to optimistic checks during UPDATE
Nikolay
For non-Set attribute non-setting of rbit will not lead to additional queries, but will prevent adding this attribute to optimistic checks during UPDATE
So it's pretty much equivalent right now?
Alexander
If you insert a new object, volatile attribute will forget its initial value, and it will be re-read from the database on access
Alexander
So it's pretty much equivalent right now?
They are similar, but right now optimistic attribute will still raise an error if the value were suddenly changed in the database, whereas volatile attribute will not
Alexander
I mean, changed not during an optimistic check, but when we load this object second time in some query
Nikolay
If you insert a new object, volatile attribute will forget its initial value, and it will be re-read from the database on access
Update an object? Or does inserting a new object make pony forget values for that attribute of all other object of same class
Nikolay
I mean, changed not during an optimistic check, but when we load this object second time in some query
This answers my "can pony be surprised by an attribute value change in some place other than optimistic check" question
Alexander
class Foo(db.Entity)
id = PrimaryKey(int)
a = Required(int)
b = Required(int, optimistic=False)
c = Required(int, volatile=True)
foo = Foo(id=1, a=10, b=20, c=30)
flush()
db.execute("UPDATE foo SET c=40 WHERE id=1")
print(foo.a) # returns 1
print(foo.b) # returns 2
print(foo.c) # will query the database, then returns value 40 from the database
db.execute("UPDATE foo SET c=50 WHERE id=1")
print(foo.a) # returns 1
print(foo.b) # returns 2
print(foo.c) # returns 40, no new query sent to the database
objects = select(foo for foo in Foo if foo.id > 0)[:] # load new values from database
print(foo.c) # returns 50
foo.a = 60
flush()
# UPDATE foo SET a=60 WHERE a=10 -- no checks for b and c
db.execute("UPDATE foo SET b=70 WHERE id=1")
objects = select(foo for foo in Foo if foo.id < 100)[:] # load new values from database
# raises UnrepeatableReadError "Value of foo.b attribute was updated outside of current transaction"
Alexander
Didn't checked, but should be like that
Alexander
So they are similar indeed, but I'm not sure at this moment we need to combine them, because the intent is a bit different
Alexander
optimistic means "Just don't check this attribute in optimistic checks"
and volatile means "The value of attribute which were saved to the database may be changed by some trigger"
Nikolay
I mean, changed not during an optimistic check, but when we load this object second time in some query
using this code https://nekobin.com/yabevimiwe.py i get
[(Child[2], 20), (Child[1], 10)]
Child[1] 10
[(Child[2], 20), (Child[1], 11)]
Child[1] 10
even with volatile=True, and no errors with optimistic=True (as expected i guess).
Note that Child[1].value did not update when directly printed the second time
Nikolay
Whether it should be updated or not is probably a different case, but it seems pony is not surprised by the change and just ignores it.
Nikolay
Tho it seems that value used in print is loaded by a different query, so i'm probably still not doing the thing that would trigger the "surprise" error (aside from a straightforward optimistic check on update).
Alexander
No, the problem is with query
Nikolay
If i just print(parent.children) pony doesn't fetch the objects the second time. Probably as expected.
Nikolay
I'm not sure how to trigger a "reload" then
Alexander
When you do
select(x for x in X)
Pony loads all columns of x
When you do
select((x, <expr1>, <expr2>) for x in X)
Pony loads primary key of x only, so the resulted select contains x.id, <expr1> and <expr2>, and also <expr1> and <expr2> are not directly associated with columns, they just considered some computed expressions and their values are not used to update the object attributes in cache
Alexander
So the query should look like
select(c for c in parent.children)[:]
or
select(c for c in parent.children).show()
Nikolay
Oh yes, it works as expected now, Error without volatile and updated value with volatile.
Nikolay
Didn't expect "c" to be treated as c.id when used that way
Nikolay
"didn't expect attributes of c to not be updated when used that way" cause "treated as c.id" is probably not exactly right
Alexander
Didn't expect "c" to be treated as c.id when used that way
This is because:
1) if query has GROUP BY section or DISTINCT adding columns can change query result
2) if you do select((parent, child) for parent in X for child in parent.children)
you have parent repeated multiple times, and it may be inefficient to parse the same parent columns again and again
Alexander
So in queries like select((x, <other staff>) for x in ...) Pony loads x non-pk columns in a separate query
Nikolay
Now i can finally sleep knowing the difference between volatile=True and optimistic=False.
Volatile=true disables all "value for was updated outside of current transaction" checks while optimistic=False only excludes the filed from optimistic WHERE filters in updates. They just fully overlap in case of an update but pretty much only there.
Alexander
Looks like a correct description :)
Nikolay
Are terms "transaction" and "session" used interchangeably by pony?
Alexander
session can consist of several sequential transactions, the cache is not cleared when transaction commit successfully inside a db_session
Nikolay
"Cannot load attribute Parent[1].value: the database session is over"
vs
"Value of Child.value for Child[1] was updated outside of current transaction"
Nikolay
session can consist of several sequential transactions, the cache is not cleared when transaction commit successfully inside a db_session
Okay that's what i would expect
Nikolay
oh i remember why i wanted to ask that
both
with db_session:
c = Child[1]
with db_session:
print(c.parent.value)
and
with db_session:
c = Child[1]
rollback()
print(c.parent.value)
result in
Cannot load attribute Parent[1].value: the database session is over
While to untrained eye the second case looks like a single session with two transactions.
Alexander
rollback results to cache reset and starting afresh
Nikolay
So rollback actually terminates not just a db transaction but the whole session and a new session is put in its place
Alexander
yes
Anonymous
Hi everyone, did anyone tried to use Pony ORM together with Flask-User (=Flask-Login with role based management)?
Alexander
I used it with flask-login. It worked.
Matthew
I’ve also used it successfully with flask login