How can I invert the NULL and DEFAULT parameters?

If I have a JSON column, can I do record_a.json_column == record_b.json_column in python and test if the values (dicts in this case) are equal, with the same rules as normal python dicts?

I have data which looks equal but isn’t evaluating as equal

I have data which looks equal but isn’t evaluating as equal

Nevermind, I had a logic mistake

Hi Elraro! Thank you for reporting! I fixed this error, and pushed the code to GitHub. The fix will be part of the next release, or you can take development version of code from GitHub. As a hack, with your current version of Pony you can define column as dt = Required(datetime, default=datetime.now, sql_type='TIMESTAMP DEFAULT CURRENT_TIMESTAMP') I specified TIMESTAMP DEFAULT as part of a type definition here. Note that for required column you need to specify Python default too

Hi Elraro! Thank you for reporting! I fixed this error, and pushed the code to GitHub. The fix will be part of the next release, or you can take development version of code from GitHub. As a hack, with your current version of Pony you can define column as dt = Required(datetime, default=datetime.now, sql_type='TIMESTAMP DEFAULT CURRENT_TIMESTAMP') I specified TIMESTAMP DEFAULT as part of a type definition here. Note that for required column you need to specify Python default too

Thank you! I will try it

Hi Elraro! Thank you for reporting! I fixed this error, and pushed the code to GitHub. The fix will be part of the next release, or you can take development version of code from GitHub. As a hack, with your current version of Pony you can define column as dt = Required(datetime, default=datetime.now, sql_type='TIMESTAMP DEFAULT CURRENT_TIMESTAMP') I specified TIMESTAMP DEFAULT as part of a type definition here. Note that for required column you need to specify Python default too

Hello again Alexander, i think i found another bug, but i cannot find it hehe

https://github.com/elraro/rajoyBot/blob/master/app/persistence/tools.py -> this query works on mysql/mariadb. i think GROUP BY sound.id its not valid for Oracle, so i remove it temporaly. Final query is something like that:

https://github.com/elraro/rajoyBot/blob/master/app/persistence/tools.py -> this query works on mysql/mariadb. i think GROUP BY sound.id its not valid for Oracle, so i remove it temporaly. Final query is something like that:

Permanent link to the file app/persistence/tools.py mentioned. (?)

SELECT sound.* FROM sound, resulthistory WHERE sound.disabled = 0 AND resulthistory.sound = sound.id AND resulthistory.user_c = 5410017 ORDER BY resulthistory.timestamp_c DESC;

table SOUND:

table RESULTHISTORY:

and the error: https://pastebin.com/WDBNgJAX

thank you for your time and support

Hi Elraro! I think you need to remove trailing semicolon from your query. Semicolon separates multiple command in cases where multiple commands may be specified (like cursor.executescript method available in sqlite3), but select_by_sql internally calls cursor.execute method which expects just a single command

> i think GROUP BY sound.id its not valid for Oracle, so i remove it temporaly Yes, in Oracle and PostgreSQL, if the GROUP BY clause is present, the SELECT clause cannot include non-aggregated column which were not listed in GROUP BY clause This is incorrect SELECT t1.id, t1.x, t1.y FROM ... WHERE ... GROUP BY t1.id You need to rewrite it as SELECT t1.id, t1.x, t1.y FROM t1 WHERE t1.id in (SELECT t1.id FROM ... WHERE ... GROUP BY t1.id) Or use DISTINCT instead of GROUP BY, if possible: SELECT DISTINCT t1.id, t1.x, t1.y FROM ... WHERE ...

Hi Elraro! I think you need to remove trailing semicolon from your query. Semicolon separates multiple command in cases where multiple commands may be specified (like cursor.executescript method available in sqlite3), but select_by_sql internally calls cursor.execute method which expects just a single command

hi again Alexander. Thank you so much for your time. I have removed semicolons and still with problems.

i tried to debug PonyORM, and i see the sql and arguments

SELECT sound.* FROM sound, resulthistory WHERE sound.disabled = 0 AND resulthistory.sound = sound.id AND resulthistory.userinfo = :p1 ORDER BY resulthistory.datetime DESC; {'p1': 5410017}

sql sentence is correct, i tried it

but, i dont know why, still error:

ORA-00933: SQL command not properly ended Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/pony/orm/dbapiprovider.py", line 50, in wrap_dbapi_exceptions return func(provider, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/pony/orm/dbproviders/oracle.py", line 479, in execute else: cursor.execute(sql, arguments) cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended During handling of the above exception, another exception occurred: Traceback (most recent call last): File "bot.py", line 304, in <module> raise e File "bot.py", line 295, in <module> bot.polling() File "/usr/local/lib/python3.7/site-packages/telebot/__init__.py", line 389, in polling self.__threaded_polling(none_stop, interval, timeout) File "/usr/local/lib/python3.7/site-packages/telebot/__init__.py", line 413, in __threaded_polling self.worker_pool.raise_exceptions() File "/usr/local/lib/python3.7/site-packages/telebot/util.py", line 108, in raise_exceptions six.reraise(self.exc_info[0], self.exc_info[1], self.exc_info[2]) File "/usr/local/lib/python3.7/site-packages/six.py", line 693, in reraise raise value File "/usr/local/lib/python3.7/site-packages/telebot/util.py", line 59, in run task(*args, **kwargs) File "bot.py", line 166, in query_empty recently_used_sounds = tools.get_latest_used_sounds_from_user(inline_query.from_user.id) File "<string>", line 2, in get_latest_used_sounds_from_user File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 528, in new_func result = func(*args, **kwargs) File "/app/persistence/tools.py", line 10, in get_latest_used_sounds_from_user results = Sound.select_by_sql('SELECT sound.* FROM sound, resulthistory WHERE sound.disabled = 0 AND resulthistory.sound = sound.id AND resulthistory.userinfo = $username ORDER BY resulthistory.datetime DESC;', globals={'username': userinfo.id})[:limit] File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 4000, in select_by_sql return entity._find_by_sql_(None, sql, globals, locals, frame_depth=cut_traceback_depth+1) File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 4145, in _find_by_sql_ cursor = database._exec_raw_sql(sql, globals, locals, frame_depth+1) File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 882, in _exec_raw_sql return database._exec_sql(adapted_sql, arguments, False, start_transaction) File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 948, in _exec_sql connection = cache.reconnect(e) File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 1782, in reconnect if not provider.should_reconnect(exc): reraise(*sys.exc_info()) File "/usr/local/lib/python3.7/site-packages/pony/utils/utils.py", line 95, in reraise try: raise exc.with_traceback(tb) File "/usr/local/lib/python3.7/site-packages/pony/orm/core.py", line 946, in _exec_sql try: new_id = provider.execute(cursor, sql, arguments, returning_id) File "<string>", line 2, in execute File "/usr/local/lib/python3.7/site-packages/pony/orm/dbapiprovider.py", line 69, in wrap_dbapi_exceptions except dbapi_module.DatabaseError as e: raise DatabaseError(e) pony.orm.dbapiprovider.DatabaseError: ORA-00933: SQL command not properly ended

Anyway, in traceback that you provide I still see that when you call results = Sound.select_by_sql('SELECT sound.* FROM sound, resulthistory WHERE sound.disabled = 0 AND resulthistory.sound = sound.id AND resulthistory.userinfo = $username ORDER BY resulthistory.datetime DESC;', globals={'username': userinfo.id})[:limit] You add semicolon at the end of SQL statement. Can you remove semicolon and check that you still receive the same traceback?

sure, give me a second

same traceback

ORA-00933: SQL command not properly ended

https://github.com/elraro/rajoyBot/blob/master/app/persistence/tools.py -> this query works on mysql/mariadb. i think GROUP BY sound.id its not valid for Oracle, so i remove it temporaly. Final query is something like that:

/)

/)

what? 😝

what? 😝

I just acted on your github profile picture.

Hi guyz. I'd like to work with 2 sqlite databases : One "in memory" to speed up the UI and another another one in a file to backup data (in the background) and populate the "in memory" db at start. How would you do it ?

Hi. I think you can connect to two file-based databases and set pragma synchronous = off for one of them. And then copy file at application exit

synchonous = off is as fast as 'in memory' ?

I think it should be comparable. This way you can "populate" work database easily

ok thanks

Another bug in the editor

@metaprogrammer would you accept a PR to add Path from pahlib module support for the filename argument of Database.bind()?

I prefer not to add non-standard dependencies to pony, but maybe it is possible to do without adding dependency

pathlib is in stdlib. 3.4, should be good

using it whith python2.7 and python 3.3 could be not allowed

ok

I think you can make PR and we discuss details

👍

hi, I have this issue uising pony with pyinstaller/ File "pony/orm/core.py", line 765, in bind File "pony/orm/core.py", line 784, in _bind File "importlib/__init__.py", line 127, in import_module if not os.path.exists(path): ModuleNotFoundError: No module named 'pony.orm.dbproviders'maybe it's related to https://github.com/ponyorm/pony/issues/227 but the fix proposed there doesn't work. Is there anything about pyinstaler and pony ? I don't have the issue running without pytinstaller

minimal example: python from pony.orm import Database db = Database() db.bind(provider="sqlite", filename=":memory:", create_db=True)and running pyinstaller essai.py -y && ./dist/essai/essai in a virtualenv with only pony and pyinstaller installed

adding `import pony.orm.dbproviders.sqlite ` to my scipt seems to fix it.

U can use hooks for pyinstaller

Hi. I've been reviewing ponyjs - wondering if there's anything like it written for use in front end since 2015...

Hi, we had no resources to write fronntend code for PonyORM, but after we finally release migrations I'll be glad if we can return to it

@metaprogrammer I'd like to help with that. I'm not sure it should have a dependency on knockout as that complicates integrations with virtual dom libraries.

I totally agree with that. Great, I can connect with you a bit later after we release migrations

Sounds goods Alexander. I'm out of the loop in terms of "migrations" what are they ?

Automatic generation of ALTER TABLE ... commands based on changes in entity declarations

And maintaining the history of such changes applied to specific databases

Ah right - like in django - if you lose em you're **cked

for the js client I was thinking something along the lines of the zodb/zeo client - run stats on the most used objects, keep objects that reference the row/object, lazy load, invalidate when changed by a separate client - I've been thinking gun.js/db might make an excellent medium for these capabilities (https://gun.eco/)

anyhow hit me up when you have time! Thanks.

Ok, sure

hi good morning, why problema?

pony.orm.core.UnrepeatableReadError: Value of Cajati.caja_sal for Cajati[217] was updated outside of current transaction (was: Decimal('11898.599999999999'), now: Decimal('11898.60'))

helpme!!

please!

C:\Program Files\Python36\lib\site-packages\pymysql\cursors.py:170: Warning: (1265, "Data truncated for column 'caja_sal' at row 1") result = self._query(query)

Hi! What database do you use?

MYSQL

It seems MySQL has some problem with decimals. There is similar issue for SQLAlchemy: https://stackoverflow.com/questions/45401259/sqlalchemy-decimal-truncation I'll try to fix it

It seems that the error appears when someone specifies string value instead of decimal in INSERT command. But Pony doesn't do this, so it is unclear to me why this error is happened. I'll ivestigate it, hopefully I can fix it tomorrow.

AngelCustodio this usually happens if one uses float data type and then it being converted to Decimal

Internally Pony should keep value of Decimal attribute as Decimal value, so I don't think this is a bug in user code. It is probalby something between Pony and pymysql Or maybe you inserted rows using db.insert(...), and in that case you should pass correct decimal value and not a string or float one

pony.orm.core.UnrepeatableReadError: Value of Cajati.caja_sal for Cajati[217] was updated outside of current transaction (was: Decimal('11898.599999999999'), now: Decimal('11898.60'))

If you can reproduce the problem, please turn on SQL logging using sql_debug(True) and show previous INSERT/UPDATE command for this object

pony.orm.core.UnrepeatableReadError: Value of Cajati.caja_sal for Cajati[217] was updated outside of current transaction (was: Decimal('11898.599999999999'), now: Decimal('11898.60'))

What is interesting about that error, is that Decimal('11898.599999999999') is *previous* value, and Decimal('11898.60') is *new* value, not the other way around

AngelCustodio this usually happens if one uses float data type and then it being converted to Decimal

yes, I just do that

Hmm

Can you show, how caja_sal attribute is defined?

Hmm

Internally Pony should keep value of Decimal attribute as Decimal value, so I don't think this is a bug in user code. It is probalby something between Pony and pymysql Or maybe you inserted rows using db.insert(...), and in that case you should pass correct decimal value and not a string or float one

it is incorrect to assign float values to decimal attributes or use floats as intermediate type for decimal calculations. Decimal are precise, but floats are imprecise

At least, you should explicitly convert resulted value to Decimal with specific precision

But it seems that Pony should check precision of decimal values assigned to attributes too, so I need to fix it anyway