Would it be possible to have timing of SQL queries when debug mode is turned on? I often find myself timing a bunch of queries when debugging performance issues. For example sql_debug(timing=True)

then after the SQL is printed, also showing the time in milliseconds to execute

Pony already gather such statistics, but don't output it to the log, because the SQL query is printed before execution, not after it (otherwise it would be hard to understand which query caused database exception). You can do the following: db.merge_local_stats() stat = db.global_stats for sql, query_stat in stat.items(): print(sql) print(query_stat.max_time, query_stat.avg_time, query_stat.sum_time, query_stat.db_count, query_stat.cache_count)

Thank you, if that is not in the documentation ,it would be good to add

http://ponyorm.readthedocs.io/en/latest/api_reference.html#QueryStat

It is in API reference, maybe we need to add it to other parts of documentation as well

It is in API reference, maybe we need to add it to other parts of documentation as well

It is not only in the API reference - https://docs.ponyorm.com/database.html#database-statistics

What about pony orm and graphql?

Vitalii Abetkin wrote the initial version of Pony GraphQL API, you can find it here: https://github.com/abetkin/pony_graphql You can ask Виталик for details of how it works

Hey @akozlovsky, I guess you couldn't have time for the Enum problem, maybe you can lead me, and I can dive in and try to work on it during the weekend..

Hi, actually I was able to make code work about half a hour ago. In order to do this it was necessary to change some code inside Pony. We don't develop Pony in a way when new types can be added completely externally without changing Pony code itself, because in order to be completely extensible the code needs to be more complex. I can push my changes to separate branch in our GitHub repository. I don't want to merge them to the main branch ('orm'), because in order to do this we need to add support of other databases as well. Also, be aware that there are some drawbacks in using enums in the database: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

thanks a lot, I can use that code for sure.. I will ready the article.. Actually I need this because the DB is already using Enums and the codebase is JS/NodeJS. So I want to make it a bit more compatible.

https://github.com/ponyorm/pony/commit/b278886d32a330d48307efe817aa4dfd8f6cbe21

The code is a bit experimental, some corner cases may not work as expected

Will this - in the long run - allow list type on postgres as well?

yes

This is great ?

Can someone please explain what the root cause of this kind of error is? UnrepeatableReadError: Phantom object SpyResult[718929855] appeared in collection SpyProduct[2777314].results

It comes in a select query that has been cast to a list, nothing exotic

I get it on a very small percentage of requests, but I can't seem to stop it happening completely

I think it's a normal @db_session

results = select(sr for sr in SpyResult if sr.product == self and sr.date >= datetime.date.today() - datetime.timedelta(days=num_days)).order_by(SpyResult.id)[ : ]

that's the query

You have one-to-many relationship between SpyProduct and SpyResult: SpyProduct.results <-> SpyResult.product. At some moment Pony loads all items of SpyProduct.results collection for specific product. In that case, Pony mark the collection as fully loaded in order to not issue additional queries to database. But then you retrieve another SpyResult object which turns out to be related with the same SpyProduct. It is possible if that SpyResult object was created a moment ago in some concurrent transaction. In that case Pony decided that another transaction modified collection which was considered fully loaded, and it is unsafe to continue working with inconsistent data

Ok, thank you. Is there a way to ignore new objects that have been created in another transaction? Isn't the idea of a transaction to have an isolated view of the system, regardless of what happens in other transactions?

Yes, but PostgreSQL by default does not provide full isolation between transactions. It works on READ COMMITTED isolation level, where you can immediately see results of completed concurrent transactions. You can try to use db_session with serializable=True option, in that case it will provide full isolation at some performance expence. When PostgreSQL works with fully isolated transactions it may throw exception if the achievement of full isolation is not possible

"At some moment Pony loads all items of SpyProduct.results collection for specific product. In that case, Pony mark the collection as fully loaded in order to not issue additional queries to database. " - I turned debug mode on with pony, and I don't see any query that selects all SpyResults, only the specific query I pasted above?

Maybe you do iteration over product.results collection. In that case Pony loads all items into memory

would doing len(product.results) trigger that?

I think it does, as it's not doing COUNT(*)

yes. You can use product.results.count() instead, in that case Pony will issue select count(*) ... query without loading all items into memory

thanks, I think this is probably the issue then

So with count(), the query that is failing would now be the first time .results is requested

Yes, the query that is failing will load just added SpyResult object but as the collection is not fully loaded it will not raise any exception

Great, thanks for your help

Sure

Would you say it's a good pattern to always do a count() query rather than len() of a normal query, to avoid this issue?

I think it depends. Sometimes you want to iterate over all items anyway, and in that case count() query will be unnecessary, and count() result may differ from the actual number of collection items you get during iteration

If you don't plan to iterate over items, i think count() is better

Right, I'm just checking if count() == 0

If you want to just know is some collection empty or not, the more efficient way is just check if product.results.is_empty()

But if it's a filtered query, not just the whole results set, is_empty() seems not to be possible? So count() would be best?

For query it should be select(...).exists() or product.results.select(lambda: ...).exists()

Maybe we need to make API a bit more uniform, I don't know

My knowledge of the API hasn't kept up to date :)

I do things "the old way" a lot of the time i think

yes. You can use product.results.count() instead, in that case Pony will issue select count(*) ... query without loading all items into memory

TIL

Hi, guys... Who can provide simple example of group and join multiple tables with model... Like this Model.Select().join(Model)...How i can get only group without using aggregation function? Why Model.delete() is not work... Model.select().delete() work...

PS... I know what Alexsander and Alexsey are core developers of pony) They only answers on the questions...

Hi, guys... Who can provide simple example of group and join multiple tables with model... Like this Model.Select().join(Model)...How i can get only group without using aggregation function? Why Model.delete() is not work... Model.select().delete() work...

Here is example of left_join https://docs.ponyorm.com/api_reference.html#left_join Model.delete() wouldn't know what object to delete - you need to call delete() on entity instance (an object), not an entity (class)

Happy holiday)

Alexsey, do you can update pony docs with more powerfull example https://docs.ponyorm.com/working_with_entity_instances.html

Yes, it does not know.. But if is not logically.. if coder dont select objects thеn tool remove all data from table

Here you can find more examples: https://github.com/ponyorm/pony/blob/orm/pony/orm/examples/estore.py https://github.com/ponyorm/pony/blob/orm/pony/orm/examples/university1.py

Aleksey , thanks..

example only for select... where is working with model entity and lambdas?

most queries that return an entity instance could be written using lambda - just put the condition part from the generator query to lambda here are more details on using generator experssions and lambdas https://docs.ponyorm.com/queries.html

Ok, examples were in other category...

Values_list? Model.select().values(fields=('name')) Columns to list... Is it right? list(Model.select(lambda x: x.name)).. i think it returns full model...

to_dict... to_list not in doc..

https://docs.ponyorm.com/api_reference.html#Entity.to_dict

yes... to_list?

For example we want to get list of properties... [c.name for c in Model.select()]... Its really looks weird

1) list(Model.select(lambda x: x.name)) 2) list comprehension, iterating on the result of to_dict

select(c.name for c in Model)

hm...

Hi Maxim, > Who can provide simple example of group and join multiple tables with model Currently to grouping you need to use select(...) form of query: select((x.a, x.b, max(x.c), sum(x.d)) for x in MyEntity if x.e > y) will result to SELECT x.a, x,b, max(x.c), sum(x.d) FROM myentity x WHERE x.e > %(param1)s GROUP BY x.a, x.b

There are two type of joins. The most convenient one is when you use explicitly defined entity relationships. The query select(s for s in Student if s.group.department.name == 'Department of Network Technologies') will lead to join of three tables - student, group and department The same query can be written using filter method with lambda: select(s for s in Student).filter(lambda s: s.group.department.name == 'Department of Network Technologies') Instead of select function you can use select method of Student entity: Student.select(lambda s: s.group.department.name == 'Department of Network Technologies')

Currently the queries which use MyEntity.select(...) form restricted to return instances of MyEntity class only. If you want to return something else, you need to use select(...) function: select((s.name, s.age) for s in Student)

In the future we probably will add method to query which will allow to specify result columns using lambda function, something like project method: Student.select(...).filter(...).project(lambda s: (s.name, s.age)) Currently we don't have such API, you need to use select((s.name, s.age) for s in Student) form or retrieve whole object and access necessary fields in Python

The second type of join is explicit join without relationships. In order to make such join you need to write select(...) function with multiple for loops. For example, if you want to find different students with the same name you can write: select( (s1, s2) for s1 in Student for s2 in Student if s1.id != s2.id and s1.name == s2.name )

If you want to convert query to list you can use slice: query = MyEntity.select(...) objects = query[:limit] Or, if you want to retrieve all objects, you can just wrap query to list() constructor: query = MyEntity.select(...) objects = list(query)

Uh, thanks.... select(s for s in Student).filter(lambda s: s.group.departament.name = '') equivalent to Model.select(lamda s: s if s.group.deparment.name == 'name' )... smaller...?

hm... understand... filter not in api... futures..

hm... understand... filter not in api... futures..

https://docs.ponyorm.com/api_reference.html#Query.filter

=)

Hi Alex, just want to ask does Pony have feature to generate entities based on existing DB tables?

Not yet. At this moment you can manually create entities which correspond to existing tables. It is possible to explicitly specify name of each table and column in model definition

ic ok because i got 100+ tables to migrate :D

That's pretty big number :)

Maybe you can still automate it. Most databases have a way to list existing tables and columns

you could maybe use django's inspect to generate django models for you

and then do some text processing to get pony-like models

obviously needs fine tuning by hand afterwards but way better than doing it all from scratch i suppose

I think we should we need to offer such a database reflection tool @bsalim what database you currently use?

I think we should we need to offer such a database reflection tool @bsalim what database you currently use?

Postgresql

i m quite new to Pony :) mostly used Django ORM, thinking to switch to more lighter REST API framework with Pony ORM as main ORM

I use flask + pony and find it works very well :)

i m currently looking at apistar project founded by DRF creator

I use flask + pony and find it works very well :)

Yup

I use Morepath and Pony and it works great!

I think we should we need to offer such a database reflection tool @bsalim what database you currently use?

This feel like being part of migrations

In [2]: select(u for u in User if u.id > 10) | select(u for u in User if 'gmail.com' in u.email_address) —------------------------------------------------------------------------- TypeError Traceback (most recent call last) <ipython-input-2-e4a413899bc9> in <module?) —--> 1 select(u for u in User if u.id > 10) | select(u for u in User if 'gmail.com' in u.email_address) TypeError: unsupported operand type(s) for |: 'Query' and 'Query'

Is composition (treating queries as sets) planned?

maybe composition is the wrong word :)

Is there an ETA on Pony migrations?