Instead, you need to wrap such a sequence in a single db_session

Ok! Thanks!

I will check my code for this case.

Is there a post commit hook in pony orm? So I can execute a Python function every time pony updates a object (row) on e.g. `Person` table?

Basically I want to notify that row to my celery working queue, so that a worker later would update/crate a fitting representation in elastic search

You can define entity methods after_insert, after_update and after_delete: class MyPerson(db.Entity): name = Required(str) def after_update(self): print('Person %s was updated' % self.name)

Awesome!

These methods are called after insert/update/delete, but before commit, and can contains some cascade actions

Is there something after the commit?

At this moment - no. If you use db_session as a decorator, you can use custom decorator which call hook after exiting from db_session. Something like that (not tested): def db_session_with_post_commit_hook(hook): def decorator(func): def new_func(*args, **kwargs): with db_session: func(*args, **kwargs): hook() return new_func return decorator def print_msg(): print('after commit!') @db_session_with_post_commit_hook(print_msg) def my_function(): select(obj for obj in MyObject)

<class 'tuple'>: ('Object Category[new:1] cannot be stored in the database. InternalError: ОШИБКА: текущая транзакция прервана, команды до конца блока транзакции игнорируются\n',)

http://pastebin.com/iWyDQRdf

I got this error on obj = self._get_or_create(parent=parent, **category)

each retry failed cause "transaction interrupted"

In some places i do manually commit to get pk after object creating, can this be reason for error above?

I don't think so

As I see, you apply @db_session to operations like _get_or_create it seems that @db_session should be defined on some outer function instead, because _get_or_create is just a part of transaction.

It's defined outer too

or you mean it should be single definition?

Not necessary, but nested db_session will be ignored with their retry options

it's failed on second object, when first will be written as new and reused as parent for next

What is primary key of Category?

class Category(db.Entity): id = orm.PrimaryKey(int, auto=True) en_name = orm.Required(str) ru_name = orm.Optional(str, nullable=True) ads = orm.Set(Advertiesment) categories = orm.Set('Category', reverse='parent') parent = orm.Optional('Category', reverse='categories', column='parent_id') orm.composite_key(en_name, parent)

Maybe you are putting to much into a single transaction. When you insert an object, it remains lock until the end of transaction, and if you do some slow operations after inserting an object you can get conflicts when another transaction tries to insert an object with the same unique key. If you use operation like get_or_create, it should be put in a short independent transaction, without updating all other stuff.

You may try to use raw sql query to call native INSERT ... ON CONFLICT DO NOTHING, maybe it will perform better

Previously I think db_session start new transaction. I can do this explicit?

It does

https://docs.ponyorm.com/transactions.html

OK, thank you for explanation

So, when commit new object to db, this cause flush db_session cache. And when i start next transaction, using previous objects causing `pony.orm.core.TransactionError("An attempt to mix objects belonging to different transactions") What am I doing wrong? It seems I use the wrong tools

You are right, exiting from db_session causes implicit commit and invalidates all used objects so they can be garbage collected

In my simple example i have list of categories. Where each category is parent for next. First has parent=None

Which idiomatic way to resolve such troubles? In other words: how to create object and use it in the same transaction.

"create" mean "get or create"

In typical situation in Pony you can create object and use it in the same transacton without any problem with db_session: obj = MyEntity(**kwargs) # or MyEntity.get(**kwargs) # do something with obj flush() # or commit() # do something else with obj # end of db_session - implicit commit Using 'get or create' is tricky, because you start encounter concurrency issues, when concurrent transactions insert the same object. In general it is better not to use 'get or create' pattern, especially in high-concurrent environment. Maybe you can restructure API in such a way that creating a Category happens as a separate atomic action, independent of any other actions.

https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

If I understand your use case correctly, you have a number of items which belongs to different hierarchical categories. I think it is better to represent such hierarchical categories as a text strings. For example, if you have some item placed in category "foo" -> "bar" -> "baz", you can have category text attribute with value 'foo|bar|baz'. If you make this attribute indexed, you can quickly find all items which belong to the same category. Another similar attribute may be for russian category names. Then if you add new item, you will not have any concurrency issues. A path into category hierarchy will be just a text column stored for that item. To search items belonging to some subcategory "foo|bar|baz" you just need to find all items which category path is between "foo|bar|baz" and "foo|bar|baz|zzzzzzzzzz". In my opinion such approach looks more manageable

Interesting idea

Hey team! How's migration going? Was looking to change a 0,1---0,1 relationship to 0,n---0,1

Hi @luckydonald! The migration tool is almost ready, we are fixing last bugs (I don't want to receive a flood of requests "the migration tool broke my database, please restore my data back") In your very specific case you don't need any migration at all — just replace web_session = Optional("WebSession") to web_sessions = Set("WebSession"), and new program will be able to continue working with the old database

I am driving right now. I will get back to you soon.

I have a slightly *strange* issue... I'm trying to extend the flask-security plugin to allow for Pony support (since I use it throughout my project, and I don't want to switch over to sqlalchemy). In a nutshell, they require a many-to-many relationship between a User and a Role class, however they also assume that to add a new entry to that Set(), the syntax is append. Pony uses add. Is there an easy/quick way to set up an alias from append to add?

You can do the following hack: from pony.orm.core import SetInstance SetInstance.append = SetInstance.add

Ahhh, nifty, let me try that....

Does the order of importing Set matter? Should I run that before I import set? or after? or does it not affect?

It doesn't matter

That worked great! Thanks!

Hi @luckydonald! The migration tool is almost ready, we are fixing last bugs (I don't want to receive a flood of requests "the migration tool broke my database, please restore my data back") In your very specific case you don't need any migration at all — just replace web_session = Optional("WebSession") to web_sessions = Set("WebSession"), and new program will be able to continue working with the old database

Now I am getting pony.orm.dbapiprovider.ProgrammingError: column "user" does not exist I assume on the WebSession table (the error could be more specific).

Just to cover that case in migrations

I now have class User(db.Entity): # ... web_session = Optional('WebSession') # TODO: remove later. Kept for compatibility. web_sessions = Set('WebSession', reverse="user") And class WebSession(db.Entity): # ... user = Optional(User)

I now have class User(db.Entity): # ... web_session = Optional('WebSession') # TODO: remove later. Kept for compatibility. web_sessions = Set('WebSession', reverse="user") And class WebSession(db.Entity): # ... user = Optional(User)

this would be handy to work, but eh.

@here any idea why pony would ever throw an AttributeError: pony.orm.dbproviders.sqlite doesn't have attribute provider_cls ?

Hey, I'm havin unexpected behaviour with bool: packs = orm.select( p for p in Pack if not p.nsfw ) Results in SELECT "p"."url", "p"."title", "p"."nsfw", "p"."owner_id", "p"."first_seen", "p"."last_crawled", "p"."sticker_count" FROM "pack" "p" WHERE ("p"."nsfw")::int = 0 Which doesn't seem to cover the not None=>True case.

Thanks for reporting. That's a bug, I'll fix it

Now I am getting pony.orm.dbapiprovider.ProgrammingError: column "user" does not exist I assume on the WebSession table (the error could be more specific).

Oops, I was wrong, sorry

Thanks for reporting. That's a bug, I'll fix it

I think it is great for foo==True or foo is True queries though

Not sure what postgres does with null::int

Hey team! How's migration going? Was looking to change a 0,1---0,1 relationship to 0,n---0,1

The operation "change type of relation" have many complex cases and will not be fully covered in first version of migration tool. So you will have to do that change from "one-to-one" to "one-to-many" manually. In order to do this, you need to perform the following command: alter table websession add column user int; After you start application the index and foreign key will be added automatically if db.generate_mapping method is called with create_tables=True option. You alse need to perform that update query: update websession w set user = (select id from "user" where web_session = w.id)

@here any idea why pony would ever throw an AttributeError: pony.orm.dbproviders.sqlite doesn't have attribute provider_cls ?

Hi Dave , what version of Pony you are using?

Not sure what postgres does with null::int

For postgres it would be like http://stackoverflow.com/a/41882010 SELECT * FROM table_name WHERE (boolean_column IS NULL OR NOT boolean_column)

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

Thanks for reporting. That's a bug, I'll fix it

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

Is there a better way to ignore an argument from a Table.get call than using if statements

something like: Table.get(name=name, version=version, ignore_on_none=True)

so if name or version is None it'll just pretend it wasn't passed

(if i'm just being dumb its okay to say that instead)

You can write something like that: def ignore_none(**kwargs): return {key: value for key, value in kwargs.items() if value is not None} Table.get(**ignore_none(name=name, version=version))

You can write something like that: def ignore_none(**kwargs): return {key: value for key, value in kwargs.items() if value is not None} Table.get(**ignore_none(name=name, version=version))

Thanks! That was my backup if it wasn't built in.

I'm getting a TypeError from sqltranslations. "ignore_none_select cannot be used this way"

The function itself seems to work, I tested it outside of a pony transaction and got the expected results

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

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

How would I execute (2) ?

name = 'X' version = 'Y' 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) objects = query[:]

Could you guy please start with code documentation? Most importantly this would be for the public methods, so IDEs like Pycharm could give quick documentation. Also reading the source is really hard this way.

I mean, you could just add a description whenever you edit somethin in that function anyway.

Also stuff like .to_json() is not documented online either: https://docs.ponyorm.com/api_reference.html?highlight=to_json#Query.to_json

I can't specify order for columns in composit_index?

it's not important, i just ask you plan or not adding tools for customization models

I remember you make a migration tool

If you specify composite_index(attr1, attr2), the columns will be in that order

Writing raw sql and used for deploy will be great. And if i right remember, it will be released with migration tool?

If you specify composite_index(attr1, attr2), the columns will be in that order

I mean: ("attr1", "attr2" DESC, "attr3")

Yes, you will be able to write raw sql in migrations

@luckydonald so you mean add docstrings to all functions?

Also stuff like .to_json() is not documented online either: https://docs.ponyorm.com/api_reference.html?highlight=to_json#Query.to_json

to_json() was an experimental API, we want to replace it with something completely different. I think we need to remove to_json mentions from the doc

@luckydonald so you mean add docstrings to all functions?

Yes.

to_json() was an experimental API, we want to replace it with something completely different. I think we need to remove to_json mentions from the doc

Meh :/ We were looking into building an elasticsearch adapter, where that to_json was providing the data in a very useful way...

Will `_get_schema_dict` (`to_json()["mapping"]`) and `.to_dict` be removed too?

to_json() was an experimental API, we want to replace it with something completely different. I think we need to remove to_json mentions from the doc

Also what are the future plans there?