Yeah that, it gives decompile errors when used in a lambda in order by

I guess i can try it with properties but I expect it to behave the same

Yep, doesn't work. If a property has a ternary op you can neither select it not order by it. Trying to do so results in a cryptic error. The error is different so it doesn't behave exactly the same.

Now the question is whether it's a bug, a current limitation of the library, or a user error and i was supposed to use different syntax.

Now the question is whether it's a bug, a current limitation of the library, or a user error and i was supposed to use different syntax.

Hi again, sorry, I'm a bit busy now, so can't answer questions quickly At this moment Pony does not support decompiling of if-expressions (a if x else b), because the bytecode of if-expression is pretty different from the usual bytecode I hope we can support it in the future. But you can specify generator or lambda expression as a text, and it should work So, instead of this: >>> from pony.orm.examples.university1 import * >>> select(s for s in Student).order_by(lambda s: s.name if s.id < 3 else s.tel).show() Traceback (most recent call last): ... pony.orm.decompiling.DecompileError you can write this: >>> select(s for s in Student).order_by("lambda s: s.name if s.id < 3 else s.tel").show() SELECT "s"."id", "s"."name", "s"."dob", "s"."tel", "s"."gpa", "s"."group" FROM "Student" "s" ORDER BY case when "s"."id" < 3 then "s"."name" else "s"."tel" end id|name |dob |tel |gpa|group --+--------------+----------+-------+---+---------- 3 |Chuan Qin |1989-02-05| |4.0|Group[101] 5 |Maria Ionescu |1991-04-23| |3.9|Group[102] 6 |Oliver Blakey |1990-09-08| |3.1|Group[102] 7 |Jing Xia |1988-12-30| |3.2|Group[102] 4 |Rebecca Lawson|1990-04-18|123-456|3.3|Group[102] 1 |John Smith |1991-03-20|123-456|3.0|Group[101] 2 |Matthew Reed |1990-11-26| |3.5|Group[101]

Inside this text representation of lambda or generator expression you can refer to outer variables just as if it was a normal python code

Hi again, sorry, I'm a bit busy now, so can't answer questions quickly At this moment Pony does not support decompiling of if-expressions (a if x else b), because the bytecode of if-expression is pretty different from the usual bytecode I hope we can support it in the future. But you can specify generator or lambda expression as a text, and it should work So, instead of this: >>> from pony.orm.examples.university1 import * >>> select(s for s in Student).order_by(lambda s: s.name if s.id < 3 else s.tel).show() Traceback (most recent call last): ... pony.orm.decompiling.DecompileError you can write this: >>> select(s for s in Student).order_by("lambda s: s.name if s.id < 3 else s.tel").show() SELECT "s"."id", "s"."name", "s"."dob", "s"."tel", "s"."gpa", "s"."group" FROM "Student" "s" ORDER BY case when "s"."id" < 3 then "s"."name" else "s"."tel" end id|name |dob |tel |gpa|group --+--------------+----------+-------+---+---------- 3 |Chuan Qin |1989-02-05| |4.0|Group[101] 5 |Maria Ionescu |1991-04-23| |3.9|Group[102] 6 |Oliver Blakey |1990-09-08| |3.1|Group[102] 7 |Jing Xia |1988-12-30| |3.2|Group[102] 4 |Rebecca Lawson|1990-04-18|123-456|3.3|Group[102] 1 |John Smith |1991-03-20|123-456|3.0|Group[101] 2 |Matthew Reed |1990-11-26| |3.5|Group[101]

So that's what this error message pony.orm.decompiling.DecompileError: Expression is too complex to decompile, try to pass query as string, e.g. select("x for x in Something") was trying to tell me. Though I only get this message instead of plain DecompileError if the ternary expression is inside of a tuple, and i couldn't figure out how to apply it to a lambda. Thank you, this works nicely.

So that's what this error message pony.orm.decompiling.DecompileError: Expression is too complex to decompile, try to pass query as string, e.g. select("x for x in Something") was trying to tell me. Though I only get this message instead of plain DecompileError if the ternary expression is inside of a tuple, and i couldn't figure out how to apply it to a lambda. Thank you, this works nicely.

I think I tried to .order_by(lmabda e: "e.a if e.a > const else e.b") instead of making the whole thing a string

Ok, maybe we can support this too to avoid confusion

I'd say a second example with a lambda in the error message would have helped me in this particular case.

I'm not sure why the error message is not present if it's just a ternary expression without extra tuples around it, that's probably an issue too.

Can you give an example, what do you mean by "extra tuples"

Can you give an example, what do you mean by "extra tuples"

now is a constant defined a few lines above .order_by(lambda e, c: e.start_time if e.start_time > now else e.end_time) - just a plain pony.orm.decompiling.DecompileError without message, like in your example .order_by(lambda e: (e.start_time > now, e.start_time if e.start_time > now else e.end_time)) 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") .order_by(lambda e: (e.start_time if e.start_time > now else e.end_time, e.id)) actually works without errors and generates correct sql? Weird.

That last scenario is new to me. Ternary can be used, but it has to be a first element of a tuple.

It is weird, I'll look into it #todo At this moment it is better not use ternary expressions except inside strings

Is there a way to enable debug mode globally as opposed to per thread? The threads are created by a library, I don't exactly have control over their target.

By now i'm pretty sure the answer is "no, at least not without monkey-patching" so I guess this is a feature request. I understand that making debug flags threadlocal is required to make db_session with different debug flags in different threads work as expected, but it would be nice to be able to set a default/global debug flag with threadlocal ones being an override.

Sure, it looks like a good feature request #todo. Can you make an issue for it?

Sure, it looks like a good feature request #todo. Can you make an issue for it?

Sure, I'll make one tomorrow.

Thanks!

hi, help me please, with saverd datetime type str

the end column is empty

Hi AngelCustodio, honestly it is a bit hard to understand, what exactly you are trying to ask... Hola AngelCustodio, para ser honesto, es un poco difícil entender qué es exactamente lo que estás tratando de preguntar.

ok, en el archivo plano, las 2 ultimas columnas almacenan fecha, pero existen registros que estan vacios, la pregunta es: ¿Como guardo eses campos vacios en un campo tipo fecha? mi base de datos es postgres

ok, in the flat file, the last 2 columns store date, but there are records that are empty, the question is: How do I save these empty fields in a date type field? my database is postgres

You need to replace empty string to None It is hard to say without looking at the code, but probably it should be something like this: import csv with open('test.csv') as csvfile: csvreader = csv.DictReader(csvfile, fieldnames=['field1', 'field2', 'field3']) for row in csvreader: if row['field3'] == '': row['field3'] = None # <-- this! obj = MyEntity(a=row['field1'], b=row['field2'], c=row['field3'])

PS D:\Proyectos\MINSA\indicadores> & "C:/Program Files/Python36/python.exe" d:/Proyectos/MINSA/indicadores/app.py Traceback (most recent call last): File "d:/Proyectos/MINSA/indicadores/app.py", line 7, in <module> pac.cargar('data/MaestroPaciente1027908.csv') File "d:\Proyectos\MINSA\indicadores\app\carga_maestro_diaria.py", line 28, in cargar row_data[19], row_data[20].strip()) File "<string>", line 2, in agregar_paciente File "C:\Program Files\Python36\lib\site-packages\pony\orm\core.py", line 528, in new_func result = func(*args, **kwargs) File "d:\Proyectos\MINSA\indicadores\app\orm.py", line 50, in agregar_paciente Maestro_Paciente(**data) File "C:\Program Files\Python36\lib\site-packages\pony\orm\core.py", line 4642, in init avdict[attr] = attr.validate(val, obj, entity, from_db=False) File "C:\Program Files\Python36\lib\site-packages\pony\orm\core.py", line 2181, in validate val = converter.validate(val, obj) File "C:\Program Files\Python36\lib\site-packages\pony\orm\dbapiprovider.py", line 672, in validate if isinstance(val, basestring): return str2date(val) File "C:\Program Files\Python36\lib\site-packages\pony\converting.py", line 153, in str2date else: raise ValueError('Unrecognized date format') ValueError: Unrecognized date format PS D:\Proyectos\MINSA\indicadores>

making these recommended changes I get this error

When you do: Maestro_Paciente(**data) Can you at first do: print(data) and check that all fields have correct format?

{'Id_Paciente': '7511065412', 'Id_Tipo_Documento_Paciente': '1', 'Numero_Documento_Paciente': '10347368', 'Apellido_Paterno_Paciente': '', 'Apellido_Materno_Paciente': '', 'Nombres_Paciente': '', 'Fecha_Nacimiento_Paciente': '1977-12-14', 'Genero': 'F', 'Id_Etnia': '58', 'Historia_Clinica': '', 'Ficha_Familiar': '', 'Ubigeo_Nacimiento': '', 'Ubigeo_Reniec': '250101', 'Domicilio_Reniec': '', 'Ubigeo_Declarado': '250101', 'Domicilio_Declarado': '', 'Referencia_Domicilio': '', 'Id_Pais': 'PER', 'Id_Establecimiento': '5412', 'Fecha_Alta': '2019-07-04 15:27:15', 'Fecha_Modificacion': None}

Probably fields with '250101' are dates? You need to parse these dates then: >>> import datetime >>> datetime.datetime.strptime('250101', '%d%m%y').date() datetime.date(2001, 1, 25) You need to choose correct format to specify where is the year, the day and the month in this string

error: DETAIL: Key (id_paciente)=(390767665457) is not present in table "maestro_paciente"., referer: http://54.39.151.44/cargarnominal

why?

no entender el error

why, heplme please

pony.orm.core.TransactionIntegrityError: Object Nominal_Trama_Nuevo[1] cannot be stored in the database. IntegrityError: insert or update on table "nomina$ [Wed Apr 08 08:57:28.872575 2020] [wsgi:error] [pid 29533:tid 140199152113408] [client 201.240.148.57:64521] DETAIL: Key (id_paciente)=(316111045392) is not present in table "maestro_paciente"., referer: http://54.39.151.44/cargarnominal

i`m new whit PonyORM

It seems you don't have paciente with ID=316111045392, you need to add it first and then add other objects which depends on it

si parece, estoy en busqueda de la solucion, en la otra tabla corre como si estaria guardando pero cuando voy a la tabla , nada todo sigue igual

TypeError: Unsupported operand types 'datetime' and 'datetime' for operation

I seem to remember there being a workaround for this, what is it?

I care about the number of seconds between now and a datetime field for a querry

now = datetime.datetime.now() models.User.select(lambda u: u.parent_user is None and (now - u.created).seconds // 3600 in (1, 6, 24, 72, 168))

That’s what I tried

It works for me: >>> from datetime import datetime >>> from pony.orm import * >>> db = Database(provider='postgres', user='pony', password='pony', host='localhost', database='pony') >>> db.generate_mapping(create_tables=True) >>> a = Foo() >>> b = Foo() >>> now = datetime.utcnow() >>> select((foo.id, now - foo.x) for foo in Foo).show() foo.id|now - foo.x ------+-------------- 1 |0:00:51.417936 2 |0:00:51.417936

Does adding “if (now - foo.created).seconds // 3600 in (1, 6, 24, 72, 168)” work for you?

>>> select((foo.id, (now - foo.x).seconds) for foo in Foo).show() AttributeError: 'timedelta' object has no attribute 'seconds': now - foo.x.seconds

Ok, so it seems we need to add attributes to timedelta #todo

I think it may be behaving differently when part of an if statement

The same error: >>> select(foo for foo in Foo if (now - foo.x).seconds > 100).show() AttributeError: 'timedelta' object has no attribute 'seconds': now - foo.x.seconds

I get this error:

TypeError: Unsupported operand types 'datetime' and 'datetime' for operation '-' in expression: now - u.created

My full query:

In [7]: models.User.select(lambda u: u.parent_user is None and (now - u.created).seconds // 3600 in (1, 6, 24, 72, 168))

Does adding “if (now - foo.created).seconds // 3600 in (1, 6, 24, 72, 168)” work for you?

Did you mean .total_seconds() or do you actually need seconds without days?

total seconds

1) Can you show more detailed traceback? 2) What version of Pony do you use?

In [6]: models.User.select(lambda u: u.parent_user is None and (now - u.created).seconds // 3600 in (1, 6, 24, 72, 168)) --------------------------------------------------------------------------- TypeError Traceback (most recent call last) <ipython-input-6-7099e47009d3> in <module> ----> 1 models.User.select(lambda u: u.parent_user is None and (now - u.created).seconds // 3600 in (1, 6, 24, 72, 168)) ~/app/.env/lib/python3.7/site-packages/pony/orm/core.py in select(entity, *args) ~/app/.env/lib/python3.7/site-packages/pony/utils/utils.py in cut_traceback(func, *args, **kwargs) 75 module_name = tb.tb_frame.f_globals.get('__name__') or '' 76 if module_name.startswith('pony.utils') and tb.tb_frame.f_code.co_name == 'throw': ---> 77 reraise(exc_type, exc, last_pony_tb) 78 reraise(exc_type, exc, full_tb) 79 finally: ~/app/.env/lib/python3.7/site-packages/pony/utils/utils.py in reraise(***failed resolving arguments***) 93 else: 94 def reraise(exc_type, exc, tb): ---> 95 try: raise exc.with_traceback(tb) 96 finally: del exc, tb 97 ~/app/.env/lib/python3.7/site-packages/pony/orm/sqltranslation.py in datetime_binop(monad, monad2) 1789 def datetime_binop(monad, monad2): 1790 if monad2.type != timedelta: throw(TypeError, -> 1791 _binop_errmsg % (type2str(monad.type), type2str(monad2.type), op)) 1792 expr_monad_cls = DateExprMonad if monad.type is date else DatetimeExprMonad 1793 delta = monad2.value if isinstance(monad2, TimedeltaConstMonad) else monad2.getsql()[0] ~/app/.env/lib/python3.7/site-packages/pony/utils/utils.py in throw(exc_type, *args, **kwargs) 106 raise exc 107 else: --> 108 raise exc # Set "pony.options.CUT_TRACEBACK = False" to see full traceback 109 finally: del exc 110

pony==0.7.9

Support of datetime - datetime was added in 0.7.11

Ah thank you

But we need to add .seconds for timedelta as well

I think you can use raw sql fragment:

Yes I now get AttributeError: 'timedelta' object has no attribute 'seconds': now - u.created.seconds

same as you

I will put that check inside my for loop, it will never be a huge number of users so no big deal

thanks for the help

Sure

total seconds

And do you actually want // instead of / ?

raw sql is: models.User.select(lambda u: u.parent_user is None and int(raw_sql("extract(epoch from ($now - u.created))")) // 3600

Yeah I want an integer number of hours

Are there naming requirements for lambda arguments? Test.select(lambda t: raw_sql('"t"."value"') > now)[:] generates SELECT DISTINCT "t-1"."id", "t-1"."value" FROM "test" "t-1" WHERE "t"."value" > %(p1)s which results in pony.orm.dbapiprovider.ProgrammingError: missing FROM-clause entry for table "t" LINE 3: WHERE "t"."value" > '2020-04-10T21:40:57.964185'::timestamp But it works if I use any other lowercase letter instead of t. Similar error occurs if I use arg name with upper case letters, but this time because pony generates full lower case table alias. I'm using postgresql with pony 0.7.13

Sadly, I don't have oracle instance to check if lower/upper case situation would be reversed with oracle

It also adds DISTINCT in both "t" and upper-case cases even if I use lower case name in raw_sql

It looks like a bug, I'll try to fix it #todo (1) If you do SomeEntity.select(lambda some_name: ...) then some_name used as an alias of the table (2) If you do SomeEntity.select().filter(...) then when the first select() is processed the lower-cased first letter of entity name is used as a table alias ("s" in this case) (3) It seems that if you do SomeEntity.select(lambda s: ...), that is, the name of lambda variable is equal to the first lowercased letter of entity, it should work just as in case (1), that is "s" should be table alias. But for some obscure reason Pony thinks there is a conflict between "default" name "s" (which is a first letter of entity name lowercased) and the name of lamda argument, and renames alias to generic name "t-1" I'll try to fix it In the mean time, as a workaround, you can use form Test.select().filter(lambda t: raw_sql('"t"."value"') > now)[:] The first .select() force table alias to be "t" in this case

Pony adds DISTINCT when SELECT does not contains primary key column to avoid duplicates. Like, if you do select(p for p in Person) Pony does not add DISTINCT, because the result contains p.id, and all rows are distinct from each other If you do select((p.id, p.name) for p in Person) Pony does not add DISTINCT as well, because primary key is present But if you do select(p.name for p in Person) Pony sees that name column is non-unique. Usually such queries are used to populate some dropdown list for filtering table content, and it makes a little sense to return name duplicates in it. So, Pony adds DISTINCT here Maybe we can reconsider this in the future

Lemme post some examples of that lower/upper case scenario, I'm pretty sure it's a bug because lambda arg name shouldn't affect DISTINCT

Ok, sure

Test.select(lambda name: raw_sql('"name"."value"') > now)[:] generates no DISTINCT SELECT "name"."id", "name"."value" FROM "test" "name" WHERE "name"."value" > %(p1)s Test.select(lambda naMe: raw_sql('"naMe"."value"') > now)[:] generates SELECT DISTINCT "name"."id", "name"."value" FROM "test" "name" WHERE "naMe"."value" > %(p1)s and results in pony.orm.dbapiprovider.ProgrammingError: missing FROM-clause entry for table "naMe" LINE 3: WHERE "naMe"."value" > '2020-04-10T21:40:57.964185'::timesta... Test.select(lambda naMe: raw_sql('"name"."value"') > now)[:] doesn't result in an error but still generates DISTINCT SELECT DISTINCT "name"."id", "name"."value" FROM "test" "name" WHERE "name"."value" > %(p1)s