hi, good morning, with traslater it`s sql to pony: select sum(cantidad) as vtotalcantidad from opermv where tipodoc = vtipodoc and serie = vserie and documento = vdocumento and codigo in(select codigo from bonifidet where idbonifica = '00001');

Something like select(sum(x.a) for x in X if x.b == param1 and x.c == param2 and x.d in select(y.foo for y in Y))

Something like select(sum(x.a) for x in X if x.b == param1 and x.c == param2 and x.d in select(y.foo for y in Y))

👌🏼 thanks!

Hey Good Evening. I've a bit of a complex problem I've found and need some guidance how to work out where the problem is and how to resolve. I'll write up a summary, and then create a Google doc with the evidence. As it's quite a subtle problem involving an array of integers...

Hey Good Evening. I've a bit of a complex problem I've found and need some guidance how to work out where the problem is and how to resolve. I'll write up a summary, and then create a Google doc with the evidence. As it's quite a subtle problem involving an array of integers...

Sounds interesting, can you elaborate?

OK, there's a Telegram BOT. EmojiBOT, that uses python3, Pony and postgresql. It creates a table to store chat details, including Telegram IDS. The class definition, for this 'administrators' field is an array of Integers, and the converter makes an array of type integers in postgre. BUT, the Telegram ID IS also a BIGINT. When, this ID is written into the array , and then the column in the table, the postgres DB returns an error 'Integer out of range'. I think the problem is that the array is created with type Integer and not bigint. As python only has type int, I've tried to see where and how I can make the type for the column in the table be created with type bigint[] and not integer[]. It's taken me a week of trawling through all the EmojiBOT code, and pony code, and also testing the database etc to find this. Which ,I'll detail ina word doc with annotated screen shots.

Sorry for the wall of text!

OK, there's a Telegram BOT. EmojiBOT, that uses python3, Pony and postgresql. It creates a table to store chat details, including Telegram IDS. The class definition, for this 'administrators' field is an array of Integers, and the converter makes an array of type integers in postgre. BUT, the Telegram ID IS also a BIGINT. When, this ID is written into the array , and then the column in the table, the postgres DB returns an error 'Integer out of range'. I think the problem is that the array is created with type Integer and not bigint. As python only has type int, I've tried to see where and how I can make the type for the column in the table be created with type bigint[] and not integer[]. It's taken me a week of trawling through all the EmojiBOT code, and pony code, and also testing the database etc to find this. Which ,I'll detail ina word doc with annotated screen shots.

Why use array though?

Set seams much more resonable here

Pony will handle that for you

The field in the table is called administrators , and is an array of all the IDs of admins in a telegram chat.

The code was written by someone else and is quite complex , so not sure where 'set' would apply , or even what it means.

To clarify, this bot uses Pony ORM, right?

Let me do the doc, and that will explain better... with more contex

class ChatAdmin(db.Entity): telegram_id = orm.Required(int) chat = orm.Required("Chat") class Chat(db.Entity): name = orm.Required(str) admins = orm.Set("ChatAdmin")

It would work same way as array does and would require minimal code change

Yes it does.. check out Fumaz,EmojiCaptchaBot on github. Goto src/db/models.py then down to line 204 and class Chat ... look for line 217 administrators ..

OK, there's a Telegram BOT. EmojiBOT, that uses python3, Pony and postgresql. It creates a table to store chat details, including Telegram IDS. The class definition, for this 'administrators' field is an array of Integers, and the converter makes an array of type integers in postgre. BUT, the Telegram ID IS also a BIGINT. When, this ID is written into the array , and then the column in the table, the postgres DB returns an error 'Integer out of range'. I think the problem is that the array is created with type Integer and not bigint. As python only has type int, I've tried to see where and how I can make the type for the column in the table be created with type bigint[] and not integer[]. It's taken me a week of trawling through all the EmojiBOT code, and pony code, and also testing the database etc to find this. Which ,I'll detail ina word doc with annotated screen shots.

Maybe this would help

https://docs.ponyorm.org/api_reference.html?highlight=64#cmdoption-arg-size

I've tried setting everything to that , and even replaced the word Integer with BIGINT in all code...

Have you updated db itself?

I mean if it's already created, you have to update type manually

I actually loaded the modified py files into docker before the BOT started and created the DB.

If you use Postgres, try connecting to db and updating column type manually

https://www.postgresqltutorial.com/postgresql-change-column-type/

Let me just show the flow quickly.. Line 217 in the models.py defines administrators = Required(IntArray, default[]) ...

Let me look at that change..

Then ormtypes.py line 394 says class IntArray(Array): item_type = int.

So how does the postgres.py code in the dBproviders then know how to create the array with the right integer type... can that line 394, also include a size=64, to allow the field to be BIGINT. ?

Thank-you Volibil for your suggestions , help and insights:)

:)

Can the code to make the integer array also use the PGIntConverter in postgres.py to make the column type.

Let me try the changing ofnthe column type... first

Can I use Pony ORM to make the change.. ? If so how ?

I'm going to sleep now, and will check tomorrow, but if you could think about the array type , as discussed above that would be great !! :)

Can I use Pony ORM to make the change.. ? If so how ?

Unfortunately you can't

Database migration tool is still in development

OK hmmm I'll look at what I can do to execute an sql command right after dB creation to change the field type

Little spoiler. Change Array type to Set(...) lead to 1. Create new 2. Move data 3. Delete old

Thanks for the suggestion ..

Little spoiler. Change Array type to Set(...) lead to 1. Create new 2. Move data 3. Delete old

Hi, I'm not sure what you mean by change the Array to Set(...) , is that in python or within psql itself..

Ah. I think you don't really need arrays for this case. One of big problem we met while developing array support was misunderstanding of this type. I suggest you to have a link to another entity of admin. What I meant by those 3 steps is - 'How do you change this type to new one'. In migration tool it will require 3 migrations which I described like steps.

Ah. I think you don't really need arrays for this case. One of big problem we met while developing array support was misunderstanding of this type. I suggest you to have a link to another entity of admin. What I meant by those 3 steps is - 'How do you change this type to new one'. In migration tool it will require 3 migrations which I described like steps.

Yeah, thats what I suggested above

Using Set would be much more reliable solution compared to arrays

OK, remember this isn't my code , so I'm reverse engineering this to try to fix

I'll lookup what you've suggested and try some things ..

Thanks guys !

Hey guys FYI. I just connected to the DB and manually issued the ALTER command which worked !!! After a week of debugging , this helped... but now i need that column type set as bigint[] to start with.... I'll also look the Set thing.tomorrow .. Is there a way I can edit one of the pony Orm files to make all integer arrays to be of type bigint[] when initialising a Table column on the DB, instead of integer[] .. Just a thought for a hack..

Thanks for the suggestion on the Alter table !! Huzzah :)

@THEPistolPete_PQ_Onboard you should be able to explicitly specify the sql_type value for the attribute: some_array = Required(IntArray, sql_type="bigint[]")

Brilliant let me try that somewhere

Good morning

Would that be in the class definition

yes

The current line is

administrators = Required(IntArray , default []) , so should I change rhe default[] or add eg Required(IntArray, default[], sql_type="bigint[]")

It worked

@metaprogrammer , Thankyou so much , that simple line addition has solved, what has been a tortuous 7 days of deep troubleshooting. That said, my knowledge of docker, python , pony orm and postgres has significantly improved..... again, many thanks! This will make the security of our TG groups much improved!!

Hello, I am using Pony in a Qt5 application and doing queries in a thread (QThread). with Python 3.9 everything works fine! under Python 3.10 the application is terminated with the following code: Process finished with exit code -1073741819 (0xC0000005) If I comment out the pony function everything is fine. I'm using Pony 0.7.15rc1. Here the query:

@db_session(immediate=True) def get_all_in_time_delta(self, t_from: datetime, t_to: datetime, net: str, item_group_selection: ViewMode): try: if item_group_selection == ViewMode.ALL: return (select(r for r in BlzEvent if r.event_time >= t_from and r.event_time <= t_to and r.net == net).order_by(BlzEvent.event_time)).fetch() elif item_group_selection == ViewMode.ACTIVATED: return (select(r for r in BlzEvent if r.event_time >= t_from and r.event_time <= t_to and r.net == net and r.action == '').order_by(BlzEvent.event_time)).fetch() elif item_group_selection == ViewMode.DEACTIVATED: return (select(r for r in BlzEvent if r.event_time >= t_from and r.event_time <= t_to and r.net == net and r.action != '').order_by(BlzEvent.event_time)).fetch() except Exception as ex: return ''

Thank you for the report! That's interesting...

It looks like access violation. Pony is pure-Python and probably cannot produce this type of error. Qt looks more suspicious in this regard

Maybe the problem is not with the query itself, but with Qt state when it processes the query results

The version of QT has not changed. The python version is now 3.10 and the pony version is 0.7.15rc1 as said before. It worked on Python 3.9. All pony functions are in one class. The instance is completely handed over to the thread.

Do you mean PyQt or just pure Qt?

Hi! Is there any Attribute Option to set a column definition (for compatible databases that support metadata)

Hi! What do you mean? Can you provide an example?

Do you mean PyQt or just pure Qt?

I mean PyQT. I suspect a connection with the new Python version. However, my knowledge is not that deep. I can't get any results with breakpoints.

As far as I remember you can wrap your code with try-except and it will most likely show you the real error.

But I'm a big expert as well, just remember some workarounds

I'm just trying something...

I used try-except in all critical places, but the application exits without warning!😡

I used try-except in all critical places, but the application exits without warning!😡

With now traceback?

This error looks like an invalid pointer exception produced by some binary code (Qt in my guess) It cannot be caught by a traceback

Maybe you can add print("debug string", flush=True) before and after query to be sure that the error is not caused by the query execution

This error looks like an invalid pointer exception produced by some binary code (Qt in my guess) It cannot be caught by a traceback

In that case tools like valgrind can be used

From my experience Qt is one big mess

won't be caught either! only output in the console: Process finished with exit code -1073741819 (0xC0000005)

Then I suspect the error in some different place, and the query execution just triggered a problematic code path execution