Resulting later in DataError('invalid input syntax for integer: "main_page_stats"\nLINE 3: WHERE "key" = \'main_page_stats\'\n ^\n',)

Why not just do key = Required(unicode, index=True)

I'm basically am building a cheap key-value store here

That doesn't require a primary key :)

Oooo that's dirty

Anyway, it shoudln't quietly ignore this misconfiguration, I think

like auto overwriting type

I think you need to remove auto=True, and provide key value explicitly But I agree that Pony should provide more meaningful error when auto=True specified for str primary key #todo

E.g. key = PrimaryKey(str, index=True) is handled fine, pony.orm.core.DBSchemaError: Index for column 'key' already exists

Just remove index=True as PrimaryKey always has a unique index.

Yeah

is there a way to specify the table name to use for a many-to-many intermediate table?

class Student(db.Entity): name = Required(str) courses = Set('Course', table='Course_Student_m2m')

ty

I'm making duplicate Entities for a model that was defined in SQLAlchemy ?

and it's not using the same column names either, e.g. "user" instead of "user_id"...

on the intermediate...

You can specify columns as well: class Student(db.Entity): name = Required(str) courses = Set('Course', table='Course_Student_m2m', column='course_id') class Course(db.Entity): title = Required(str) students = Set('Course', column='student_id')

neat, ty

Hello I declared a field for usng timezone like that : created = orm.Required( datetime, default=datetime.now(tz=tz), sql_type="timestamp with time zone" )For retrieving, postgre bring me back a datetime with timezone but sqlite do not. Is there a way to achieve this ?

Hi, at this moment Pony does not support datetime type with timezone. You can use UTC datetime instead, and convert to local timezone in Python after retrieving from the database

ok , is there an equivalent hook which could do "after_select" like "after_update" oo "after_insert" ?

For what purpose?

convert datetime retrieved to aware datetime (with timezone) automaticaly :-)

I think you can make a property: from pony import orm from datetime import datetime import pytz user_timezone = pytz.timezone('US/Eastern') db = orm.Database() class Message(db.Entity): text = orm.Required(str) time_in_utc = orm.Required(datetime, default=datetime.utcnow) @property def time(self): return self.time_in_utc.replace( tzinfo=pytz.utc).astimezone(user_timezone) @time.setter def time(self, value): self.time_in_utc = value.astimezone( pytz.utc).replace(tzinfo=None)

Is there a way to order/sort set property of an entity? Example: mygroup = Group[1] for member in mygroup.members.order_by(Person.name): ...

indeed, it's usable. The only thing I loose is the to_dict method. But anyway I already had to overwrite it to make it jsonable so it's ok. thank you

Is there a way to order/sort set property of an entity? Example: mygroup = Group[1] for member in mygroup.members.order_by(Person.name): ...

you need to add .select() to make a query: mygroup = Group[1] for member in mygroup.members.select().order_by(Person.name):

you need to add .select() to make a query: mygroup = Group[1] for member in mygroup.members.select().order_by(Person.name):

Okay thanks. Is there any documentation on methods for Set instances?

https://docs.ponyorm.com/working_with_relationships.html#operations-with-collections

Oh wow I missed the Collection attribute queries and other methods. I was looking on API page. ?

Thanks

You should not store datetime with timezone to database

sorry misclick I didnt want to send it

ok, no problem

you need to add .select() to make a query: mygroup = Group[1] for member in mygroup.members.select().order_by(Person.name):

Cool didn't know that was possible!

Anyone who can help me figure out the problem with this query?

https://stackoverflow.com/questions/49924356/python-ponyorm-mysql-query-using-multiple-url-args

I answered your question: https://stackoverflow.com/questions/49924356/python-ponyorm-mysql-query-using-multiple-url-args/49927455#49927455 If you think the answer is correct, then please accept the answer as a valid one

Thanks Alexander. It worked with a small modification. You saved me there!

I asked here about datatime few days ago. Alex gave me a good solution with properties. Because I have many, It was very verbose. So I made a decriptor. The idea is : a "hidden" pony field wich starts with "_" and the descriptor field with same name without the "_". This is usable only with python 3.6 because of the set_name method. Here is the example (I use pendulum datetime library) is some is interested : _created = orm.Required(datetime, default=datetime.utcnow) created = PendulumDateTime() class PendulumDateTime: def __set_name__(self, owner, name): self.name = name self.field = "_" + name def __get__(self, instance, owner) -> pendulum.DateTime: return pendulum.instance(getattr(instance, self.field)).in_tz("UTC") def __set__(self, instance, value): setattr(instance, self.field, value.in_tz("UTC").naive())

select((l, select(a for a in ListingAlertsAlert if a.current_listing == l and a.snapshot == self)) for l in ListingAlertsListing if l.snapshot == self).order_by(ListingAlertsListing.position)

AttributeError: 'QuerySetMonad' object has no attribute 'getsql'

this is probably a silly mistake, any idea?

Are nested selects possible?

anyway nested select are unreadable ??

For now I made do by doing for result in main query: yield result, subquery select()

But are nested selects possible?

Nested queries are possible, but not in the expression part of generator, because the following SQL does not provide a correct result: SELECT t1.a, (SELECT t2.b FROM Table2 t2 WHERE ...) FROM Table1 t1 WHERE... This SQL is not correct, because usual SQL does not allow nested tables in result. In some databases this SQL generates an error, if nested SELECT returns more than one column and one row, while in other databases this query silently returns just one arbitrary value from the nested SELECT, and this is most probably not what you expect to get. But Pony allows attribute lifting inside the expression part of generator, so it is possible to write: select((l, l.alerts) for l in ListingAlertsListing if l.snapshot == self) .order_by(ListingAlertsListing.position) In this query Pony uses JOIN isntead of subselect, so it works as expected. But it does not contains condition a.snapshot == self, so the result is not fully correspond with what you want I think you can reformulate a query to avoid nested select, in a way similar to the following: select( a for a in ListingAlertsAlert if a.current_listing.snapshot == self and a.snapshot == self ).order_by( lambda a: a.current_listing.position )

And then: for a in query: print(a, a.current_listing)

In Pony each relationship is bi-directional, so you can always write the same query using the other side of relationship

Maybe some time later Pony could automatically reformulate your original query to the alternative form I wrote above, but right now it is not as smart yet

Cool didn't know that was possible!

I second that

Is there something built into pony to partner page() to tell you the maximum page that's available for a query?

I've seen something somewhere, but I'm not sure that it wasnt PonyJS :(

You can do query.count() / pagesize

It would be nice. Currently I use the following:

"pagination": { "total": total, "per_page": per_page, "current_page": page, "last_page": total//per_page + 1 if total % per_page else 0,

Hey everyone! I have a question: I want to perform some actions after creating an object. Can I somehow modify def init of my class?

Yes, you can. But keep in mind, that __init__ is called only when new entity instance is created, not when the object is loaded from the database

class MyEntity(db.Entity): foo = Required(int) bar = Optional(str) def __init__(self, **kwargs): # process kwargs if necessary super().__init__(**kwargs) # do some additional action

Yeah, this is what I need, thank you!

Is there a minimum version of Pony required to use Flask 1.0?

There are no any specific dependencies between Flask and Pony, so I think you can use Flask 1.0 with any version of Pony

What is your reasoning for not using the latest version?

Yep, Flask 1.0 worked with Pony 0.7.3

I was just checking if there was any known issue with versions

What is your reasoning for not using the latest version?

Because testing etc means that it's work to upgrade the version of any library

what's a good way to implement an in-memory cache of entities that could be accessed in limited fashion outside a db session?

what's a good way to implement an in-memory cache of entities that could be accessed in limited fashion outside a db session?

which Python are you using? Have you heard of lru cache? https://docs.python.org/3/library/functools.html#functools.lru_cache

which Python are you using? Have you heard of lru cache? https://docs.python.org/3/library/functools.html#functools.lru_cache

Disclaimer: I have not tried this. I also am not very well versed in Pony so perhaps there is a better way.

I have a situation where I am doing image processing on pages from a PDF. I have a Document entity which has many Page s (each having many Feature s) and want to store processed information on these pages in my database using Pony. Some confusion comes when I think about how to separate the image processing logic from the database modeling. I made UploadedDocument and UploadedPage classes for handling directory mapping and image processing. After looking at https://docs.ponyorm.com/entities.html#adding-custom-methods-to-entities I was left wondering if I wanted to do with db_session: every time I want to work with an image. It might be nicer if I pass db_session to my UploadedDocument/UploadedPage instances and hide the with statement inside the various functions. In this case though, I'm letting the database logic get into my image processing logic so it feels like you have to lose something either way. I guess this is part of a more general pattern and I wondered, what do others think?

Do you know about using @db_session decorator on a model method?

Do you know about using @db_session decorator on a model method?

I had somehow forgotten about that! ? So far I'm considering having Pony models in models.py, having image processing methods in another file like image_utils.py and then having another file for my app specific classes which stitch these together (ie an app_models.py file containing UploadedDocument, UploadedPage and so on). I can use the decorator on the latter as you suggested. Thanks!

:)

what's a good way to implement an in-memory cache of entities that could be accessed in limited fashion outside a db session?

Why do you want this? It is much more convenient to incapsulate all dealing with entities inside db session

Greetings!

I have been looking for documentation on how to extend PonyORM's support to another currently unsupported database (SQL Server via SQL Relay). Is there documentation on how to do this?

I don't think so, but you may have a look at dbapiprovider in Pony source code to adapt another provider for your database engine

How can I use Pony with an postgres database using int[] column? I couldn't find how to define the mapping as list

Is there some kind of Required(int, list=True) I am missing?

At this moment Pony does not support PostgreSQL array types. You can try to use jsonb type instead, it can be used to store arrays

We chose native lists when implementing the database because storage requirements of jsonb are bigger as lists are.

Jsonb is easily 3 times bigger: https://stackoverflow.com/a/49600267/3423324

What operations do you need to perform with this column?

Sometimes later we will add support of array types to Pony, but as a temporary solution you probably can use raw SQL fragments with Postgres array operators inside your queries

It will be better if you implement api for custom data types. Like routes in flask.

Which has 2 fixed method. From url and to.

Something like this: db.register_data_converter(my_converter)

Class my_converter(pony_base_converter): bla