Yes, the new API should be fully typed and async-compatible

Do you need any help on that somehow?

Would love to contribute

Sure, we can discuss. I'll be pretty busy onboarding a new job this week, but I should have time from September 24th (next Tuesday). We can connect starting this date.

Hi everyone, is there any news about the support of Python 3.13? And about the support of "is" in selects, like db.Something.select(lambda x: x.some_field is None)

Hi! It all depends on how many changes in a bytecode new version has. We havent look at it yet

How to implement logical deletion in Pony ORM?

How to implement logical deletion in Pony ORM?

like in any another ORM, make is_delete bool field

like in any another ORM, make is_delete bool field

How should I modify the entity's delete method to update the logical deletion field?

In general you do this in an application/service logic side. For example on DELETE /{item_id} you implement this not DELETE but UPDATE

good morning. https://bpa.st/Q6QQ want to use a function to create filters, but the resulting sql is just using the last value (notice that there is no "hello") _could_ do lambda x: x.title == orm.raw_sql(f"'{value}'")

on pony 1.0.2, python 3.10.14

Sadly newest version of Pony is 0.7.19 Isn't this link a virus?

silly me, we have a custom branch. now went to 0.7.17, the query that generates SELECT "x"."id", "bar"."id" FROM "MyEntity" "x", ( SELECT "x".* FROM "MyEntity" "x" WHERE "x"."title" = ? ) "bar" WHERE "x"."title" = ? arguments=('hello', 'hello') so takes 'hello' instead of 'world'

from pony import orm db = orm.Database('sqlite', ':memory:', create_db=True) class MyEntity(db.Entity): id = orm.PrimaryKey(int) title = orm.Required(str) description = orm.Required(str) db.generate_mapping(create_tables=True) def add_filter(sql, value): sql = sql.filter(lambda x: x.title == value) return sql with orm.db_session(): sql_a = orm.select(x for x in MyEntity) sql_b = orm.select(x for x in MyEntity) sql_a = add_filter(sql_a, 'hello') sql_b = add_filter(sql_b, 'world') sql_z = orm.select( (foo, bar) for foo in sql_a for bar in sql_b ) raw_query, arguments = sql_z._construct_sql_and_arguments()[:2] print(f'{raw_query}\n{arguments=}')

good morning. https://bpa.st/Q6QQ want to use a function to create filters, but the resulting sql is just using the last value (notice that there is no "hello") _could_ do lambda x: x.title == orm.raw_sql(f"'{value}'")

Thank you for reporting! It is a bug; I can reproduce it. I'll look into the details on Saturday. The bug is caused by the way how Pony constructs keys when caches query fragments.

Hello, I am new to Python and Pony. I was trying to make a sqlite db with write ahead logging enabled. When doing so I get an exception on the execute statement stating "cannot change into wal mode from within a transaction". I am not entirely sure why I am getting this error. class PonyCacheDatabase: dbFileName: str writeAheadLogCheck = False def __init__(self, dbFileName): self.dbFileName = dbFileName db.bind(provider='sqlite', filename=self.dbFileName, create_db=True) db.generate_mapping(create_tables=True) if PonyCacheDatabase.writeAheadLogCheck == False: with db_session: db.execute('PRAGMA journal_mode=WAL') PonyCacheDatabase.writeAheadLogCheck = True

Hello, I am new to Python and Pony. I was trying to make a sqlite db with write ahead logging enabled. When doing so I get an exception on the execute statement stating "cannot change into wal mode from within a transaction". I am not entirely sure why I am getting this error. class PonyCacheDatabase: dbFileName: str writeAheadLogCheck = False def __init__(self, dbFileName): self.dbFileName = dbFileName db.bind(provider='sqlite', filename=self.dbFileName, create_db=True) db.generate_mapping(create_tables=True) if PonyCacheDatabase.writeAheadLogCheck == False: with db_session: db.execute('PRAGMA journal_mode=WAL') PonyCacheDatabase.writeAheadLogCheck = True

Hello! That's because db_session is a transaction manager. What you trying to execute is on db management side - so shouldn't be done via orm.

Ah okay. I saw a post on the github page for pony, but the ORM doesn't appear to allow db execution without it being in a transaction. This didn't seem to work https://github.com/ponyorm/pony/issues/329

good morning, i've defined a column like foo = orm.Optional(orm.Json), but when looking at the .db file (sqlite) it shows foo JSON NOT NULL as the column definition was expecting that it would allow NULL, as it was declared as Optional if that is not convenient, then perhaps should provide a sane default: JSON NOT NULL DEFAULT {} this would easy migrations with external tools

# orm/dbschema.py class Column: def add_default(): if column.sql_default not in (None, True, False): append(case('DEFAULT')) append(column.sql_default) if column.sql_type == 'TEXT': append(case('DEFAULT')) append('""') elif column.sql_type == 'JSON': append(case('DEFAULT')) append('"{}"') ... could not find how to set sql_default for those particular columns, so resolved like that

Hello, also as fot strings pony prefers using empty values instead of nulls.

Hello, also as fot strings pony prefers using empty values instead of nulls.

sorry, ¿what's a fot string?

Optional strings are not nullable columns

Hi, here and there it is recommended to not use auto increment as ID because it exposes information about the items. Is there anything builtin pony (except for uuid) that solves this?

Hello. You can use any type of primary key that db allows. There is nothing actually "to solve"

Yes, so pony does not bring any mechanism to create a more randomized id? I have the feeling, if I create it myself I also have to manage collisions. So I Need a try catch for each insert

uuid is pretty good solution overall

Hi, here and there it is recommended to not use auto increment as ID because it exposes information about the items. Is there anything builtin pony (except for uuid) that solves this?

Hi! From the performance perspective, an autoincrement ID is the best you can have, so it is recommended that all primary keys in the database be defined as autoincrement IDs and used for foreign keys to link tables. However, from a business perspective, it may be beneficial to hide surrogate IDs. For this, you can define a secondary unique UUID key and expose it to users as if it was the primary key. class Product(db.Entity): id = PrimaryKey(int) uuid = Required(uuid.UUID, default=uuid.uuid4, unique=True) ... You can use other value types for the secondary key, but you need to write a custom logic that creates a new unique value that is not used yet.

Hi! From the performance perspective, an autoincrement ID is the best you can have, so it is recommended that all primary keys in the database be defined as autoincrement IDs and used for foreign keys to link tables. However, from a business perspective, it may be beneficial to hide surrogate IDs. For this, you can define a secondary unique UUID key and expose it to users as if it was the primary key. class Product(db.Entity): id = PrimaryKey(int) uuid = Required(uuid.UUID, default=uuid.uuid4, unique=True) ... You can use other value types for the secondary key, but you need to write a custom logic that creates a new unique value that is not used yet.

Hi, thank for this comment. That was my second idea and probably the way to go.

uuid is pretty good solution overall

AFAIK there is a performance impact. Though that's something I have to measure and decide if it's critical

UUID potentially has a large performance impact when it is used as a primary key, as a secondary key it should be fine

I don't believe this is noticeable nowadays

Thanks for your opinion on this topic and happy Christmas to you two :)

good morning. have several models that map to different tables, now they want to add some common attributes like: is_deleted, {created,modified,deleted}_at and comment. can't use inheritance as it will end with only one table, ¿any ideas? ps: happy new year

Hello, you can use inheritance in Pony. But for this case base entity would be too "meta" and it would end with really wide table.

indeed. also there would be too many rows

Hello, guys! Is there any news about the support of Python 3.12?

Hello, guys! Is there any news about the support of Python 3.12?

Hello, it is supported since august or so

Hi. Can someone tell me what I'm doing wrong? with db_session: found_object = Test.get(id=self.test_id) if found_object is None: return if not found_object.violation: found_object.violation = {} print(violation) found_object.violation = violation violation: {'interrupt_type': 'limit_overrun', 'limit_type': 'upper_limit', 'limit': 'co2_inh', 'time': 2} model: violation = Optional(Json) Error when updating a value: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON))' at line 4")

mariadb --version mariadb from 11.4.3-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using EditLine wrapper

I belive that might happen because pony doesnt support mariadb. It supports mysql but probably their syntax is different in some cases now

I resolved it by istalling it: violation = Optional(Json, nullable=True) By default Pony writes {} as an empty value and it cannot be overwritten, but NULL can be overwritten

Any update on python 3.13 support

Does current version of python works with 3.13?

It doesnt

Hey everyone! I am having some weirdness with manipulating an entity that was created with a different transaction. Here is the python code I am using to test from datetime import datetime from pony import orm from pony.orm import Required, db_session, Set db = orm.Database() class Foo(db.Entity): insert_time = Required( datetime, default = datetime.now()) key = Required(str) value = Required(str) bar = Set('Bar', reverse = "foo") class Bar(db.Entity): key = Required(str) value =Required(str) foo = Set('Foo', reverse="bar") def add_to_foo(self, key, value): if Foo.get(key = key): print(f"{key} already exists in foo!") return Foo(key = key, value = value) db.bind(provider = 'sqlite', filename = ':memory:') db.generate_mapping(create_tables=True, check_tables=True) # Simulates manipulating related objects with different transaction with db_session: f = Foo(key = "hello", value = "world") with db_session: b = Bar(key = "msg1", value = 'msg2', foo = f) with db_session: b.add_to_foo("msg3", "this is important!") This tosses the following error Traceback (most recent call last): File "/home/miversen/git/spotify-playlist-exporter/test.py", line 31, in <module> b = Bar(key = "msg1", value = 'msg2', foo = f) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/orm/core.py", line 4706, in __init__ avdict[attr] = attr.validate(val, obj, from_db=False) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/orm/core.py", line 2827, in validate throw(TransactionError, 'An attempt to mix objects belonging to different transactions') File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/utils/utils.py", line 99, in throw raise exc pony.orm.core.TransactionError: An attempt to mix objects belonging to different transactions I'm not really sure how to proceed here. This is of course a boiled down version of what I was actually doing when I encountered the error but any guidance would be great

Hey everyone! I am having some weirdness with manipulating an entity that was created with a different transaction. Here is the python code I am using to test from datetime import datetime from pony import orm from pony.orm import Required, db_session, Set db = orm.Database() class Foo(db.Entity): insert_time = Required( datetime, default = datetime.now()) key = Required(str) value = Required(str) bar = Set('Bar', reverse = "foo") class Bar(db.Entity): key = Required(str) value =Required(str) foo = Set('Foo', reverse="bar") def add_to_foo(self, key, value): if Foo.get(key = key): print(f"{key} already exists in foo!") return Foo(key = key, value = value) db.bind(provider = 'sqlite', filename = ':memory:') db.generate_mapping(create_tables=True, check_tables=True) # Simulates manipulating related objects with different transaction with db_session: f = Foo(key = "hello", value = "world") with db_session: b = Bar(key = "msg1", value = 'msg2', foo = f) with db_session: b.add_to_foo("msg3", "this is important!") This tosses the following error Traceback (most recent call last): File "/home/miversen/git/spotify-playlist-exporter/test.py", line 31, in <module> b = Bar(key = "msg1", value = 'msg2', foo = f) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/orm/core.py", line 4706, in __init__ avdict[attr] = attr.validate(val, obj, from_db=False) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/orm/core.py", line 2827, in validate throw(TransactionError, 'An attempt to mix objects belonging to different transactions') File "/home/miversen/git/spotify-playlist-exporter/.venv/lib/python3.12/site-packages/pony/utils/utils.py", line 99, in throw raise exc pony.orm.core.TransactionError: An attempt to mix objects belonging to different transactions I'm not really sure how to proceed here. This is of course a boiled down version of what I was actually doing when I encountered the error but any guidance would be great

Why don't you use one session for these 3 operations with DB?

The example above is a highly boiled down version of the problem. I _can_ use one session for everything but then if anything happens during that session, all transactions are rolled back and lost

Which is exactly what happened and how I ended up here trying to figure out the "correct" way to do this lol

The example above is a highly boiled down version of the problem. I _can_ use one session for everything but then if anything happens during that session, all transactions are rolled back and lost

Which is good

How so? If I have an insert (for example) that is completely fine, but there is a completely unrelated issue down the road, why would it be ok for that insert to be rolled back?

Encapsulating everything in one transaction feels very wrong

How so? If I have an insert (for example) that is completely fine, but there is a completely unrelated issue down the road, why would it be ok for that insert to be rolled back?

When you close the session, you can assume that this object is already read-only. If you need to make changes to it, you will have to find this object again in the new session and then change it. with db_session: f = Foo(key = "hello", value = "world") b = Bar(key = "msg1", value = 'msg2', foo = f) b.add_to_foo("msg3", "this is important!") looks fine for me

That makes sense, but it counters my understanding of how pony works. I have revised the above code to better reflect what I am actually seeing from pony import orm from pony.orm import Required, db_session, Set db = orm.Database() class Foo(db.Entity): key = Required(str) value = Required(str) bar = Set('Bar', reverse = "foo") class Bar(db.Entity): key = Required(str) value =Required(str) foo = Set('Foo', reverse="bar") def add_to_foo(self, key, value): if Foo.get(key = key): print(f"{key} already exists in foo!") return Foo(key = key, value = value) db.bind(provider = 'sqlite', filename = ':memory:') db.generate_mapping(create_tables=True, check_tables=True) @db_session def create_foo() -> Foo: return Foo(key = "hello", value = "world") @db_session def create_bar(foo: Foo) -> Bar: return Bar(key = "msg1", value = "msg2", foo = foo) @db_session def add_item_to_foo(bar: Bar): bar.add_to_foo(key = "msg3", value = "this is important!") foo = create_foo() bar = create_bar(foo) # ... Do all kinds of other completely unrelated tasks add_item_to_foo(bar)

If _anything_ fails in "do all kinds of other completely unrelated tasks", foo and bar are lost as they never got written to memory

*the database.

You can use commit() if you need to save something inside a session, BTW.

I understand that. I just don't understand why I should have to wrap my entire program under one session. That is not how the documentation reads.

I understand that. I just don't understand why I should have to wrap my entire program under one session. That is not how the documentation reads.

You don't have to. Just open a session only when you need to read or write something from/to DB.

I feel like that is what I am trying to do with the above code?

I am opening a session for each of the methods that manipulate the database. But I get the exception pony.orm.core.TransactionError: An attempt to mix objects belonging to different transactions

I suppose my issue is that I don't know what I am doing wrong here. https://docs.ponyorm.org/firststeps.html#db-session Comparing what I have to the documentation, it _seems_ like I am doing it correctly but I am getting different results. The error is clear that I am doing something wrong but I don't know how to make it not wrong

I am opening a session for each of the methods that manipulate the database. But I get the exception pony.orm.core.TransactionError: An attempt to mix objects belonging to different transactions

You cannot refer to an object from another session in one session. As I said above, then you will first have to find this object in the database again.

something like @db_session def add_item_to_foo(bar: Bar): bar = Bar.get(id=bar.id) bar.add_to_foo(key = "msg3", value = "this is important!")

Ohhhhh

That makes sense, I suppose I was misunderstanding what you said earlier. So I can't treat entities quite the same as other python objects due to how transactions work?

After the session is closed, you cannot change this object so that the changes are recorded in the database.

I'm tracking now. I may be back, lemme go make some changes in my code and see if I can grok through how to correctly do that

That made it work :) I also opened an issue on the repo, I am going to summarize our convo there and close the issue out

Thank you so much @supershurik !

Thank you so much @supershurik !

You're welcome :)

@metaprogrammer any plans to support python 3.13 in near future ?

Any one know about convert natural language into sql query useing pony ORM

There is no such thing as natural language processing in ORM. You should take either a parsing route, or an AI route. For parsing, Pony is quite handy for actually constructing queries. For AI... Pony can execute raw SQL queries, and you can get schema from object definitions. If you use capable LLM, it will know Pony and can produce Python code, which is easier to review. (For safety, you should really review any mutating code from LLM, it almost as dangerous as UGC)

good morning, I need to read a sqlite db that has this table class Thumb(db.Entity): _table_ = 'thumbnails' Id = orm.Required(str, index=True) Image = orm.Optional(bytes) the problem is that pony expects id to be an int, tries to convert and fails with an exception

have to go now, will connect tomorrow

Hi, I think I have a fix for some of the issues with python 3.12 support, would anyone here be in a position to give it a review? https://github.com/ponyorm/pony/pull/741

Hi, I think I have a fix for some of the issues with python 3.12 support, would anyone here be in a position to give it a review? https://github.com/ponyorm/pony/pull/741

Hi Nick! Thank you for this! I can review it on Saturday

good morning, I need to read a sqlite db that has this table class Thumb(db.Entity): _table_ = 'thumbnails' Id = orm.Required(str, index=True) Image = orm.Optional(bytes) the problem is that pony expects id to be an int, tries to convert and fails with an exception

sorry, had to leave in a hurry yesterday. when doing db.Thumb.get(Id=id_) got this error Traceback (most recent call last): File "python3.10/site-packages/pony/orm/core.py", line 4007, in get try: return entity._find_one_(kwargs) # can throw MultipleObjectsFoundError File "python3.10/site-packages/pony/orm/core.py", line 4114, in _find_one_ if obj is None: obj = entity._find_in_db_(avdict, unique, for_update, nowait, skip_locked) File "python3.10/site-packages/pony/orm/core.py", line 4174, in _find_in_db_ objects = entity._fetch_objects(cursor, attr_offsets, 1, for_update, avdict) File "python3.10/site-packages/pony/orm/core.py", line 4305, in _fetch_objects real_entity_subclass, pkval, avdict = entity._parse_row_(row, attr_offsets) File "python3.10/site-packages/pony/orm/core.py", line 4345, in _parse_row_ avdict[attr] = attr.parse_value(row, offsets, cache.dbvals_deduplication_cache) File "python3.10/site-packages/pony/orm/core.py", line 2232, in parse_value dbval = attr.validate(row[offset], None, attr.entity, from_db=True) File "python3.10/site-packages/pony/orm/core.py", line 2534, in validate val = Attribute.validate(attr, val, obj, entity, from_db) File "python3.10/site-packages/pony/orm/core.py", line 2204, in validate if from_db: return converter.sql2py(val) File "python3.10/site-packages/pony/orm/dbapiprovider.py", line 550, in sql2py return int(val) ValueError: invalid literal for int() with base 10: 'Media/WhatsApp/Media/WhatsApp Images/Sent/IMG-WA0001.jpg40927'

the db is generated by another software and it's quite big. i think that pony expects a column id with int type, not sure if can opt out from that