Federico I know that many developers try to separate modules and apply @db_session only to modules which directly perform queries to the database. In my opinion, this is a mistake. The goal of PonyORM is to provide a common way to represent business data, and @db_session should wrap entire scope where PonyORM objects are used. For example, if we have a function which processes HTTP request, it may contain two sub-function. The first one retrieves objects from the database, and the second one uses retrieved objects to render HTML template using Jinja2. In my opinion @db_session should wrap the main function, which processes entire HTTP request.

This way the development of the application becomes much less painful

Just wrap both?

Yes, it is also possible

wrapping both would mean it queries twice right? because the cache is cleared on exit?

No, the inner db_session is ignored

yeah I see your point, I should probably make inner functions and wrap the external function

In my experience it works much better this way

I think, one counter-argument may be, "I don't want to allow the ORM to make queries inside template rendering function, because it will lead to N+1 query problem". Indeed, if you use Django or some other usual ORM, it will. But Pony prevents "N+1 query problem" automatically, so this argument is not applicable for PonyORM

With Pony there are nothing wrong if you lazy load some attributes in template redering function or in any other place outside of the function which makes the initial query

Is there a article about lazy loading? When does it make sense, when not?

I'm not sure about the article. I think, default behavior in Pony should be good enough for most cases

Is there a helper function to get the classtype of an db.Entity subclass?

You mean for specific instance?

This is what I actually want https://editor.ponyorm.com/user/luckydonald/Tags

Cuz, with my tags from before, I want do query Tags, StickerTags or PackTags based on a given variable.

depending on type variable I want to select one of the Tag classes

if type == 'pack': tag_class = 'PackTag' elif type == 'sticker': tag_class = StickerTag else: tag_class = Tag tags = select(t for t in tag_class if ...)

Or, if you pass class name as a parameter, you can do: tags = select(t for t in Tag if t.classname == param and ...)

if type == 'pack': tag_class = 'PackTag' elif type == 'sticker': tag_class = StickerTag else: tag_class = Tag tags = select(t for t in tag_class if ...)

My question is, if I can get StickerTag.classname? In case I need to refactor later

This is StickerTag._discriminator_

I've rewritten that a bit. Thanks Edit: <type> not <str:type>

Another Bug in the editor

Another Bug in the editor

Thanks for reporting @luckydonald !

Your welcome :D

do_tags = Optional(bool, default=None) Is that possible? What is the default default of an optinal bool?

Thanks for reporting @luckydonald !

Note: I wrote "None" there before. Not that that is mistaken for the bug

do_tags = Optional(bool, default=None) Is that possible? What is the default default of an optinal bool?

Default for Optional is None, so you don't need to write 'default=None'

Hello! why then i do: Emails.get(post_parent=meeting.meeting, post_type=EmailPost.MEETING_REGISTRATION.value) pony translate it to SELECT "id", "post_type", "author", "title", "body", "date", "banner", "slug", "post_parent", "special", "classtype" FROM "Posts" WHERE "classtype" IN ('Emails') AND "post_type" = ? AND "post_parent" = ? LIMIT 2 [12, 1] why limit 2?

The get method assumes that no more than one object satisfies specified criteria. Pony attempts to retrieve more than one object in order to throw exception if multiple objects were found. Sometimes the query condition is wrong and a very big number of objects satisfy the criteria. Pony adds LIMIT 2 in order not to load all that objects to memory.

Thank you!

The get method assumes that no more than one object satisfies specified criteria. Pony attempts to retrieve more than one object in order to throw exception if multiple objects were found. Sometimes the query condition is wrong and a very big number of objects satisfy the criteria. Pony adds LIMIT 2 in order not to load all that objects to memory.

You guys thought of everything.

Is there a way to force .get() to only take the first one?

In order to not throw an exception?

Yes.

To do that you can use first instead of get

Awesome

Better then .select( ... )

The first is a method of Query, you can use select(...).first() or SomeEntity.select(...).first()

Hi, everyone. I felt tired of ugly raw SQL queries in my Python code, so I decided to find a database ORM tool with elegant syntax. Found pony! Do you guys like this thing? ☺️

Thats why were here.

👍

Useing it to write https://getstickers.me/

Impressing!

If someone uses that and sees bugs, please tell me

Are you working on this for fun?

Or are you somehow earning money?

Just interested

Or are you somehow earning money?

I don't think so.

Are you working on this for fun?

Maybe for glory.

And because I thought such tool was missing.

If any of you can help me test by breaking stuff, I'd appreaciate it.

I like your website. It looks 100% fancy

Breaking stuff? 😅

And the online editor is very nice done

I like your website. It looks 100% fancy

It still needs some kind of search field. Still looking into how to do it (GUI I mean)

I am confused, why all the "or" becomes "AND".

Oh never mind, I read it wrong.

Still for some reason query_text == st.sticker.file_id doen't seem to work. Maybe I am doing something wrong

I thought it should be query_text == st.sticker.emoji instead

It also is in the line above

hmm. Give me a few minutes

If you want a SQL dump to play with, I can provide you such thing

pairs = orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage for s_t in st.sticker.tags # for p_t in st.sticker.sticker_pack.tags if # (t.use_global or st.user.id == user_id or bot_id) and ( query_text.lower() in s_t.string.lower() or query_text.lower() in st.sticker.sticker_pack.url or query_text.lower() in st.sticker.sticker_pack.title or # query_text.lower() in p_t.string.lower() or query_text == st.sticker.emoji or query_text == st.sticker.file_id ) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset)

Or access to phppgadmin

I think that's enough for now

The for p_t in st.sticker.sticker_pack.tags part should actually be included, as well as the query_text.lower() in p_t.string.lower() or to check the Tags on a StickerPack too. `

Can you show me the the full text of the resulting SQL query?

Yes

SELECT DISTINCT "sticker"."file_id", MAX("st"."date") FROM "stickermessage" "st", "tag" "s_t", "sticker" "sticker", "stickerpack" "stickerpack" WHERE (lower("s_t"."string") LIKE ('%%' || replace(replace(replace(%(p1)s, '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' OR "sticker"."sticker_pack_url" LIKE ('%%' || replace(replace(replace(%(p1)s, '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' OR "stickerpack"."title" LIKE ('%%' || replace(replace(replace(%(p1)s, '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' OR %(p4)s = "sticker"."emoji" OR %(p4)s = "sticker"."file_id") AND NOT EXISTS ( SELECT 1 FROM "tag" "t-1" WHERE "st"."sticker_file_id" = "t-1"."sticker_file_id" AND "st"."sticker_emoji" = "t-1"."sticker_emoji" AND "t-1"."classtype" IN ('StickerTag') AND lower("t-1"."string") = 'nsfw' ) AND NOT EXISTS ( SELECT 1 FROM "sticker" "sticker-2", "tag" "t-2" WHERE "st"."sticker_file_id" = "sticker-2"."file_id" AND "st"."sticker_emoji" = "sticker-2"."emoji" AND lower("t-2"."string") = 'nsfw' AND ("sticker-2"."sticker_pack_url" = "t-2"."pack") ) AND "st"."sticker_file_id" = "s_t"."sticker_file_id" AND "st"."sticker_emoji" = "s_t"."sticker_emoji" AND "s_t"."classtype" IN ('StickerTag') AND "st"."sticker_file_id" = "sticker"."file_id" AND "st"."sticker_emoji" = "sticker"."emoji" AND "sticker"."sticker_pack_url" = "stickerpack"."url" GROUP BY "sticker"."file_id" ORDER BY 2 DESC, 1 LIMIT 50

I think I just wrote the query wrong or something

So, what in your opinion is wrong?

I confused the AND s at the bottom with my or s

It seems that all ORs are present, they are in the top part of the query

Still for some reason query_text == st.sticker.file_id doen't seem to work. Maybe I am doing something wrong

It's here: OR %(p4)s = "sticker"."file_id"

What is that LIKE ('%%' || replace(replace(replace(%(p1)s, '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' doing?

In SQL, LIKE has special symbols _ (which means "any symbol") and % (which means "any number of any symbols"). It is possible that the param value contains these symbols. When we translate Python string in operator, we need to suppress the special meaning of that symbols and treat them as an ordinary symbols

And so we replace _ to !_ and % to !% and use ! as an escape symbol which denotes that the following symbol is just a usual symbol without any special meaning

I forgot the .lower() thats why my queries didn't return anything.

I noticed, this will only return stuff when there are st.sticker.tags. How can I improve the query that e.g. st.sticker.sticker_pack.title.lower() can trigger when there aren't any st.sticker.tags (s_t)?