@mszaro I've dug up a little in the source code and the dbprovider for postgres is written by using exclusively psycopg2

https://github.com/ponyorm/pony/blob/orm/pony/orm/dbproviders/postgres.py

https://github.com/ponyorm/pony/blob/orm/pony/orm/dbproviders/postgres.py

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

I think someone could just rewrite this file to use pg8000 instead of psy :) Then it could work, although now it's only psycopg2

I think someone could just rewrite this file to use pg8000 instead of psy :) Then it could work, although now it's only psycopg2

s/someone/myself/g

Quick question, is there a pythonic way to do json field queries? I wanna see if an element in the json exsits, and has a specified value: Interactive example: https://rextester.com/LYGPYL94483 create table if not exists states ( name varchar(32), data JSON ); insert into states values ('STATE1', '{"chat_verification": {"token": "abc123"}, "whatever" : true}'); insert into states values ('STATE2', '{"chat_verification": {"token": "xDnope"}, "whatever" : true}'); insert into states values ('STATE3', '{"chat_verification": {"token": "abcDEF"}, "whatever" : true}'); # -- https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains; SELECT * FROM states WHERE data->"$.chat_verification.token" = "abc123";

You mean, with Pony?

yes

I'll use postgres, I think the above example was written for mysql

I think it should work: select(obj for obj in MyObject if in obj.json_field["key"] == value)

I think it should work: select(obj for obj in MyObject if in obj.json_field["key"] == value)

But that would load all of the elements in memory? I want to select only the state which has exactly the token.

For this tabe, select(state for state in State if state.data['chat_verification']['token'] == value)

No, it will be performed in database

Oh, wow

That's so cool!

It was not easy to implement :)

@mszaro I've dug up a little in the source code and the dbprovider for postgres is written by using exclusively psycopg2

Cheers

Yeah, but that's great. I was shortly contemplating If I need to use mongodb, but that is just perfect :D

https://docs.ponyorm.org/json.html#querying-json-structures

No, it will be performed in database

Well that’s pretty sweet

It was not easy to implement :)

I bet!

I think someone could just rewrite this file to use pg8000 instead of psy :) Then it could work, although now it's only psycopg2

Probably not worth it, was just a curiosity thing

For this tabe, select(state for state in State if state.data['chat_verification']['token'] == value)

So this wouldn't fail if any of the json keys doesn't exist, right? Like data = "{}" just not select those fields?

Does Pony work on top of pg8000 or only psycopg2?

At this moment Pony supports only psycopg2 and psycopg2cffi

👍🏻

Does Pony work on top of pg8000 or only psycopg2?

pg8000 is sweet though edit: if going raw sql, that is

So this wouldn't fail if any of the json keys doesn't exist, right? Like data = "{}" just not select those fields?

No, it wouldn't fail, it just don't select this row

No, it wouldn't fail, it just don't select this row

You guys are awesome :D

pg8000 is sweet though edit: if going raw sql, that is

Too bad we don’t have an awesome feature rich ORM instead

Too bad we don’t have an awesome feature rich ORM instead

Yeah, but if there were one, I'd definitly be in a telegram group of them.

We have already tried to add pg8000, but had no time to finish it. Maybe we can return to it later

Yeah, but if there were one, I'd definitly be in a telegram group of them.

Haha exactly!

We have already tried to add pg8000, but had no time to finish it. Maybe we can return to it later

Probably migrations and hopefully blueprints come first 😇

yeah

I'm so sorry to bring this up again😂 But would love to hear Y/N from @metaprogrammer whether it is considered an anti-pattern to cover with @db_session only general areas where DB is accessed, and not the entire application (say, all flask endpoints, as shown in the Docs). I'd do it to make more efficient use (??) of the connection pool, although this means increased usage/testing complexity. Can't shake the feeling that I'm prematurely optimizing, and any benefits from minimizing db_session area are insignificant, would love to hear from a dev about this

Hi Anon! Yes, I'm looking at your question right now. Thought to answer on StackOverflow and post link kere, but probably it is easier to answer here first

That's so awesome! Take your time, answer to either one would be greatly appreciated👍

The perpose of db_session is to manage three things: 1) database connection 2) transaction state 3) in-memory cache of loaded objects 1) Pony associates separate connection to each thread. If process does not use threads, then only one connection will be used. When db_session ends, it returns its connection to the connection pool. This means that connection remains open and keeps for the future usage. Next db_session will use the same connection. So, regarding conection usage, there is no difference between a single db_session and several sequential db_sessions

2) When db_session ends, it perform implicit commit. There is no difference between implicit and explicit commits, so if you have a single db_session with manual commit() call it is the same as several sequential db_sessions. However, if you don't use explicit commit(), then long db_session may hold database lock and prevent other processes to work with database or specific table until commit is performed

1) Perfectly clear now 2) Got it! This is exactly what I've searched for

One quick last additional question regarding (2)

As far as I can understand from (2), this code will not lock database, but it will hold a connection with db_session: time.sleep(999)

for the 999 seconds

3) The main difference between a single db_session and several sequential db_sessions is in managing in-memory cache of objects loaded from the database. Each db_session has separate cache. Objects inside a cache are cross-linked by relations. If you load a bunch od Course, Student and Group objects, they are all interlinked to each other via relationship attributes. Because of this, it is impossible to unload some objects off the cache and keep the rest. Garbage collector cannot collect just some objects from the cache, because they all are point to each other with circular references. So the cache can be deleted only as a whole when db_session is finished. So, if you have a single long-standing db_session, it will not free memory until the end. But several smaller db_session may require load the same object from the database several times. So there is a memory/performance trade-off

As far as I can understand from (2), this code will not lock database, but it will hold a connection with db_session: time.sleep(999)

Yes, it is. Even if db_session is finished, connection will still be hold until program end or explicit db.disconnect() call

Pony closes connection implicitly only if some database exception inside db_session leads to rollback

(3) Explains a lot, didn't know this to be the case

Regarding the question on StackOverflow, the first example will not work: def do_stuff(): with db_session: task = Task.select().first() task.mark_started() ... this_function_might_take_a_long_time(task) ... with db_session: task.mark_done() In this code you create task object inside the first db_session, and then use it in a following db_session. Pony forbids changing object inside a different db_session. Each db_session has its own cache of objects, and it is not possible to cut object from one db_session and inject it to another db_session, as it breaks relations with another objects. But, if you wrap this entire code to global db_session it will work, because Pony just ignores nested db_sessions

right! orm.make_proxy() would've fixed it😂

yes

Hope I answered your questions

Yes, all the important stuff is clear now!

One more - How could I make a donation for the project?

Been a pleasure so far using for work/hobby stuff

That's the most important question :D

Thanks, we plan to add donation button to our website page soon. We'll write here when it will be implemented

Awesome, I'll check it out! Thanks again

Sure!

(I'd love to keep the answer for future reference, so I added it to my SO question - feel free to answer it yourself, I'll delete my own answer)

We have already tried to add pg8000, but had no time to finish it. Maybe we can return to it later

For context, the reason I ask is AWS Lambda. They don't support psycopg2 due to the C extension, you have to do some weird hacks and upload a custom C binary to get it to work. Amazon just recommends to use pg8000 🙂

I think this makes support of pg8000 more important

right! orm.make_proxy() would've fixed it😂

I'm not sure to understand. isn't the aim of make_proxy to make objects reusable in another db_session ?

I'm not sure to understand. isn't the aim of make_proxy to make objects reusable in another db_session ?

Yes. So, if in the first db_session he adds task = orm.make_proxy(task) he would be able to do in the following db_session task.mark_done() Without make_proxy it would be an error

Alexander, do you work on ponyorm as full time job?

No, currently I'm working at Yandex and can only work on Pony on weekdays. It would be nice to develop PonyORM as a full-time job, but Pony doesn't provide sufficient cash flow for it

Hi, I'm doing unittest, I have a BindingError because I tried create a db in differents test. Can I unbind database object in tearDown? thanks

Hi, it is not possible to unbind db object, but you can do db.disconnect() and then create new db object in the next test

thanks

Heya, was db.entities.items() renamed/removed recently?

No, db.entities is just a dict

Oh, never mind, for some reason db is None here.

No, currently I'm working at Yandex and can only work on Pony on weekdays. It would be nice to develop PonyORM as a full-time job, but Pony doesn't provide sufficient cash flow for it

Seems like you put a lot of work into this on the side, then! Maybe you could find a source of revenue to make that possible: sponsorship, premium support tier for enterprise, etc. Are you aware of https://opencollective.com/how-it-works?

Thanks for the link, I'll look into it

Seems like you put a lot of work into this on the side, then! Maybe you could find a source of revenue to make that possible: sponsorship, premium support tier for enterprise, etc. Are you aware of https://opencollective.com/how-it-works?

Thanks for sending this. Actually we are working on a donation page at the moment, going to post information about this soon

Weirdly, at least in the United States, it's very easy for me to get my employer to spend money on a subscription, but very hard to make a one time donation, if it's worded as such

For example if I tell work I want them to "donate" $100 to an OSS project, there is a ton of paperwork, I have to get it reviewed and accepted, justify why I want the donation, all this stuff. It's a process

But if I tell them I want to buy a subscription for $10/month to the editor, because it will help me do my job better (easier database design!) then it's instantly approved

Because now it is a "business and development cost"

They will also throw money at any kind of premium support package. Like every startup in Silicon Valley (hi from san jose by the way) has a paid AWS support tier, probably paid support tier for their major dependencies. The companies look at it like an insurance policy, their app is mission critical so why would you not pay easily $250-500 per month to be able to relax knowing the devs will help you in an outage 🙂

That’s interesting. Thanks for the info!