Alexander
Do you use SQLite?
Volbil
Basically it's simple blockchain parser so it has to do everything synchronously in single thread
Volbil
Also it repeatedly happens in same place so it can be reproduced
Alexander
I'm not sure about the reason of the problem with MySQL, but in SQLite you need to specify the column balance as
balance = orm.Required(Decimal, precision=20, scale=8, default=0, sql_type="DECIMAL_TEXT")
Alexander
Can you try does it fixes the problem?
Volbil
Basically it happens because I'm querying balance object twice
Volbil
After I created dict with balance object it happens less frequently (but still happens)
Volbil
I also tried doing orm.commit() after accessing balance object for the first time, but it doesn't help
Alexander
I still think that the problem has nothing to do with querying the same balance object twice.
In my opinion, the reason for the problem is the incorrect processing of Decimal values.
For SQLite, Pony currently creates a column of SQL type DECIMAL for Python type Decimal. SQLite does not handle it correctly and stores float values (with rounding errors) into columns of this type. We need to switch SQL type to something like DECIMAL_TEXT so SQLite will treat this column as a text value and not introduce rounding errors. Note that if you just change column definition in the model and run the application with the previously created database, it will not change the table definition.
Now I see that MySQL connectors also have bugs with Decimal type handling:
https://bugs.mysql.com/bug.php?id=92790
Actually, it may be better to store balance as an int64 value (actual value multiplied by 10**8). But it provides only 18 digits of precision, and you want 20. Another option is to store it as a string.
Alexander
> I been stuck with following issue
pony.orm.core.UnrepeatableReadError: Value of Balance.balance for Balance[171] was updated outside of current transaction (was: Decimal('50001.605299999996'), now: Decimal('50001.60530000'))
you can see it definitely looks like a rounding error
Alexander
Like, Pony stores Decimal('50001.605299999996'), then read it from the database and surprisingly get Decimal('50001.60530000') instead
Alexander
So, the second querying of the same object detects the presence of the error, but is not the reason of the error
Christian
Are there any FastAPI users here that can give me a hint on how I can marry PonyORM and Pydantic? I'm struggling with putting a Pony Model with several Relationships into a Pydantic Model. @sashaaero ?
Christian
class Game(db.Entity):
names = Set(lambda: Name)
...
class Name(db.Entity):
game = Required(Game)
slug = Required(str)
full = Required(str)
class GameInDB(BaseModel):
id: int
names: List[Any]
@validator('names', pre=True):
def pony_set_to_list(cls, v):
return list(v)
class Config:
orm_mode = True
Gives me
names -> 0
value is not a valid dict (type=type_error.dict)
Christian
Going the Pony route I would do this:
game = game.to_dict(with_collections=True, related_objects=True)
game['names'] = [name.to_dict() for name in game['names']
Which would result in a perfectly fine dictionary:
{ ... "names":[{"slug":"trash-game","full":"Trash Game"}] ...}
Alexander
I think you can add a custom method like serialize to each entity class which return dict of required structure
Alexander
class Person(db.Entity):
name = Required(str)
cars = Set("Car")
def to_pydantic(self):
car_list = list(self.cars)
result = ... # some custome logic
return result
class Car(db.Entity):
number = Required(str)
mark = Required(str)
owner = Optional("Person")
Christian
Huh! Thanks for the idea! Instead of the Pony Model, I put it into the Pydantic Model now, and it seems to work. I'll experiment more with this.
@validator('names', pre=True, allow_reuse=True)
def pony_set_to_list(cls, values):
return [v.to_dict() for v in values]
Christian
On a related note: I found a few questions by people trying to use PonyORM with FastAPI (which I think is awesome!), but mentioning that they struggled with finding information on how to do it. The FastAPI docs only go into detail for SQLAlchemy and Peewee. Would it make sense to collect best practices in the Pony Docs (like the framework specific "Integration with flask" section)? Are PRs welcome for https://github.com/ponyorm/pony-doc ?
Permalink Bot
On a related note: I found a few questions by people trying to use PonyORM with FastAPI (which I think is awesome!), but mentioning that they struggled with finding information on how to do it. The FastAPI docs only go into detail for SQLAlchemy and Peewee. Would it make sense to collect best practices in the Pony Docs (like the framework specific "Integration with flask" section)? Are PRs welcome for https://github.com/ponyorm/pony-doc ?
Permanent link to the ponyorm/pony-doc project you mentioned. (?)
Alexander
It would be nice. Yes, I think we can accept such a PR
Alexey
On a related note: I found a few questions by people trying to use PonyORM with FastAPI (which I think is awesome!), but mentioning that they struggled with finding information on how to do it. The FastAPI docs only go into detail for SQLAlchemy and Peewee. Would it make sense to collect best practices in the Pony Docs (like the framework specific "Integration with flask" section)? Are PRs welcome for https://github.com/ponyorm/pony-doc ?
That’s great! Definitely. We also have experience of using Pony with FastAPI and it is a good opportunity to share it
Christian
Wonderful! I'll draft something and create a PR, maybe people can just add their learnings on to that then.
Alexey
Wonderful! I'll draft something and create a PR, maybe people can just add their learnings on to that then.
Awesome. Looking forward 👍
Christian
FYI PonyORM being mentioned here by @tiangolo: https://github.com/tiangolo/fastapi/issues/891#issuecomment-612831548
Christian
@alexey115 Here's a first draft for a FastAPI guide for the Pony docs: https://github.com/ponyorm/pony-doc/pull/12/files - This needs more content, if anyone wants to have a go.
El Mojo
@alexey115 Here's a first draft for a FastAPI guide for the Pony docs: https://github.com/ponyorm/pony-doc/pull/12/files - This needs more content, if anyone wants to have a go.
Thanks Chris for the initiative, I will add my own xp too
Christian
Thanks Chris for the initiative, I will add my own xp too
That's great, thank you! I don't know if you can add to the PR, let me know if there's a better way.
Alexey
@alexey115 Here's a first draft for a FastAPI guide for the Pony docs: https://github.com/ponyorm/pony-doc/pull/12/files - This needs more content, if anyone wants to have a go.
Christian, thanks a lot for this effort. It is a great start 🙌
I’ve merged and updated at https://docs.ponyorm.org/
Christian
Christian, thanks a lot for this effort. It is a great start 🙌
I’ve merged and updated at https://docs.ponyorm.org/
Great, I'm happy you see value in it! I added a working example for the Pydantic part in a new PR so this part is at least complete, but I really do hope there are better solutions out there than my @validator workaround. https://github.com/ponyorm/pony-doc/pull/13
El Mojo
Great, I'm happy you see value in it! I added a working example for the Pydantic part in a new PR so this part is at least complete, but I really do hope there are better solutions out there than my @validator workaround. https://github.com/ponyorm/pony-doc/pull/13
i actually do the same 🙂
Santosh
For faster insert without using pony object creation we can use db.insert, similarly is there anything for update as well
Santosh
@metaprogrammer Also do we have something like insert if not exist else update
Santosh
In SQL we can use on duplicate key update
Santosh
How do we achieve with Pony
Genesis Shards Community
Ok
Alexey
Great, I'm happy you see value in it! I added a working example for the Pydantic part in a new PR so this part is at least complete, but I really do hope there are better solutions out there than my @validator workaround. https://github.com/ponyorm/pony-doc/pull/13
Awesome, Christian! Just merged the PR and updated https://docs.ponyorm.org/integration_with_fastapi.html#
Alexey
Thank you for your involvement 🌟☀️
Christian
Thank you for your involvement 🌟☀️
Thank you, it's a pleasure! I really enjoy working with Pony, I'd love to see it healthy and developing for a long time. Keep up the good work! 👍
Alexey
Christian
Just saw the deprecation warnings for /thirdparty/compiler: old parser will be gone in 3.10, should use the ast module. Filed an issue https://github.com/ponyorm/pony/issues/598 - As I see it, it's about 1600 lines of code in compiler/transfomer.py. Is this a big deal @metaprogrammer ?
Alexander
Yes, this probably is a big deal and we need spend some time on it. Thanks for opening the issue
El Mojo
thank you guys 🙂
Volbil
Oh wow
Volbil
Seams like somebody is spinning up new botnet
Christian
I was using Pony with SQLite and :memory: but when executing the tests, it errors out with no such table. If I use SQLite with file-storage, it works, but then I have to delete the file in teardown...
Christian
Should I use :memory: when testing a full app or is it really just for trying out smaller bits of code?
Alexey
Should I use :memory: when testing a full app or is it really just for trying out smaller bits of code?
Do you have the create_tables=True when you generate the mapping?
https://docs.ponyorm.org/api_reference.html#Database.generate_mapping
Christian
Yeah, that was my first check. But I think it's not even primarily a Pony issue: https://stackoverflow.com/questions/10841003/sqlite-in-memory-unit-testing-show-no-such-table-error-instead-of-real-error-m
Christian
It says there, that Sqlite inmemory databases are dependend on the connection, so each connection has its own database. .. but I don't really understand..
Christian
I was just wondering if there is a general "don't use in-memory for complex use-cases" advice or if I should just try harder 😅
Alexey
I don’t thinks I can add anything here. It worked for the Pony tests..
Christian
I don’t thinks I can add anything here. It worked for the Pony tests..
I'll try harder then ;)
Christian
Thanks..... 😁
Alexander
Do you understand that :memory: option doesn't share in-memory-base between runs?
Christian
That sounds relevant. I saw that the memory object is the same in the pytest fixture and the test-function (print(db)). Tell me more? What do you mean by 'runs'?
Alexander
I mean if you run same python code in parallel with :memory: database - they will have separate databases
Christian
#conftest.py
def teardown(db):
db.drop_all_tables(with_all_data=True)
db.disconnect()
db.provider = None
db.schema = None
@pytest.fixture()
def client():
app = get_app()
client = TestClient(app)
configure(app) # db configured here, sqlite :memory:
yield client
teardown(db)
---
# test_views.py
test_read_single(client):
with db_session:
games = Game.select()[:]
return games
conftest.py itself works, when I print() in test_views.py I get the identical database object, but still the Game.select() line fails because: no such table: Game
Alexander
But how Game entity is related to db which you create inside app?
Alexander
You probably need something like
# test_views.py
test_read_single(client):
db = client.app.db
with db_session:
games = db.Game.select()[:]
return games
Christian
I don't think the db object is tied to the FastAPI() app? It's separate in my main.py: There is app = FastAPI(...) and the usual two lines of Pony:
db.bind('provider': 'sqlite', 'filename': ':memory:')
db.generate_mapping(create_tables=True)
Christian
You probably need something like
# test_views.py
test_read_single(client):
db = client.app.db
with db_session:
games = db.Game.select()[:]
return games
That said, you are probably right. Irritatingly, the <pony.orm.core.Database object at 0x7eff04310190> is already identical in both functions.
Christian
And it is working straight away if I use a file-based db.
Yunus Emre
hello. When will Pony orm provide sql server support
Christian
You probably need something like
# test_views.py
test_read_single(client):
db = client.app.db
with db_session:
games = db.Game.select()[:]
return games
My test case is working now, it's something in my (more complex) real code. I'll dig deeper and report back. Thanks for the support so far!
Alexander
I think you need something like:
# models.py
def define_entities(db):
class Game(db.Entity):
name = Required(str)
class User(db.Entity):
username = Required(str)
# main.py
from models import define_entities
from settings import db_params
db = Database(**db_params)
define_entities(db)
db.generate_mapping(create_tables=True)
# test_models.py
from models import define_entities
def populate_test_data(db):
with db_session:
game1 = db.Game(name='foo')
user1 = db.User(username='bar')
@pytest.fixture
def database():
db = Database('sqlite', ':memory:')
define_entities(db)
populate_test_data(db)
return db
@pytest.fixture
def client(database):
app = get_app(database)
client = TestClient(app)
configure(app)
return app
test_foo(client):
db = client.app.db
with db_session:
games = db.Game.select()[:]
assert len(games) == 1
Christian
Thanks @metaprogrammer ! I'll look into this and let you know!
Christian
(Amazing speed, btw! :) )
Alexander
You mean, ORM performance comparing to other ORMs? Yeah, it's pretty good :)
Christian
Your typing speed :P
Alexey
we have optimized @metaprogrammer for the typing speed too 😁
Christian
But yeah, Tortoise agrees: https://github.com/tortoise/tortoise-orm#why-was-tortoise-orm-built - Pony is the one to beat.
Permalink Bot
But yeah, Tortoise agrees: https://github.com/tortoise/tortoise-orm#why-was-tortoise-orm-built - Pony is the one to beat.
Permanent link to the tortoise/tortoise-orm project you mentioned. (?)
Christian
I think you need something like:
# models.py
def define_entities(db):
class Game(db.Entity):
name = Required(str)
class User(db.Entity):
username = Required(str)
# main.py
from models import define_entities
from settings import db_params
db = Database(**db_params)
define_entities(db)
db.generate_mapping(create_tables=True)
# test_models.py
from models import define_entities
def populate_test_data(db):
with db_session:
game1 = db.Game(name='foo')
user1 = db.User(username='bar')
@pytest.fixture
def database():
db = Database('sqlite', ':memory:')
define_entities(db)
populate_test_data(db)
return db
@pytest.fixture
def client(database):
app = get_app(database)
client = TestClient(app)
configure(app)
return app
test_foo(client):
db = client.app.db
with db_session:
games = db.Game.select()[:]
assert len(games) == 1
So, funny enough, I think your example was missing the generate_mapping() in the pytest.fixture - but fixing that, it's working perfectly!