well, my goal is just storing things in DB but ID doesn't matter, do i just use random id and i used a large number to avoid duplicated-primary-key
If an id value really does not matter and it is not necessary for it to be globally unique, then PrimaryKey(int, auto=True) should be more efficient than UUID
Alexander
Anonymous
yes, that is that i do! Thanks!
Ben
Hi! One quick question. If I use an object property in a query that's actually a sum. Will Pony make a flush() before executing my query as well?
Ben
For example, if I have my boat object
Ben
class Boat(db.Entity):
name = Optional(str)
passengers = Set('Passenger')
@property
def number_of_rabbits_aboard(self):
return sum(p for p in self.passengers if p.type == 'rabbit')
Ben
and I have a function that adds a bunch of passenger and checks if the number of rabbits above is below the max authorized by checking if myboat.number_of_rabbits_aboard < 20
Ben
It is expected that this property always updates right?
Ben
like that a flush is performed on the db before executing the sum?
Ben
and then if I try to get all the boats with number of rabbits > 50 in one query, it should work exactly the same?
Alexander
Pony caches object attribute values and query results. So, if you access this property two times in a row, the second time, it will not issue a new query but return the previous result.
If you modify some object in this db_session, it invalidates the query cache and marks the object cache as modified. If after that you access this property again, Pony will:
- perform flush() to save updated objects from the object cache,
- execute the query again
- store query result in the query cache
So, yes, Pony should return the correct sum value.
But, if you get this query result, and then *another* transaction in a different db_session adds some new object to the collection, then the query cache of the current db_session will not be invalidated, as the current db_session does not receive notifications from concurrent db_sessions. In that case, if you, for example, already executed self.passengers.select() but not executed self.number_of_rabbits_aboard yet (or vice versa), then it is possible that these two queries will be cached for a different number of items. This is because most databases use READ_COMMITTED isolation level by default, which means you can execute the same query several times and see different results because some transactions modified the database before the second query. If you want to avoid this, you can, for example, do boat = Boat.get_for_update(boat_id) at the beginning of each transaction, which works with the passenger list. In this case, transactions will be serialized and see correct results even with concurrent execution, but it will decrease overall database performance because of additional locking.
Ben
It does explain the weird issue I had haha, it was in different DB sessions.
Alexey
Hello folks, when Pony ORM migrations will be available for production usage?
Alexander
Hello folks, when Pony ORM migrations will be available for production usage?
Right now we don't have enough time resources to continuously work on migration support, so it is hard to predict any specific date. For now you can use Flyway: https://flywaydb.org/
Lucky
Additionally, there's still pony_up, made for manual PonyORM migrations specifically:
https://github.com/luckydonald/pony_up
I use that every day, and am actually writing a migration right now with it.
Permalink Bot
Additionally, there's still pony_up, made for manual PonyORM migrations specifically:
https://github.com/luckydonald/pony_up
I use that every day, and am actually writing a migration right now with it.
Permanent link to the luckydonald/pony_up project you mentioned. (?)
Lucky
It is 4 years old now, but still works very well.
Jonah
I've been able to use peewee's migration module with PonyORM, no problems. You just have to write a bunch of helper scripts and it's non-trivial but it works
Lucky
The online editor has problems with negative default values.
Required(int, default=1)
vs
Required(int, default="-1")
Lucky
The rename button in the snapshot section is still broken
Lucky
I'm getting
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/pony/orm/dbapiprovider.py", line 52, in wrap_dbapi_exceptions
return func(provider, *args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/pony/orm/dbproviders/postgres.py", line 255, in execute
if arguments is None: cursor.execute(sql)
psycopg2.errors.UndefinedColumn: column "now" does not exist
But it I have no clue what could be causing it.
Matthew
what is your query?
Lucky
That I don't know.
Lucky
That's the full stacktrace, it has no part of my code in it.
Lucky
I'll dig some more.
Matthew
yeah figure out where in your code it is being triggered
Matthew
but also try searching your queries for "now", as that is the column that doesn't exist
Lucky
It seems to be from a db.generate_mapping(create_tables=True).
Currently it isn't loading the migration which should happen before so I'm not surprised it's failing, still there's no reason for it expecting a field called now, there's no such thing in there.
Alexander
Do you have some manually defined triggers?
Alexander
Or sql_defaults?
Lucky
I have stuff like
date_added = Required(datetime, sql_default='NOW', default=lambda: datetime.now())
Alexander
It is for PostgreSQL?
Lucky
Oh, actually I could have a trigger outside of pony.
Lucky
Yes. Postgres
Lucky
(yay slowmode)
Alexander
It probably should be CURRENT_TIMESTAMP, I'm not sure about NOW
Lucky
Huh, actually I meant to use NOW(), but the online editor always changes that to sql_default="'NOW()'".
Lucky
Hey, what is the state of python Enums support?
It would be really helpful if one could do
class Fruits(int, Enum):
MANGO = 1
BANANA = -7
OTHER = 42
and then use that in queries and everywhere else.
class Table(db.Entity):
id = …
fruit = Required(Fruits)
That would make it much more readable.
select(
t if
t.fruit=1 or
t.fruit=-7
for t in Table
)
could become a better version:
select(
t if
t.fruit=Fruits.MANGO or
t.fruit=Fruits.BANANA
for t in Table
)
Lucky
because 1 or -7 are really not that helpful but Fruits.MANGO and Fruits.MANGO would speak for them self.
Edit: I replaced FoobarEnum with Fruits and enum_field with fruit for having a simpler example.
Alexander
Enums are not supported yet, but you still can do:
class Fruits:
MANGO = 1
BANANA = -7
ANSWER = 42
class Table(db.Entity):
id = …
enum_field = Required(int)
select(
t if
t.enum_field in (Fruits.MANGO, Fruits.BANANA)
for t in Table
)
Lucky
Yeah currently I can do Fruits.MAMGO.value for enums already, too.
But it would be really great if that could be supported by Pony, that automatically would validate values when reading and writing them to the database, and using real enums would be the pythonic way.
Alexander
I hope eventually we'll add enum support
Lucky
Yeah. I already made a proposal in the linked issue #502.
It would be great if someone of the ponyorm team could give me feedback on that, then I'd look into creating a branch and a pull request.
Alexander
I'll try to review it tomorrow
Lucky
That'd be fantastic!
Lucky
How can I run the tests of ponyorm?
Alexander
cd pony/pony/orm/tests
python -m unittest discover
Alexander
This will run tests with SQLite
Alexander
To test with some other provider, you need to specify pony_test_db environment variable with settings filename. The content of the settings file may look like:
settings = dict(
provider='sqlite', filename=':memory:'
# provider='postgres', user='pony', password='pony', host='localhost', port='5432', database='pony'
# provider='cockroach', user='root', host='localhost', port=26257, sslmode='disable', database='test'
)
Lucky
To test with some other provider, you need to specify pony_test_db environment variable with settings filename. The content of the settings file may look like:
settings = dict(
provider='sqlite', filename=':memory:'
# provider='postgres', user='pony', password='pony', host='localhost', port='5432', database='pony'
# provider='cockroach', user='root', host='localhost', port=26257, sslmode='disable', database='test'
)
I'm getting ModuleNotFoundError: No module named 'pony'
Alexander
Do you have it in PYTHONPATH?
Lucky
Do you run setup.py or do it via PYTHONPATH?
Alexander
any way
Alexander
I usually just specify it in PYTHONPATH
Lucky
Okey, Pycharm could simply run it.
I am now actively working on a pull request:
https://github.com/ponyorm/pony/pull/585
Lucky
For normal positive numbers, should I prefer a unsinged or a signed number?
Lucky
Lucky
Added tests for all the things I could think of when working with the database.
Tested against Sqlite.
https://github.com/luckydonald-forks/pony/blob/enum_support/pony/orm/tests/test_enum.py
Permalink Bot
Added tests for all the things I could think of when working with the database.
Tested against Sqlite.
https://github.com/luckydonald-forks/pony/blob/enum_support/pony/orm/tests/test_enum.py
Permanent link to the file pony/orm/tests/test_enum.py mentioned. (?)
Lucky
I just found out you can't make bool enums
xD
Lucky
For normal positive numbers, should I prefer a unsinged or a signed number?
Ah. SQLite provider does not support unsigned bigint type.
Can I query a provider easily if it likes unsigned?
Lucky
Technically sqlite does not support any unsigned types at all but pony just uses the next bigger one, right?
That's why only errors at 64 bit ones (converter.provider.uint64_support)?
Alexander
Technically sqlite does not support any unsigned types at all but pony just uses the next bigger one, right?
That's why only errors at 64 bit ones (converter.provider.uint64_support)?
Yes. SQLite uses a single INTEGER type, which stores signed 64-bit values. Unsigned 64 bit values may be too big to be saved as a signed 64-bit values, so they are not allowed for SQLite
Anonymous
is there a pony method that can replace this:
1 in Model.get(id=ID).numbers # Model.numbers: IntArray
Lucky
Yes. SQLite uses a single INTEGER type, which stores signed 64-bit values. Unsigned 64 bit values may be too big to be saved as a signed 64-bit values, so they are not allowed for SQLite
if id is your primary key, you can go Model[ID].numbers
Lucky
Yes. SQLite uses a single INTEGER type, which stores signed 64-bit values. Unsigned 64 bit values may be too big to be saved as a signed 64-bit values, so they are not allowed for SQLite
Alright.
If the database doesn't supports 64-bit unsigned it will try the signed 64-bit instead. Will commit that soon, together with some tests.
Did you had the time to get a general look over the implementation of #502?
Volbil
Hello everyone, recently I've been playing around with Pony queries and stuck on rather trivial task. Let's say we have User which has messages represented by Message model. It looks like this:
class User(db.Entity):
messages = orm.Set("Message")
username = orm.Required(str)
class Message(db.Entity):
user = orm.Required("User")
text = orm.Required(str)
So question is - how to query users which posted specific message without duplicates? I've tried to do it like that but if user has 2 same messages it will duplicated used in query result:
orm.select(m.user for m in Message if m.text == "test")
Volbil
In regular SQL I would use something like GROUP BY for this but unfortunately I wasn't able to find alternative for that in pony docs.
El Mojo
I usually deal with this issue by putting the results in a set and it removes duplicates. Definitely not elegant but does the job
Volbil
I usually deal with this issue by putting the results in a set and it removes duplicates. Definitely not elegant but does the job
Yeah, that would be an obvious way to do that but slow as hell
Volbil
Also I want to use default Pony pagination
Volbil
.distinct() solved it for me
Volbil
Could you explain?
Sergey
Hello, is it possible to somehow use a pony with a singleton-class?
El Mojo
Could you explain?
Like if you access a collection of objects through another collection :
userObj.posts.likes
Volbil