Alexander
```
patern = '%x%'
query = query.filter(lambda obj: raw_sql("""
mytablealias.mycolumn COLLATE UTF8_GENERAL_CI LIKE $pattern
"""))`
Alexander
or just:
substring = 'x'.lower()
query = query.filter(lambda obj: substring in obj.name.lower())
Santosh
or just:
substring = 'x'.lower()
query = query.filter(lambda obj: substring in obj.name.lower())
Rite now I have this solution
But was curious to know if I can use IN the we we use in select
Alexander
there is no difference between select and filter here
Lucky
How can I efficiently make an insert but ignore if it is already in the database?
Lucky
Like the postgres's ON CONFLICT DO NOTHING for INSERTs.
Alexander
Pony does not support it yet, you can issue raw sql using db.execute
Anon
Hello :)
I've got two tables like this:
class Student(db.Entity):
name = Required(unicode, column="fio")
group = Optional("Group")
class Group(db.Entity):
name = Required(unicode, column="fio")
students = Set("Student")
When I do the selection:
select((s.name, s.group.name) for s in Student)
I get the results of all students that have a group.
How do I also include students that have no group in the select?
Vitaliy
Use left_join instead select
Anon
left_join((s.name, s.group.name) for s in Student)[:]
Like this?
I think I'm missing something, it gives me the same result as the normal select.
Anon
Aah I see,
I was trying to do a left_join on another query:
students = select(s for s in Student)
then left_join((s.name, s.group.name) for s in students)
when I changed to iterate on the base class I got the correct result
Matthew
Is there a way in Pony to express that one of these two Optional columns is required?
Alexander
Not in model description.
You can add before_insert & before_update hooks with this check.
Also in the database you can manually add a check constraint
Matthew
Thank you
Genesis Shards Community
Genesis Shards Community
la relacion esta bien y en otras base de datos si me corre, solo en esta no
Genesis Shards Community
Genesis Shards Community
help
Genesis Shards Community
please
Genesis Shards Community
Alexander
For some reason object Agencias['001'] cannot be found in the database. It looks like Usuarios instance refers to primary key of Agencias which does not exist in Agencias table. Can you check the database content?
Genesis Shards Community
Genesis Shards Community
Alexander
Check Agensias table
Lucky
When using UUID as primary key I can set auto=True.
Can I use an integer as primeray key instead but still have the UUID set automatically?
I wanna create API Keys but referencing those with UUID fields all over the database seems like a waste of storage, as I have to do UUID in every other table too.
Alexander
You can define secondary key as:
uid = Required(uuid.uuid, default=uuid.uuid4, unique=True)
Matthew
I use both int and uuid keys on all tables of my database and it works well. Storage is cheap.
Alexander
Lucky
I only got a cheap SSD server with 500 GB total 😅
Volbil
I only got a cheap SSD server with 500 GB total 😅
What about bandwidth (just curious) ?
Matthew
it takes a lot of 16 byte UUIDs to matter when you have many gigabytes of storage
Matthew
UUIDs are super useful to have in URLs, so people can't tell how many of an object you have created, like with "/projects/643"
gokay
As far as I understood, sending an UPDATE query is not possible without using raw_sql right? Is this correct?
Alexander
You can do
obj.foo = 100
obj.bar = 200
and the object will be updated on flush() or commit()
If you mean
update(obj.set(foo=100, bar=200) for obj in MyEntity if obj.baz > 300)
it is not supported yet
gokay
yes i mean an operation that results in UPDATE queries to database
gokay
thank you
Vladimir
Hello!
I'm trying to set default values for JSON field...
==========================
class Channel(db.Entity):
geo = Optional(Json, default={"enabled":False,"codes":[],"type_list":True})
==========================
but getting following migration erro:
File "XXXXXXX-py3.7/src/pony/pony/orm/sqlbuilding.py", line 94, in unicode
assert False, repr(value) # pragma: no cover
AssertionError: {'enabled': False, 'codes': [], 'type_list': True}
What is wrong? How it can be fixed?
Alexander
Try to use function instead:
default=(lambda: {"enabled":False,"codes":[],"type_list":True})
#bug #tofix
Vladimir
the problem is gone, but new migration file w/o defaults:
operations = [
op.AddAttr('Channel', 'geo', orm.Optional(pony.orm.ormtypes.Json))]
Alexander
I can't look into it today, will check it in a few days
Vladimir
ok. thank you.
Santosh
In MySQL we can create table like CREATE TABLE test( id int(32))
Santosh
Similarly how do we define in pony
Santosh
Like how do I set 32 value in pony,
Alexander
id = PrimaryKey(int, size=32)
Santosh
In pony size is for define it's int or big int or something like this
But in MySQL int(32) or int(11) is just for display width of int type
Alexander
Ah, ok
Santosh
In simple words in pony how can I specify display width
Alexander
Pony does not support it natively, but you can use sql_type option to define the exact type in the database:
my_column = Required(int, sql_type="int(32)")
Santosh
Okay thanks
Santosh
Is there any option to use pony with already existing tables with data
Santosh
Something like can we generate entity relationship based on existing tables
Alexander
We don't have it at this moment. You can define models manually specifying existing table and column names:
class MyEntity(db.Entity):
_table_ = "MyExistingTable"
id = PrimaryKey(int)
foo = Required(int)
bar = Required(str, column="actual_column_name")
...
db.generate_mapping(check_tables=True) # don't include create_tables=True
Santosh
Ookay, but it would be really cool feature if we can get entity defined from ddl, I am not sure to wat extent it's possible, but this would be fantastic feature
Alexander
We have some unofficial code for PostgreSQL for simple cases. I hope later we can implement full solution as an official part of Pony
Santosh
We have some unofficial code for PostgreSQL for simple cases. I hope later we can implement full solution as an official part of Pony
Do you have something for MySQL as well
Santosh
Where can I get that
Alexander
We don't have a similar code for MySQL
Santosh
Where can I get for postgres
Santosh
Is it in your repo?
Alexander
No, wait a sec
Alexander
https://github.com/abetkin/pony-inspect
Permalink Bot
https://github.com/abetkin/pony-inspect
Permanent link to the abetkin/pony-inspect project you mentioned. (?)
Santosh
Ok
Ben
Hi all! Any idea how I can order_by with two different arguments using a lambda?
Alexander
order_by(lambda x: (x.a, desc(x.b + x.c)))
Ben
thaanks!
Alexander
Sure
Andrea
Hi, I wrote a fairly big query with Pony select, and everything works fine. The query potentially returns duplicate rows, thus Pony automatically inserts DISTINCT.
However, I noticed that when I try to order_by the query, the DISTINCT keyword is removed, and the query returns all the duplicates. Is this normal behaviour? If so, how can I explicitly tell the query to discard duplicates?
Alexander
Removal of DISTINCT when ORDER BY is present was added in latest releases of Pony. The reason for this is not all combinations of DISTINCT and ORDER BY are possible. In PostgreSQL queries like
select distinct a, b from t1 order by b, c
lead to error, because c does not appeared in SELECT list
The logic to understand will query result in error or not is non-trivial, as a, b and c may be not only a simple columns, but arbitrary expressions. So currently it is hard to determine, was arbitrary expression c mentioned in ORDER BY section, but not in SELECT section.
So, in previous releases Pony added DISTINCT to queries automatically, and in some queries it was the reason of the error. Right now we don't automatically add DISTINCT if a query has ORDER BY section. Maybe we will be able to add it back in the future.
Right now you can explicitly add .distinct() (or .without_distinct()) to the query
Andrea
Thanks Alex
Lucky
Having two entries of one Entity, how can I move values from the first's one Set() to the second one?
Something like this?
old = Foo.get(id=old_id)
new = Foo.get(id=new_id)
for elem in old.some_set:
new.some_set.append(elem)
# end for
old.some_set = []
Alexander
What is on the other side of relationship (elem.some_attr)? Set? Optional? Required?
Lucky
Ah, changing that other side is probably better. It's a one-to-many relation.
for message in duplicate.sticker_messages:
message.sticker = original
# end if
or for the previous example:
for other_side in old.some_set:
other_side.foo = old
# end for
Alexander
You can just do
new.some_set.add(old.some_set)
that should be enough
Alexander
and it is not necessary to clear old.some_set, as it will happen automatically (as we have to-one relationship on the other side)
Lucky
Wouldn't that duplicate the entries?
I wanna delete the other Foo anyway.
Lucky
That's actually unexpected.