>>> results = orm.select((orm.count(ju), ju.referrer_chat_id, ju.referrer_external) for ju in JoiningUser) >>> results.first() None

Oh wait.

Silly me, there were no data in that table

No rows = no result = None

Hi all. I've had a good hunt through google and various github repos. I'm running into an error when using Pony ORM with PyPy on Ubuntu 18. Does anyone have any advice or similar experience? With PyPy 7.3.1 installed, the issue is easily reproduced like so: virtualenv -p pypy3 venv source venv/bin/activate pip install pony python -c 'from pony.orm import Database; db = Database(); db.bind(provider="sqlite",filename=":memory:")' This results in the following, for me:

Traceback (most recent call last): File "./app.py", line 30, in <module> db.bind(**db_params) File "/tmp/whatever/venv-pypy/site-packages/pony/orm/core.py", line 769, in bind self._bind(*args, **kwargs) File "/tmp/whatever/venv-pypy/site-packages/pony/orm/core.py", line 791, in _bind self.provider = provider_cls(*args, **kwargs) File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbproviders/sqlite.py", line 328, in __init__ DBAPIProvider.__init__(provider, *args, **kwargs) File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbapiprovider.py", line 130, in __init__ connection, is_new_connection = provider.connect() File "<string>", line 2, in connect File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbapiprovider.py", line 55, in wrap_dbapi_exceptions try: return func(provider, *args, **kwargs) File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbapiprovider.py", line 230, in connect return provider.pool.connect() File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbapiprovider.py", line 351, in connect pool._connect() File "/tmp/whatever/venv-pypy/site-packages/pony/orm/dbproviders/sqlite.py", line 671, in _connect con.execute('PRAGMA foreign_keys = true') File "/snap/pypy3/current/lib_pypy/_sqlite3.py", line 419, in execute return cur.execute(*args) File "/snap/pypy3/current/lib_pypy/_sqlite3.py", line 773, in wrapper return func(self, *args, **kwargs) File "/snap/pypy3/current/lib_pypy/_sqlite3.py", line 934, in execute return self.__execute(False, sql, [params]) File "/snap/pypy3/current/lib_pypy/_sqlite3.py", line 884, in __execute if self.__connection._begin_statement and self.__statement._is_dml: AttributeError: 'Connection' object has no attribute '_begin_statement'

It seems something wrong with sqlite3. I don't think this is directly related to Pony

Indeed. Hmm. Okay, thanks @metaprogrammer I'll continue my investigations!

Quick update: Seems to be some combination of PyPy + sqlite3. I was using PyPy 7.3.1 installed using snap. By uninstalling that and installing PyPy using apt instead, I'm not seeing this error.

Ok, good to know

Hello! Is it possible to use the one model for multiple databases?

No, db object connects to database first to determine server version, as it may be important to know, what columns should be generated But if you define entities inside a function, you can create several db objects and connect them to different databases

Thank!

I need help with a query. I have a Join table (see https://editor.ponyorm.com/user/luckydonald/channel_network/snapshots/176 ) query = orm.select( (orm.count(j.id), j.joining_user is None, j.joining_user.referrer_chat_id, j.joining_user.referrer_external) for j in Join if j.chat.id == update.message.chat.id ).order_by(orm.desc(1))

Now I want to get the count of - no j.joining_user - each unique j.joining_user.referrer_chat_id - each unique j.joining_user.referrer_external

This is how my joins look like

The generated Query: SELECT COUNT(DISTINCT "j"."id"), "j"."joining_user" IS NULL, "joininguser"."referrer_chat_id", "joininguser"."referrer_external" FROM "join" "j", "joininguser" "joininguser" WHERE "j"."chat" = -1001496723017 AND "j"."joining_user" = "joininguser"."id" GROUP BY "j"."joining_user" IS NULL, "joininguser"."referrer_chat_id", "joininguser"."referrer_external" ORDER BY 1 DESC

The generated Query: SELECT COUNT(DISTINCT "j"."id"), "j"."joining_user" IS NULL, "joininguser"."referrer_chat_id", "joininguser"."referrer_external" FROM "join" "j", "joininguser" "joininguser" WHERE "j"."chat" = -1001496723017 AND "j"."joining_user" = "joininguser"."id" GROUP BY "j"."joining_user" IS NULL, "joininguser"."referrer_chat_id", "joininguser"."referrer_external" ORDER BY 1 DESC

It is missing the (null, null) entities.

With SELECT COUNT("j"."id"), "joining_user" IS NULL AS "jan", "joininguser"."referrer_chat_id", "joininguser"."referrer_external" FROM "join" "j" FULL JOIN "joininguser" "joininguser" ON "j"."joining_user" = "joininguser"."id" WHERE "j"."chat" = -1001496723017 GROUP BY "jan", "joininguser"."referrer_chat_id", "joininguser"."referrer_external" ORDER BY 1 DESC I could come closer to an correct result, but I think it's still missing one.

For reference: Here is a pure python implementation of the count: with orm.db_session(): stats = {} # {(1,2) : 3} # Key = (referrer_chat_id, referrer_external) # Value = count stats: Dict[Tuple[Union[None, int], Union[None, int]], int] join: Join for join in orm.select(join for join in Join if join.chat.id == update.message.chat.id): key = (join.joining_user.referrer_chat_id, join.joining_user.referrer_external) if join.joining_user else (None, None) if key not in stats: stats[key] = 0 # end if stats[key] += 1 # end for # end with

So I think the problem sums up to me liking to include NULLs in the joining_user field, with a select like ( orm.count(j.id), j.joining_user.referrer_chat_id if j.joining_user else None, j.joining_user.referrer_external if j.joining_user else None ) Instead of ( orm.count(j.id), j.joining_user.referrer_chat_id, j.joining_user.referrer_external )

Sadly that results in pony.orm.decompiling.DecompileError: Expression is too complex to decompile, try to pass query as string, e.g. select("x for x in Something")

How do I order a select result randomly? I tried select(x for x in Entity).random(), but that doesn't work because I have to specify a limit. Using .order_by('random()') doesn't work either: chat_network_cron_1 | )).order_by('random()') chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 5991, in order_by chat_network_cron_1 | return query._order_by('order_by', *args) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 6011, in _order_by chat_network_cron_1 | return query._process_lambda(func, globals, locals, order_by=True) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 6069, in _process_lambda chat_network_cron_1 | func_id, func_ast, globals, locals, special_functions, const_functions, argnames or prev_translator.namespace) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 360, in create_extractors chat_network_cron_1 | pretranslator = PreTranslator(tree, globals, locals, special_functions, const_functions, outer_names) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 276, in __init__ chat_network_cron_1 | translator.dispatch(tree) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 285, in dispatch chat_network_cron_1 | ASTTranslator.dispatch(translator, node) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 42, in dispatch chat_network_cron_1 | translator.call(post_method, node) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 44, in call chat_network_cron_1 | return method(translator, node) chat_network_cron_1 | File "/usr/local/lib/python3.7/site-packages/pony/orm/asttranslation.py", line 338, in postCallFunc chat_network_cron_1 | x = eval(expr, translator.globals, translator.locals) chat_network_cron_1 | File "<string>", line 1, in <module> chat_network_cron_1 | NameError: name 'random' is not defined

You have to specify a limit, or select all results, but them in a list, then shuffle the list

Is that a limitation of Postgres?

I’m not sure, but if you have a huge amount of rows performance won’t be good whether you shuffle in python or postgres

Try running a postgres query with “order by random”

but if I limit and do that multiple times I would get duplicates

What is your use case?

You could use raw sql from pony

It's a cron job, and should process all items matched.

Why do you need random ordering for the cron job?

In case of an error it would always spam the first element, so if I do a random I can minimize that risk

Maybe try doing newest first then

Or catching and logging the error

Or recording the last error on that row and not trying again for a certain amount of time

Maybe try doing newest first then

Yeah, still it would always start with the same, so the errors would slow it down.

You never add new rows?

I guess limiting to the max int of the database will be the same effect.

)).order_by(orm.raw_sql('RANDOM()')) works

Thanks!

I need help with a query. I have a Join table (see https://editor.ponyorm.com/user/luckydonald/channel_network/snapshots/176 ) query = orm.select( (orm.count(j.id), j.joining_user is None, j.joining_user.referrer_chat_id, j.joining_user.referrer_external) for j in Join if j.chat.id == update.message.chat.id ).order_by(orm.desc(1))

Any Idea on this?

> How do I order a select result randomly? You need to understand this is very slow, and the more rows will be in the table the worse it will get The practical way is to go through rows in linear order, mark the current row as being processed, commit to be sure the update is saved and then process it and commit again to save the result of processing

> Using .order_by('random()') doesn't work either If you write Python code inside a query as a string, you still need to define all names used inside the code Here you need to add from random import random at the beginning of your module

So I think the problem sums up to me liking to include NULLs in the joining_user field, with a select like ( orm.count(j.id), j.joining_user.referrer_chat_id if j.joining_user else None, j.joining_user.referrer_external if j.joining_user else None ) Instead of ( orm.count(j.id), j.joining_user.referrer_chat_id, j.joining_user.referrer_external )

Pony does not support decompiling if-expressions, but you can provide generator code as a text string, if-expressions are supported inside a strings: query = orm.left_join(""" ( orm.count(j.id), j.joining_user is None, j.joining_user.referrer_chat_id if j.joining_user is None else None, j.joining_user.referrer_external if j.joining_user is None else None ) for j in Join if j.chat.id == update.message.chat.id """ ).order_by(-1)

I didn't check this query, but I think it should work

Maybe using left_join is enough: query = orm.left_join( ( orm.count(j.id), j.joining_user is None, j.joining_user.referrer_chat_id, j.joining_user.referrer_external ) for j in Join if j.chat.id == update.message.chat.id ).order_by(-1)

Ah, thanks a ton.

Hi there, I somehow broke a diagram in the online editor, maybe by editing in two browser tabs at the same time. When I want to open the diagram I get {"msg":"Server error"} Any possibility to rollback? Or even to delete? It's a private diagram and my counter still counts it. Second question: wouldn't it be nice to have the possibility to import entities code into the online editor? Or is that already possible and I've just missed something?

Hi there, I somehow broke a diagram in the online editor, maybe by editing in two browser tabs at the same time. When I want to open the diagram I get {"msg":"Server error"} Any possibility to rollback? Or even to delete? It's a private diagram and my counter still counts it. Second question: wouldn't it be nice to have the possibility to import entities code into the online editor? Or is that already possible and I've just missed something?

No that doesn'T exist yet, and yes a feature I periodically request too.

And another #bug I face, multiline comments break the syntax. Please either replace the linebreak with a space or make it intend under the started comment and start with a # too.

There doesn't seem to be any progress on that editor. Maybe you guys should think about open sourcing it, so people can implement the features they need (like proper import/export) or a few of the simple features I requested.

Another feature I'm missing is custom export templates, so I can use the typing module to annotate the types of those db fields.

AFAIK this bug is fixed but probably not deployed.

hi there, this is my first time using pony, my question is: i have an exists mysql database, to use pony should i create models with tables first or there is a method to work with pony without defining models

?

Hi, you need to define entities which corresponds to existing tables

great, thank you

how can i get the last row in a table ?

myquery[-1]

myquery[-1]

not from a query instance. from a model like this Order.last()

Order.select()[:1] I guess. there is a first() but I don't know if there is a last()

sorry Order.select()[1] I mean

sorry Order.select()[1] I mean

Order.select()[-1] you mean this or it is wrong ?

Oh forget I do it again

Order.select()[-1] for the last one and Order.select()[:-1] to get last item but as a list, like python slice

Order.select()[-1] for the last one and Order.select()[:-1] to get last item but as a list, like python slice

thank you

guys i am using this option = Option.select(lambda o: o.id==1) but the result keeps giving me Option[1] even when i want to get the first one like this: option = Option.select(lambda o: o.id==1)[0] it gives me Option[1] again

not from a query instance. from a model like this Order.last()

Order.select().order_by(desc(Order.id)).first()

guys i am using this option = Option.select(lambda o: o.id==1) but the result keeps giving me Option[1] even when i want to get the first one like this: option = Option.select(lambda o: o.id==1)[0] it gives me Option[1] again

What result do you expect?

@Karrar_220 to get Option[1] you can do Option[1] or Option.get(id=1)

Does Pony support a // b?

yes

Well, it understands // in Python, but I'm not sure it translates it to different SQL

I need to have it to round down (floor) the result, actually.

In SQL "/" for integer values works as "//" in Python. So, to calculate "//" both operands should be integers And for "/" equivalent at least one operand should be rational Pony should add conversion to float or to int to correctly translate / and // to SQL, but currently it does not cast operands. I need to fix it #todo Right now, if both operands are int and you use // in Python, the result will be correct in SQL

I have a Channel object telling when it should have the cronjob post something, with a posting_interval and posting_offset. Say interval = 20 and offset = 5 would result in every 20 minutes, shifted by 5 minutes: 00:05, 00:25, 00:45, 01:05, …, 23:45 I mean I have two different versions, one with // and one with modulo %, but don't know which one is better for performace. delta = this_time_in_minutes - last_time_in_minutes # precomputed in python return Channel.select( lambda c: (last_time_in_minutes - c.posting_offset) % c.posting_interval < delta ) or return Channel.select( lambda c: ((this_time_in_minutes - c.posting_offset) // c.posting_interval) > ((last_time_in_minutes - c.posting_offset) // c.posting_interval) )

In SQL "/" for integer values works as "//" in Python. So, to calculate "//" both operands should be integers And for "/" equivalent at least one operand should be rational Pony should add conversion to float or to int to correctly translate / and // to SQL, but currently it does not cast operands. I need to fix it #todo Right now, if both operands are int and you use // in Python, the result will be correct in SQL

> both operands are int This means in the database, or in python?

If they are int in Python, they should be int in the database too