I cannot thank you enough for this constant, awesome support here. There were a few ideas in your example that did things differently from my approach. It's not just Pony, I'm learning how to write better code in general. Thank you so much for taking the time to again and again write detailed example code, I really appreciate it! 😌

You are welcome :)

Hey @metaprogrammer I have another question, I got this error: pony.orm.core.ConstraintError: Cannot unlink Input[8834207] from previous Output[10293640] object, because Input.vout attribute is required Database models looks like this: class Input(db.Entity): transaction = orm.Required("Transaction") vout = orm.Required("Output") class Output(db.Entity): _table_ = "chain_outputs" amount = orm.Required(float) address = orm.Required("Address") vin = orm.Optional("Input", cascade_delete=True)

I thought cascade_delete=True will prevent this constraint error since input will be deleted before output is but seams like I was wrong

Hey @metaprogrammer I have another question, I got this error: pony.orm.core.ConstraintError: Cannot unlink Input[8834207] from previous Output[10293640] object, because Input.vout attribute is required Database models looks like this: class Input(db.Entity): transaction = orm.Required("Transaction") vout = orm.Required("Output") class Output(db.Entity): _table_ = "chain_outputs" amount = orm.Required(float) address = orm.Required("Address") vin = orm.Optional("Input", cascade_delete=True)

Nevermind, I realized that this issue was caused by second thread running in background (Falsk shenanigans)

Everything works perfectly fine in single thread

Are there any known problems with using pony with AWS Aurora Serverless (Postgres version)?

Is there a way to integrate Pony with an async application? I am making a discord bot using Discord.py so I was looking for a good asynchronous ORM for Postgres.

Is there a way to integrate Pony with an async application? I am making a discord bot using Discord.py so I was looking for a good asynchronous ORM for Postgres.

https://docs.ponyorm.org/integration_with_fastapi.html#async-and-db-session

https://docs.ponyorm.org/integration_with_fastapi.html#async-and-db-session

That just looks like a workaround for using it, will I be better off with Tortoise?

That just looks like a workaround for using it, will I be better off with Tortoise?

yes, pony doesn't support async for now ; the developers of the project are focused on migrations for now ; so if you're concerned about async, i suggest using sql-alchemy (more maturity)

Are there any known problems with using pony with AWS Aurora Serverless (Postgres version)?

I have tried with aurora serverless MySQL non-issue

I have json column defined in entity, Json structure is an array of dictionary like [{'name':'foo', 'age': 35, 'gender':'male'},{'name':'bar', 'age': 45, 'gender':'male'}] Now how do I query for age filed in Json @metaprogrammer request your help

Anyone please suggest me the possible solution

Product.select(lambda p: p.info['age'] > 5)

https://docs.ponyorm.org/json.html#querying-json-structures

Oh I misread that as a simple dict

Yes it's not simple dict, it's list of dict

I have json column defined in entity, Json structure is an array of dictionary like [{'name':'foo', 'age': 35, 'gender':'male'},{'name':'bar', 'age': 45, 'gender':'male'}] Now how do I query for age filed in Json @metaprogrammer request your help

What database do you use?

MySQL

MySQL does not have an easy way to implement this type of queries You can try something like the following (not tested): age = 42 select(obj for obj in MyEntity if raw_sql( "$age IN JSON_EXTRACT(obj.json_column, '$$[*].age')" )) But it is probably much better to store this name/age/gender information in a separate entity which is linked to the original entity via relation

MySQL does not have an easy way to implement this type of queries You can try something like the following (not tested): age = 42 select(obj for obj in MyEntity if raw_sql( "$age IN JSON_EXTRACT(obj.json_column, '$$[*].age')" )) But it is probably much better to store this name/age/gender information in a separate entity which is linked to the original entity via relation

Thanks will try it out

MySQL does not have an easy way to implement this type of queries You can try something like the following (not tested): age = 42 select(obj for obj in MyEntity if raw_sql( "$age IN JSON_EXTRACT(obj.json_column, '$$[*].age')" )) But it is probably much better to store this name/age/gender information in a separate entity which is linked to the original entity via relation

With support of json field I want to reduce relationship as much as possible, I need to look into multiple tables and that would hit the performance

I think overusing JSON can hurt performance even more. Native database columns can be indexed more easily, and with JSON many of your queries can require a full table scan

Currently I am querying by native columns, further filtering of JSON data, I am doing I program, not in SQL, suggest me if this approach is okay

it's fine to a point but there's no substitute for using tables as the data gets more complex

Okay

Understood

Lets say I have some records like athletes related to a team. That team has records in a table called matches. I'm wondering what would happen to those records in matches if I switch an athlete from one team to another.

Would there be any risk of those records in matches being deleted which include a former team member in a particular match record?

In general in this case i suggest you to have intermediate table between athlete and team, which will be repsresenting face athlete being a part of team (with dates and some other information, like number on t-shirt). But on your previous question - no, matches will be deleted if you delete table Team. In case you have 'on delete cascade'

hey why dont we switch the pony orm community from telegram to discord because discord has more active users than telegram

hey why dont we switch the pony orm community from telegram to discord because discord has more active users than telegram

I'm totally fine with using Telegram

Discord usually requires more time for managing different channels and etc

Hello @metaprogrammer I have question regarding count function in Pony. I noticed that calling count function is much slower than running raw SQL query SELECT count(*) FROM table;

Is there any way to improve speed of count function?

Hi! What database do you use? How do you call the count function?

At the moment I use SQLite as test database. For comparison I used orm.count(t for t in Transaction) code and raw SQL query SELECT count(*) FROM chain_transactions; which I ran inside sql management tool I use.

Raw query executes in around 0.01 second

But for Pony count function takes around 1 second

SELECT COUNT(DISTINCT "t-1"."id") FROM "chain_transactions" "t-1"

SELECT COUNT(DISTINCT "t-1"."id") FROM "chain_transactions" "t-1"

This is Pony's query from sql_debug

It looks strange, maybe the database was blocked by some concurrent transaction?

It looks strange, maybe the database was blocked by some concurrent transaction?

Not really, only thing which uses database is test script

DISTINCT is unnecessary here. Looks like a bug. Thanks for reporting!

DISTINCT is unnecessary here. Looks like a bug. Thanks for reporting!

Ok, I did Transaction.select().count(distinct=False) and it runs much faster now, thanks :)

But yes, looking at the docs it should be False by default

Hello, I try to use order_by by datetime type, and I get "TypeError: Unsupported type 'datetime'". After that, I tried to create hybrid property to convet datetime to integer, but I also get this error (already in property). Does it have any workaround to order by datetime field?

Can you show an example?

Can you show an example?

Sorry for delay, while I tried to create example I suppose what I found the problem. I create subclass for datetime which works with UTC time by default, so its type still is "datetime" but "datetime is not (my) datetime". So itn't pony error. But maybe pony should check primitive types throught "subclass", same as "is".

Hello again @metaprogrammer, I have little question about best practices when using pony. I have set in my model which looks like this transactions = orm.Set("Transaction"). What would be best way to count it: len(self.transactions or self.transactions.count() ?

Basically does it matter if I use len instead of .count() ?

len does a single query to fetch all items and return length count just calculate total count, but does not retrieve individual items if you need to iterate over all items in the same transaction, use len if you just need to know total count, use count

len does a single query to fetch all items and return length count just calculate total count, but does not retrieve individual items if you need to iterate over all items in the same transaction, use len if you just need to know total count, use count

Got it, thanks!

Ok

Hello again @metaprogrammer I have another question regarding Pony. I've been using SQLite as my test database and it worked fine with timedelta values but now I switched to MySQL and got OperationalError: 1292 Incorrect time value: '1033:48:45' for column 'activetime' at row 1 error for same timedelta value.

Is there any pitfalls I should be aware when using timedelta inside MySQL?

is editor.ponyorm.com open source ?

is editor.ponyorm.com open source ?

no

no

=(

why?

Because they need money to keep the project online

But it's free to use for public diagrams.

When using Oracle, why does the pony declare "str" columns like... VARCHAR2(1000 CHAR) ? (insead of just ```VARCHAR2(1000)```)

When using Oracle, why does the pony declare "str" columns like... VARCHAR2(1000 CHAR) ? (insead of just ```VARCHAR2(1000)```)

nm - https://community.oracle.com/tech/developers/discussion/2379822/difference-between-byte-char

Howdy! I'm getting a new strange assertion error

The error is: 'assert t is translator' on line 229 of sqltranslation.py in pony code. I'm on version 0.7.14 and using Postgres.

Hi, what's your query?

Also - some cases were fixed few months ago. @metaprogrammer was fix released?

Also - some cases were fixed few months ago. @metaprogrammer was fix released?

I think the fix wasn't released yet. We should make a release in a few days

I think the fix wasn't released yet. We should make a release in a few days

with migrations ? 😄

Not yet

Hello, Sorry if the question has been asked thousand times, but can't find my mistake from pony.orm import * set_sql_debug(True) db = Database() db.bind( provider='postgres', user='postgres', password='example', host='0.0.0.0', database='test' ) class Content(db.Entity): id = Required(int, auto=True) kind = Required(str) title = Required(str) description = Optional(str) picture_url = Optional(str) picture_cache = Optional(str) parent_id = Optional(int) PrimaryKey(id, kind) db.generate_mapping() new_channel = Content( kind='channel', title='TF1', description="Channel TF1", picture_url="/img/pictures/211267/", picture_cache="" )

TypeError: 'NoneType' object is not iterable

Ok found the answer in this thread https://t.me/ponyorm/3908

Finally it is not the same error

Hey! Is there a known bug in the current version about pony throwing "KeyError" issues about keys that totally exists in the model?

Pls show the line of code and complete traceback

TypeError: 'NoneType' object is not iterable

Same, pls provide traceback