If you select the two Store objects with the same id in two different db_session they are not considered equal, so you need to compare store.id instead

Hmm, I understand. I will check it

Thanks

hi, can i do id column with autoinsert but not Primarykey?

maybe with the hook berfore_insert

Pony does not support such columns at this moment. You can manually execute alter table mytable add column id serial to add such column, but can't add such attribute to model

Hello

First I would like to thanks for this awesome package

I would like to know what happens if any error (python logic included) happens inside a db_session block

Does it roll back all data?

with db_session:

Hi!

Im used to django orm, but now I want to run my programs with pypy and found pony orm, that is already faster and run with pypy

Yes, it performs rollback

Nice

Only drawback right now is to recreate all entities

There are too many 😅

Maybe you can avoid exception?

Maybe you can avoid exception?

I can with some effort... Right now I generate data with python logic inside django transaction block, there are some chained operations (logic and sql), if any fails all changes should be rollbacked. Exception are useful for me in case I'm trying to change or delete an already deleted row

Did anybody elsed noticed mypy complaining about missing types for Pony? I'm sifting through typeshed repo (as per mypy's docs) at the moment

Looks like TypeShed agreed, that they're missing: https://github.com/python/typeshed/issues/4361#issuecomment-663746390 PRs welcome 😊

Pony code still support Python2, and Python2 does not understand type annotations

Thus, you can't add .pyi files for Python3 support?

Feel free to comment this fact on the issue. However, I saw a 2 directory on typeshed for Python2 libraries. Plus, a 2and3 for projects supporting both.

So I think, there must be some way. I even found an issue in your bug tracker (and linked it in the issue I reported above).

I think adding .pyi files is possible

Shall I fill an issue against Pony?

Yes, please

Done. https://github.com/ponyorm/pony/issues/537

Thanks!

You're welcome 😊

Morning

Hi, looks like pony fails to use json notation with raw_sql queries on MySQL, or I'm doing something wrong. I use the following function to generate a query to use with the raw_sql function. def filter_to_rawsql(match_obj='elem', attr_data='json_data', **filters): array_parameters = ['jobs', 'companies', 'salaris'] array_attr_filter = "{value} MEMBER OF(`{match_obj}`.`{attr_data}`->>'$.{attr}')" obj_attr_filter = "`{match_obj}`.`{attr}` = {value}" filters_processed = [] for attr, value in filters.items(): value = (f"'{value}'") if isinstance(value, str) else value if attr in array_parameters: filters_processed.append(array_attr_filter.format(**locals())) else: filters_processed.append(obj_attr_filter.format(**locals())) query = " and ".join(filters_processed) return query I use it like this ids = select(elem.id for elem in Table if orm.raw_sql(filter_to_rawsql(**filters)))[:] And pony fails to perform the query, with the traceback below, is it expected? How I should do it instead? Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/pony/orm/ormtypes.py", line 88, in parse_raw_sql try: expr, _ = parse_expr(sql, i+1) File "/usr/local/lib/python3.7/site-packages/pony/utils/utils.py", line 279, in parse_expr if match is None: raise ValueError() ValueError During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 5629, in extract_vars try: value = extractor(globals, locals) File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 370, in extractor return eval(code, globals, locals) File "orm.raw_sql(query)", line 1, in <module> from datetime import datetime File "/usr/local/lib/python3.7/site-packages/pony/orm/ormtypes.py", line 103, in raw_sql return RawSQL(sql, globals, locals, result_type) File "/usr/local/lib/python3.7/site-packages/pony/orm/ormtypes.py", line 110, in __init__ self.items, self.codes = parse_raw_sql(sql) File "/usr/local/lib/python3.7/site-packages/pony/orm/ormtypes.py", line 90, in parse_raw_sql raise ValueError(sql[i:]) ValueError: $.jobs')

In Pony raw_sql text you need to write $ as $$, because single $ refers to Python variables, like a = 10 select(x for x in MyEntity if raw_sql("x.column1 > $a"))

So you need to replace $.{attr} to $$.{attr}

thanks Alexander

pony.orm.core.CommitException: OperationalError: database is locked What happened? There have been no updates or anything else in my code.

It solved itself, but I don't know how.

This error happens when one of processes or threads starts db_session, performs INSERT/UPDATE/DELETE and continue working for long time without issuing COMMIT (that is, without finishing this db_session). In this case other processes / threads cannot start db_session. The sqlite driver will wait until timeout (5 seconds by default) and then will throw `OperationalError: database is locked`error

This error happens when one of processes or threads starts db_session, performs INSERT/UPDATE/DELETE and continue working for long time without issuing COMMIT (that is, without finishing this db_session). In this case other processes / threads cannot start db_session. The sqlite driver will wait until timeout (5 seconds by default) and then will throw `OperationalError: database is locked`error

Thank you. In this case, what has resolved the issue?

Probably that long-running process that held database lock was finished

Like, maybe you have opened Python console and started doing updates without subsequent commit. In that case another processes weren't able to access the database

Like, maybe you have opened Python console and started doing updates without subsequent commit. In that case another processes weren't able to access the database

Yes, I tried to update the code with a longer timeout.

Hi! Pony plans to support bson fields?

Hi! You mean this? https://github.com/maciekgajewski/postgresbson

Hi! You mean this? https://github.com/maciekgajewski/postgresbson

Permanent link to the maciekgajewski/postgresbson project you mentioned. (?)

Hi! You mean this? https://github.com/maciekgajewski/postgresbson

Yes.

We don't have such plans yet

Thank.

post = Post.select( lambda p: not p.processed and p.scheduled > datetime.datetime.now() + datetime.timedelta(seconds=int(p.timezone_offset_hours_utc * 3600))

I’m getting a not implemented error on this, is there a trick to get it working?

Try replace datetime.timedelta(...) to raw_sql("(p.timezone_offset_hours_utc * interval '1 hour')")

TypeError: Unsupported operand types 'datetime' and '<pony.orm.ormtypes.RawSQLType object at 0x7fa59441f9b0>

raw_sql("(p.timezone_offset_hours_utc * interval '1 hour')", result_type=timedelta)

That seems to have worked, thank you.

But I’ve now hit another weird error:

pony.orm.dbapiprovider.ProgrammingError: improper qualified name (too many dotted names): p.facebook_page.facebook_business.facebook_ad_account.facebook_timezone_offset_hours_utc

That’s in the raw sql fragment I think

That's strange... In raw sql code you have just p.timezone_offset_hours_utc, correct?

No, I had simplified for my question

pony.orm.dbapiprovider.ProgrammingError: improper qualified name (too many dotted names): p.facebook_page.facebook_business.facebook_ad_account.facebook_timezone_offset_hours_utc

that is the actual “path” in the fragment

full query now:

facebook_post = models.FacebookScheduledPost.select( lambda p: not p.processed and p.scheduled > datetime.datetime.now() + models.raw_sql("(p.facebook_page.facebook_business.facebook_ad_account.facebook_timezone_offset_hours_utc * interval '1 hour')", result_type=datetime.timedelta) ).first()

I am using postgres btw

When you do path expression like p.facebook_page.facebook_business.facebook_ad_account.facebook_timezone_offset_hours_utc in Pony, it joins all these tables to get the necessary column value But inside raw_sql such path expressions are not processed, it is expected to just have table_alias_name.column_name inside raw_sql fragment So, you need to force Pony to join tables outside of raw_sql, look at the table alias in debug SQL output, and then use the same alias in raw_sql It will be something like that: facebook_post = models.FacebookScheduledPost.select( lambda p: not p.processed # dummy condition to force joining all these tables and p.facebook_page.facebook_business.facebook_ad_account.facebook_timezone_offset_hours_utc >= 0 and p.scheduled > datetime.datetime.now() + models.raw_sql("""("some-alias".facebook_timezone_offset_hours_utc * interval '1 hour')""", result_type=datetime.timedelta) ).first()

Maybe I can avoid raw sql by also storing the offset as a (in pony) timedelta in another column?

I think yes

using a timedelta worked 🙂

Cool

Hi, there's a method like this in pony for items pagination?

You can do query.page(3, page_size=20) But it returns query result (list of objects), not some Paginator class instance

You can do query.page(3, page_size=20) But it returns query result (list of objects), not some Paginator class instance

Ok thank you, so then I use the result as a normal query result?

Yes, it just adds limit and offset

Thank you

Hi

Can I store an orm object, do some changes and save at specific time?

I want to just save it when the user sessions terminates

Is that possible?

Hi! You mean, not earlier? Is user session consists of multiple db_sessons in your application?

Yes