Alexander

no, the one of the tags exists and the other is not

Lucky

Right. Is that where correct? Or should that be if?

Alexander

yes, it should be if

Lucky

:D Thanks.

Lucky

Awesome, works!

Alexey

Awesome, works!

@luckydonald how do you find the resulting query witten with Pony? does it look simple and understandable for you?

Lucky

@luckydonald how do you find the resulting query witten with Pony? does it look simple and understandable for you?

It is a bit long. Yeah. But I think that comes with the complexity of the task.

Lucky

I just think the two fors are strange, and not instantly to understand

Alexey

it would be interesting to compare this good real-life example of a long query with one written with other mappers

Alexander

It is not necessary to use two `for`s here. The query can be rewritten as: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or orm.exists(t for t in st.sticker.tags if query_text.lower() in t.string.lower())) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) Also, I don't think it is good to calculate t.string.lower() inside query, it is better to pre-calculate it in a separate column. If it is possible to check this string for exact equality instead of substring test, the query can be expressed a bit simpler: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or query_string.lower() in st.sticker.tags.lowerercase) and "nsfw" not in st.sticker.tags.lowercase) ).order_by(orm.desc(2), 1).limit(50, offset=offset)

Lucky

How can I specify a contraint on multible colums to be unique? I want Tag.string, .user, .message_id to be together unique.

Lucky

It is not necessary to use two `for`s here. The query can be rewritten as: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or orm.exists(t for t in st.sticker.tags if query_text.lower() in t.string.lower())) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) Also, I don't think it is good to calculate t.string.lower() inside query, it is better to pre-calculate it in a separate column. If it is possible to check this string for exact equality instead of substring test, the query can be expressed a bit simpler: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or query_string.lower() in st.sticker.tags.lowerercase) and "nsfw" not in st.sticker.tags.lowercase) ).order_by(orm.desc(2), 1).limit(50, offset=offset)

I'll have a look, thank you!

Lucky

This is composite_key(string, user, message_id), right?

Alexander

yes

Lucky

Thanks

Lucky

Will it be applied, when the table already exists?

Alexander

I think it should. I go offline, will answer other questions tomorrow. Bye!

Lucky

Thanks for your time! Have a good night!

Lucky

I can't get COUNT to work... :/ orm.select((t.string, orm.count(t.string)) for t in Tag).order_by(orm.desc(2), 1) This returns ["MLP", 1] But the database definitly contains more than one

Lucky

I can't get COUNT to work... :/ orm.select((t.string, orm.count(t.string)) for t in Tag).order_by(orm.desc(2), 1) This returns ["MLP", 1] But the database definitly contains more than one

Lucky

If instead of t.string I use t, there are indeeed multible results

Alexander

When a specific attribute is specified inside count, pony counts distinct values of that attribute. In order to count total number of rows just write count() without any arguments, it translates to COUNT(*)

Lucky

I want to count all equal strings

Lucky

And generate a list with [string, COUNT]

Lucky

E.g. 12x "MLP", 12x "Pony", 5x "Smiling"

Alexander

then do 'select((t.string, orm.count()) for t in ...)' or 'select((t.string, orm.count(t)) for t in ...)'

Alexander

The former query means "for each string count number of rows", the later one means "for each string count number of distinct Tag objects", the result should be the same in your case

Lucky

Which would be more efficient?

Alexander

The first one, I think. The difference should not be large

Serg

"In this case, even though the object had already been loaded to the cache, the query still had to be sent to the database because the name attribute is not a unique key. The database session cache will only be used if we lookup an object by its primary or unique key." https://docs.ponyorm.com/firststeps.html#getting-objects If object would be in the cache and someone would change this row in database would be cached object updated or not?

Serg

I think here is answer on my questions https://docs.ponyorm.com/transactions.html#optimistic-concurrency-control

Alexander

If Pony read an object which was already loaded in a current db_session, then Pony checks whether an attributes still holds the same value. There are four possible situations: 1) The column value is the same as previously, Pony does nothing; 2) The column value is updated in the database, but the attribute value was no read or modified by application code. In that case Pony updates the attribute value silently. 3) The application code has already read previous value of the attribute. In that case, it is possible that current state of db_session depends on the obsolete value which was read from the attribute, and to prevent possible inconsistency Pony throws UnrepeatableRead exception. 4) The value of column in the database is changed. An application code already assign a new value to the attribute without reading the previous value. In that case the "db value" of attribute will be updated silently and later during COMMIT it will be overwritten by newly assigned value

Serg

Thank you @akozlovsky for that clear explanation. By the way, I reed that you planned to include GrapQL into new PonyORM relase. When I see GraphQL query it seems stict and clear: type Project { name: String tagline: String contributors: [User] } In Python 3.6 will be variable annotaion, and table defination code coul be like this:

Serg

class Project: name: str tagline: str contibutors: [User]

Serg

I think it could be cool thing to define db schemas

Serg

What do you think, guys?

Romet

Agreed

Alexander

Type is just a part of the information about attribute. Beside type, there are many other things, like is it nullable, is it unique, what a maximul leghts of a string attribute, etc. Regarding collection attributes, currently we have Set attributes only, but in the future we plan to add List attributes as well, so the square bracket syntax is not enough to declare all necessary information

Serg

What about typing library? There we could manually add custom types

Alexander

Can you give an example

Serg

https://docs.python.org/3/library/typing.html#generics

Romet

@akozlovsky with the new annotation system you can also just create custom types for annotation purposes

Alexander

But what may be an example in context of ORM usage

Alexander

Some simple use-case

Serg

For example it would be good for type checker

Alexander

Can you give an example of code?

Serg

I didn't see it

Serg

Maybe @amPerl could help with it

Romet

i'd have to write up an example

Romet

remind me to never google "pony model" again

Lucky

;:D

Lucky

remind me to never google "pony model" again

Serge

Funny that @akozlovsky has no admin rights in this chat:)

stsouko

Hello! is it possible to get objects in many-to-many relations in creation order?

stsouko

class Structures(db.Entity): id = PrimaryKey(int, auto=True) data = Required(LongStr) reagents = Set('Reactions', reverse='reagents') products = Set('Reactions', reverse='products') class Reactions(db.Entity): id = PrimaryKey(int, auto=True) reagents = Set(Structures, reverse='reagents') products = Set(Structures, reverse='products')

stsouko

r=Reactions() r.reagents.add(s1) r.reagents.add(s2) r.reagents.add(s3) list(r.reagents)

Alexander

Currently Set collections are unordered. You can get ordered list of items if you performs a query: r.reagents.select().order_by(lambda r: r.id)[:]

Alexander

By the way it is better to name all entities in a singlular form: Structure, Reagent, etc.

Alexander

Also, it looks strange that both end of relation have the same name: Structure.reagents <-> Reaction.reagents Structure.products <-> Reaction.products Typically the name is different, like: Student.courses <-> Course.students

Lucky

Also, it looks strange that both end of relation have the same name: Structure.reagents <-> Reaction.reagents Structure.products <-> Reaction.products Typically the name is different, like: Student.courses <-> Course.students

Oh great, one of them new urls

stsouko

Thanks. But this recipe order objects by structures. This is chemical db. Any reaction have reagents and products. And any structures may be in reagent role or product

stsouko

I will rewrite many to many to combination of 2 many to one

Alexander

Ok, now I understand. I think you should not replace many-to-many with several many-to-one, it will be not correct. I think your current scheme is good

Павел

http://joxi.ru/KAxjMx7uMZOD0m?d=1 Thanks for magnificent ORM!

Lucky

ValueError: Attribute StickerPack.url is required

Lucky

ValueError: Attribute StickerPack.url is required

class Sticker(db.Entity): file_id = Required(str) # BQADBAAD6QIAAqQW9AX8GaFqUwWnrgI emoji = Required(unicode) # ❓ sticker_pack = Optional('StickerPack', column='sticker_pack_url') tags = Set('Tag', cascade_delete=True) sticker_messages = Set('StickerMessage') tl_id = Optional(int, size=8) # The id field of the telegram api schema the apps use. tl_access_hash = Optional(int, size=8) # The access_hash field of the telegram api schema the apps use. PrimaryKey(file_id, emoji) - class StickerPack(db.Entity): url = PrimaryKey(str) title = Required(str) owner = Required(User, column='owner_id') stickers = Set(Sticker) first_seen = Required(datetime, sql_default='NOW()')

Lucky

ValueError: Attribute StickerPack.url is required

Lucky

For my own reference: sticker:BQADAgADRgADyIsGAAF-kAhqVu7-1QI

Alexander

I think this is a bug, and I'll fix it. The reason for the error is: the attribute is specified as required, but in the database its value for some object is empty string. So when Pony attempts to load that object from the database it encounters validation error. I'll change that behavior so Pony will ignore the error for empty string values loaded from the database.

Serge

I think this is a bug, and I'll fix it. The reason for the error is: the attribute is specified as required, but in the database its value for some object is empty string. So when Pony attempts to load that object from the database it encounters validation error. I'll change that behavior so Pony will ignore the error for empty string values loaded from the database.

Easy:)

Lucky

I think this is a bug, and I'll fix it. The reason for the error is: the attribute is specified as required, but in the database its value for some object is empty string. So when Pony attempts to load that object from the database it encounters validation error. I'll change that behavior so Pony will ignore the error for empty string values loaded from the database.

Yes, the string field is indeed empty.

Lucky

Yeah, the value was set with a java client, where there is no such nice framework. (I was really sad)

Alexander

Done: https://github.com/ponyorm/pony/commit/60ddecb060b8ea3f8637d87ec1bcbac51d8c4ddf

Alexander

Have a good time of day, guys, I'm going to sleep

Lucky

Thank you for the fix. Ping me when there is a new release, please

Lucky

Until then pip install -e git://github.com/ponyorm/pony.git@dfedd28d02330fb351dc019f4dd03134299c23ab#egg=pony will work

Lucky

---

Lucky

TIL, https://telegram.me/addstickers/ is a stickerpack

Lucky

https://telegram.me/StickerTagBot?start=sticker:BQADAgADRgADyIsGAAF-kAhqVu7-1QI