Hi Jim! At first, some introduction about db session: Any operations with a database take place in context of database transaction. Transaction allow multiple users to work with the same database concurrently and still see some coherent state of data. During the transaction some resources are hold inside the database and cannot be used by other users, so it is important to finish transaction when the work is done. There are several approaches to transaction management. Some ORMs (say, Django in default mode) start separate transaction for each database request. This approach is simple, but have several drawbacks. First, it leads to potential data corruption: as each request is performed in context of a different transaction, the received information may be incoherent, and it is possible to have lost updates, when update unknowingly overwrite date written by concurrent transaction. Secondly, transaction commit is expensive operation, and for good performance is it better to perform consequent updates inside a single transaction. Another approach used by advanced ORMs like SQLAlchemy. Such ORMs allow user to explicitly start a database session, represented as a Python object, and then finish db session when transaction should commit or rollback. This approach solves problems mentioned above, but explicit management of db session may be verbose and cumbersome, as it is necessary to specify db session object on each request. With PonyORM a programmer denotes boundaries of db session using a special decorator or context manager db_session, and then Pony automatically creates implicit db session object and use it for each database request in db_session scope. This approach gives simplicity for programmer and good performance for database.

Now, before I move on to answer your question, I notice that you wrap each function with db_session. This is not necessary. db_session should wrap the entire scope of actions. If you have a function which retrieves some object from the database and then returns that object as a function result, it means that db_session should be applied not to this function, but to an outer function, so all operations with the object have place inside the same db_session. So, if you have several functions, each with its own db_session, and use object returned from the one function as an argument for another function, it leads to exception, because db_session used for retrieving the object was already finished, internal structures were cleared, and it not possible anymore to use that object in context of a new db_session. Your last function, when you specify primary key instead of object, works, but for a wrong reason. The possibility to pass id value instead of object is a hack, created for performance reasons. When id value is passed instead of object, it means that we assure Pony that object with that id exists inside the database, and Pony may not bother to select it just for operating with that id. So, when you specify pk value instead of object, Pony creates a new instance of that object, belonging to new db_session. It sort of works in this particular case, but in general it may be inefficient.

So you need to use single db_session for all sequential actions. But it may be cumbersome to do when working in a shell, because it is not easy to wrap separate shell commands with a single context manager. In order to simplify working with shells, Pony have concept of "mode". Pony detects current mode during the import, and keeps its value in pony.MODE variable. You can see current mode value in the following way: >>> import pony >>> pony.MODE 'INTERACTIVE' When the mode is 'INTERACTIVE', Pony allows working with object without using db_session context manager or decorator. All commands executed in the same shell belongs to the same db_session. But this logic only works if no explicit db_session is specified. Each explicit db_session works as usual, and finishes current transaction upon exiting from db_session. So, in order to make the code work, you need two things: 1) Remove all db_session decorator from functions which does not hold entire transaction. 2) Be sure that pony.MODE is indeed 'INTERACTIVE'. Pony uses some heuristics in order to detect is it running inside a shell or not, and for some shells these heuristics may give a wrong result. If this is the case, you can set pony.MODE manually, but if pony.MODE in your shell is indeed wrong, it is better if we can fix it, so Pony will detect shell correctly.

What an answer !! thanks lot. thats very clear now. I choose pony against sqlalchemy for his conciseness and simplicity so no I better understand why love it now.

I am creating bunch of copied tables because I dont want to use multiple string columns as Primary key, but it seems Pony allows only 1:1 matching between classes and tables.

Is there any way to create multiple tables with same attributes using single class?

I don't think so.

You can probably do some magic with python's reflection, but it's easier to just copy-paste those classes.

You can probably do some magic with python's reflection, but it's easier to just copy-paste those classes.

I need to change the name every time I paste, so I think I will find and try magic :(

The number of tables are not constant(It can be increased)

Try this

In simple cases (if you don't use composite keys) you can do something like that: from pony import orm from pony.orm import core def define_entity(db, entity_name, attrs): return core.EntityMeta(entity_name, (db.Entity,), attrs) db = orm.Database() entity = define_entity(db, 'Person', dict( name=orm.Required(str), age=orm.Optional(int) )) orm.sql_debug(True) db.bind('sqlite', ':memory:') db.generate_mapping(create_tables=True)

In simple cases (if you don't use composite keys) you can do something like that: from pony import orm from pony.orm import core def define_entity(db, entity_name, attrs): return core.EntityMeta(entity_name, (db.Entity,), attrs) db = orm.Database() entity = define_entity(db, 'Person', dict( name=orm.Required(str), age=orm.Optional(int) )) orm.sql_debug(True) db.bind('sqlite', ':memory:') db.generate_mapping(create_tables=True)

Thank you!

But before this solution I will try in my way

Hello. I'd like to restart sequences in database. I think to do it with something like : db.execute('ALTER sequence sequence_name restart;')I see I can deduce sequence_name from attribute name with auto=True. My question is : is there another way to get sequence name in pony ? I know SELECT * FROM information_schema.sequences; in postgre but I don"t think it's ok for other databases. I did not find anything like this in python dbapi. Jimmy

Hi. What databases do you use?

Is it only PostgreSQL?

it's for my pytest-ponyorm plugin. initialy I did drop/create tables after each test but except for sqlite in memory that was horribly slow. I changed it and know every entry is deleted after each test which is very much faster to have a clean database for next test. But it does not reset the sequences. So the idea for me would be to support sequence reset on database supported by pony

At this moment Pony explicitly specifies sequence names only when working with Oracle. In PostgreSQL Pony just specifies column as a SERIAL PRIMARY KEY. In PostgreSQL it is possible to get sequence name from primary key column name using pg_get_serial_sequence function, see https://www.postgresql.org/docs/current/static/functions-info.html I think the code for resetting sequence will be different for each DBMS

Ok, I will start with sqlite and postgre first. how do you check DBMS used ? isinstance(db.provider, pony.orm.dbproviders.postgres.PGProvider) ?

You can check db.provider.dialect string value, currently it may be 'SQLite', 'PostgreSQL', 'MySQL' and 'Oracle'

OK cool

Hello, currently fighting with pony, probably something easy to fix. I'm fiddling about with modularizing a discord bot I'm building. Currently I'm trying to build a plugin system. The plugins should be able to define their own entities, so I can use plugins without having to touch the core bot code. Is there a way to get Pony to work with the plugin entities without having them in place before the initial db connection?

Hi, you cannot add new entities to db after db.generate_mapping() was called. But you can create new db object and define more complete set of entities in it. Currently the easiest way to do it is defining entities inside a function. You can do something like that: import settings from core_entities import define_core_entities db1 = Database() define_core_entities(db1) db1.bind(**settings.db_params) db1.generate_mapping() ... # inspect database and found which plugins to use db2 = Database() define_core_entities(db2) from some_plugin import define_plugin_entities define_plugin_entities(db2) db2.bind(**settings.db_params) db2.generate_mapping() The function for defining entities may look in the following way: def define_entities(db): class Foo(db.Entity): name = Required(str) bar = Optional("Bar") class Bar(db.Entity): name = Required(str) foos = Set("Foo") After you define entity you can access it as attribute of db object select(foo for foo in db.Foo if foo.name == 'X') The drawback of using functions is that IDE like PyCharm cannot do auto-completion on entities, because IDE does not understand class definition

-EDIT- Okay, seems to work without too much hassle.

What's the best way to add some static python data to a Pony class?

class X(db.Entity): _z = [1,2,3]

is that sensible?

I think yes

Thanks!

About it, I wanted to store a python list in db using json attribute. default = [] or {[]} don't work. I made it work tweeking init of my entity. Is there any other way to do it ?

I think we don't support default values for JSON attributes yet, need to fix it #todo

I think we don't support default values for JSON attributes yet, need to fix it #todo

As well as implement list types for postgres xD

Out of curiosity, are there plans for async support?

Hi guys, There is any migration strategy been used for Pony?

Out of curiosity, are there plans for async support?

Theretically we can add it, but we don't have any specific plans in the near future, as other tasks looks more important

Theretically we can add it, but we don't have any specific plans in the near future, as other tasks looks more important

We have orm-migrations branch with experimental version of migrations. It should be able to handle simple cases. The description is here: https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate

We have orm-migrations branch with experimental version of migrations. It should be able to handle simple cases. The description is here: https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate

Thanks!

hello, is there a way to test if a we are actually in a db_session context manager ?

You can check it in the following way: import pony.orm.core def is_inside_db_session(): return pony.orm.core.local.db_session is not None

ok thats cool thanks

Hi, what is the proper way to perform a query like: keywords = ['champ', 'recipe'] results = orm.select(i for i in Item if all(k in i.name for k in keywords))[:]

I.name is a Unicode column?

You could loop over the keywords and do query = query.filter(lambda I: keyword in I.name)

I.name is a Unicode column?

Yes

You could loop over the keywords and do query = query.filter(lambda I: keyword in I.name)

This works thanks

No problem

May not be the best way but it should produce sensible sql

May not be the best way but it should produce sensible sql

Yes it seems so. SELECT "i"."id", "i"."name", "i"."complex" FROM "Item" "i" WHERE "i"."id" IS NOT NULL AND "i"."name" LIKE ('%' || replace(replace(replace(?, '!', '!!'), '%', '!%'), '_', '!_') || '%') ESCAPE '!' AND "i"."name" LIKE ('%' || replace(replace(replace(?, '!', '!!'), '%', '!%'), '_', '!_') || '%') ESCAPE '!' ['champ', 'recipe']

packs = Pack.select(Pack.url).order_by(orm.desc(Pack.last_crawled)).limit(count) Raises TypeError('The first positional argument must be lambda function or its text source. Got: Pack.url',)

How do I specify that I only want the url field?

SELECT url FROM pack WHERE ...

You can.t return attribute with lambda. Use select function and generator expression : https://docs.ponyorm.com/queries.html

urls = select(p.url for p in Pack).order_by(lambda: orm.desc(p.last_crawled)).limit(count)

Shouldn't the "pack" at the start of that be "p"?

Yes

Fixed

urls = select(p.url for p in Pack).order_by(lambda: orm.desc(p.last_crawled)).limit(count)

PyCharm doesn't understand this code, but it is valid

Would lambda p: orm.desc(p.last_crawled) still be working (adding the p parameter)?

That way PyCharm doesn't complain

It doesnt because you just declare it in lambda

But you'll get error if you try to run it

Yeah, but would still be executable?

So I can either use correct syntax or have something working?

For now yes

Just ignore pycharm for now

Can't I do order_by(Pack.last_crawled)? No need for any result that way?

p in p.last_crawled is p.url object

> Would lambda p: orm.desc(p.last_crawled) still be working (adding the p parameter)? No, because p would mean the result of query, that is p.url In the next release we plan to separate behavior of two methods: In query.sort_by(lambda x: ...) x will mean the result of previous query, while in query.order_by(lambda x: ...) the name 'x' will mean the name of iterator from the original query select(... for x in ...)

So you are accessing p.url.last_crawled

Can't I do order_by(Pack.last_crawled)? No need for any result that way?

This would be valid syntax and easy to understand

> Would lambda p: orm.desc(p.last_crawled) still be working (adding the p parameter)? No, because p would mean the result of query, that is p.url In the next release we plan to separate behavior of two methods: In query.sort_by(lambda x: ...) x will mean the result of previous query, while in query.order_by(lambda x: ...) the name 'x' will mean the name of iterator from the original query select(... for x in ...)

Those are so similar, and probably very confusing to most people. How about order_by and order_result_by, to have describing names?

For now it doesn't work if the resulting query type is not Pack. I think future order_by lambda semantics is more general and allow complex expressions as well. But we probably can add the syntax that your suggest to order_by too, while sort_by will work with query result only

> How about order_by and order_result_by, to have describing names? I don't think it is a better names, because they both sort query result, so the name difference is actually not intuitive

According currently selected names, the logic is the following:

sort and filter are words typically used for working with iterators in Python and JavaScript, so it is somewhat logical that these function lambdas process the result of previous iterator. order_by and where are SQL-related names, so it is logical that these function lambdas work with names from the original select(...)

urls = select(p.url for p in Pack).order_by(lambda: orm.desc(p.last_crawled)).limit(count)

ProgrammingError('for SELECT DISTINCT, ORDER BY expressions must appear in select list\nLINE 3: ORDER BY "p"."last_crawled" DESC\n ^\n',)

Ok, we need to fix it. In order to make it works, add .without_distinct() before .order_by: urls = select(p.url for p in Pack).without_distinct().order_by(lambda: orm.desc(p.last_crawled)).limit(count)