It is translated to SQL, so you can't just use arbitrary Python

Ok, thank you very much :)

Hello, I am trying to implement the following SQL in Pony: SELECT AVG(r.points), AVG(r.maxforanswered) FROM "result" r, "user" u WHERE u.birth_date BETWEEN CURRENT_DATE - ($highage * 365) AND CURRENT_DATE - ($lowage * 365) AND u.gender = $gender AND r.user = u.id AND r.time_generated = (SELECT MAX(r2.time_generated) FROM "result" r2 WHERE r2.user = u.id)

I have tried: select((avg(r.points), avg(r.maxforanswered)) for r in Result for u in User if r.user == u and u.gender == gender and between(u.birth_date, dminus, dplus) and r.time_generated == max(r2.time_generated for r2 in Result if r2.user == u))

But I get: pony.orm.dbapiprovider.ProgrammingError: aggregate functions are not allowed in GROUP BY LINE 11: GROUP BY AVG("r"."points"), AVG("r"."maxforanswered")

If I asdd a non-aggregate to the select column tuple it works. But I should be able to write it so it corresponds to the query I actually want to execute no?

If I have two postgres columns, both of which can be null, is there is a neat way to require one to not be null using pony?

Hi Jim! Pony uses implicit grouping. If the generator expression part is a tuple containing both aggregated and non-aggregated expressions, then Pony places non-aggregated expressions to GROUP BY section. If select tuple consists from aggregated expressions only, Pony should not add GROUP BY section at all. So, it looks like a bug #todo I hope we'll fix it soon.

If I have two postgres columns, both of which can be null, is there is a neat way to require one to not be null using pony?

Pony doesnt have explicit check for this. You can manually execute SQL command ALTER TABLE mytable ADD CONSTRAINTS mycheck CHECK(column1 is not null or column2 is not null) in order to be sure that the database always contains valid data, and also you can define before_insert/before_update check to throw the error in Python as soon as possible def check_columns(obj): if obj.column1 is None and obj.column2 is None: raise TypeError("column1 or column2 should be specified") MyClass.before_insert = check_columns MyClass.before_update = check_columns

Hi Jim! Pony uses implicit grouping. If the generator expression part is a tuple containing both aggregated and non-aggregated expressions, then Pony places non-aggregated expressions to GROUP BY section. If select tuple consists from aggregated expressions only, Pony should not add GROUP BY section at all. So, it looks like a bug #todo I hope we'll fix it soon.

Yep it looked like a bug to me too. I can put together a concise test case if you would like.

It would be great

Thanks Alexander 🙂

How can this sql can be translated to pony orm query? SELECT DISTINCT entiny_name FROM table1 INNER JOIN table2 ON table1.id = table2.entity_id WHERE table2.property = '...' ORDER BY table2.creation_time LIMIT 10

select( e1.entity_name for e1 in Entity1 for e2 in e1.some_collection if e2.property == x ).order_by(lambda: e2.creation_time)[:10]

Thanks, works fine

Hello guys. is there any solution to use PonyORM async?

Database transactions are not that good with async, cause they're atomic

Hi Alex! I don't recommend use PonyORM with async code right now. The resulted code can be error-prone and hard to debug. The safe way is to place all database code in a separate layer with separate API and make short synchronous calls to it from async code

Hi there, in order to get all columns and rows of a table including column heads as Pandas DataFrame, this works (using Panda's read_sql method): `df = pd.read_sql('SELECT * FROM MyEntity', db.get_connection())` But how to do the same with Pony and without raw SQL?

do you want a mapping of column -> value for each column?

If so, you can use for x in MyEntity.select(): x.to_dict()

Hi. I need make my search with case insensitive in postgres. Have ease method to make that?

I think I have found a bug

pony.orm.core.DBSchemaError: Foreign key fk_facebooktargetingspec_facebooktargetingspectag__facebooktarg cannot be created, name is already in use

that name is 63 characters

I am pretty sure the name is not already in use

I think it’s hitting a postgres limit?

That schema works fine with sqlite

Maybe the name is getting truncated, then it is colliding with another foreign key name?

Aha, from https://github.com/ponyorm/pony/issues/16 -

Another problematic situation is with a long names which don't fit within specified database maximum object name length. Current version of Pony just cut such names to maximum allowable length, but this can lead to situation when several different columns/foreign keys/indexes has the same name. The most problematic database in this aspect is Oracle, because in it the maximum name length cannot be greater than 30 chars. So even several independent columns can be erroneously cut to the same name. PostgreSQL and MySQL has less restrictive constraints of 63 and 64 max symbols in name, but even that may be not enough for automatically generated names of foreign keys and composite indexes. The correct solution is to add some unique md5 hash to the end of a truncated identifier name, if the length of the original name is too big.

Hi Matthew! Yes, this is the bug which will be fixed when we release migrations. Until that you can workaround the bug by specifying explicit fk_name value for relationship attribute

Hi. I need make my search with case insensitive in postgres. Have ease method to make that?

Hi Jones, you can write comparison as if obj.attr.lower() == some_value.lower()

I’m happy to use fk_name, but I suspect there may be more to it from looking at the name:

the name seems to be repeating

class FacebookTargetingSpec(db.Entity): id = PrimaryKey(int, size=64, auto=True) uuid = Required(uuid.UUID, default=uuid.uuid4, index=True) created = Optional(datetime.datetime, default=datetime.datetime.now, index=True) updated = Optional(datetime.datetime, default=datetime.datetime.now, index=True) facebook_created_time = Required(datetime.datetime) facebook_updated_time = Required(datetime.datetime) user = Optional(lambda: User) facebook_id = Optional(str, index=True) name = Required(str, 2048) description = Optional(str, 2048) sentence_lines = Required(Json) targeting = Required(Json) approximate_count = Required(int, size=64) facebook_run_status = Optional(str) tags = Set(lambda: FacebookTargetingSpecTag) class FacebookTargetingSpecTag(db.Entity): id = PrimaryKey(int, size=64, auto=True) uuid = Required(uuid.UUID, default=uuid.uuid4, index=True) created = Optional(datetime.datetime, default=datetime.datetime.now, index=True) tag = Required(str) tag_slug = Optional(unicode, 200, default='') facebook_ad_account = Required(lambda: FacebookAdAccount) facebook_targeting_spec = Set(lambda: FacebookTargetingSpec) composite_key(tag, facebook_ad_account)

Those are the relevant models

there are no other models with similar names

fk_facebooktargetingspec_facebooktargetingspectag__facebooktarg

the last part seems to be referencing facebooktargetingspec for the second time?

Default fk name constructed as fk_childtablename__fkcolumn1_fkcolumn2_fkcolumnN

Oh so would it be the composite key generating that name?

So the last part referencing attribute

Oh so would it be the composite key generating that name?

It name includes all element of foreign key, the number of column equal to the number of columns in the primary key of a linked table

Aren’t the primary keys in the models I shared only a single column though?

the fk_name workaround did work 🙂

the last part seems to be referencing facebooktargetingspec for the second time?

This is fk for many-to-many table for relationship FacebookTargetingSpec.tags <-> FacebookTargetingSpecTag.facebook_targeting_spe The name of m2m currently generated as entity1name_entity2name So the resulted fk name for m2m table is fk_entity1name_entity2name__columnname

I'm trying out Pony ORM here and running into a strange issue. I declare a table column as Optional(str), but it results in TEXT NOT NULL when the table is created. I thought Optional was meant for nullable columns. Is this a bug or am I missing something?

OK, reading the API Reference put me on the right track... Seems like I had to explicitly set nullable=True. This doesn't happen automatically with str type (except for in Oracle DB if I understand the documentation correctly).

Hi Jones, you can write comparison as if obj.attr.lower() == some_value.lower()

I need make it in select function.... but I suspect is slowly

you can write raw sql fragment inside a query to use ILIKE: pattern = 'FOOBAR' select(x for x in MyEntity if x.a > b and raw_sql("x.some_column ILIKE $pattern"))

@matthewrobertbell thanks! Difference between pd.read_sql('SELECT * FROM MyEntity') and for x in MyEntity.select(): x.to_dict(): the to_dict loses the column order. In MyEntity, attributes have a meaningful order which I'd like to preserve in the DataFrame. Any ideas?

You can get the column order from Myentity._columns_

Note that’s one underscore on each side

It's MyEntity._attrs_

What’s the difference?

Oh, depends if you want it as a string or not I guess

Ah, ok, I forgot we have _columns_ too in entity class. The difference is that _attrs_ contains attribute desctiptors as objects, and you can retrieve attr.name, attr.py_type, etc., while _columns_ is column's names. If some attribute is a reference to another entity with a composite primary key, then a single attribute will correspond to several columns, so the number of elements in _attrs_ and _columns_ can be different

does anyone integrate pony into a flask app? is it okay?

We use this combination for every project. Perfect.

does anyone integrate pony into a flask app? is it okay?

Yup, here too.

me too

with this adapter, right? https://pypi.org/project/Flask-Pony/

I don’t use that

I just directly use pony

Basically this adapter sets the current user from the http session for each request

There is another way of automating this - using some kind of a ‘login required’ decorator for each handler http://flask.pocoo.org/docs/0.12/patterns/viewdecorators/

ok, thanks

does anyone integrate pony into a flask app? is it okay?

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

@smlkw

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

yes, i am seeing this now

thanks :)

and one question, does it work https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate ?

and one question, does it work https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate ?

Permanent link to the file pony/migrate mentioned. (?)

I mean migrations

It was experimental version, we'll soon release another one

okay

I got an error when I am using Pony with Flask

Can I use Russian here?

Can I use Russian here?

This is an English speaking chat

This is an English speaking chat

Because of it I am asking. Maybe there is a group for Russian speaking audience?