Lucky
Okey, so the AUTOINCREMENT thing isn't working
Lucky
pony.orm.core.TransactionIntegrityError: Object RedditChannel[new:2] cannot be stored in the database. IntegrityError: duplicate key value violates unique constraint "channel_pkey"
DETAIL: Key (id)=(2) already exists.
Which is really strange as I have
class Channel(db.Entity):
id = PrimaryKey(int, auto=True, unsigned=True)
for my posgres database
Probably I need something like
ALTER SEQUENCE "channel_pkey" RESTART WITH (SELECT max("id") + 1 FROM "channel");
Lucky
⚠️ Error: "channel_pkey" is not a sequence
Lucky
What are the sequence names of a postgres pony sequece?
Alexander
Pony doesnt create specific sequence
Lucky
sorry, serial
Lucky
Can I somehow select with kwargs?
Lucky
channels = Channel.select(allow_safe=True, allow_nsfw=False)
Instead of the lengthy
channels = Channel.select(lambda c: (c.allow_safe is True) and (c.allow_nsfw False))
or
channels = orm.select(c for c in Channel if (c.allow_safe is True) and (c.allow_nsfw is False))
Alexander
You can filter with kwargs:
channels = Channel.select().filter(allow_safe=True, allow_nsfw=False)
Lucky
Ah, I always forget about the first, empty .select()
Lucky
shouldn't select simply support both args and kwargs?
Lucky
I feel like that would be a compatible change, to let Entity.select(*args, **kwargs) behave like a Entity.select(*args).filter(**kwargs)
Alexander
Yes, it was missed, we'll add kwargs support to select too
Lucky
Yay!
Lucky
You guys rock.
Alexander
Thanks :)
Lucky
Question about it, does that .filter() properly handle None and bool?
Lucky
In the past there was something about select(lambda x: x.foo == None) not resulting in the postgres query foo IS NULL but foo = NULL, and thus not working
Alexander
=None should be converter to IS NULL, if not - this is a bug
about =True I'm not sure right now
Lucky
^ found it, https://t.me/ponyorm/14125
Ah yes.
I confused not bool_foo vs. bool_foo is not True is what I confused it with
Lucky
*is not True
Lucky
I'm not quite awake today.
Lucky
=None should be converter to IS NULL, if not - this is a bug
about =True I'm not sure right now
Actually I think I'm getting a bug now:
@r2tBot | pony.orm.dbapiprovider.ProgrammingError: operator does not exist: smallint = boolean
@r2tBot | LINE 4: AND "c"."allow_nsfw" = false
@r2tBot | ^
@r2tBot | HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Lucky
Nevermind
Lucky
AAahh, I'm stupid
Lucky
It is supposed to be an int
Lucky
because there are no simple int enums yet
Lucky
Lucky
Becase The column can not be added because it's mandatory (not null) but unknown how to fill it for the existing rows.
Lucky
It is set to Optional, still doensn't allow NULL:
admin_json = Optional(Json, sql_default='NULL')
Anon
@metaprogrammer Hello Alexander, I've added basic SQL Server support in https://github.com/ponyorm/pony/pull/552, are you guys currently not processing new pull requests at this time? :)
Alexander
Lucky
How do I escape $ in my db.execute(…)?
Alexander
$$
Lucky
Alexander
yes
Lucky
Somehow
query = ChannelPost.select(
lambda chan_post:
# we want it to be a ChannelPost, is one is actually for the IDE.
isinstance(chan_post, ChannelPost) and
# get not only the reddit currently posting but all Reddit/Rss Channels ending up in the same chat.
chan_post.channel.telegram_id == channel.telegram_id and
# make sure we posted a image there, otherwise the hash will not exist.
isinstance(chan_post.post, ImagePost) and
# check the luma values.
threshold_ne_min <= chan_post.post.hash.luma_northeast and chan_post.post.hash.luma_northeast <= threshold_ne_max and
threshold_nw_min <= chan_post.post.hash.luma_northwest and chan_post.post.hash.luma_northwest <= threshold_nw_max and
threshold_se_min <= chan_post.post.hash.luma_southeast and chan_post.post.hash.luma_southeast <= threshold_se_max and
threshold_sw_min <= chan_post.post.hash.luma_southwest and chan_post.post.hash.luma_southwest <= threshold_sw_max
)
get's translated to
SELECT "chan_post"."id", "chan_post"."channel", "chan_post"."post", "chan_post"."message_id", "chan_post"."different_chat_id", "chan_post"."date_posted", "chan_post"."classtype", "chan_post"."reddit_channel", "chan_post"."submission_id", "chan_post"."rss_channel", "chan_post"."entry_id"
FROM "channelpost" "chan_post", "channel" "channel", "post" "post", "imagehash" "imagehash"
WHERE "chan_post"."classtype" IN ('RssChannelPost', 'RedditChannelPost', 'ChannelPost')
AND 1 = 1
AND "channel"."telegram_id" = -1001266992102
AND "chan_post"."classtype" IN ('ImagePost')
AND 15.097155570983887 <= "imagehash"."luma_northeast"
AND "imagehash"."luma_northeast" <= 15.597155570983887
AND 21.189044952392578 <= "imagehash"."luma_northwest"
AND "imagehash"."luma_northwest" <= 15.597155570983887
AND 19.937698364257812 <= "imagehash"."luma_southeast"
AND "imagehash"."luma_southeast" <= 15.597155570983887
AND 23.463228225708008 <= "imagehash"."luma_southwest"
AND "imagehash"."luma_southwest" <= 15.597155570983887
AND ("chan_post"."channel" = "channel"."id")
AND ("chan_post"."post" = "post"."id")
AND "post"."middle_frame_hash" = "imagehash"."id"
Lucky
somehow that ends up as
"chan_post"."classtype" IN ('RssChannelPost', 'RedditChannelPost', 'ChannelPost')
AND
"chan_post"."classtype" IN ('ImagePost')
thus never returning a result.
obviously x in (A, B, C) and x in (D) can't return anything result
Lucky
Looks to me, for some reason
isinstance(chan_post, ChannelPost)
and
isinstance(chan_post.post, ImagePost)
are actually both applied to
"chan_post"."classtype"?
Lucky
I believe instead it should do:
"post"."classtype" IN ('ImagePost')
Lucky
Shouldn't it?
Lucky
Alexander
Looks like a bug, I’ll try to look into it at the evening, in the meantime as a workaround you can test chan_post.post.classtype attribute directly
Lucky
If it helps, I can send the full model code.
https://editor.ponyorm.com/user/luckydonald/r2tg/settings
Lucky
Looks like a bug, I’ll try to look into it at the evening, in the meantime as a workaround you can test chan_post.post.classtype attribute directly
Now I don't get any code completion. Can I use typing.cast in there to help the IDE, or would that confuse the parser?
Alexander
Currently parser does not support typing module.
For simple conversions you can use int(...), str(...) and similar
Lucky
Yeah. When I'm done with my finals I'd love to look into it if it maybe would be possible to replace the models with a modern python type hint version:
class Table(db.Entity):
id = Required(int, size=64)
name = Optional(str, max_length=56)
Would probably be much nicer if it could be written as
class Table(orm.Entity):
id: int = Field(size=64)
name: Optional[str] = Field(max_length=56)
Lucky
Ever since I've been working with FastAPI this bothers me :D
Lucky
I think it will be possible as long as there's something similar to a
db.add_field(table="Table", name="id", type=int, required=True, **kwargs)
function which can be called to add a field to a database.
Lucky
I mean at some point it needs to do that anyway,
building an internal representation of the tables I mean.
Lucky
I have a table with 750.000 objects which need some updating of adding a new field before that becomes required in the next update.
It's pulling out one file_id which it needs to process to get the new field.
So how do I best do this?
cursor = db.execute('SELECT "id", "file_id" FROM "sticker"', dict())
update = {}
for row in cursor:
id = row[0]
file_id = row[1]
parsed_file_id = FileId.from_file_id(file_id)
file_id_unique = FileUniqueId.from_file_id(parsed_file_id)
db.execute('UPDATE "sticker" SET "file_id_unique" = $file_id_unique WHERE "id" = $id', dict(file_id_unique=file_id_unique, id=))
# end for
Lucky
Like is having an UPDATE in a for loop where I still have a cursor over the data is something I can do?
Santosh
In filter stamennt I want to check if the key exists in JSON type, and I am using get method on JSON data type object I get error JSON object had no attribute get
Alexander
you can use in
Alexander
key in obj.json_field
or
key in obj.json_field[x][y]
Lucky
TypeError: order_by() method receive an argument of invalid type: <function ElasticPony.yield_table_json.<locals>.<lambda> at 0x7f92c94e2730>
from random import random
order = (lambda: random(),)
query.order_by(*order)
Lucky
Alexander
I think you need to use one of PostgreSQL window functions, probably FIRST_VALUE
https://www.postgresqltutorial.com/postgresql-window-function/
Alexander
This is in example how to use window functions in UPDATE statement
https://stackoverflow.com/questions/4358613/using-window-functions-in-an-update-statement
Matthew
try pulling the data into something like dicts in python using a few big queries, then in python work out the changes you want to make
Lucky
That could actually be a better idea.
Lucky
Lucky
OOOoooh, I have to put first_value(file_unique_id)
Lucky
it needs a colum to work with
Santosh
There could be chances that JSON field is null
Alexander
Try to add a check:
obj.json_field is not None and key in obj.json_field
Alexander
Can you show the generated SQL of your query?
Santosh
Is there a way I can just filter matching keys of two json object
Santosh
Santosh
Santosh
Both sql and code
Alexander
What database do you use?
Santosh
MySQL