Anonymous
Ok, well, lets see if it also works with Flask-User, but I have no doubt that it does. 😁
Lucky
Lucky
wait. Not sure which of the two I'm actually using
Lucky
Oh, I just use Flask Login
Lucky
sorry.
Lucky
But that works very well
Lucky
Also, role based management sounds like something I'd like to look into
Lucky
Tell me how it works for you
Lucky
How can I restrict the DB session (actually the session's cache) to a for loop?
Lucky
Use case:
for obj in SomeTable.select(…):
obj.something += 1
# end for
Lucky
I don't need the obj outside of the for loop
Lucky
and definitly don't need all of the 179000 elements in the other iterations.
Just one single SomeTable instance at a time.
Lucky
That's in a cronjob.
I just want to know two fields from that database, do some actions based on that, and set a processed_at = datetime.now() at the end.
Lucky
but wrapping that all in with orm.db_session would mean I basically cache every single one of those
Lucky
Which is totally not what I want.
Nikolay
but wrapping that all in with orm.db_session would mean I basically cache every single one of those
So do you want to wrap the loop in db_session or not? with db_session
Nikolay
If you need to clear session cache you can issue a rollback() but that will make objects from that session basically read only
Nikolay
perhaps you are looking for bulk update, which is not a thing yet
https://docs.ponyorm.org/working_with_entity_instances.html?highlight=bulk%20update#updating-an-object
"In future, Pony is going to support bulk update. "
Lucky
this is my old mongodb code
rows: Cursor = db.sent_messages.find(
filter={
"sent": {"$lt": now - timedelta(minutes=5)},
"is_processed": None,
}
)
rows is now a Cursor object, quite like an iterator/generator
for row in rows:
do_processing(row.foo, row.bar)
# …
db.sent_messages.update_one(
filter={"_id": row['_id']},
update={
"$set": {
"is_processed": datetime.now(),
},
}
)
With that a row get's deleted in the next iteration, and replaced by the next one form the database.
Nikolay
You can select a list of ids in a separate with db_session outside of the loop and then update the object by id with a with db_session inside of the loop
Nikolay
for entity_id in entity_id_list:
with db_session:
do something
Nikolay
I'm not sure what exactly you are trying to optimize here
Nikolay
this is my old mongodb code
rows: Cursor = db.sent_messages.find(
filter={
"sent": {"$lt": now - timedelta(minutes=5)},
"is_processed": None,
}
)
rows is now a Cursor object, quite like an iterator/generator
for row in rows:
do_processing(row.foo, row.bar)
# …
db.sent_messages.update_one(
filter={"_id": row['_id']},
update={
"$set": {
"is_processed": datetime.now(),
},
}
)
With that a row get's deleted in the next iteration, and replaced by the next one form the database.
this sill loads all the rows in memory just like a pony select would, doesn't it?
Nikolay
you'd need a server side cursor to avoid fetching the results and that's usually not the default behavior
Matthew
Do you need to update all 179000 rows?
Matthew
Are you filtering as much as possible in the database query instead of in python?
Lucky
this sill loads all the rows in memory just like a pony select would, doesn't it?
But only one at a time
Lucky
Anonymous
Heya, is there an integrated INSERT OR REPLACE or do we have to make a condition on the select and then insert/update ?
Lucky
Heya, is there an integrated INSERT OR REPLACE or do we have to make a condition on the select and then insert/update ?
Sounds like the second approach could get you in all kinds of race condition issues.
Nikolay
Sounds like the second approach could get you in all kinds of race condition issues.
well, upserts are not yet supported
Lucky
So the pythonic way would be to try to insert it, and with a except in case it failes do the update.
Alexander
As I remember correctly exceptions are slow
Nikolay
So the pythonic way would be to try to insert it, and with a except in case it failes do the update.
No that's just the only way that doesn't involve raw sql. But it requires a rollback and that makes things very inconvenient.
Anonymous
can do it in that way :
if usr := User[ctx.author.id]:
usr.id = ctx.author.id
usr.system = sys
usr.discord_tag = str(ctx.author)
else:
User(id=ctx.author.id, system=sys, discord_tag=str(ctx.author))
Anonymous
idk if it's better than a try except tho
J J
What I used in one of my bots a while ago. Not sure if it still works.
class UserMixin:
@classmethod
def update_or_create(cls, obj):
if isinstance(obj, User):
params = obj.to_dict()
elif isinstance(obj, dict):
params = obj
try:
# try retrieve object via primary key
instance = cls[tuple(
params[pk_attr.name]
for pk_attr in cls._pk_attrs_
)]
except orm.ObjectNotFound:
# if object not found, create and return it
return cls(**params)
else:
# else it was found, lets update existing params and unset missing params
newparams = {k.name: params.setdefault(k.name, k.py_type()) for k in cls._attrs_}
instance.set(**newparams)
return instance
Anonymous
this might be better looking but is the same, performance-wise :
usr = User[ctx.author.id] or User()
usr.id = ctx.author.id
usr.system = sys
usr.discord_tag = str(ctx.author)
Anonymous
guys can i have something like created_at and updated_at that fills automatically in Pony ?
Andrey
guys can i have something like created_at and updated_at that fills automatically in Pony ?
See here: https://t.me/ponyorm/13100
Anonymous
See here: https://t.me/ponyorm/13100
is before_update function nesseccary ? or the field definition default value is enough to do that ?!
Andrey
is before_update function nesseccary ? or the field definition default value is enough to do that ?!
For updated_at it's nesseccary. For created_at default_value is enough
Anonymous
Alexander
For unique columns index is created automatically, you can specify index name if you wish don't want to use default index name for some reason
Anonymous
how should i make a one to many relation that connects something like parent_id field in child to id field of parent ? i don't need the Set field when i am creating the relation and i also want to make a relation to id field, not that Set
Anonymous
Alexander
If you do this:
class Parent(db.Entity):
id = PrimaryKey(int, auto=True)
children = Set("Child")
class Child(db.Entiity):
id = PrimaryKey(int, auto=True)
parent = Required("Parent", column="parent_id")
The you will have two tables and a foreign key which connects child.parent_id column to parent.id primary key
Set field is a virtual field which is not presented in the database but may be useful when defining queries
Anonymous
If you do this:
class Parent(db.Entity):
id = PrimaryKey(int, auto=True)
children = Set("Child")
class Child(db.Entiity):
id = PrimaryKey(int, auto=True)
parent = Required("Parent", column="parent_id")
The you will have two tables and a foreign key which connects child.parent_id column to parent.id primary key
Set field is a virtual field which is not presented in the database but may be useful when defining queries
Alexander
It is Order.transaction field that is virtual, because it is optional
But you can specify column name explicitly if you want to move column to other table
Anonymous
It is Order.transaction field that is virtual, because it is optional
But you can specify column name explicitly if you want to move column to other table
what should i do if i wanna make Transaction.order virtual and the other one real ?
Anonymous
what should i do if i wanna make Transaction.order virtual and the other one real ?
i reversed the relation and i think it happened
Nikolay
what should i do if i wanna make Transaction.order virtual and the other one real ?
if you are talking about 1to1 you can specify a column name on the side you want to have a real column
Anonymous
hey, having a problem with the reverse attribute feature of pony. I've 1 foreign key that has a one-to-one relationship and 1 fk which has a many-to-one relationship between the same tables, but they don't have the same meanings, and i'm getting a Reverse attribute for x.x not found what should i do ?
Anonymous
(see that as having two tables Team and Member and there's a team_leader key in the Team table and a team key in the Member table)
Anonymous
it would make no sense to have both those keys represented in Member
Anonymous
should i make a fake private attribute in Member named _team_led or sth ?
Alexander
Yes, just define some fake attribute
Anonymous
that sucks :/
Anonymous
we should be able to define no reverse attribute if we wish so
Alexander
I think we'll add this feature later
But is it really that bad?
Reverse attribute turns out to be useful in queries more often then it seems
Anonymous
well, i have few examples like that, with circular keys where it can be annoying
Anonymous
i know reverse attribute is useful
Anonymous
but in this case, it's really not useful to have 1 reverse attribute to know the team from which the member is and the team that it leds, because they're the same, and with this structure, they will always be
Anonymous
now, imagine if you have roles defined for a team, like 10 of them, and they are each foreign keys to the Member table, that means we have to make 10 fake reverse attribute that will never be used
Anonymous
you get my point ?
Alexander
class Team(db.Entity)
name = Required(str, unique=True)
city = Required(str)
members = Set("Member")
team_lead = Optional("Member")
class Member(db.Entity):
name = Required(str)
team = Required("Team")
_lead_of_team = Optional("Team")
# select all members of NY teams
select(m for m in Member if m.team.city = "NY")
# select only team leads of NY teams
select(m for m in Member if m._lead_of_team.city = "NY")
Anonymous
that's not instinctive
Anonymous
directly doing Team[id].team_lead is better
Anonymous
and there's only 1 team_lead, that would make sense if there was several of them, and that would even more make sense if there was several team a member can be in
Anonymous
but when it's not the case, it isn't useful
Alexander
It is not the same, if you have multiple teams in one city and don't know all their ids (as in this invented example)
Alexander
Actually, when I have to define such attributes I don't like it as well, so I see your point
Anonymous
if you don't know their id you can do
Anonymous
select(m for m in Member if m.name == m.team.team_lead.name)
Anonymous
which might be longer but is more instinctive
Anonymous
but ye, that's only if you have one of those attributes