If I could just loop over them and remove the deleted ones, it'd work fine

I think, right now you can write some raw sql query, it should be pretty simple, something like: id = db.select('select id from MyTable where id = $prev_object.id') if id is None: # skip prev_object from list

[_ for _ in weekly_rankings_data.keys() if _ in models.UserProduct.select(lambda up: up.tag.user == models.User[5707])]

This seems to work :)

so get all valid UserProducts, check if the current UserProduct is in there

it's hacky but oh well

If UserProduct list is not too big, it may be efficient

Yeah, for the biggest user, it's 1000 objects

I guess it should be a set if its contents are being queried a lot?

Yep, this works :)

weekly_rankings_data = cPickle.loads(models.redis.get(redis_key)) current_user_products = set(models.UserProduct.select(lambda up: up.tag.user == current_user_object())) weekly_rankings_data = {k: v for k, v in weekly_rankings_data.items() if k in current_user_products}

Yes, I think you can convert query result to set and then check individual objects against it

Hello. Does Pony have a GROUP_CONCAT function?

It has no direct support of it, but you can write raw SQL fragment inside your generator query

Thank you!

select(x for x in X).limit(lambda x: sum(x.y) <= 100)

is a query like that possible?

"get rows until the sum of the y attribute is max 100"

It has no direct support of it, but you can write raw SQL fragment inside your generator query

select(x.a, raw_sql("GROUP_CONCAT(x.b)" for x in MyEntity if x.c > 100))

"get rows until the sum of the y attribute is max 100"

It is not quite clear what "until" means in that context. Is it rollup sum or what?

if all rows had y = 10, then it'd get 10 rows

if the first two rows had y = 50, then it'd just get 2 rows

You can use analytical functions for that, we want to add support of them, but right now you need to write it as raw SQL fragment too

https://www.postgresql.org/docs/10/static/functions-window.html

https://www.postgresql.org/docs/10/static/tutorial-window.html

Something like select(x for x in X if raw_sql("sum(x.y) OVER (ORDER BY x.z)") < 100)

thanks! I didn't know it was possible even with raw sql :)

Window functions are very powerful

are they similar across mysql, sqlite etc as well?

If I remember correctly, they are similar in PostgreSQL, Oracle and MySQL, but not supported in SQLite. When we will add support of them, probably we can find a way to somehow partially emulate them in SQLite https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

It seems like Pony development could continue for the next ten years if you wanted it to

Yeah, the potential possibilities are limitless :)

Why does the query not work? res = select(raw_sql('(s.name) (s.id)') for s in SewingMachine)[?

res = select(raw_sql('(s.name) (s.id)') for s in SewingMachine)[?

You can use triple backquotes to mark code blocks

I think (s.name) (s.id) is incorrect SQL syntax, I don't know what it means

select(raw_sql("""SELECT s.name, s.id """) for s in SewingMachine)[?

res = select((raw_sql('s.name'), raw_sql('s.id')) for s in SewingMachine)[:] It most probably can be written as res = select((s.name, s.id) for s in SewingMachine)[:] without using of raw_sql

good. Thank you!

Sure

I can not. select((s.id, s.name, raw_sql('GROUP_CONCAT(t.name)')) for s in SewingMachine) error: pony.orm.dbapiprovider.InternalError: (1054, "Unknown column 't.name' in 'field list'")

It should turn out: [(1, 'juki', 'type1 type2 type3')... ]

My Models http://joxi.ru/52aYXbZs4J7KR2.jpg

Amendment : 'GROUP_CONCAT(t.type.name)'

You cannot write t.type.name in SQL, it does not understand attribute traversing. You need to use the alias of the table from which you select name column select((s.id, s.name, raw_sql('GROUP_CONCAT(t.name)')) for s in SewingMachine for t in s.type) By the way, it looks strange that your attribute called type (singular form), but its type is collection. Either attribute should be called something like types, or its type should not be Set

Hello! It did not work! select((s.id, s.name, raw_sql('GROUP_CONCAT(t.name)')) for s in SewingMachine for t in s.type) pony.orm.dbapiprovider.InternalError: (1054, "Unknown column 't.name' in 'field list'")

Maybe it is a bug that I need to fix: Pony sees that t is not used (it does not alalyze raw SQL part) and "optimizes" the query by removing joining of TypeSewingMachine from the query. As a workaround try to add the following condition to the query: and t.id > 0

select((s.id, s.name, raw_sql('GROUP_CONCAT(t.name)')) for s in SewingMachine for t in s.type if t.id > 0) select((s.id, s.name, raw_sql('GROUP_CONCAT(t.name)'), t.id) for s in SewingMachine for t in s.type) pony.orm.dbapiprovider.InternalError: (1054, "Unknown column 't.name' in 'field list'")

Did I try to do the right thing?

So, too, does not work! select((raw_sql('GROUP_CONCAT(t.name)')) for t in TypeSewingMachine) pony.orm.dbapiprovider.InternalError: (1054, "Unknown column 't.name' in 'field list'")

Two things to fix: 1) I was mistaken, as we have many-to-many relationships, the condition t.id > 0 is not enough to force Pony to join TypeSewingMachine. Pony sees that it is enough to join imtermediate table for that. So you need to replace it with something like t.name is not None. 2) For some reason, the alias for the table is t-2 instead of t. You can see SQL text if you specify sql_debug=True option for db_session The following query should work: select((s.id, s.name, raw_sql('GROUP_CONCAT(`t-2`.name)')) for s in SewingMachine for t in s.type if t.name is not None)[:] I think, we need to add direct group_concat support to Pony, this will eliminate all hacks for joining the necessary table. #todo

Right now it looks more robust to write subquery in raw_sql instead of use automatic join feature: select((s.id, s.name, raw_sql(''' (SELECT GROUP_CONCAT(t.name) FROM typesewingmachine t INNER JOIN sewingmachine_typesewingmachine t2 ON t.id = t2.typesewingmachine WHERE t2.sewingmachine = s.id) ''')) for s in SewingMachine)

This way it is not necessary to guess table alias or add fake conditions

Yes it works as it should! Although very strange! ))

You probably need to specify separator as a second argument of GROUP_CONCAT

Thank you! In this chat you can write in Russian? Or not?

No, this chat is for English only. You can write direct messages to me in Russian

Yes, I will add separator! Thank you, I understand you!

Hello, I'm having trouble clearing my memory after committing a few inserts. How can I go about releasing the entity objects if I don't store any references of them myself.

The entity instances*

If you exit from the most outer db_session the memory should be cleared. If you want to save changes and continue working inside the same db_session, you can do the following trick: with db_session: <perform some queries, update some objects> commit() rollback() # the cache should be clear here <perform new queries>

But generally you can just wrap separate units of work with different db_session s

Thanks that did the trick :)

Hi everybody!!!

My name is Germán. I'm from Venezuela and I love PonyORM

Hi :)

I have a question

What is it?

I can't get why this error... Reverse attribute for Voucher.oferta_id not found

Paste your models, maybe on http://gist.github.com/

Ok!

Https://pastebin.com/kvv4WY2K

Add this to Offer: voucher = Required('Voucher')

By the way, I think it's better to remove "_id" from attributes, as you are referencing an object, not an ID of an object

Oh!!! ? Thank a lot!!!

Hi Germán! I think you need to fix some relationship attributes. For example, in the following definition ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest_id = orm.Required(Guest) offer_id = orm.Required(Offer) ... ``` It is incorrect to name attribute `guest_id`, because in Pony its value is Giest object itself and not its id value. It is implemented using a column which keeps id value, but in Python you will get Guest object when accessing this attribute. The same is with `offer_id` attribute. So the code should look like ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest = orm.Required(Guest) offer = orm.Required(Offer) ... ``` If for some reason you want the column name which ends with _id, you can specify it using `column` option: ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest = orm.Required(Guest, column=guest_id) offer = orm.Required(Offer, column=offer_id) ... ```

Oh Great!!! Thank a lot!!!! Nice group guys. One point more to love ponyorm even more.

Each relationship attribute should have corresponding reverse attribute in another entity. If one entity Foo has attribute bar = orm.Required("Bar") then Bar entity should have something like foos = orm.Set("Bar")

By the way, Alexander is the creator of Pony, he's probably too modest to say :)

?

?

Alexander thank you for such great thing. I really ❤ Ponyorm

You are welcome! :)

Good morning guys!

One question. The entity relationship may be with an Optional?

Ok I saw it!

Good. Yes, it stands for 'Zero or One'. As I remember.

Yes. I found it in the docs. Thank you again.

Hello again ?

I have to insert two billion rows into a DB so I'm trying to speed up every part of the process. I've noticed the initialization of instances is a big bottleneck. In particular cut_traceback and core.validate? Do you know how to switch off cut_traceback? And could I switch off validate as well if I'm confident in the integrity of my data?

I have to insert two billion rows into a DB so I'm trying to speed up every part of the process. I've noticed the initialization of instances is a big bottleneck. In particular cut_traceback and core.validate? Do you know how to switch off cut_traceback? And could I switch off validate as well if I'm confident in the integrity of my data?

Maybe you should not use an ORM for this process

I should not but I don't want to have to add raw sql to my code if I can just change a few config options and gain substantial speed.

https://docs.ponyorm.com/api_reference.html

Look for the insert method. On mobile so can't direct link

insert(table_name|entity, returning=None, **kwargs) Insert new rows into a table. This command bypasses the identity map cache and can be used in order to increase the performance when you need to create lots of objects and not going to read them in the same transaction. Also you can use the execute() method for this purpose. If you need to work with those objects in the same transaction it is better to create instances of entities and have Pony to save them in the database.

Thanks for the tip. It made the process 1.5 times faster but it won't be enough, looks like I'll have to go with raw sql after all.

Are you doing a lot of inserts per transaction?