In some sense it is correct that lambda or generator passed to select is not executed. Pony just analyzes its AST to build an equivalent SQL query. Probably there needs some cooperation between Pony and coverage.py to not report this code as not executed

Guys, we just released PonyORM 0.7.4 https://github.com/ponyorm/pony/releases/tag/0.7.4 Blog post will be written soon. The release includes a huge change of internal mechanics. Now you can do the following: 1) declare method or property on Entity which will be translated into SQL when called inside select(...) like: class Student(db.Entity): ... def full_name(self): return self.first_name + ' ' + last_name @property def is_good_student(self): return self.gpa >= 4 query = select(s.full_name() for s in Student if s.is_good_student)) 2) Use query as a source for another query: query = select(s for s in Student if s.gpa > 3) query2 = select(s.full_name for s in query if s.first_name.startswith('J')) Other features: - support of Python 3.7 and PyPy was added - group_concat() aggregate function for joining of a string values - pony.flask package for basic integration with Flask and Flask-Login

:D

looks great

Cool!!! hybrid property is very nice. You finally delayed the migration tool ?

Unfortunately yes, but now we will concentrate on it

ok so good luke as it seems to be a real mess

Greatest news! Thanks ?

Forgot to mention, it is not necessary for function to be a method of property of an instance. Any one-line function will suffice: def has_equal_name(a, b): return a.name == b.name select((p1, p2) for p1 in Person for p2 in Person if p1 != p2 and has_equal_name(p1, p2))

Wow! Cool update.

Sir Alexander , you definitely rock!

Hi all! I have noticed that following expression doesn't work in 0.7.4: User[1].servers.filter('s.expire is None') But it works using lambda: User[1].servers.filter(lambda s: s.expire is None) I widely use such filtering in jinja templates, where lambdas are not supported. I found out that I can use such expression: User[1].servers.filter('lambda s: s.expire is None') So my problem is resolved :) But I need to know is this a bug or feature? Because I use mentioned above expression in my another project.

Hi Vitaly! What do you mean by "doesn't work"? Does it throw an exception or returns an incorrect result?

it throws s is not defined

Ok, I was able to reproduce it

Also I have q question about hybrid methods. Expression from your example select((p, p.cars_by_color('yellow')) for p in Person if p.has_car) throws an error: pony.orm.sqlbuilding.AstError: An SQL AST list was expected. Got string: 'SELECT'

it seems there might be count instead of select: def cars_by_color(self, color): return count(car for car in self.cars if car.color == color)

Regarding your first question about lambdas, probably we can fix it, but I recommend to use explicit lambda s: with argument name. Lambda without argument looks strange, and probably should be deprecated

You are right, the query text is incorrect. We should change example to something like select(p for p in Person if count(p.cars_by_color('red')) > 1)

it throws s is not defined

Thank you Vitaliy! It is indeed a bug, which affects query.where method as well. We will release a fix soon

We've released 0.7.5 version where the bug that Vitaliy discovered was fixed

New release every day ☺️

I found another bug. This declaration doesn't work in some cases: def cars_by_color(self, color): return select(car for car in self.cars if car.color == color) If we call it inside query as in your example select(p for p in Person if count(p.cars_by_color('yellow')) > 1) it works OK. But if we call it on instance like Person[1].cars_by_color('yellow') it throws TypeError: Query can only iterate over entity or another query (not a list of objects) However this expression return self.cars.select(lambda car: car.color == color) works well in both cases.

Thanks for reporting! It is not as critical as previous issue, so we will fix it a bit later

Hi again! Is it possible to use Python Enum as a discriminator with Pony? Something like class PersonType(Enum): regular_person = 0 student = 1 Professor = 2 class Person(db.Entity) type = Discriminator(PersonType) _discriminator_ = PersonType.regular_person

Right now Pony does not support enums

There was some pretty workaround in google

Regarding using enums?

Hi again! Is it possible to use Python Enum as a discriminator with Pony? Something like class PersonType(Enum): regular_person = 0 student = 1 Professor = 2 class Person(db.Entity) type = Discriminator(PersonType) _discriminator_ = PersonType.regular_person

I used an int and added the py_check keyword-arg to ensure that the ranges fit. The Enums __call__ function also seemed somehow usable (pycheck=PersonType). If I remember correctly?!

Hey, long time no see. I've always used ponyorm through something like heroku where there were pretty clear instructions on how to connect to postgres. Now I'm running it on my own VPS and I'm pretty lost

Regarding using enums?

Well, actually that was just custom converter, somethong like that from pony.orm.dbapiprovider import StrConverter class EnumConverter(StrConverter): def validate(self, val, **kwargs): if isinstance(val, str) and val in self.py_type: return self.py_type(str) if isinstance(val, self.py_type): return val raise ValueError('Instance or value of {} enum expected, got {}'.format(self.py_type, type(val))) def py2sql(self, val): return val.value def sql2py(self, value): return self.py_type(value) def sql_type(self): return 'ENUM ({})'.format(', '.join(repr(i.value) for i in self.py_type))

Hello again! Hybrid properties does not lifted on Sets. Will it be implemented in future releases?

Hi Vitaliy! I think we can add it

Great! Also I want to ask: can you extend group_concat function to return a list of objects instead of string in case when group_concat's argument is entity or primary key. For example: select((t, group_concat(t.messages)) for t in Ticket) return something like: [(Ticket[1], [Message[1], Message[2], Message[3]]), (Ticket[2], [Message[4], Message[5], Message[6]])] ?

Can you explain group_concat please?

SQL's GROUP_CONCAT function adds the contents of one field from different lines, inserting a delimiter between them (by default it is a comma).

Something like simpified JOIN with aggregation of several records to one field

More info here https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/

Great! Also I want to ask: can you extend group_concat function to return a list of objects instead of string in case when group_concat's argument is entity or primary key. For example: select((t, group_concat(t.messages)) for t in Ticket) return something like: [(Ticket[1], [Message[1], Message[2], Message[3]]), (Ticket[2], [Message[4], Message[5], Message[6]])] ?

Hmm... classic group_concat returns string. In order to return list of object we need to do some additional magic. In principle it is possible, but with multiple limitations: the object should have int primary key column, and the function should be in expression part of a generator. I think it is better not to use group_concat for this, but just translate queries like select((t, t.messages) for t in Ticket) this way (currently such queries are not supported) But I think it is not trivial to implement and other tasks are more urgent now

Ok, thank you for answer!

Is it possible to get a list of objects of different types from Pony's select statement? Like, if we have some student`s and `person`s in the database, and want to do `select on some criteria, so the returned list will consist of both students and persons?

It is possible if Student and Person have common inheritance root

For example, if Student is inherited from Person

The if you select Person, and some students are satisfy select criteria, you will select these students as well

So, when I do orm.select, the returned objects could have different types, but if I call 'person.select', what to I get? Would I get a list of person' objects whose `_discriminator_ equals to those for a pure person? Or would I get a list of all objects that inherit from 'person', in the form of 'person' objects?

The second. You will get Person and all its subclasses. There is no differense between Person.sellect(...) and select(x for x in Person)

You can look at generated SQL and check what conditions pony write for discriminator column

So, when Pony returns an object from any kind of query, the object will always be of the type corresponding to the _discriminator_ row. Thanks, Alexander !

Yes. And the same for attribute access. If you have Car.owner attribute of Person type, and the actual car1 owner is of Student type which is inherited from Person, then car1.owner will return the correct Student instance.

That is how one properly does ORM!

?

I attached file too earlier. Hi group - the code in previously attached file worked in an older version of PONY but now the function defined within the class definition throws an error. Does anyone have any ideas to help me. Thanks very much, David

Hi David! Can you show the error traceback?

Yes and thanks for super quick reply. How do I embed "code" into Telegraph message or should I attach as file?

some code

use -> [```some code```]

triple backquote

```Traceback (most recent call last): File "C:/cis/main.py", line 246, in onCompButtonClick self.showComponentFrame(name) File "C:/cis/main.py", line 251, in showComponentFrame self.activeFrames[name]=ComponentFrame(None, "<< " + name + " >>", name, self.frameSize) File "C:\cis\componentframe.py", line 81, in init self.loadComponents() File "C:\cis\componentframe.py", line 96, in loadComponents self.allComponents = self.componentFunctions.allComponents(stock=self.filterInStock) File "C:\cis\FunctionsInductors.py", line 68, in allComponents comp.dcr, comp.shielded, comp.numAMLS, File "C:\cis\dbDefines.py", line 103, in numAMLS return count(self.amls) File "C:\python\lib\site-packages\pony\orm\core.py", line 5188, in aggrfunc return std_func(*args) File "C:\python\lib\site-packages\pony\utils\utils.py", line 331, in count if hasattr(arg, 'count'): return arg.count() File "<string>", line 2, in count File "C:\python\lib\site-packages\pony\utils\utils.py", line 58, in cut_traceback return func(*args, **kwargs) File "C:\python\lib\site-packages\pony\orm\core.py", line 3112, in count if cache is None or not cache.is_alive: throw_db_session_is_over('read value of', obj, attr) File "C:\python\lib\site-packages\pony\orm\core.py", line 614, in throw_db_session_is_over throw(DatabaseSessionIsOver, msg % (action, safe_repr(obj), '.%s' % attr.name if attr else '')) File "C:\python\lib\site-packages\pony\utils\utils.py", line 98, in throw raise exc pony.orm.core.DatabaseSessionIsOver: Cannot read value of Inductors['IND00000',1].amls: the database session is over

before and after the code

The actual code (not pseudo) is in next attached file, see line 102

According to traceback, you use PonyORM 0.7.3, not the newest release (which is PonyORM 0.7.5) But if I understand correctly, the error is not caused by Pony. The reason for the error is that you call comp.numAMLS after the db_session is over. comp.numAMLS calls count(obj.collection), which needs to send query to the database, but after the db_session is over it is impossible to sent new queries

I understand. So how could I write a method of a db class that returns the length of a Set?

Do I understand correctly that your application is some GUI application and not web-based one?

Yes - but just solved it. I defined a function external to the class definition, with it's own database seesion. This works fine. I can share if you want?

yes, you can show it to be sure that the code is correct

``` @db_session def getCount(component): return select(count(c.amls) for c in Component if c == component)[:][0]

and here is the call: ``` @property def numAMLS(self): return getCount(self)

To me it looks like a hack

In this code you create a new db_session to perform a query. But the original object is created in previous db_session. The correct solution is to extend previous db_session so it embrace the query

In order to write correct code, it is necessary to understand what is the life cycle of ORM objects in application. How long they should exist in memory. Classical PonyORM usage is for web application. In web application, each HTTP request to the site has separate db_session. During these db_session ORM objects are loaded from the database, then they are used for generating HTML page or JSON and then they are discarded from the memore by garbahe collector after the db_session is over. Typical db_session in a web application exists less then a second, and then all objects are garbage collected. If you write GUI application, then it is not as clear what should be included in a single db_session. What is the expected size of your SQLite database (rougly?)

I think you can use a different approach: in the beginning of your application code you can write pony.MODE == 'INTERACTIVE' After that you will have implicit db_session just like in Python shell. Then it will be not necessary to wrap any function with db_session. But you need to call commit when the changes should be saved to the database. Also, you need to perform all work with the database from a single thread

Wow - thanks for the info and explanation. I am using GUI not web. Using MySQL not sqlite (sqlite when I developed then changed to MySQL). Most tables maximum few thousand rows. Will think about your approach and how much effort to do manual commit. Thanks again.

I think, the main question is how many concurrent users will be connected to the database at the same time. If it is the single-user application you may have pretty long db_sessions, but for concurrent users sessions should be short in order to avoid database locks and see fresh information from the database

Usually 1 person but there are times when >1 concurrently. I think using manual commit is still viable option , but need to think about time required to find all relevant pieces of code (and possible new bugs)

Alexander , now there is a tricky question: is it possible to have Pony-managed classes bound to some external object? Like, we have our class Base, and sometimes (specifically, when we do nosetests) there simultaneously exist several instances of it. And we want each instance of the Base class to have methods/members that are actually Pony-managed classes bound to DBs which are different for each instance of Base. Is it possible to do something like this?

Something like: class Base(): def __init__(self): db_path = ":memory:" db = orm.Database() db.bind(provider='sqlite', filename=db_path, create_db=True) class OrmManaged(db.Entity): someProperty = orm.PrimaryKey(int, auto=True) and then call a = Base() b = Base() a.OrmManaged() b.OrmManaged()

?