Note that in SQL NULL have very counterintuitive behavior for some queries

Oh, in what cases is it inconsistent?

I'll try to fix it today later

Hi, fixed?

Oh, in what cases is it inconsistent?

I don't remember all the details :) The main problem is, in SQL, the NULL meaning is not "the value is empty", but "the value is unknown". Because of this, if you do some operation on arguments, some of which are NULL, the total result of the operation will be NULL as well. It makes sense if we really treat NULL as "unknown": if some operation arguments are unknown, then the operation result is usually unknown as well. But as in practice we usually use NULL to denote "the value is missing" and not "the value is unknown", the results are often counter-intuitive. For example, assume last_name attribute is optional and can contain NULL values. Then, for the following query: select(p for p in Person if p.last_name == 'Smith') You can assume that the query with a negative condition will return all other rows in the table: select(p for p in Person if p.last_name != 'Smith') This second query will return persons whose last names are the empty string. But it will not return persons whose last names are NULL, as, according to SQL logic, we cannot be sure that the person with NULL last_name isn't actually 'Smith' (NULL means it is unknown to us, is the last name actually 'Smith' or not). But, if you expect to return persons with empty last name in the second query, it will not be obvious to you that some of them are missing, as you got persons who have '' in their last name. So this error will be hard to notice and to debug. And the same is true for queries with more complex expressions, like select(p for p in Person if not p.last_name.upper().starts_with('S')) You will receive persons with empty last names, but not with NULL last names.

Hi, fixed?

Not yet, thanks for reminding

all = select(avg(s.cpu_load_percent) for s in Statistics) last_hundred = select(s for s in Statistics).order_by(lambda s: desc(s.create_at)).limit(100) avg_last_hundred = select(avg(s.cpu_load_percent) for s in last_hundred)

It is not easy to properly fix it, but for now you can use a workaround: in the inner query replace select with left_join: all = select(avg(s.cpu_load_percent) for s in Statistics) last_hundred = left_join(s for s in Statistics).order_by(lambda s: desc(s.create_at)).limit(100) avg_last_hundred = select(avg(s.cpu_load_percent) for s in last_hundred)

Thanks

Note that other ORMs do the same: https://docs.djangoproject.com/en/3.1/ref/models/fields/#django.db.models.Field.null

The difference here seems to be that empty values (string with zero lenght) isn't allowed either until blank=true if I read django correctly. That way the gotcha is communicated much better, because you have to use either or.

The recommended default is the same: don't specify null=True for optional string attributes: https://simpleisbetterthancomplex.com/tips/2016/07/25/django-tip-8-blank-or-null.html

What was the solution?

Hello Donald, The solution was pretty simple, for some reason I had some debugging/async jazz going on that prevented the break points from triggering, after I got them to work the solution was simple to implement. I just override the Value class that converts the python object to the SQL string and add a check for datetime: class MSSQLValue(Value): slots = [] def unicode(self): value = self.value if isinstance(value, timedelta): if value.microseconds: return "INTERVAL '%s' HOUR_MICROSECOND" % timedelta2str(value) return "INTERVAL '%s' HOUR_SECOND" % timedelta2str(value) if isinstance(value, datetime): result = value.isoformat(' ') return self.quote_str(result) return Value.unicode(self) if not PY2: str = unicode I've got a pull request open here https://github.com/ponyorm/pony/pull/552 in case you'd like to check it out :)

Hi

I would like some help

I'm doing this for filtering logs from a json sent by user

This clearly is unsafe

Table

Anyone could suggest a safe way to do this?

I've tried this but sadly it doesn't works for json fields

@metaprogrammer Can you help me ?

I changed to this

I guess it will be safe ?

Do you have any specific reason to write raw sql?

The recommended default is the same: don't specify null=True for optional string attributes: https://simpleisbetterthancomplex.com/tips/2016/07/25/django-tip-8-blank-or-null.html

I mean that is basically always about "having two possible values for “no data”, that is: None and an empty string". But I don't even want to allow an empty string. For this very example, are no telegram usernames which are simply "@" with no string after.

I guess it will be safe ?

Only if you whitelist the keys. Otherwise one could do an SQL injection on the f"...{k}..." part. For both actually.

Only if you whitelist the keys. Otherwise one could do an SQL injection on the f"...{k}..." part. For both actually.

For example have this perfectly valid JSON: {"foo' = 'whatever'; DROP TABLE Log": 123}

I'm not too sure about pony syntax, I might be wrong, but I believe it could be something like: query = orm.select(log for log in Log) for k, v in filter.items(): query = query.filter(lambda log: log.data[key] == value) # end for for log in logs: # do stuff with log # end for

Check out the documentation about JSON Data Type Support

I'm not too sure about pony syntax, I might be wrong, but I believe it could be something like: query = orm.select(log for log in Log) for k, v in filter.items(): query = query.filter(lambda log: log.data[key] == value) # end for for log in logs: # do stuff with log # end for

@Onkcol this should work

Hi, I see that json type is stored in sql as TEXT. it means adding an optional json column looks like ?: db.execute("ALTER TABLE Annotation ADD myjsoncolumn TEXT") db.commit()

Do you use SQLite?

Hi, I'm back

Do you have any specific reason to write raw sql?

No... I'm using it to be able to filter in one query

I'm not too sure about pony syntax, I might be wrong, but I believe it could be something like: query = orm.select(log for log in Log) for k, v in filter.items(): query = query.filter(lambda log: log.data[key] == value) # end for for log in logs: # do stuff with log # end for

Will this query the db for all data and then filter it in python? This is a logging dB, it grows few millions row daily...

No, query2 = query.filter(...) adds condition to query2 SQL

Amazing

Thanks @luckydonald and @metaprogrammer

combined by and

✌️

Do you use SQLite?

yes

You can add column as you wrote and it should work in SQLite, but Pony itself use the type JSON. Actually, type names mean little in SQLite, except that some types auto-convert strings to numbers when possible

but the doc says : For storing JSON in the database Pony uses the following types: SQLite - TEXT

Need to make it more clear in the documentation. When a column named JSON, SQLite will threat is the same way as TEXT, and store just a text string inside it. So you can use type name TEXT or JSON, it does not matter in SQLite. Pony name it JSON for convenience, but actual data format is the text string

👍

So like SQLite - JSON (which is the same as TEXT)

yes

Hi, how can I update tables without dropping all the tables? I have to modify a field from int to something longer, how can I do that? I really can't drop the tables because it's in production, hope you can help me out (i'm using postgres)

Ideally, you should use a migration tool like Flyway to specify the migration: ALTER TABLE mytable ALTER COLUMN mycolumn TYPE BIGINT; Or you can execute this command manually. This change is backward-compatible, so the old code of your model should work fine after this change. Then, after you applied this change to all databases you can update the int attribute in your model and specify size=64. Note that if you have a really big table execution of this command can take some time during which the table will be locked

Ideally, you should use a migration tool like Flyway to specify the migration: ALTER TABLE mytable ALTER COLUMN mycolumn TYPE BIGINT; Or you can execute this command manually. This change is backward-compatible, so the old code of your model should work fine after this change. Then, after you applied this change to all databases you can update the int attribute in your model and specify size=64. Note that if you have a really big table execution of this command can take some time during which the table will be locked

Thank you, I'll try now and I'll have a look at Flyway soon, fortunately the table is still relatively small because the website launched today but that are already a lot of posts so that's why I can't drop everything

by the way it seems that pony doesn't modify PRAGMA user_version; on it's own, can you confirm it alexander ? (I'd like to use it to store the schema version)

Pony does not use this pragma, you can store your data with it

ok thank you

Ideally, you should use a migration tool like Flyway to specify the migration: ALTER TABLE mytable ALTER COLUMN mycolumn TYPE BIGINT; Or you can execute this command manually. This change is backward-compatible, so the old code of your model should work fine after this change. Then, after you applied this change to all databases you can update the int attribute in your model and specify size=64. Note that if you have a really big table execution of this command can take some time during which the table will be locked

I still get integer out of range, it's a phone number (without international code), should I just store it as a string? If yes, can I do that the same way but instead of BIGINT use STRING?

yes, phone numbers should definitely be stored as string values. You don't do arithmetic operations on phone numbers

Ok thank you

> can I do that the same way but instead of BIGINT use STRING not STRING, but TEXT

> can I do that the same way but instead of BIGINT use STRING not STRING, but TEXT

Thank you, I checked that also on the awesome pony online editor haha, I love it

Thank you for always helping me Alexander

Hi, Dad.

Is there any performance benefit if you set max_length for columns to powers of two? For example 8/16/32/64? Postgress / Mysql / sqlite

I do not quite understand the structure of data storage in a database, that's interesting.

No, it does not provide any performance benefit. Roughly, a row stored in a single database page, and after the row header the fields are stored one after the other, with small headers for variable-length fields Aligning the length to the power of two may make sense for column-oriented datastores, and typical SQL databases are all row-oriented

Thanks

What you talking about?

What you talking about?

I think it is a bot which generates random phrases that pretend to be human

Ah, I see

hi, is there a way to use a sqlite Database() with io.BytesIO() ?

or to get ":memory:" into "BytesIo" ?

I'm not sure about that

I have a code doing migration/backup/test_migration . it uses several Database() sometime pointing on the same file. It's great on linux but fails on windows because I can't remove a file if disconnect wasn't explicitely called on Database (PermissionError). Removing the container doesn't work : old_schema.db.disconnect() old_path.unlink() => works del old_schema old_path.unlink() => failsSo I need to call db.disconnect() after each db_session Is there some "autodisconnect" somewhere ?

Pony does not have "autodisconnect", you probably can make decorator or context manager for this. But may be it is a good idea for any db_session have autodisconnect "on" by default in some next Pony version

Hello. Tell me please. I want to create the following tables in the database: 1. Table Users class User(db.Entity): telegram_id = Required(int, size=64, unique=True) username = Optional(str, unique=True) addresses = Set('Address') 2. table with addresses (Ethereum addresses) class Address(db.Entity): name = Optional(str) <----------------------- How I need to change this? operator = Required(str, unique=True) users = Set('User', reverse='addresses') events = Set('Events', reverse='e_addresses') In this case, each user can be subscribed to several addresses, but at the same time, each Ethereum address can be tracked by several users. It's Many-To-Many, no problem here. But the problem occurs at the moment when I want to make sure that each user can add their own custom name to the address. I do not know how to create such a table correctly. I will be glad of your help.

Hello. Tell me please. I want to create the following tables in the database: 1. Table Users class User(db.Entity): telegram_id = Required(int, size=64, unique=True) username = Optional(str, unique=True) addresses = Set('Address') 2. table with addresses (Ethereum addresses) class Address(db.Entity): name = Optional(str) <----------------------- How I need to change this? operator = Required(str, unique=True) users = Set('User', reverse='addresses') events = Set('Events', reverse='e_addresses') In this case, each user can be subscribed to several addresses, but at the same time, each Ethereum address can be tracked by several users. It's Many-To-Many, no problem here. But the problem occurs at the moment when I want to make sure that each user can add their own custom name to the address. I do not know how to create such a table correctly. I will be glad of your help.

You probably need address book table

Which will hold user, address and name fields

Which will hold user, address and name fields

You are helping me again, mr. savior =))

;)

class Addressbook(db.Entity): user = Required(User) address = Required(Address) name = Required(str)

Like that

Hello