Alexander
Yes. We had enough resources before to focus only on Pony without any other work.
Ben
Hi guys! Quick easy question: is there a way to set the maximum size of a JSON column?
Alexander
Hi! What database do you use?
Ben
Postgres. I couldnt find anything about that in the doc.
Alexander
I think setting max size for JSON is not possible in PostgreSQL
Alexander
You can serialize it manually before updating and check the resulted size, but it will take additional CPU cycles
Ben
Thanks! I ended up doing that, it's not great but not terrible either haha
Sigmund
Alexander
In Pony query, the set of elements in x in <set_of_elements> clause should be taken from some database table.
You are trying to mix items from the database table
list(admin.id for admin in x.administrators)
and some other items
+ [x.creator.id]
Pony does not understand how to convert it to SQL
Try the following instead:
group = Group.select(lambda x:
message.from_user.id in (admin.id for admin in x.administrators)
or message.from_user.id == x.creator.id
)
Alexander
By the way, it may be simplified a bit:
group = Group.select(lambda x:
message.from_user in (admin for admin in x.administrators)
or message.from_user == x.creator
)
or even:
group = Group.select(lambda x:
message.from_user in x.administrators
or message.from_user == x.creator
)
Alexander
How the message.from_user attribute is defined?
Alexander
If the type of message.from_user is not the same as the type of x.administrators element, then the comparison of id values should not be correct as well, as these ids are from different tables
Alexander
Ah, ok, I just incorrectly assumed that message is some Pony object
Alexander
What is groups here?
Alexander
If it is literally a list of Group objects, then you can't do groups.admin_in_groups, as a list instance does not have admin_in_groups attribute, same for groups.owning_groups
Alexander
It is strange that the user instance is called "groups".
But anyway, you can do it as single SQL select statement this way:
groups = select(
g for g in Group
for u in User
if u.id == message.from_user
and g in u.admin_in_groups or g in u.owning_groups
)
But I don't think it will be faster than the current solution
Alexander
Probably better to leave it as is
Alexander
Sorry, I need to go offline, bye
Alexander
For adding a column you can manually perform SQL command "alter table sometable add columnname columntype"
If you need to perform it on multiple database instance, it is better to use some tool like FiywayDB
https://flywaydb.org/
Alexander
In this case you probably can do it manually
Alexander
In Pony you can execute a command db.execute(sql) with your SQL command.
To change schema automatically Pony need to support database migrations, and it isn't here yet officially.
You can try branch migrations_dev with experimental support of migrations, but it is not officially supported yet.
https://github.com/ponyorm/pony/tree/migrations_dev
Permalink Bot
In Pony you can execute a command db.execute(sql) with your SQL command.
To change schema automatically Pony need to support database migrations, and it isn't here yet officially.
You can try branch migrations_dev with experimental support of migrations, but it is not officially supported yet.
https://github.com/ponyorm/pony/tree/migrations_dev
Permanent link to the ponyorm/pony project you mentioned. (?)
Santosh
I use MySQL how to interpret
Below SQL in Pony
DATE(end_date) = DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY)
Volbil
I use MySQL how to interpret
Below SQL in Pony
DATE(end_date) = DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY)
Check how python's timedelta works
Volbil
from datetime import datetime, timedelta
datetime.utcnow() + timedelta(days=3)
Santosh
select(c for c in Contract if c.end_date + timedelta(days=1) == datetime.utcnow())
Volbil
I think it would be better to use >= instead of ==
Santosh
But my problem is SQL debug will show as
WHERE ADDDATE(`c`.`end_date`, INTERVAL '72:0:0' HOUR_SECOND) = %s
Santosh
HOUR_SECOND not days
Santosh
SO i want SQL debug to show something like
WHERE ADDDATE(`c`.`end_date`, INTERVAL '3' DAY)
Volbil
Well, not sure if it's possible
Volbil
Volbil
24 hours * 3 is equal to 72
Volbil
72 hours is same thing as 3 days
Santosh
Yes its correct, but query should be interms of days
Volbil
I don't see why using hours for interval is an issue
Volbil
Thats how Pony interprets your query and I don't think there is a way to change that unless you modify Pony's interpreter itself
Santosh
Is it something like, Pony interprets hours since i use datetime.utcnow(), which has hours and seconds object,
Or i would like to know if this is possible
Volbil
SO i want SQL debug to show something like
WHERE ADDDATE(`c`.`end_date`, INTERVAL '3' DAY)
May I ask why you want to have this exact query?
Santosh
I am creating PoC, to have migrate existing one from to use Pony, so ideally i want the queris to be same, as old raw queris
Santosh
Also result from these two are different
Santosh
contracts = orm.select(c for c in Contract if c.end_date + timedelta(days=1) >= datetime.utcnow())[:]
Santosh
this would return 4 data
Santosh
select contract_id, end_date from odm_contract where DATE(end_date) = DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY);
This would give two
Santosh
@metaprogrammer please provide your insights for this issue
Alexander
Sorry, I'm without computer now, can answer tomorrow
Christian
If you have the queries already, couldn't you just use raw_sql()?
https://docs.ponyorm.org/api_reference.html#raw_sql
Volbil
Hello @metaprogrammer I have question regarding queries. I have model which looks like this:
class Transaction(db.Entity):
amount = orm.Required(Decimal, precision=20, scale=8)
addresses = orm.Set("Address")
I'm trying to make a query which would allow selecting transactions involving different addresses. For example we have address a and address b, query should return all involving those addresses.
Volbil
Only solution I came up with is making multiple queries which is less than optimal. Also I couldn't figure out a way to join results of those queries together in order to sort them and etc.
Volbil
Ok, nevermind
Volbil
I found solution, instead of querying transactions I did it like this:
orm.select((a.transactions) for a in Address if a.address in args["addresses"])
Volbil
I found solution, instead of querying transactions I did it like this:
orm.select((a.transactions) for a in Address if a.address in args["addresses"])
Unfortunately this solution doesn't work well with order_by and filter :(
Volbil
I found solution, instead of querying transactions I did it like this:
orm.select((a.transactions) for a in Address if a.address in args["addresses"])
Quick update on my issue, after checking Pony docs now my query looks like this and it works blazingly fast :)
Thanks again for such amazing library!
transactions = orm.left_join(
transaction
for transaction in Transaction for address in transaction.addresses
if address in addresses
)
Ben
Hi all! Does anybody have experience using Pony with Gevent (Greenlets)? And if so is there anything in particular to look for?
Volbil
Hi all! Does anybody have experience using Pony with Gevent (Greenlets)? And if so is there anything in particular to look for?
Well, I used Pony inside Flask app which is run using gevent, works as intended
Ben
ah nice
Ben
you're using the psycopg-green plugin too?
Ben
Thanks!
Alexander
I think Log should be specified before db.Entity in the inheritance list
Ben
Hi! Quick question that I cant find an answer to; how can I use "before_update" to trigger when a new object is added to a many to many relationships
Ben
Like I have Users and Tags
Ben
a tag can be applied to many users, and I want to trigger a hook when a tag is added to a user
Ben
It is a Set() on both sides
Alexander
Hi! Right now, many-to-many relationships are not covered by hooks.
You can add a custom method like User.add_tag in which you add a tag and do additional actions.
Ben
Ohh I see
Ben
ok thanks a lot for the quick reply! That's a good way to solve my issue :)
Karsten
Hello, everyone, have a problem with a query:
r.code contains entries with a code as a string.
I use int(r.code) to limit it to 10 ... 98. I want to cancel the query if there is no decimal value in r.code.
r.code.isnumeric() always returns False. Does anyone have a solution?
result = (select(r for r in BlzEvent if r.event_time >= start_time and
r.event_time <= end_time and
r.train == train and
# r.code.isnumeric() is True and
r.code != '7a' and
r.code != '7b' and
int(r.code) > 9 and
int(r.code) < 99 and
r.action == '').order_by(BlzEvent.event_time)).fetch()
At the moment I solve the problem by sorting out the two relevant entries with r.code! = '7a' and r.code! = '7b'. But that's not a nice solution !
Thanks for your ideas
Karsten
Is r.code hex maybe?
No, r.code is of the data type string. The conversion into an integer works. However, there is an exception for '7a' and '7b'. That's why I wanted to exclude with r.code.isnumeric ().
М
Oh, you should try isdigit instead
М
Isnumeric return true if at least one character is number
Karsten
Oh, you should try isdigit instead
Thanks, but false should be returned for '7a' and '7b'
М
Isnumeric return true if at least one character is number
Sorry, that is incorrect. Well, than i dunno.
Kaustubh
hey guys, i am using ponyorm with a flask app and now wondering how to manage the migrations. i was able to find this https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate but didn't understand how pony would infer which models need to be migrated for
Permalink Bot
hey guys, i am using ponyorm with a flask app and now wondering how to manage the migrations. i was able to find this https://github.com/ponyorm/pony/tree/orm-migrations/pony/migrate but didn't understand how pony would infer which models need to be migrated for
Permanent link to the file pony/migrate mentioned. (?)
Alexander
Hello
Alexander
First of all you need migrations_dev branch
Alexander
About "which models needs to be migrated".
Pony migrations has mechanism of comparing current models state and previous, which is reconstructed by applying previous migrations virtually.