TypeError: range() integer end argument expected, got datetime.date.

You can do a list comprehension, producing a "range" of datetime objects, which will work with Pony, although performance may not be as good as <, >, etc

Maybe it can work with date, but with datetime it is error-prone, because in check will not work if datetime objects differ by second or millisecond

Good point :)

If anyone uses digital ocean, you can save 50% by resizing your existing VMs: https://blog.digitalocean.com/new-droplet-plans/

Hello! Thank you for great product! Is there a way in Pony to refine the query depending on some conditions? It seems not possible to do inside the generator expressions. What I’d like to get: query = "SELECT * FROM users" if is_adult: query += " WHERE age > 18"

is_adult is local variable?

yep

select(u for u in Users if (u.age > 18 if is_adult else True))

But if you want to just modify it, you can do like q = select(u for u in User) if is_adult: q = q.filter(lambda u: u.age > 18)

Second way seems better, cause produces better SQL. Just same SQL that you want.

That’s it! Thank you

No problem.

I have pickled a python object which includes Pony instances, for example Product[123]

Product[123] has since been deleted, is there a way in python to detect that the object no longer exists, without throwing an exception?

In [21]: models.UserProduct[196416].product Out[21]: Product[41014804] In [22]: models.UserProduct[196416].product.to_dict() —------------------------------------------------------------------------- UnrepeatableReadError Traceback (most recent call last) <ipython-input-22-48cfb4a2236a> in <module😠) —--> 1 models.UserProduct[196416].product.to_dict() /root/app/.env/local/lib/python2.7/site-packages/pony/orm/core.pyc in to_dict(obj, only, exclude, with_collections, with_lazy, related_objects) /root/app/.env/local/lib/python2.7/site-packages/pony/utils/utils.pyc in cut_traceback(func, *args, **kwargs) 74 module_name = tb.tb_frame.f_globals.get('name') or '' 75 if module_name.startswith('pony.utils') and tb.tb_frame.f_code.co_name == 'throw': —-> 76 reraise(exc_type, exc, last_pony_tb) 77 reraise(exc_type, exc, full_tb) 78 finally: /root/app/.env/local/lib/python2.7/site-packages/pony/orm/core.pyc in _load_(obj) 4426 objects = entity._fetch_objects(cursor, attr_offsets) 4427 if obj not in objects: throw(UnrepeatableReadError, -> 4428 'Phantom object %s disappeared' % safe_repr(obj)) 4429 @cut_traceback 4430 def load(obj, *attrs): /root/app/.env/local/lib/python2.7/site-packages/pony/utils/utils.pyc in throw(exc_type, *args, **kwargs) 98 raise exc 99 else: —> 100 raise exc # Set "pony.options.CUT_TRACEBACK = False" to see full traceback 101 finally: del exc 102 UnrepeatableReadError: Phantom object Product[41014804] disappeared

So how can I tell that the product is phantom, without throwing an exception?

I'm not sure it is possible at this moment, maybe we need to add something to API

Is it possible to tell if the parent object (UserProduct) still exists in the database?

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>