It seems that the bug is not in Pony. I tested on 0.7.2 and master, and the following code works without any problem: >>> from pony.orm.examples.university1 import * >>> select(s for s in Student if raw_sql('s.id % 4') == 0).random(5)

that example also works for me, that is weird

Maybe you can show traceback of your error

I was able to reproduce. The reason for the error is, PostgreSQL psycopg2 driver uses % symbols to represent query parameters, so in PostgreSQL it is necessary to double % sign inside RawSQL expression: select(u for u in User if raw_sql("u.id %% 4") == 0).random(5) It seems that without native Pony support of modulo division its using is a bit cumbersome. Probably we need to add it sooner

Thank you very much!

was the pony example using sqlite?

Yes, by default it uses SQLite

i can confirm double %% works

Now you can use native modulo division for all databases we support. select(x for x in X if x.id % 1337 == 0)

Using GitHub version ofc.

Will be included in the next release

great :D

Good. I need also string.strip :D

For what use case?

For stupid legacy database that store array like this "1 2 3"

We already support strip method. Maybe you mean split

Oh, yeah. My bad.

It's not really a feature request as it's not really needed by everyone

May be I'll do it myself someday.

Ok. We can add it if you suggest how to translate it to SQL

sqlite3.create_function() will translate any your wish

It is not as easy even for SQLite, because you need to return array of strings from this function

Why not just do it in python?

The problem is I need to search in them. I used to do "if number in ugly_string_array" it was enough... but... you know. There can be collisions the future.

In python I would need to itterate trough quite big amount of record. But still I can itterate after using "in"... but it's as ugly as database structure itself :D

You can search using LIKE operator if string components are separated properly. class Article(db.Entity): name = Required(str) tags = Optional(str) ... with db_session: a1 = Article(name='Article1', tags='*foo*bar*baz*') ... with db_session: baz_articles = Article.select(lambda a: '*baz*' in a.tags)

Hmm. Interesting.

hey guys. I'm new to PonyORM and SQL in general. I've been browsing the documentation, trying to decide between sqlalchemy and pony, and I noticed there is no apparent way to do database migrations (ie. /w Alembic). What is the best practice around this?

It depends on, whether you need migrations to modify database schema. Official migration tool is not ready at this moment, we are working on it and hope it will be released soon. Until this moment, you can do migrations manually, typically it is easy to write ALTER TABLE command if you know the syntax. Another option is to use migration tool written by @luckydonald: https://github.com/luckydonald/pony_up

^

cool. I'll play with that. Is there any ETA on official migration tool?

Should be here already since quite some time, but It seems to be delayed.

Yes. There are good reasons for that.

I mean delay.

Please elaborate

Im not sure is that super secret inside info or not. But we keep work on them.

Uhhh. Seeeeeeeeecrets!

Like: migrations that we have now is not that thing we really want to release.

what in your opinion is the hardest thing about migrations?

In my - merge migration branches.

Hah, merging branches always is fun

pony.orm.core.OptimisticCheckError: Object User[x] was updated outside of current transaction - would it be possible to expand this error message to show which attribute(s) were updated?

then I can know which can be marked as optimistic

It is not easy to do. If a program retrieves an object with specific id value, read attributes b, c and d and then update attributes a and b, Pony will send a query which looks like UPDATE t1 SET a = <new_a>, b = <new_b> WHERE id = <id> AND b = <prev_b> AND c = <prev_c> AND d = <prev_d> And then Pony checks the number of rows which were updated. It may be 1 or 0. Zero means that some attribute where changed, but it is hard to say which exactly. Maybe in this case we can do additional select in order to retrieve new attribute values

I'm not sure that marking attribute as not optimistic is correct solution, because this way you just hide some lost update

In this case, I am not editing the object apart from a single attribute, a "date" type, is there a way to ignore all of the other attributes changing?

this is a very CPU intensive task, so redoing it isn't ideal

Maybe catching the OptimisticCheckError, then retrying that single attribute update?

I don't think I want to lock the object, as this would stop users from doing other things during the multiple minute background processing job

Maybe we can add new option to db_session, something like: with db_session(optimistic=False): ...

This option will turn off optimistic checks. It may lead to lost updates, so programmer need to be careful about when to use it

That sounds like it could work, but maybe I'm missing something and Pony already allows this?

I don't think restarting the whole transaction makes sense

the use case is doing a lot of reads from the database, writing some computed data to redis as a cache, then marking that user object as computed for today

Since the modulus now works, there are multiple processes doing this, so there are a lot more optimistic errors

I think you can attempt the following: 1) Mark that single datetime attribute as volatile. It is possible that you have concurrent processes that tries to do the same calculation on the same object in parallel. You can also add some in_process volatile flag to object which will prevent concurrent processes to start the same calculation for object that is already processing. 2) If concurrent processes actually changes some attributes beside that single datetime attribute, then I'm not sure your redis cache contains valid non-obsolete data. It looks suspicious to me. But if you think that all is OK, you can prevent optimistic errors by extracting that single datetime attribute to another object. Then you entities may look like: class MyObject(db.Entity): id = PrimaryKey(int, auto=True) foo = Required(int) bar = Optional(str) object_state = Optional(lambda: ObjectState) class ObjectState(db.Entity): id = PrimaryKey(int, auto=True) object = Required(MyObject, unique=True) in_process = Required(bool, default=False) processed_at = Optional(datetime) Then, you should create objects in pairs: obj = MyObject(foo=10, bar='hello') obj_state = ObjectState(object=obj) And then you can process object as following: with db_session: query = MyObject.select(lambda obj: obj.id % N == M) query = query.filter(obj.foo == X) query = query.filter(lambda obj: not obj.object_state.in_process and ( obj.processed_at is None or obj.processed_at < datetime.now() - timedelta(days=1))) objects_to_process = query[:] for obj in objects_to_process: obj.object_state.in_process = True commit() for obj in object_to_process: make_some_complex_caclulation(obj) obj.object_state.in_process = False obj.object_state.processed_at = datetime.now() In that case, you read foo and bar attributes from obj and write in_process and processed_at attributes to obj_state, so concurrent errors should not appear

But in any case I need to do the following: 1) Change Pony code so if you start db_session with serializable=True option (which means full isolation) than optimistic checks are not added to queries. 2) In case of optimistic errors, make additional query to database in order to determine which attribute was changed

./

Hola.

Has anyone tried pony with the postgres 10 betas yet? I plan on doing so soon and wanted to know if there are any problems

Has anyone tried pony with the postgres 10 betas yet? I plan on doing so soon and wanted to know if there are any problems

What's new, or what new feature do you want to use?

Parallel queries is the big advantage I see

https://news.ycombinator.com/item?id=15306398

/translate

@akozlovsky can I add @bonbot for the /translate command and for the github permalinks?

/translate

It seems missclick. Nvm

/translate

Sorry for that, wrong tab=)

Ah, ok xD Point still stays xD

@akozlovsky can I add @bonbot for the /translate command and for the github permalinks?

I think yes. I never used bots yet :)

Any docs of usage?

/translate@bonbot

/translate@bonbot

Please provide a text to translate. Either use /translate some text goes here or reply `/translate` to some text message.

Any docs of usage?

It will tell you with /help xD

/help@bonbot

/help@bonbot

Sending help to you privately.

Anyone have issues with pony and gunicorn?

My app is failing if I import my models (which includes binding to the database)

With what exception?

Nevermind, it was a dumb syntax error :/

Hi

definitely not, please give a link to your diagram

https://editor.ponyorm.com/user/joery/test/designer

I always happens, after you add the first entity

it is fixed now

Awesome! Thank you