Well, this table is going to have very many data points, so I need some way to divide it

Roughly how many rows?

100M

Or more

If you index that column all is great

Well, this table is going to have very many data points, so I need some way to divide it

I have in a PHP project on work to conform to the boss's decision to have multiple tables being all the same, and ít really sucks.

100M

Even billions wouldn’t be an issue for one table assuming your hardware can handle it

Hi, is the result of MyEntity.select().count() cached ? I have to make an heavy use of this and I'm wondering to cache it myself.

It seems like it is cached:

In [2]: models.sql_debug(True) In [3]: models.UserPlan.select().count() GET CONNECTION FROM THE LOCAL POOL SWITCH TO AUTOCOMMIT MODE SELECT COUNT(*) FROM "userplan" "up" Out[3]: 5L In [4]: models.UserPlan.select().count() Out[4]: 5L

SQL query isn’t generated the second time?

SQL query isn’t generated the second time?

from what I know only the sql query generation is cached in db_session, not the count result

from what I know only the sql query generation is cached in db_session, not the count result

query results are cached as well

SQL query isn’t generated the second time?

Pony caches query results in db_session. If some objects were modified in memory, then before next SELECT execution Pony clears query result cache and flushes modified objects to database

🙈 sorry

thanks for the explanation

thank for answer guys. I give your more explanation. I'm trying to use pony inside QAbstractListModel of pyside2 lib(python QT binding). This kind of model is used by a QML ListView to show the collection in the UI.Every time you scroll the collection, the View calls the model to update the UI. Calling model means calling the collection size thats to say calling Model.select().count() and then open/close a db_session each time. So I was initially wondering if there was a way to have the count() whithout opening a transaction each time if collection size didn't changed.

by the way if someone has a template/example of QAbstractListModel or QAbstractItemModel with pony I'm interrested :-)

How do you know if the collection size changed without doing a query? Application state within python?

You can't. It's a single user app so I can track it if I want. There is a system of Signal like dataChanged which when emitted could force a count update.

Maybe just query every time, it is probably cheap to do

Hi, can I make changes to a created database later? For example, adding a new table or adding new columns to a table

There will be a migration system in place later. For now have a look at pony_up. https://github.com/luckydonald/pony_up

Permanent link to the luckydonald/pony_up project.

Is threre any place where "pony.MODE == 'INTERACTIVE' " is explained? i'm runing a single thread long running app, and i'm having some performance penalties with usage of "discard all" on postgresql after release connection.

There will be a migration system in place later. For now have a look at pony_up. https://github.com/luckydonald/pony_up

Thanks. Well, is it possible to say an estimated time for the migration system?

Thanks. Well, is it possible to say an estimated time for the migration system?

🤔 it's probably the language barrier but why do people ask for estimates on features for oss projects? You could have went to the "github" repo and look for any work related to migrations: eg. https://github.com/ponyorm/pony/issues/16#issuecomment-489266938 Then since you have access to the code and documentation it would have been a little bit more clear to you if "migrations" are going to be available in near future.

You're right, I didn't think it would be in the repo, it's my fault. Thank you

Actually we work on migrations in private branch (what you can see on github is previous attempt to do migrations), so it may be hard to do correct estimates from the outside. At this moment we have quite a few bugs which need to be fixed, and developers time divided between migrations and several commercial projects. I hope we can release somewhere in February

ah, sorry then.

Actually we work on migrations in private branch (what you can see on github is previous attempt to do migrations), so it may be hard to do correct estimates from the outside. At this moment we have quite a few bugs which need to be fixed, and developers time divided between migrations and several commercial projects. I hope we can release somewhere in February

This is really good news. Thank you for your effort.

Is threre any place where "pony.MODE == 'INTERACTIVE' " is explained? i'm runing a single thread long running app, and i'm having some performance penalties with usage of "discard all" on postgresql after release connection.

It seems pony.MODE is not properly documented yet #todo. Basically, Pony tries to detect if it runs from interactive console (and then set pony.MODE to 'INTERACTIVE'). In interactive mode Pony allows executing queries outside of db_session. This is necessary, because in interactive mode using db_session is very inconvenient. Also, in interactive mode Pony uses @cut_traceback decorator to remove internal frames from long tracebacks to make them looks less fearful for novice users. DISCARD ALL does not related to interactive mode. It executes when Pony releases database connection to connection pool. It happens at the end of db_session and after explicit rollback(). Pony adds DISCARD ALL to be sure that next db_session will have database connection with clean initial state (it is possible that in previous db_session user executes db.get_connection() and then set some unusual options) If your application is single thread and long running, you can try using single db_session at the top level and avoid releasing connection (you need to use explicit` commit()` to finish transactions, and objects will not be released from memory, so it is possible to encounter memory problems) Are you sure that DISCARD ALL really affect performance in your code? At this moment Pony doe not have option to turn off DISCARD ALL command, because nobody had any complains about it yet. Maybe we can add such option in the future

Actually, I'm a bit dislike the magic behind interactive mode detection, because sometimes it can work incorrectly. Yesterday I considered to add explicit pony.go_interactive() call and requiring users to execute it when starting interactive console

Thanks Alexander for your complete response. I understand the reasoning to add DISCARD ALL, but also add some perf penalties: it cleans cached plans resulting it's not possible to share plans between db_sessions, Discard all it's a CPU eater & locks generator and add a round trip. we have an app that process kafka-msgs we moved the session to create 1 session per msg(1:1), when we process over 400 msg/s we are generating 400 discard all per second, we encountered these add over 5-10% overhead on CPU in Postgresql (the factor depends heavily on how many garbage should collect discard command) Per discard command we usually execute +-10 different queries,all using PKs.

Ok, maybe I can use the following logic: If user didn't call db.get_connection() during db_session execution and hence didn't have the possibility to change connection settings, Pony does not call DISCARD ALL when using PostgreSQL

to be honest i prefer to be explicit and go out of magic, you could keep the things as they are and add a parameter to db_session to explicitly set the discard all behavior, beacause at the end, discard all do things outside db.get_connection() : https://www.postgresql.org/docs/current/sql-discard.html For example; SELECT pg_advisory_unlock_all() or purge temp tables

If I understand correctly, all these things relate to a current connection (or, in other words, to a session). Like, temp tables are tables that visible from current session only We can add parameter to db_session, but my intention is to streamline Pony usage, so in 90% of cases it does correct work without specifying any additional parameters. I think the following is a way to achieve that: 1) During typical usage Pony should not automatically execute DISCARD ALL command anymore. I think this is the correct behavior which is suitable for 99% of users, and it solves the performance problem that you described without requiring a user to do any action. 2) If for some reason a user want to get raw database connection from Pony, it means he does something unusual, and has a chance to modify the session state. In this case Pony should execute DISCARD ALL at the end of db_session to avoid strange problems in following db_sessions. Again, this behavior is suitable for 99% of users, albeit has some performance penalty, usually not too big to be worried about 3) If for some reason a user wants to work with raw database connection AND want to avoid performance penalty from DISCARD ALL, then the user should be able to turn off DISCARD ALL. But this case is pretty specific, so it is probably not worth to clutter db_session interface with PostgreSQL-specific parameter, and we can provide some alternative way to do it

make sense

i just take a look for curiosity in other ORM that we use with postgres (not for heavy processes) and they are also use discard all: https://www.npgsql.org/doc/performance.html#pooled-connection-reset

Thanks for info, good to know

Hi! When I define an entity, can I choose the table name?

Because is nice to have class User in Python but nicer to have the table users in postgres

Hi! When I define an entity, can I choose the table name?

yes

yes

...how?

If you need to set your own table name use the _table_ class attribute: class Person(db.Entity): _table_ = "person_table" name = Required(str)

https://docs.ponyorm.org/entities.html#mapping-customization

Hi! Again,sorry for all the questions! But I'm having this error while I try to boot up the script for the first time (yes, I've added db.generate_mappings())

You probably haven't created tables yet. In that case you need to call generate_mapping with create_tables=True option

You probably haven't created tables yet. In that case you need to call generate_mapping with create_tables=True option

That was it! Sorry for the n00b error :P

Hi. in a select() query is there a default order_by behind which order items by pk or anything else ?

In [2]: models.sql_debug(True) In [3]: models.UserPlan.select() Out[3]: <pony.orm.core.Query at 0x7f1843742c90> In [4]: models.UserPlan.select()[:] GET CONNECTION FROM THE LOCAL POOL SWITCH TO AUTOCOMMIT MODE SELECT ... FROM "userplan" "up"

That’s with Postgres

so the ordering is chosen by the database

on thanks.

I always forget thesql_debug 😓

Hi. in a select() query is there a default order_by behind which order items by pk or anything else ?

it order by last updated but if you need order set it

Hey guys, tonight Pony made its way to @sfpythontutorial @py_bay

Visibility is important

cool!

Hello! Please can anyone tell me if there is how to use "Pony" with "MS SQL SERVER" and where can I find the documentation? Sorry for the text ... I'm brazilian and I used google translator

Hi! At this moment PonyORM does not support MS SQL Server. Supported databases are PostgreSQL, MySQL, SQLite and Oracle

Alexander, thank you very much for the quick return

Hi! At this moment PonyORM does not support MS SQL Server. Supported databases are PostgreSQL, MySQL, SQLite and Oracle

I found this driver, but no documentation about it: https://github.com/ponyorm/pony/pull/35/files

thankx

[ ]'s

Hey guys, how can I generate the model using excisting MySQL db?

An exemple hère. https://github.com/jgirardet/mdk_backup/blob/master/mdk_backup/entities.py

An exemple hère. https://github.com/jgirardet/mdk_backup/blob/master/mdk_backup/entities.py

Permanent link to the file mdk_backup/entities.py mentioned. (?)

thanks!

I’m starting to write tests on a new project, is this currently the best way to setup tests with pony? https://pypi.org/project/pytest-ponyorm/

I'ts mine a bit buggy :-) look a the code . i think it s better to implement your own pytest hook and fixtures

Thank you!

https://github.com/jgirardet/MyCartable/blob/master/tests/conftest.py

https://github.com/jgirardet/MyCartable/blob/master/tests/conftest.py

Permanent link to the file tests/conftest.py mentioned. (?)

Maybe something like it

Hi. select(p for p in Activite if p.matiere.id==matiere.id and p.famille==famille).first() is it doable with a get or should I stay like this ? the result can be only one element or nothing

Activite.get(matiere=x, famille=y)

I was trying to use get with lambdas an generator expressions. thank you.😓😱