Cuz I made a (for me) non-trivial DB change so I wanted to ask.

The difference is that I moved sticker into a subclass, and added anotherone with a stickerpack attribute

an example of to_dict and json.dumps

The difference is that I moved sticker into a subclass, and added anotherone with a stickerpack attribute

What I understood, Pony does that in one big table, distinguising it with a special row, telling it to be a specific class.

def account_page(): user = get_current_user() projects = Project.select(lambda p: p.user == user).order_by(desc(Project.updated_at)) account = user.to_dict(exclude=['id', 'password', 'balance']) return json.dumps({ 'account': account, 'projects': [dict(p.to_dict(exclude=['id', 'user', 'data'])) for p in projects] }, default=json_handler)

def json_handler(obj): if hasattr(obj, 'isoformat'): return obj.isoformat() elif isinstance(obj, UUID): return str(obj) else: raise TypeError("Unserializable object {} of type {}".format(obj, type(obj)))

What I understood, Pony does that in one big table, distinguising it with a special row, telling it to be a specific class.

So, I'd need to add a row for that? If I remember correctly thats type "text" and contains a string of the class name? In that migration I would need to fill in "StickerTag", right? (I still have the sticker attribute) And add another pack field?

I think that first version of migration tool will not support such a changes which move attributes between subclasses. But this migration is actually simple. The first change is adding classtype system column which Pony uses to distinguish between subclasses. The second one is adding pack column for PackTag.pack relation. So the SQL for migration looks like (I assume PostgreSQL is used): alter table "tag" add column "classtype" text; update "tag" set "classtype" = "StickerTag"; --for previous objects alter table "tag" add column "pack" int references "stickerpack" ("id");

I think that first version of migration tool will not support such a changes which move attributes between subclasses. But this migration is actually simple. The first change is adding classtype system column which Pony uses to distinguish between subclasses. The second one is adding pack column for PackTag.pack relation. So the SQL for migration looks like (I assume PostgreSQL is used): alter table "tag" add column "classtype" text; update "tag" set "classtype" = "StickerTag"; --for previous objects alter table "tag" add column "pack" int references "stickerpack" ("id");

I just love you guys!

I think that first version of migration tool will not support such a changes which move attributes between subclasses. But this migration is actually simple. The first change is adding classtype system column which Pony uses to distinguish between subclasses. The second one is adding pack column for PackTag.pack relation. So the SQL for migration looks like (I assume PostgreSQL is used): alter table "tag" add column "classtype" text; update "tag" set "classtype" = "StickerTag"; --for previous objects alter table "tag" add column "pack" int references "stickerpack" ("id");

If I still have an old version of that code which needs to run, can I change it anyway? Probably should default the classtype to "StickerTag", can that work?

Yes, I think if you add default value it should work with previous code without problems

Cool, because I have seperated everything between Dev and Production, well except the database :/ The DB is shared.

Maybe it is better to have two different database copies for Dev and Prod

That way you may be sure that you non-tested dev code does not delete all objects from the database

Yes, definitly. Probably I'll learn that as soon as I fry up the DB. Couldn't find a way to easily export/import all the data, so I instead make periodically backups of the postgres folder... ... .. . and hope nothing bad happens... (Yeah that is terrible, I know)

Maybe we need some dump/load functionality in Pony

Not sure if that should be part of pony (or pony core at least)

But It could indeed make switching Databases easy

I think making backup of entire database is outside of Pony scope. But maybe we should have a method to save content of a known entities into a big JSON file and then to load data from that file

That way it may be easier to populate a dev & test databases

I think making backup of entire database is outside of Pony scope. But maybe we should have a method to save content of a known entities into a big JSON file and then to load data from that file

You could still "abuse" that, to have something similar to a backup, yes

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?

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_

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)

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?