At this moment you need to use lambdas. In principle it is possible to add kwargs support too

I mean, I wouldn't change the select method, but there currently is no .get method there anyway, isn't it? EntityMeta: def get(entity, *args, **kwargs): if args: return entity._query_from_args_(args, kwargs, frame_depth=3).get() try: return entity._find_one_(kwargs) # can throw MultipleObjectsFoundError except ObjectNotFound: return None

At this moment you can't do obj.collection.select(foo=bar) or obj.collection.select().get(foo=bar) but you can do obj.collection.select().filter(foo=bar).get() or obj.collection.select(lambda item: item.foo == bar) I think there is no technical limitations which prevent implementation of the first two examples, so we can add them in the future

Or maybe I don't fully understand the question

yeah, but I would recommend adding obj.collection.get(foo=bar) similar to Entity.get(foo=bar),

Ah, ok, this is a good idea

Is there some way to put the project into the parent Access, but still keep access_users and access_tokens?

Or would I just do a = AccessUser.get(user=u, project=234) if a is None: .... ?

Basically like this

Maybe you can just define user and token as optional attributes of Access entity and remove subclasses

But this should have the benifit, that I can't end up with Access entities which have neither a token and a user or both of them.

Or would those composite_index statements fail when using the parent class attributes?

Can AccessToken later be transformed to AccessUser?

Can AccessToken later be transformed to AccessUser?

No, either you get a token link, or you are a registered telegram user.

Hi I have a doubt, I added a column to a alredy created table via "alter table" and added this to the code and pony says that don't exist

Hi I have a doubt, I added a column to a alredy created table via "alter table" and added this to the code and pony says that don't exist

Yeah, you have to create that column manually

i did but pony says it dont exist is the model stored in som place apart of the code?

Hi I have a doubt, I added a column to a alredy created table via "alter table" and added this to the code and pony says that don't exist

What database do you use?

postgres

Maybe you need to commit changes after you did alter table

The next time the script restarted, it should work

In PostgreSQL, alter table command can be part of transaction. Admin interface can be in auto-commit mode or in manual commit mode

it's possible that it's a transaction problem. I will check it, thank you

Also the column name could be different to what pony expects, try setting column='...', e.g. column = Required(str, column='database_column')

Also the column name could be different to what pony expects, try setting column='...', e.g. column = Required(str, column='database_column')

thank you

If you want to have capital letters in a colum name, like "MyColumn_Name", you need to use double quotes around it in alter table command: alter table mytable add "MyColumn_Name" int, or else it will be created as "mycolumn_name". In general, it is easier to use lower-case names in PostgreSQL

Or would those composite_index statements fail when using the parent class attributes?

I think composite index should work in this case

I think composite index should work in this case

Thank you

> But this should have the benifit, that I can't end up with Access entities which have neither a token and a user or both of them. I think you can enforce this by adding check constraint to a table alter table access add constraint check_access_token_and_user check (token is not null and user is null or token is null and user is not null) and use just a single Access entity

> But this should have the benifit, that I can't end up with Access entities which have neither a token and a user or both of them. I think you can enforce this by adding check constraint to a table alter table access add constraint check_access_token_and_user check (token is not null and user is null or token is null and user is not null) and use just a single Access entity

Yeah, but I don't like manual SQL, it is difficult to version control, and am to lazy to set up pony_up for the upgrade automation goodness.

Join the official Python Developers Survey 2018 and win valuable prizes: https://surveys.jetbrains.com/s3/c19-python-developers-survey-2018

Can we get PonyORM in this year, too?

Hi Krzystsof! Thank you for the full and detailed answer! I will answer a little later, maybe tomorrow

I know I'm a liiitle bit late, but would you be so kind to explain the details of the implementation? I'd rather avoid any mistakes in analyzing the code.

Hi Krzystsof! Sorry for the late reply. This topic was a bit difficult to describe in simple words.

In PonyORM we have composite and non-composite primary keys. At the same time, we can speak about "high-level" and "low-level" (or "raw") primary keys. The difference between high-level and low-lewel primary keys is that a high-level composite key can consist of another objects, while a low-level composite key is a flat tuple which consists only of primitive values that corresponds to database column values. You can pass high-level PK to a __getitem__ method of entity class in order to get corresponding object: obj = MyEntity[high_level_pk] For existing object you can obtain its high-level primary key value by accessing obj._pkval_ You can get low-level PK by calling get_pk method: low_level_pk = obj.get_pk() And in most cases (but not always) you could pass low-level PK to __getitem__ method: obj = MyEntity[low_level_pk] But in some cases it did not work because of a bug. Thanks to Krzysztof, this bug was fixed now.

But also Krzystsof suggested to add a new type of primary key (which can be called "nested primary keys"), which is something in-between high-level and low-level primary keys. Nested primary keys consists of primitive values or recursively of another nested primary keys. In most cases they are the same as a flat low-level primary keys, but they will be different if object has a composite high-level primary key, which consist of objects that in turn have composite high-level primary key. Actually, this topic is a bit hard to understand and probably is irrelevant for 90% of PonyORM users, because in most entity classes primary keys are not composite. If PK is just a simple id value, then high-level and low-level primary keys will be the same - just a simple id value. To discover the difference between low-level primary key and a new supposed type of primary keys you should have not just a model which PK is composite, but a model which PK consists of another models with composite primary keys. Considering such situations may be important for someone who, for example, writes some universal serialization library for PonyORM objects, but not for a typical user. So, probably, most members of this chat can skip further discussion without any problem.

Consider the following scheme: from datetime import datetime from pony.orm import * db = Database('sqlite', ':memory:') class Student(db.Entity): id = PrimaryKey(int, auto=True) name = Required(str) class Subject(db.Entity): name = PrimaryKey(str) courses = Set("Course") class Course(db.Entity): subject = Required("Subject") semester = Required(int) PrimaryKey(subject, semester) lectures = Set("Lecture") class Building(db.Entity): name = PrimaryKey(str) rooms = Set("Room") class Room(db.Entity): building = Required("Building") room_number = Required(int) PrimaryKey(building, room_number) lectures = Set("Lecture") class Lecture(db.Entity): course = Required("Course") room = Required("Room") dt = Required(datetime) PrimaryKey(course, room, dt) db.generate_mapping(create_tables=True) with db_session(sql_debug=True): john = Student(name='John') math = Subject(name='Math') c1 = Course(subject=math, semester=2) b = Building(name='BuildingA') r = Room(building=b, room_number=16) lect = Lecture(course=c1, room=r, dt=datetime(2018, 10, 26, 12, 00)) In this diagram, there are some classes with non-composite primary keys: Student (with int primary key), Subject and Building (with str primary keys). Another classes have composite primary keys.

High-level PKs is what PonyORM itself uses inside db_session identity map cache. Each object loaded inside a db_session is stored in a special dict which keys are high-level PKs. It is possible to use __getitem__ method of entity class to get an instance of that class by its high-level primary key value. You can write: stud1 = Student[123] subj1 = Subject["Math"] course1 = Course[subj1, 2] For course1 the high-level PK is a tuple (Subject["Math"], 2) (the number 2 is the semester's number). The low-level PK is a tuple ("Math", 2). This low-level key corresponds to a database primary key.

For Lecture object a high-level PK may be something like (Subject["Math", 2], Room["BuildingA", 16], datetime(2018, 10, 29, 12, 00)) this is a tuple of three elements. The low-level PK of the same object will be a 5-element tuple ("Math", 2, "BuildingA", 16, datetime(2018, 10, 29, 12, 00))

Krzystsof wants to have an additional "nested" primary key of form (("Math", 2), ("BuildingA", 16), datetime(2018, 10, 29, 12, 00)) arguing that it is more convenient, and asked if it is possible to add support of "nested" primary keys too (for example, instead of currently used low-level flat primary keys).

To this I want to say that I do not see the possibility of giving up of currently used high-level or low-level primary keys. High-level PK is what realy is entity primary key from a logical point of view, like (some_subject, some_room, some_date) for Lecture example above. Low-level PK is what really need when we load a row from Lecture table and want to find or create a corresponding object in db_session cache.

But in principle it is possible to have "nested" primary keys as a some additional form, with the following API: nested_pk = obj.get_pk(nested=True) obj = MyEntity[nested_pk]

We thought "why not" and actually tried to do that. It was relatively easy to implement obj.get_pk(nested=True), but then we discover that implementing Entity.__getitem__ for nested primary keys is surprisingly hard. The reason is the following: __getitem__ already supports flat low-level primary keys (it is convenient and also we cannot remove it because it breaks backward compatibility). For composite keys, high-level primary keys, flat low-level primary keys and nested primary keys are all tuples, and just looking at that tuple it is hard to say what type of PK it is. But nested PKs required different processing, and it turned out to be crazy hard to write correct __getitem__ method which accepts both flat and nested low-level primary keys and don't fail on some exotic test cases. We spent the whole day, but could not make the __getitem__ implementation, which successfully passed all the tests.

Actually, Python Zen says "Flat is better then nested". Current high-level and low-level primary keys are all flat. After unsuccessful attempt to implement nested primary keys I want to leave API as is, with just flat primary keys.

Now I want to say that with current API __getitem__ accepts flat low-level primary keys as a substitution for object inside a high-level primary key, so the next line will actually work: lect = Lecture[("Math", 2), ("BuildingA", 16), datetime(2018, 10, 29, 12, 00)] But for general "nested" PK with multiple level of nesting it will not work.

Krzystsof, if you really want to use nested primary keys, you can implement separate methods get_nested_pk(obj) and get_object_by_nested_pk(nested_pk) But in my opinion it's too complex task to add support of "nested" primary keys to Entity.__getitem__.

Whew, this was a complex topic to describe :)

Hey, I'm getting an error pony.orm.core.ERDiagramError: Inconsistent reverse attributes Project.access_tokens and Access.project. Here is an excerpt of the database declaration: class Project(db.Entity): id = PrimaryKey(str) name = Required(str) # project identifier owner = Required(User, reverse='projects') description = Optional(str) originals = Set('Original') access_all = Required(bool, sql_default='FALSE', default=False) # access_users = Set('User', reverse='access_projects', table="access_user", column="user") access_users = Set('AccessUser', reverse='project') access_tokens = Set('AccessToken', reverse='project') created_at = Required(datetime, sql_default='NOW()') ... class Access(db.Entity): id = PrimaryKey(int, auto=True) project = Required(Project) allow_edit_project = Required(bool, sql_default='FALSE', default=False) allow_edit_originals = Required(bool, sql_default='FALSE', default=False) allow_translate = Required(bool, sql_default='FALSE', default=False) class AccessToken(Access): token = Required(UUID) composite_index(Access.project, token) class AccessUser(Access): user = Required(User) composite_index(Access.project, user) ... db.bind(provider='sqlite', filename=':memory:') db.generate_mapping(create_tables=True)

AccessUser.project and AccessToken.project is the same attribute. Instead of access_users = Set('AccessUser', reverse='project') access_tokens = Set('AccessToken', reverse='project') You need to define a single collection, like access_items = Set('Access', reverse='project') And then filter it in select, like: access_users = project1.access_items.select(lambda access: access.user is not None)

At this moment you cannot have several virtual collections based on the same physical attribute

I think you have two options:

1) Have just a single class Access with two optional attributes user and token, and a single collection project.access_items 2) Have two separate clases AccessUser and AccessToken which are not inherited from the same base class. In this case you need to duplicate attributes allow_edit_project, allow_edit_originals and allow_translate

In the second case there are duplication of attributes, but then the code will be more clear

In the future we need to add two features to Pony: 1) Abstract base classes, so AccessUser and AccessToken, can share the same attribute definitions from abstract Access base class, but have different tables, and 2) virtual collections, based on the physical collection with additional filter applied: class Project(db.Entity): access_items = Set("Access") access_users = access_items.filter(lambda item: isinstance(item, AccessUser)) access_tokens = access_items.filter(lambda item: isinstance(item, AccessToken))

Testing shows this is already working if I make the proposed project.access_items changes, I don't need to change the subclassing.

Is there some UPSERT available, or what is there a way to make p = Project.get(**kwargs) if not p: p = Project(**kwargs) behaving atomic?

Is there some UPSERT available, or what is there a way to make p = Project.get(**kwargs) if not p: p = Project(**kwargs) behaving atomic?

*if not p

I think it's time to add upsert to Pony Without it you probably can use the following trick: add some dummy entity and do SELECT ... FOR UPDATE to it. class UpsertLock(db.Entity): id = PrimaryKey(int) with db_session: # on application start if not UpsertLock.exists(): obj = UpsertLock() with db_session: UpsertLock.select().for_update()[:] ... p = Progect.get(**kwargs) or Project(**kwargs)

I think it's time to add upsert to Pony Without it you probably can use the following trick: add some dummy entity and do SELECT ... FOR UPDATE to it. class UpsertLock(db.Entity): id = PrimaryKey(int) with db_session: # on application start if not UpsertLock.exists(): obj = UpsertLock() with db_session: UpsertLock.select().for_update()[:] ... p = Progect.get(**kwargs) or Project(**kwargs)

When will that lock be released?

on commit

So in interactive mode (unit tests) I need orm.commit()?

yes

But probably unit tests don't need interactive mode

Hmm...

I get pony.orm.core.DatabaseSessionIsOver: Cannot delete object Project[6]: the database session is over This is the relevant part of my unittest: class TestProjectIntegrations(TestCase): def setUp(self): self.app = app.test_client() # end def def tearDown(self): for entry in self.delete_me: entry.delete() # end def orm.commit() # end with # end def def test_get(self): p = Project(owner=self.user, name='prefilled') self.delete_me.append(p) #... Basically I try to collect all the stuff created in tests, and delete them in tearDown...

db_session is over on rollback or on exiting from db_session. An exception inside PostgreSQL (for example, IntergrityError) will cause immediate rollback

I think it is more robust (albeit slower) to clear all tables and re-create rows in setUp

The code that manually tracks all changes may be brittle

My workaround now is: self.delete_me.append((Project, dict(id=p.id))) With cleanup: for clazz, select in self.delete_me: clazz.get(**select).delete() # end def

Hi!

Hello

I just started using pony ORM, works pretty well

I have been lurking here for a few weeks now. Better say hi @metaprogrammer

I don't have any use for PonyORM at the moment, but I love the idea of using generator expressions for querying.

Great idea.

Hi, you are welcome!

@metaprogrammer Are you the lead developer on this project?

yes

I remember an interview on Talk Python To Me a couple of years ago. That must have been you.