csa3d
drop_table(which) clears it, but feels odd to create every table to be able to add to it again
csa3d
is there a single table create equivalent>
csa3d
for x in db.MyEntity.select():
x.delete()
# code to repopulate here?
J J
db.MyEntity. drop_table(with_all_data=True) ?
csa3d
dropping table prevents repopulation, no?
J J
can just do db.create_tables(True)
csa3d
are there side affects to that? the other tables are fine
csa3d
this is me just not knowing what to expect in this regard
csa3d
and feeling like i'd rather explicilty rebuild just the one
J J
It will only create tables for entities that are missing iirc
csa3d
ty, i appreciate the advice
Lucky
Yes, the ones you already have are left as they are
Nikolay
I'm pretty sure there are groups specifically for general python questions. This group group was made for a specific library, and you can get answer directly from the library developers here, don't turn it into a general python group.
You get your answers now, then someone else will follow your suit, then more and more people will ask questions unrelated to the library.
stsouko
see for descriptor protocol in python documentation
Jim
Hi guys. I have the entity Page which has sections Set. each section has position attribute wich should be set by default at" number of section" in the Set.
I there a better way than doing a count(s for s in db.Section if s.page.id == page) in __init_ ?
Jim
hum, what's going on in init ?
print(select(s for s in db.Section if s.page.id == page).count())
print(count(s for s in db.Section if s.page.id == page))
print(select(s for s in db.Section if s.page.id == page)[:])print : 0 0 [Section[1]] 😵😵😵
Matthew
enable sql_debug and compare the SQL queries
Jim
oh I had a higher db_session opened... The first question is still opened :()
Matthew
page.sections.count() or len(page.sections)
Matthew
I think one of those should work
Jim
In __init__ I can't use the page since super().__init__ has not yet benn called. But I can't call super().__init__ since my position attibute is a required one.
Matthew
Ok use the query you already have then
Christian
I'm getting started with the new Django ORM in v3.1, and defining the models looks so much like Pony. Did django-3 get inspired by Pony or was it always like this?
Alexander
It was always like this. There are many differences actually
Anonymous
what is equivalent of long type ?
Alexander
int size=64 I guess
М
Hello! Is there something like bulk_insert in pony?
Alexander
No, but you can do db.insert(table_name, **columns)
This will not create entity instance in memory and so will be more efficient then doing MyEntity(**attrs)
М
Oh, thank you!
Muhammed
Hi, is the following case a program error or a usage error?
category.products_set.sort_by(lambda p: p.id and desc(p.name))[:]
- - >
SELECT "p"."id", "p"."name", "p"."short_description_html", "p"."description_html", "p"."sold", "p"."store_ref", "p"."product_category_ref", "p"."data_status_ref"
FROM "Product" "p"
WHERE "p"."product_category_ref" = ?
ORDER BY "p"."id" <> 0 AND "p"."name" DESC IS NOT NULL
[3]
Alexander
Probably what you want is
category.products_set.sort_by(lambda p: (p.id, desc(p.name)))[:]
Muhammed
I want to sort by more than one attribute. I think I should use it as you say
Muhammed
Thanks
Alexander
Sure
Muhammed
I have two tables called Product and Comment. How can I sort the products according to the average of their points in their comments.
class Product(db.Entity):
id = PrimaryKey(int, auto=True)
comments_set = Set('Comment')
@property
def point(self):
average = select(c.point for c in self.comments_set).avg()
return round(average, 2) if average else 0
and
class Comment(db.Entity):
id = PrimaryKey(int, auto=True)
point = Required(int, size=8, unsigned=True)
to_product_ref = Optional(Product)
Alexander
if you want to use point property in declarative queries, you need to:
1) avoid if-expressions at this moment, as PonyORM does not support it yet
2) rewrote the property getter as one-liner:
@property
def point(self):
return avg(c.point for c in self.comments)
Then the following should work:
Product.select().sort_by(lambda p: p.point)
Full working example:
from pony.orm import *
db = Database('sqlite', ':memory:')
class Product(db.Entity):
name = Required(str)
comments = Set('Comment')
@property
def point(self):
return avg(c.point for c in self.comments)
class Comment(db.Entity):
point = Required(int)
product = Required('Product')
sql_debug(True)
db.generate_mapping(create_tables=True)
with db_session:
Product.select().sort_by(lambda p: p.point).show()
Muhammed
thank you so much for your detailed description
Alexander
Also, to convert NULL to zero for empty comment set you can use coalesce function:
@property
def point(self):
return coalesce(avg(c.point for c in self.comments), 0.0)
Muhammed
thanks
Muhammed
Sorry but I want to do the same thing for their total. But I get an error (AssertionError)
The only place I changed is this:
@property
def point(self):
return coalesce(select(c.point for c in self.comments).sum(), 0.0)
Anonymous
Is there any trick to reuse connections on Flask app? every time a make a Get/Post request on flask y see on pony logs: "GET NEW CONNECTION" i expect to reuse the pool
Alexander
Sorry but I want to do the same thing for their total. But I get an error (AssertionError)
The only place I changed is this:
@property
def point(self):
return coalesce(select(c.point for c in self.comments).sum(), 0.0)
Thanks for the reporting, it's a bug, I'll fix it #todo
Alexander
Is there any trick to reuse connections on Flask app? every time a make a Get/Post request on flask y see on pony logs: "GET NEW CONNECTION" i expect to reuse the pool
How do you use Flask? with some production-level web server like nginx and gunicorn? Or just in development mode?
Alexander
By the way, while I'm fixing the assertion error, this form should work:
@property
def point(self):
return coalesce(select(c.point for c in self.comments).sum(), 0)
(Use 0 instead of 0.0 as a second argument of coalesce)
Muhammed
Actually I tried but it didn't work. Now I tried again, it works as you say. There must have been another problem at that moment. So I'll use this.
Alexander
Yep, other forms are not working for some reason
Anonymous
Anonymous
i see GET CONNECTION FROM THE LOCAL POOL
Anonymous
when i use development i see "GET NEW CONNECTION"
Anonymous
this is the normal behavior?
Alexander
Flask starting from 1.0 uses threaded mode by default, and serve each request in a new thread. I discover it just a few days ago and was surprised by that because this fact was not described in Flask documentation, or I missed that
https://github.com/pallets/flask/pull/2529
Pony's connection pool stores one connection per thread, so, if each new request served in a new connection, Pony has not a chance to re-use it. I think in development mode this should not be a problem, as local connections are usually created pretty fast
Anonymous
woW
Anonymous
good to know!! thanks
Anonymous
this was by mistake as i remember that pony create a connection also per thread
Anonymous
*by = my
Nikolay
Flask starting from 1.0 uses threaded mode by default, and serve each request in a new thread. I discover it just a few days ago and was surprised by that because this fact was not described in Flask documentation, or I missed that
https://github.com/pallets/flask/pull/2529
Pony's connection pool stores one connection per thread, so, if each new request served in a new connection, Pony has not a chance to re-use it. I think in development mode this should not be a problem, as local connections are usually created pretty fast
> Pony's connection pool stores one connection per thread
That doesn't mean the pool grows indefinitely with new threads, does it?
Alexander
For Flask development server probably so. I think it should not be a problem, because development server should not be used in production. But probably we should do db.disconnect() after flask request processing is finished in development mode, this way db connection will be closed properly
Nikolay
There are some use cases where arbitrary number of limited lifespan threads may be created. If every single dead thread leaves a behind a connection that will never be reused I'd say it's a leak.
Nikolay
I guess the need to manually free db connections on thread termination could be just documented with more emphasis, but I imagine it can become a huge pain in some scenarios.
Nikolay
There's a certain library that creates a set of long-lived threads and calls a bunch of user callbacks form these threads. The threads are associated with an object that has start() and stop() methods. It doesn't have thread life cycle hooks, it doesn't know anything about pony. If i want to be able to stop() and discard the object celanly, I either have to disconnect after each callback or hack the library.
Alexander
PonyORM connection pool was created for the following scenario: there are only one or limited number of threads which are reused between HTTP requests. If some framework creates a new thread on each request, it looks inefficient. As Python does not provide some universal hooks to run code when tread execution is about to finish, I think it is framework's responsibility to do something with it and provide a clean way to users to handle this situation.
Pony provides db.disconnect() method to close connection in current thread, maybe we also should provide a mode when exiting from db_session does not save connection to pool, but immediately closes it instead
Nikolay
It's not necessarily about creating a thread per request because it's not necessarily about requests at all. Unless pony is supposed to be used only with web application.
Any library that spawns threads that call user code is doomed to leak pony connections unless it also provides a termination hook for each such thread, or the threads are supposed to run until the whole python process is stopped. Or unless the user code db.disconnects after each call but then what's the point of having a connection pool?
Sorry for bringing up sqlalchmey, but it greatly influenced my expectations of things named "pool". Well, QueuePool specifically, I guess, sqlalchemy's SingletonThreadPool would be completely unusable in this scenario, and the rest are not real pools.
Nikolay
It's not necessarily about creating a thread per request because it's not necessarily about requests at all. Unless pony is supposed to be used only with web application.
Any library that spawns threads that call user code is doomed to leak pony connections unless it also provides a termination hook for each such thread, or the threads are supposed to run until the whole python process is stopped. Or unless the user code db.disconnects after each call but then what's the point of having a connection pool?
Sorry for bringing up sqlalchmey, but it greatly influenced my expectations of things named "pool". Well, QueuePool specifically, I guess, sqlalchemy's SingletonThreadPool would be completely unusable in this scenario, and the rest are not real pools.
What if one wants to use stdlib ThreadPoolExecutor to run a bunch of tasks that use pony and later wants to destroy that pool?
Nikolay
What if one wants to use stdlib ThreadPoolExecutor to run a bunch of tasks that use pony and later wants to destroy that pool?
It's either no connection pooling, leaked connection, or horrible hacks. The less horrible hack I can think of is "poisoning" the work queue with a bunch of db.disconnect tasks. But the poison must include a time delay to ensure you can saturate the queue, since workers don't consume it in a round-robin fashion.
André
Pony expects that concurrent requests are processed in transactions, each parallel transaction decorated with db_session, and have separate connection to the database. Pony handles connection under the hood, each connection associated with current thread (or current process if the application is single-threaded). In async framework all coroutines use the same thread, so the approach that connections are tied to specific threads does not work. It may lead to situation that different coroutines use the same connection, which may break transaction boundaries.
Also, async code can lead to enormous amount of concurrent coroutines with parallel transactions, which are very long comparing with transactions in a usual application. Database server does not work well in situation, when there are thousands of concurent long-living transactions.
But it seems that many people who choose to use async approach does not understand what does it mean from the database point of view. The just say, "hey, async code should definitely be better, because it async", and don't think about transactions and all that database stuff.
For async application it is better to have separate database access layer which communicates with the rest part of application using queues, but such architecture is much more complex than the usual synchronous approach.
This is a very good explanation, thanks! But I believe you will keep receiving the same async related questions over and over. Is there any project, article, or link you can share related to your suggestion? Thus, you can just point to it instead of answering them again.
André
Do you know of a good article or resource that explains this queued db access architecture? I want to understand it better so that if an async project warrants the complexity, I'll have that pattern available to use.
Jacob, have you found any article or resource about that?
Jacob
I have not. I've been working on other projects and haven't revisited the project I'm using pony in for while. When I do, I'll need to address the mismatch between async and pony. I'm fine blocking in a particular async method until pony returns, but I wish pony used localcontext instead of threadlocal so I could use more connections without threads. Anyway, I'll have to figure that out eventually.
Vladyslav 🇺🇦🇪🇺
hi folks
Andrea
Hi, just wondering if it is safe to use nested classes like this:
class Group(db.Entity):
class Member(db.Entity):
user = Required(User)
group = Required('Group')
# ...
Andrea
It works, but since entities are identified using entity.__name__ there is no difference between, say, Group.Member and Chat.Member, they are both 'Member'. Wouldn't it be possible to use qualified names, e.g. entity.__qualname__?
Alexander
Hi! What do you want to achieve with that?
Andrea
I wanted to nest the Member class in Group instead of having GroupMember outside
Andrea
It's not essential I know
Andrea
I just wanted to know if it is possible
Alexander
Pony does not support it, Pony expect that each entity has unique class name disregarding in what Python namespace you define the entity
Alexander
So you probably can put Member inside Group, but not define another Member inside Chat
Andrea
okok, thanks
Alexander
It's not necessarily about creating a thread per request because it's not necessarily about requests at all. Unless pony is supposed to be used only with web application.
Any library that spawns threads that call user code is doomed to leak pony connections unless it also provides a termination hook for each such thread, or the threads are supposed to run until the whole python process is stopped. Or unless the user code db.disconnects after each call but then what's the point of having a connection pool?
Sorry for bringing up sqlalchmey, but it greatly influenced my expectations of things named "pool". Well, QueuePool specifically, I guess, sqlalchemy's SingletonThreadPool would be completely unusable in this scenario, and the rest are not real pools.
Do you have some specific solution in mind?
How about adding pool option to db_session?
1) if pool is not specified, Pony will keep current behavior when each connection is tied to current thread, suitable for single-threaded application or application with a long-lived threads
2) When using as db_session(pool=None) Pony will close connection after exiting from db_session
3) When specifying db_session(pool=some_pool) it will become possible to use pools with different semantics, for example to reuse the same connection in different thread