I have a Channel object telling when it should have the cronjob post something, with a posting_interval and posting_offset.
Say interval = 20 and offset = 5 would result in every 20 minutes, shifted by 5 minutes:
00:05, 00:25, 00:45, 01:05, …, 23:45
I mean I have two different versions, one with // and one with modulo %, but don't know which one is better for performace.
delta = this_time_in_minutes - last_time_in_minutes # precomputed in python
return Channel.select(
lambda c:
(last_time_in_minutes - c.posting_offset) % c.posting_interval < delta
)
or
return Channel.select(
lambda c:
((this_time_in_minutes - c.posting_offset) // c.posting_interval) > ((last_time_in_minutes - c.posting_offset) // c.posting_interval)
)
I mean this might be premature optimisation here and not really matter with less then 1K Channels,
but I'm kinda curious.
Lucky
Alexander
I think both versions should be quite slow, as PostgreSQL can't use index here
If it would be possible to reformulate condition as
c.posting_interval > (expression1) and c.posting_interval <= (expression2)
then it should be more efficient
Lucky
My guts tell me that modulo would be more expensive then having two devisions and a subtraction more.
Alexander
Arithmetic will be 1000s times faster then scanning all table rows and loading them into PostgreSQL memory
Alexander
So the difference between these two queries probably will not be noticeable
Lucky
I think both versions should be quite slow, as PostgreSQL can't use index here
If it would be possible to reformulate condition as
c.posting_interval > (expression1) and c.posting_interval <= (expression2)
then it should be more efficient
Uh, I don't get what expression{1,2} are here?
Or is it generically ment to mean: Comparing is cheap, Math is expensive?
Lucky
Oh, those two are doing the same thing actually.
Checking if the combination of interval and offset is between last_time_in_minutes and this_time_in_minutes
Lucky
Sorry, for that confusion.
Alexander
I think that arithmetic calculations should be several orders of magnitude faster then fetching data from the disk, so optimization should be applied not to arithmetic, but to reducing the number of database pages that should be fetched
Alexander
Probably this is a premature optimization though
Lucky
Yes, definitly is 😅
Lucky
vin
Hi! rookie here; loving PonyORM!! Need help in understanding why this fails:
from pony import orm
orm.set_sql_debug(True)
db = orm.Database()
class NetworkElement(db.Entity):
"""common attributes"""
name = orm.Required(str, unique=True)
description = orm.Optional(orm.LongStr)
class VPC(NetworkElement):
owner = orm.Optional('Stack')
class Subnet(NetworkElement):
owner = orm.Optional('EC2_Instance_Detail')
class EC2(NetworkElement):
owner = orm.Optional('Stack')
instance_details = orm.Required('EC2_Instance_Detail')
class EC2_Instance_Detail(NetworkElement):
owner = orm.Optional('EC2')
subnet = orm.Required('Subnet')
# top class
class Stack(NetworkElement):
vpc = orm.Required('VPC')
ec2_instances = orm.Set('EC2')
db.bind(provider='sqlite', filename='db.sqlite', create_db=True)
db.generate_mapping(check_tables=True, create_tables=True)
db.disconnect()
vin
get complaint that owner is duplicate attribute, though it points to different _tables_
vin
pony.orm.core.ERDiagramError: Attribute Subnet.owner conflicts with attribute VPC.owner because both entities inherit from NetworkElement. To fix this, move attribute definition to base class
BlueWolf 🇮🇹🏳️🌈(LGBT)
Good evening everyone I'm going to PonyORM recently from MySQLDB do you have any advice to give me?
Lucky
Good evening everyone I'm going to PonyORM recently from MySQLDB do you have any advice to give me?
editor.ponyorm.com is quite handy.
Lucky
does Entity.attribute += 1 do an atomic operation?
Matthew
I think it is only atomic when the transaction has committed
Lucky
I mean I'd expect
foo = Entity.attribute
Entity.attribute = foo + 1
to be prone to errors.
Lucky
But for Entity.attribute += 1
I would expect an update like
UPDATE TABLE `Entity` SET `attribute` = `attribute` + 1
Anonymous
.
Alexander
does Entity.attribute += 1 do an atomic operation?
Whith optimistick checks, which are enabled by default, it translates to
SELECT <columns> FROM "entity" WHERE ...
...
UPDATE "entity" SET "attribute" = <new_value> WHERE id = <id> AND "attribute" = <prev_value>
If update query does not found the row with specified <id> and <prev_value> then Pony throws an error and does not commit changes
So, there is no difference between val = obj.attr; obj.attr = val + 1 and obj.val+= 1
As pony uses transactions, it is atomic
Alexander
Hi! rookie here; loving PonyORM!! Need help in understanding why this fails:
from pony import orm
orm.set_sql_debug(True)
db = orm.Database()
class NetworkElement(db.Entity):
"""common attributes"""
name = orm.Required(str, unique=True)
description = orm.Optional(orm.LongStr)
class VPC(NetworkElement):
owner = orm.Optional('Stack')
class Subnet(NetworkElement):
owner = orm.Optional('EC2_Instance_Detail')
class EC2(NetworkElement):
owner = orm.Optional('Stack')
instance_details = orm.Required('EC2_Instance_Detail')
class EC2_Instance_Detail(NetworkElement):
owner = orm.Optional('EC2')
subnet = orm.Required('Subnet')
# top class
class Stack(NetworkElement):
vpc = orm.Required('VPC')
ec2_instances = orm.Set('EC2')
db.bind(provider='sqlite', filename='db.sqlite', create_db=True)
db.generate_mapping(check_tables=True, create_tables=True)
db.disconnect()
Hi vin!
Pony uses single-table type of inheritance - all inherited entities store columns in the same table. Because of this, it is not possible to have attributes with the same name in subclasses. You can call attributes vpc_owner, subnet_owner, etc.
Lucky
Whith optimistick checks, which are enabled by default, it translates to
SELECT <columns> FROM "entity" WHERE ...
...
UPDATE "entity" SET "attribute" = <new_value> WHERE id = <id> AND "attribute" = <prev_value>
If update query does not found the row with specified <id> and <prev_value> then Pony throws an error and does not commit changes
So, there is no difference between val = obj.attr; obj.attr = val + 1 and obj.val+= 1
As pony uses transactions, it is atomic
How can I make sure it does still commit that? It is an login error count, and I need to make sure the account is blocked even if some other instance increments that value already. I set it to volatile=True, does that help?
Alexander
No, instead of using volatile do
obj = MyEntity.get_for_update(id=some_id)
This way the object will be blocked until other transaction which has updated the counter was completed, and the conflict does not occurred
Lucky
Thank you!
Lucky
Another question, I'm currently using sqlite for some bot testing, and between the webserver process and the worker process I often get pony.orm.dbapiprovider.OperationalError: database is locked.
Are there any tips how I can have them both use the same sqlite in the file system?
Anonymous
It sucks orm
Lucky
Lol
Anonymous
They use shitty threading
Anonymous
That's crap
Lucky
I mean it certainly has some issues, one of them being "pythonic" only for the py2 era, but no support for stuff like typing for example, or forcing you to have a database to create the models.
Lucky
Still it is one of the best ones around, in terms of query writing
Lucky
Still it becomes more and more evident that the core is written in a not flexible enough way - otherwise this could have been fixed by 3rd party packages
Alexander
@alkomenu Pony is indeed uses threading model, and assumes there are some pool of threads (or pool of single-threaded processes) which are reused between requests. For async appications you may use something like TortoiseORM, which has different trade-offs
Lucky
If for example the entity definitions would work by having a list of Classes to map to tables, and then there is an method adding column definitions to it (that's what the meta class does) this could be used to create the blueprint functionality, by invoking that method at a later time from somewhere else.
db.register_class(SomeClass, table="SomeTable")
# for attr in SomeClass.__dict__().keys():
db.register_attribute(SomeClass, "id", Required(int)
Alexander
Another question, I'm currently using sqlite for some bot testing, and between the webserver process and the worker process I often get pony.orm.dbapiprovider.OperationalError: database is locked.
Are there any tips how I can have them both use the same sqlite in the file system?
Actually, Pony should greatly reduce frequency of "database is locked" errors for SQLite.
If you work with SQLite using plain sqlite3 module, then two parallel processes which simultaneously do SELECT then UPDATE will almost certainly generate this error
With Pony the error should appear only if one process has long-living transaction which was not finished before timeout (5 seconds by default)
Alexander
If for example the entity definitions would work by having a list of Classes to map to tables, and then there is an method adding column definitions to it (that's what the meta class does) this could be used to create the blueprint functionality, by invoking that method at a later time from somewhere else.
db.register_class(SomeClass, table="SomeTable")
# for attr in SomeClass.__dict__().keys():
db.register_attribute(SomeClass, "id", Required(int)
I think it would be possible after migration functionality will be added
Lucky
If for example the entity definitions would work by having a list of Classes to map to tables, and then there is an method adding column definitions to it (that's what the meta class does) this could be used to create the blueprint functionality, by invoking that method at a later time from somewhere else.
db.register_class(SomeClass, table="SomeTable")
# for attr in SomeClass.__dict__().keys():
db.register_attribute(SomeClass, "id", Required(int)
With an api like this it would be easy to improve it for modern python.
Have a look at pydantic and e.g. FastAPI for how that could look like.
Lucky
Actually, Pony should greatly reduce frequency of "database is locked" errors for SQLite.
If you work with SQLite using plain sqlite3 module, then two parallel processes which simultaneously do SELECT then UPDATE will almost certainly generate this error
With Pony the error should appear only if one process has long-living transaction which was not finished before timeout (5 seconds by default)
I think My with orm.db_session is open for ~15minutes 😅
Alexander
SQLite does not allow parallel transactions
Alexander
I think, it is better to split it to batch of 1-second long transactions if possible
Lucky
I don't think I can.
That's because I get a list of Channels to update with for channel in Channel.select(…):
And in there doing very slow downloading and processing of image data ~1minute per image.
That's why the session around the loop ends up having 15minutes.
As far as I know you can't use an entity outside of the dp_session thing, so it has to stay open...
Alexander
channel_ids = get_channel_ids()
for id in channel_ids:
data = download_data_for_channel(id)
update_channel(id, data)
@db_session
def get_channel_ids():
...
def download_data_for_channel(id):
...
@db_session
def update_channel(id, data)
...
Jim
Hi, is it possible to store the "version" (or some other meta info) of the database and retrieve the it before generate_mapping ?
Alexander
You can do db.generate_tables(check_tables=False) and create tables later. Also you can call db.execute() after db.bind() but before db.generate_mapping()
Jim
you mean create_tables ?
Alexander
Yes
Alexander
db = Database()
db.bind(**db_params)
with db_session:
cursor = db.execute("select * from mytable")
class MyEntity(db.Entity):
a = Required(int)
db.generate_mapping(check_tables=False)
db.drop_all_tables(with_all_data=True)
db.create_tables()
Jim
oh nice, exactly what I needed 👍
vin
Lucky
Hey. There is query.get_sql().
How can I also retrive the values of variables?
I only want to log them so I can understand what it is doing, I don't need them to be built into the SQL, that I can do manually.
Alexander
You can do
sql, arguments, _, _ = query._construct_sql_and_arguments()
Evgeniy
I made a small library for logging and profiling code based on Pony ORM. I invite everyone to use it. Thank you to Alexander Kozlovsky for some tips on how to properly organize your work with Pony. At the moment, the documentation is written in Russian, so if you do not understand it, I recommend using the google translator. If someone likes the library, you can help me translate the documentation into other languages, particularly English. A link to the library: https://github.com/pomponchik/polog
Permalink Bot
I made a small library for logging and profiling code based on Pony ORM. I invite everyone to use it. Thank you to Alexander Kozlovsky for some tips on how to properly organize your work with Pony. At the moment, the documentation is written in Russian, so if you do not understand it, I recommend using the google translator. If someone likes the library, you can help me translate the documentation into other languages, particularly English. A link to the library: https://github.com/pomponchik/polog
Permanent link to the pomponchik/polog project you mentioned. (?)
Evgeniy
Polog = Pony + Logging :)
Alexey
Great job, @blinof !
Evgeniy
Thank :)
BlueWolf 🇮🇹🏳️🌈(LGBT)
I developed a Telegram bot for groups and I like this ORM and I decided to redo all the logic including ponyORM
Alexey
I developed a Telegram bot for groups and I like this ORM and I decided to redo all the logic including ponyORM
✨🔥
it is a day of contribution to Pony ORM community 😀
Alexey
great stuff guys ☀️
Lucky
I developed a Telegram bot for groups and I like this ORM and I decided to redo all the logic including ponyORM
I also use it for my bots. I wrote Telestate being a library on top of that to handle chat states in an database agnostic way, one of them is Pony.
https://github.com/luckydonald/telestate
Permalink Bot
I also use it for my bots. I wrote Telestate being a library on top of that to handle chat states in an database agnostic way, one of them is Pony.
https://github.com/luckydonald/telestate
Permanent link to the luckydonald/telestate project you mentioned. (?)
BlueWolf 🇮🇹🏳️🌈(LGBT)
Evgeniy
👍
Anonymous
Anonymous
class Student(db_handle.Entity):
"""Students have names, emails, nicknames, and grades."""
email = orm.PrimaryKey(str)
first_name = orm.Required(str)
last_name = orm.Required(str)
nickname = orm.Optional(str)
grades = orm.Set('Grade')
Anonymous
That's the code to create the Student table.
Alexander
Can you issue the command
.schema Student
?
Alexander
I think you have created that table with previous entity definition, and then have changed the entity and don't recreate the table
Alexander
Because the table doesn't have correct number of columns
Anonymous
@metaprogrammer You are exactly right, and I am a dope not to have seen that!
Anonymous
Thank you.
Alexander
Sure