In my experience pony only creates missing tables, not missing columns.

You can manually add column to the table ofc. Or if you don't care about the data just delete the table and let pony recreatae it.

its historic data so i've been having to manually edit table schema

was curious if this can be handled at load

I get a _"Commands out of sync" ProgrammingError exception when using select_by_sql to fetch results from a stored procedure: python with db_session: commit_hash = '345d0f9dfca376049bf99df6356e5d5a13336739' commits = PostCommit.select_by_sql('call commit_chain($commit_hash)') print(commits)

Any idea how to solve this? I don't think I have done anything wrong

The print statement is executed correctly and I can see the output in the console, I guess that the exception is raised in the session teardown process.

Can you show stored procedure code?

It seems that the procedure returns a table as a result

I think we can fix it

yes, shouln't it?

yes, I was a bit slow when asking that question

sql delimiter // create procedure commit_chain(in commit_hash varchar(40)) deterministic begin declare current_hash varchar(40) default NULL; declare post_id bigint(20) unsigned default NULL; -- Create temporary table to store chain. -- Init with initial commit. drop temporary table if exists chain; create temporary table chain select * from postcommit where hash = commit_hash; -- Get current hash and global post id. select post, parent from chain into post_id, current_hash; while current_hash is not NULL do insert into chain select * from postcommit where hash = current_hash and post = post_id; -- Sanity check. -- Update current hash. select parent from chain where hash = current_hash into current_hash; end while; select * from chain order by created_at; -- Drop temporary table. drop temporary table if exists chain; end // delimiter ;

It seems that the problem is with this issue: https://stackoverflow.com/questions/11583083/python-commands-out-of-sync-you-cant-run-this-command-now So stored procedure returns several results (at least two - table and the number of affected rows), and we need to call cursor.nextset() multiple times But I tested it on my machine, and select_by_sql works without any issue even without calling cursor.nextset So I wonder what is different with you code In order to fix this I need to reproduce the problem at first

This file contains the Post and PostCommit entities referenced in the sql procedure

Thanks

Thanks to you, appreciate the help 😁

I guess you can get rid of columns that reference other type of entities

Sorry, the previous file was quite full of other stuff. This is a stripped version, it should be easier to test.

if you prefer

ok, thanks. I'll be able to look at it tomorrow

It seems that the problem is with this issue: https://stackoverflow.com/questions/11583083/python-commands-out-of-sync-you-cant-run-this-command-now So stored procedure returns several results (at least two - table and the number of affected rows), and we need to call cursor.nextset() multiple times But I tested it on my machine, and select_by_sql works without any issue even without calling cursor.nextset So I wonder what is different with you code In order to fix this I need to reproduce the problem at first

https://gist.github.com/sneppy/63ea892b7b18f8231f57d9211dacda74 Here's everything you should need to reproduce the error. I tested it on Ubuntu 18.04, Python 3.6.10, MySQL 8.0.19 and PonyORM 0.7.13 and it keeps giving me the same Commands out of sync error.

@metaprogrammer small update, I switched from mysqlclient==1.4.4 to PyMySQL==0.9.3 and the problem is gone. Thus it probably has nothing to do with Pony.

Ok, good to know

lol.

Is there a more efficient way than text = Text.get(chat=Chat[123], type=TextType.WELCOME_GREET) So the part chat=Chat[123] is basically stored as chat=123 in the database, as it's the database. So retrieving the chat object just for then doing a join seems pretty unefficient.

Is there a more efficient way than text = Text.get(chat=Chat[123], type=TextType.WELCOME_GREET) So the part chat=Chat[123] is basically stored as chat=123 in the database, as it's the database. So retrieving the chat object just for then doing a join seems pretty unefficient.

iirc you can use just the id if you get by kwargs.

chat=123

If you get/select by lambda or generator and use text.chat.id == 123 it will also not join chat table

Thanks Николай.

How do I use python enums? currently I have foobar = Required(int) but using class FoobarType(IntEnum): MANGO = 1 BANANA = 2 SOMETHING = 123 WORLD_DOMINATION = 666 EASTEREGG = 4458 can I use foobar = Required(FoobarType)? All it has to do is treat it as int on insert (IntEnum is a subclass of int anyway), and load it back on database retriaval, like return FoobarType(123).

How do I reset the cache of orm.db_session?

Like how do I close it?

I need to make sure I'm getting the newest database version, no matter what

To reset cache you can do rollback(). To save previous chaghes you can do commit() and then rollback() Or you can start a new db_session

Ah, good to know.

How do I use python enums? currently I have foobar = Required(int) but using class FoobarType(IntEnum): MANGO = 1 BANANA = 2 SOMETHING = 123 WORLD_DOMINATION = 666 EASTEREGG = 4458 can I use foobar = Required(FoobarType)? All it has to do is treat it as int on insert (IntEnum is a subclass of int anyway), and load it back on database retriaval, like return FoobarType(123).

Pony does not support enums yet

Pony does not support enums yet

This would be very similar of what it does with the Json type. That one also has a encode step of json.dumps(py_var) which would be int(py_var) here, and a decode step json.loads(db_var) which would be Type(db_var).

Question: what is a TrackedValue?

I can't find any documentation

When you have a json or array field, and do something like obj.json_field['x'][1]['y'] = 100 obj needs to known that its json field was modified. If json_field result value were plain Python dict, obj had no chance to know was the value of json_filed or some of it subitems modified or not So Pony uses special subclasses for dict and list, which inherited from TrackedValue as well using multiple inheritance. TrackedValue knows which object to notifiy about changes

Ah, so that is specific to the JsonConverter.

So I had a look at those converters. My Idea would be something like that to support int and str based columns, by just treating them as int and str, and only adding a step where they are converted back into the enum when loaded from the database. So self.py_type(py_val) would result in something similar to FoobarType(1), which is FoobarType.MANGO. Below is no tested code, and most certainly not working, but hopefully enough to illustrate my idea. from pony.orm.dbapiprovider import IntConverter, StrConverter, Converter from enum import Enum class EnumConverter(Converter): def _get_real_converter(self): """ Gets a converter for the underlying type. :return: """ if issubclass(self.py_type, int): return IntConverter elif issubclass(self.py_type, str): return StrConverter else: raise TypeError('only str and int based enums supported') def validate(self, val, obj=None): assert issubclass(self.py_type, Enum) assert issubclass(self.py_type, (int, str)) def val2dbval(self, val, obj=None): """ grabs the right converter and passes on the value """ converter = self._get_real_converter() return converter.val2dbval(val=val, obj=obj) def dbval2val(self, dbval, obj=None): """ grabs the right converter and passes on the value """ converter = self._get_real_converter() py_val = converter.dbval2val(self, dbval=dbval, obj=obj) if py_val is None: return None return self.py_type(py_val) # SomeEnum(123) => SomeEnum.SOMETHING def dbvals_equal(self, x, y): converter = self._get_real_converter() return x == y def sql_type(self): converter = self._get_real_converter() return converter.sql_type()

I've created an Ticket tracking that: https://github.com/ponyorm/pony/issues/502

Great issue! Pay attention to this pull request also: https://github.com/ponyorm/pony/pull/392

Helloo

How I can delete the first 50 elements of a table?

I don't see information in doc

I try this delete(select(x for x in Table_name if x.state=="publish")[:1200])

but no works

objects = list( MyEntity.select().order_by(MyEntity.id).limit(50) ) MyEntity.select( lambda obj: obj in objects ).delete(bulk=True)

ok, thanks!

objects = list( MyEntity.select().order_by(MyEntity.id).limit(50) ) MyEntity.select( lambda obj: obj in objects ).delete(bulk=True)

MyEntity.select(objects.contains) так лучше?

What do you mean?

It is possible to pass first select directly, without converting it to list of id values subquery = MyEntity.select().order_by(MyEntity.id).limit(50) MyEntity.select( lambda obj: obj in subquery ).delete(bulk=True) But, for example, MySQL may not support LIMIT clause in subqueries

Great issue! Pay attention to this pull request also: https://github.com/ponyorm/pony/pull/392

Hah, I did. You were just too fast :D

It is possible to pass first select directly, without converting it to list of id values subquery = MyEntity.select().order_by(MyEntity.id).limit(50) MyEntity.select( lambda obj: obj in subquery ).delete(bulk=True) But, for example, MySQL may not support LIMIT clause in subqueries

Is a order by not taken in Account on a delete query? I feel like the following should be possible? DELETE FROM table ORDER BY field LIMIT 123 And that I would write as Table.select().order_by(Table.field).[:123].delete() Why is there a need for a subquery?

Not all databases support DELETE + ORDER BY + LIMIT in the same query, so it wasn't added to pony API yet

hi guys

I found pony orm on internet.I tried peewee but It was not fas. Pony orm is really fast or not ?

I found pony orm on internet.I tried peewee but It was not fas. Pony orm is really fast or not ?

This post mentions the speed of Pony. i hope it helps... https://hackernoon.com/performance-testing-of-python-orms-based-on-the-tpc-c-benchmark-ae3g3yis

thanks a lot

Hi Elvin! What type of application you are developing?

Just select nothing else. But data is too much

Usually Pony is fast. But don't use it with async frameworks

Why what is reason

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.

thanks

Guys, I connected to db . But how can I connect specific table .there is no related information in docs

my db is remote

You define entities (a.k.a models) which are corresponding to tables in db, and use them to define query to the database

sorry for disturbing, But why docs is so narrow. How can I get define foreign key in entity

with SET ?

https://docs.ponyorm.org/relationships.html

no required

op optional

char_field is (str) ?

yes

How to edit table name

class Meta: table_name = 'strategy_source_data_strategy_source_data'