At this moment Pony support SELECT...FOR UPDATE and SELECT ... FOR UDATE NOWAIT We can add SELECT ... FOR UPDATE SKIP LOCKED too, it should not be too hard #todo

If you could find some free time to add this, that would be great!

Ok, I'll try

I also have another small question, if I may: When I do select(a for a in A).limit(1), the returned object has QueryResult type instead of Query, which means I can't chain get_sql() to it. Is it intended behaviour? Why?

The initial reasoning was, LIMIT is logically the last section of SQL query, so after somebody do query2 = query.limit(...), he cannot do, for example query3 = query2.filter(...). So after LIMIT nothing else remains except to execute the query and get a result (actually, it is possible with queries of type SELECT ... FROM (SELECT ... LIMIT ...) but Pony started support of such queries just recently) query.get_sql() was added later, so it was too late to change API to allow get_sql on this type of queries The same problem is with query.first(), query.get(), etc. — they are all executed immediately and return an object, so you cannot call get_sql on them Maybe it would be more correct to add some final execute method to the query, and before calling it the query is just a query and not a query result. But this will be more verbouse for typical use-cases, and breaks backward compatibility

Actually, maybe it is possible to change API to make it more general and consistent, but I don't have any specific idea how to do it

As it would require major changes in a lot of unexpected places, you should probably wait with this for the next major release. The one with migrations could be such release, but as far as I remember, it doesn't break backwards compatibility for now.

But, about the nested query example you wrote – is it possible to have QueryResult (from limit(…), for example) nested in Query right now?

Yes, it is a pretty big change. So probably not now, if at all. But it is possible to discuss what would be correct API if PonyORM was designed from scratch

But, about the nested query example you wrote – is it possible to have QueryResult (from limit(…), for example) nested in Query right now?

yes

This is possible, because from the recent release QueryResult is lazy

Well

Maybe this is the answer

We can add get_sql to lazy QueryResult

After that it will be possible to do query.limit(...).get_sql(). But still not for query.first() or query.get(), because they return not QueryResult, but object or None. Maybe they are fine as is

The functions first() (as in "get_first") and get() even sound like execute(), so they are fine.

Well, it's not like I need this feature, I just stumbled upon this problem when I tried to have the whole query with for_update(nowait=True) prepared by Pony, to just replace NOWAIT with SKIP LOCKED and then pass the result to select_by_sql as a workaround for now. I know it's an ugly workaround, but at least it would work for all types of queries and wouldn't break anything (at least that's my guess).

By the way, I made a decision to switch to PostgreSQL yesterday, and while reading the documentation I got the impression that PostgreSQL is also your personal preference. If that is the case, why do you like it more? I was never really into databases (which I know sounds silly) and I'm just curious.

What was your previous DBMS before PostrgreSQL?

MySQL, because that was the only one I ever used (in the very distant past, with PHP).

I just realized that back then, when I was learning it, I didn't even know there was something else. :D Well, but I was just a kid.

I think PostgreSQL architecture is more well-thought than MySQL one. PostgreSQL was developed in more academic style. Previously it was slower than MySQL but now they are comparable. MySQL approach was more ad-hock. There are some outstanding MySQL bugs which remains not fixed for years. For example, CHECK constrains are just silently ignored https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working

With PostgreSQL I experienced less bugs, and I think it has more features now comparing to MySQL

Yeah, that's my impression too, but that's only after reading about it for a day, so I'll take your experience as a confirmation. :-)

Actually, both of these databases have pros and cons, so for some specific use-cases I may prefer MySQL over PostgreSQL. For example, MySQL has more options for replication, and may have better performance in UPDATE-heavy scenarios. On the other side, in PostgreSQL you can place series of CREATE TABLE commands inside a single transaction, which may be very convenient. But my default choice is usually PostgreSQL

I started to work on a pretty big (at least for me) project, but I don't even know where the bottlenecks in database could appear. I decided to stick to PostgreSQL and try to find some solutions to optimize things if that's necessary and possible to identify.

But, about the transactions: is there any way I can switch from AUTOCOMMIT to TRANSACTION mode? My workaround (manually replacing NOWAIT with SKIP LOCKED) works for now (not with limit(…), of course), but when I execute select_by_sql with prepared statement, Pony switches to autocommit mode automatically, which renders FOR UPDATE lock useless.

Again, not really an important question, as that would be fixed with adding SKIP LOCKED feature, but I don't want to rush you – I looked at the code and tried to think about how to add it, and it seems like it needs a lot of small checks in a lot of places. But switching mode might be useful as well for some other things, so it's always good to know.

Are you sure? It should be another way around. Pony start db_session with AUTOCOMMIT mode (in order to avoid COMMIT entirely if all operations inside db_session are SELECTs), and then starts explicit transaction before the first UPDATE/INSERT/DELETE/SELECT FOR UPDATE command

But Pony doesn't know it's FOR UPDATE when I specify it in raw SQL.

Here's my ugly test code:

Recorder.select_by_sql(select(r for r in Recorder).for_update(nowait=True).order_by(Recorder.name).get_sql().replace('NOWAIT', 'SKIP LOCKED'))

What Pony does then is this: GET CONNECTION FROM THE LOCAL POOL SWITCH TO AUTOCOMMIT MODE SELECT "r"."id", "r"."name" FROM "recorder" "r" ORDER BY "r"."name" FOR UPDATE SKIP LOCKED

You can do db.execute(some_sql) command to force entering transaction mode. db.execute('select 1') should be enough. Pony assumes that sql passed to db.execute may be UPDATE command and starts a real transaction

That works. Thanks!

@metaprogrammer please let me know if I can be of assistance wiith that inner join bug, it is blocking my work so I'm motivated to fix it :)

Matthew, I cannot understand the meaning of your query models.select((p, p.reviews.variation_asin, models.min(p.reviews.review_date), models.max(p.reviews.review_date)) for p in models.ReviewProduct)[:] I think it is equivalent to the following query: from pony.orm.examples.university1 import * select((g.number, g.students.name, min(g.students.gpa), max(g.students.gpa)) for g in Group)[:] I can indeed reproduce the error with this type of query But I cannot understand its meaning. g.students.name contains multiple names, what do you expect as a result?

a product can have multiple variation_asin's. I want every unqiue variation_asin, with the min / max review date for that variation_asin

Ah, ok, that makes sense

I know it's weird :)

No, it is normal

Thanks for accepting my query choices!

I want to suggest a quick workaround until I fixed the bug. You can rewrite the query as models.select( (r.product, r.variation_asin, min(r.review_date), max(r.review_date)) for r in models.ProductReview )[:] It should work

So, in this query I assign explicit alias to ProductReview

This should work too: models.select((p, r.variation_asin, models.min(r.review_date), models.max(r.review_date)) for p in models.ReviewProduct for r in p.reviews)[:]

The first one works, thanks!

Guys. We just pushed Array support on GitHub. Please take a look if you can https://docs.ponyorm.com/array.html

Why Is it IntArray and not simply Array(int) ?

why need to implement Array(MyClass) checks

It would fit the current style more

I mean it is Required(int) not IntRequired() etc.

Required(IntArray)

IntArray is datatype

with fixed typing

It actually is https://github.com/ponyorm/pony/blob/orm/pony/orm/ormtypes.py#L373

It actually is https://github.com/ponyorm/pony/blob/orm/pony/orm/ormtypes.py#L373

Permanent link to the file pony/orm/ormtypes.py mentioned. (?)

But we thought that Required(Array(int)) looks worse and many users will make mistakes with it.

Hello! I have two questions about using array slices in a query. 1) Why variable passing as a slice limit causes TypeError: Array indices should be type of int? For example: product = Product(name='abc', stars=[1, 2, 3]) end = 3 stars = select(p.stars[:end] for p in Product)[:]2) Is it planned to add support for slices so that it can be done like this: stars = select(p.stars[slice(0, 2)] for p in Product)[:]

Yeah, sorry. It's a bug. Didn't test on slices as variables. We will fix it soon. We support [a:b] instead of slice(a, b).

And why do you need slice(a, b) instead of [a:b]?

Thank you! It would be slightly more convenient to pass the same once calculated slice to several arrays. But there is definitely no strong need for it

Hello! Tell me how to get rid of a large number of requests?

https://pastebin.com/Xbyk8F7Y

I'm trying to get the data through the link. But the pony generates a query for each row.

select(m.migration_card.number for m in Staff_worker_info if m.migration_card)

When pony load object, it doesnt load all linked objects if you dont ask

You just ask pony to load all Staff_worker_info objects. Then you start to iterate over them as python objects and for each of them you are loading linked objects. Pony understands generators inside select function, not any python loops outside of it.

What if I need more data? print(model_worker.migration_card.number) print(model_worker.insurance.number) print(model_worker.passport.number) print(model_worker.patent.number) Then you need such a request? model_workers = select((m.migration_card.number, m.insurance.number, m.passport.number, m.patent.number) for m in Staff_worker_info)

?

Yes

ok

What if I need more data? print(model_worker.migration_card.number) print(model_worker.insurance.number) print(model_worker.passport.number) print(model_worker.patent.number) Then you need such a request? model_workers = select((m.migration_card.number, m.insurance.number, m.passport.number, m.patent.number) for m in Staff_worker_info)

You can use prefetch: query = select(m for m in Staff_worker) \ .prefetch(Staff_worker.migration_card, Staff_worker.insurance_number, Staff_worker.pasport, Staff_worker.patent) for m in query: print(m.migration_card.number) print(m.insurance.number) ...

In dev version of PonyORM it should execute small number of efficient queries

Here it is better!

We just pushed improved versions of Array support on GitHub. For indexing and slicing you can use values, variables and entity atrributes (of type int) (also as expresssions).

Matthew, I was finally able to fix the bug with duplicate JOIN in query, it was surprisingly complex to fix

wow good job!

Can you link to the commit?

Sure https://github.com/ponyorm/pony/commit/873470698f8cf8a9ade8bc9eda832869d82a1c27

Looks complex!

Internal logic for joins is not simple to understand even for me, I think it is the most complex part of PonyORM. But in general it works good, except for some corner-cases