The problem with to_json is that it has weak possibilities in querying hierarchical data. We want to replace it with something like that: select(g for g in UniversityGroup if g.graduate_year == 2018).extract(""" number, major, department { number, name, faculty { name } }, courses foreach(c) orderby(c.name) { id, name, credits } students foreach(s) where(s.gpa > 3.5) orderby(s.name) limit(10) { id, name, gpa }, """)

With that you mean, that there are only the ids of other tables? And the plan is to actually include the subdata there? Actually I like how it works with reference keys now.

The problem with to_json is that it has weak possibilities in querying hierarchical data. We want to replace it with something like that: select(g for g in UniversityGroup if g.graduate_year == 2018).extract(""" number, major, department { number, name, faculty { name } }, courses foreach(c) orderby(c.name) { id, name, credits } students foreach(s) where(s.gpa > 3.5) orderby(s.name) limit(10) { id, name, gpa }, """)

Also this looks neither like python nor like sql I'm not sure if inventing your own query language for that is a good idea.

This based on our experience we got during developing the site fineartbiblio.com. Sometimes it is useful to load complex hierarhical data following object relationships. For example, on art gallery site page you want to load 10 most popular artists, and for each of them load some specific fields, like name and year of birth, and also load several related collections - 10 most significant artworks, 5 most recent catalogs, and for each catalog load 3 illustrations, and for each artwork load list of provenance records, etc. As a result you will get some hierarchical JSON structure. This requires some hierarchical query API. There are several ways how it may be done. One way is to use some pure-Python constructions. We are experimented with this, and the result was disappointing. Either the API looks very ugly, or its insufficiently powerful when working with subcollections. Another way is to use some existing hierarchical query API, like GraphQL. It is possible, but often looks overcomplicate ("cursors" and all that stuff) and too heavyweight. The third way is to use custom query language with easy to understand syntax, which allows to embed arbitrary Python expressions for conditions. After experimenting with other approaches, I believe this is the most convenient way.

> Actually I like how it works with reference keys now. So you are already use to_json? Can you tell a bit more how you use it? Do you manually process resulted data structure? Actually, the data format of to_json result is pretty good. But it was intened to use with corresponding PonyJS library which uses that data to construct JavaScript obejcts which are equivalent to Python objects. Do you use unreleased version of PonyJS, or process data manually instead?

What I dislike in to_json function is not the data format, but the lack of expressive power when it is necessary to recursively specify fields and subcollections which need to be retrieved

We don't use it already, but @mshekhter and I am looking to build an adapter to put sql data to an elasticsearch index. And the data structure returned looks perfect already. All left to to would be to map the datatypes to an elastic equivalent. From what we found so far, we'd could use the json of the elements unchanged to push the data into the ES index.

What I dislike in to_json function is not the data format, but the lack of expressive power when it is necessary to recursively specify fields and subcollections which need to be retrieved

But why implement another lanuage if you could just write python?

@luckydonald did you try https://docs.ponyorm.com/api_reference.html#Entity.to_dict may be this will work for you?

Can I specify that on a query restult too? But probably yes.

In conjunction with database._get_schema_dict() it is basicly the same.

But why implement another lanuage if you could just write python?

We don't found expressive enough way to write hierarchical queries using pure Python, maybe you can suggest it. I think custom DSL with embedded Python fragments provides the good compromise between familiarity and expressivity. But in the nearest perspective you can use to_dict(), maybe it fully suits you needs

anything with flask or hug that i do uses to_dict() extensively

Hi, i'm having... like..

and odd error

i have this function

@db_session def join(user, group): jugador = Jugador.get(user=user,group=group) res = NOTHING if (not jugador): user = User.get(id_user=user.id_user) user = select( u for u in User if u.id_user == user.id_user)[:][0] print(user.id_user) jugador = Jugador(user=user,group=group) juego = Juego.get(group=group) if (jugador not in juego.jugadores): juego.jugadores.add(jugador) res = JOINED return res

(sorry for not pastebin)

user and group are preloaded objects from the table

when i print the id_user i get the number

(sorry for not pastebin)

This is fine I think. So it won't be lost.

but in this line jugador = Jugador(user=user,group=group)

i get ValueError: Attribute User.id_user is required

Is the id_user set to PrimaryKey?

Yep

Hmm, strange.

Can you provide full traceback?

In 5 mins

I think this is what happened: 1) Jugador class derived from User 2) User class has id_user column 3) This column does not have auto=True option 4) You create Jugador instance without specifying id_user value

You need to specify auto=True option when defining id_user attribute. Or you can omiss definition of id_user at all, and pony creates default id attribute with auto=True

oh ..

it was the 1 option

i don't know what happened but i wanted jugador nor to be a child class from User

well ... i think im a mess ?

Thanks!!

Sure!

Hi all. I am new to relational dbs and have some questions. I have existing project with mongodb and my own data access objects. I want to migrate it to postgresql using ponyorm. I already created db model and done with data migration. The question is: What is the best way to integrate pony in existing code? Should I replace existing models?

I think it will be much easier to replace models than to use some wrappers

Hi! If you use your customORM like CRUD and no more, I think you will have no problems if you replace your 'models'. it would be better if you have tests

Thank you for replies.

Hello! Which rulles to work with to_json of Query objects? [link to doc](https://docs.ponyorm.com/api_reference.html?highlight=json#Query.to_json) when i try to corvert my Query object to json with to_json() i gex exception The current user None which belongs to groups ['anybody'] has no rights to see the object ORDER[1088962] on the frontend ORDER - is my entity

What is simple way to convert objects to json?

to_json is getting replaced (i think). Will to_dict work?

Hello! Which rulles to work with to_json of Query objects? [link to doc](https://docs.ponyorm.com/api_reference.html?highlight=json#Query.to_json) when i try to corvert my Query object to json with to_json() i gex exception The current user None which belongs to groups ['anybody'] has no rights to see the object ORDER[1088962] on the frontend ORDER - is my entity

Hi Artur! The `to_json()` method is part of PonyJS addon to Pony - an ability to get the database models at frontend. It is not released yet. Currently the best option is to use https://docs.ponyorm.com/api_reference.html?highlight=to_dict#Entity.to_dict

`to_dict` works and will work what we'd like to do is to offer a simple way of creating hierarchial JSON

Here is an example:

select(g for g in UniversityGroup if g.graduate_year == 2018).extract(""" number, major, department { number, name, faculty { name } }, courses foreach(c) orderby(c.name) { id, name, credits } students foreach(s) where(s.gpa > 3.5) orderby(s.name) limit(10) { id, name, gpa }, """)

as the result you'll get JSON

currently you can use to_dict method and then convert it to JSON

to_json is getting replaced (i think). Will to_dict work?

Thx man! it works!

select(g for g in UniversityGroup if g.graduate_year == 2018).extract(""" number, major, department { number, name, faculty { name } }, courses foreach(c) orderby(c.name) { id, name, credits } students foreach(s) where(s.gpa > 3.5) orderby(s.name) limit(10) { id, name, gpa }, """)

Could there be a sort of Extract object that can be used in place of """..."""

`to_dict` works and will work what we'd like to do is to offer a simple way of creating hierarchial JSON

Cool realisation, i will try it too!

Could there be a sort of Extract object that can be used in place of """..."""

what is the use case of such Extract object?

Hmmm... what version include this fiture? 'Query' object has no attribute 'extract'

it is the next thing we are going to work on after we release the migration tool

Ok :) Thank you

?

By the way, can you provide some examples or real project repos that use ponyorm?

here https://libraries.io/pypi/pony/usage

what is the use case of such Extract object?

Nevermind, I was misreading what extrac did

Nevermind, I was misreading what extrac did

ok

here https://libraries.io/pypi/pony/usage

Thank you. I will check it out.

select(g for g in UniversityGroup if g.graduate_year == 2018).extract(""" number, major, department { number, name, faculty { name } }, courses foreach(c) orderby(c.name) { id, name, credits } students foreach(s) where(s.gpa > 3.5) orderby(s.name) limit(10) { id, name, gpa }, """)

Pony ORM strives to be the most pythonic framework, but uses some completely new language just to generate json? This strucks me as really a wrong in that scope.

I agree, I've been trying to think of a better way to do it but, I haven't yet.

Some sort of schema object might do

what on earth

keeps track of if statements, i've seen it done if there is a lot of conditional logic dictating when the function loses scope

you'd think indentation would be obvious enough

there might also be a plug in for an IDE which benefits from those kind of comments

I just started doing that, and figured it helps

I'm not really being nitpicky, I just found it sort of amusing since I've never seen it before

return { "number": g.number, "major": g.major, "department": { "number": g.department.number, "faculty": g.faculty.name, }, "courses": [ {"id": c.id, "name": c.name, "credits": c.credits} for c in g.courses.order_by(c.name) ], "students": [ {"id": s.id, "name": s.name, "gpa": s.credits} for s in g.students.order_by(s.name).limit(10) if s.gpa > 3.5 ] }

telegram really needs to expand chat bubbles to full width..

telegram really needs to expand chat bubbles to full width..

That's why I mostly also send a screenshot

telegram really needs to expand chat bubbles to full width..

Also syntax highlighting would be awesome

definitely

syntax highlighting would be great

There is already some markdown support, idk why they don't allow ```python to work

return { "number": g.number, "major": g.major, "department": { "number": g.department.number, "faculty": g.faculty.name, }, "courses": [ {"id": c.id, "name": c.name, "credits": c.credits} for c in g.courses.order_by(c.name) ], "students": [ {"id": s.id, "name": s.name, "gpa": s.credits} for s in g.students.order_by(s.name).limit(10) if s.gpa > 3.5 ] }

Getting back to topic :D I really don't think you guys should invent a new language just for that. What are the reasons not to use python?

Tracked here: https://github.com/ponyorm/pony/issues/232

Is there an ETA for this?

Indeed, workaround is: p for p in Pack if p.nsfw is True or p.nsfw is None

I meant p for p in Pack if not (p.nsfw is True or p.nsfw is None) as workaround for if not p.nsfw back then.

next(dict(model=m.id, name=m.name, description=m.description, type=m.type, destinations=[dict(host=x.host, port=x.port, password=x.password, name=x.name) for x in m.destinations]) for m in select(m for m in Model if m.model_type == _type.value))

for JSONize I think this good

pure python.

I meant p for p in Pack if not (p.nsfw is True or p.nsfw is None) as workaround for if not p.nsfw back then.

Nevermind. My condition should only remove p's if nsfw is really True. So maybe p.nsfw in [False, None] could work too.

for JSONize I think this good

Yep.

Well... you asked about get, and select is entirely different story. Pony should translate select generator code to SQL, and cannot translate your function, because it doesn't know what equivalent SQL should be. I don't know it too :) Also, the function that you wrote expects keyword arguments, not positional ones as you use inside the query. 1) I suggest you to write selects explicitly: games = select(g for g in Game if (g.name == name or name is None) and (g.version == version or version is None)) 2) You can also build query incrementally: query = select(g for g in Game) if name is not None: query = query.filter(lambda g: g.name == name) if version is not None: query = query.filter(lambda g: g.version == version) 3) Probably even something like that should work: def ignore_none(query, **kwargs): for key, value in kwargs.items(): if value is not None: query = query.filter(lambda x: getattr(x, key) == value) return query query = select(g for g in Game) query = ignore_none(query, name='X', version='Y') I didn't test it. Theoretically it should work, but there is an open issue 223, which describes a bug with getattr which is not fixed yet. You may follow approach (2) for safety

This .filter(lambda args: bool) in 2) is awesome. packs = orm.select(p for p in Pack if not only_published or p.published) Is now packs = orm.select(p for p in Pack) if only_published: packs = packs.filter(lambda p : p.published) # end if

Is there a way to create tables using a json file that handles field declaration?

In principle, it is possible to create entity classes dynamically. Simplified example: from pony.orm import * from pony.orm import core db = Database() def define_person_entity(db): attrs = [] attrs.append(('name', Required(str))) attrs.append(('age', Required(int))) Person = core.EntityMeta('Person', (db.Entity,), dict(attrs)) define_person_entity(db) db.bind('sqlite', ':memory:') sql_debug(True) db.generate_mapping(create_tables=True) with db_session: p1 = db.Person(name='John', age=18) p2 = db.Person(name='Mike', age=20) persons = select(p for p in db.Person)[:] You can use the same idea to generate entity definitions based on information from some configuration file

This schema library looks like it would be useful to JSON based Pony work, and has a similar API https://pypi.python.org/pypi/schema

Ty guys and awesome :)

> from pony.orm import * > from pony.orm import core O_O?