Alexander
Hi @vitalium! Thank you for reporting the bug, I should be able to dedicate time to it tomorrow
For a quick fix you probably can use a query with a raw SQL fragment and explicitly use MySQL JSON expression in this fragment
value = ...
select(u for u in User if raw_sql('a native MySQL expression with u.emails column and $value parameter'))
Vitaliy
Thank you very much! 👍
Alexander
Thank you for reminding this, I really hope to be able to fix it tomorrow. It would be great if we could discuss the details tomorrow at some point in the day.
Vitaliy
Yes, sure, you PM me any time
Alexander
Great!
Daniel
Please I need an advice,I'm starting a project in fastapi, do you advice to go with ponyorm because I really love it
SlavaMGTU
Hi, guys! I have been looking into options of implementing ORM structure for storing logins and passwords. Could you please advise how to find it, based on the PonyORM?
Alexander
Please I need an advice,I'm starting a project in fastapi, do you advice to go with ponyorm because I really love it
Beware that you have to do migrations by yourself and read about pony with async
Alexander
Hi, guys! I have been looking into options of implementing ORM structure for storing logins and passwords. Could you please advise how to find it, based on the PonyORM?
Isnt having login and password fields sufficient?
Daniel
Beware that you have to do migrations by yourself and read about pony with async
Can you recommend any tools for that migration with ponyorm
Alexander
I used flyway as a migration management tool
Alexander
But for generating migrations there are nothing specific for pony
Daniel
Thanks
Daniel
Is it a package that I can use
Alexander
That is an application
Ben
Hello! I'm having issue after switching my database from local to network
Alexander
Hi! What issue do you have?
Ben
Sorry I meant to send another message and it didnt. Basically it seems that the remote connection overhead is making things quite slow. I understood that pony had builting connection pooling which should make things better but i was wondering if there were ways to fine tune it (e.g. number of connections, lifetime, etc.) or share it between flask workers. And if you think there would be performance benefits of using external connection pooling like pgpool
Matthew
Is it slow for each individual query or when you do many queries in a loop?
Alexander
Currently pony uses one connection per thread, and holds it until db.disconnect() is called in this thread
Ben
It's slow for each individual query
Ben
Currently pony uses one connection per thread, and holds it until db.disconnect() is called in this thread
Ok I see, for each db_session a new connection is created?
Alexander
No, sequential db_sessions reuse the same connection
Alexander
Maybe the query itself is slow, and you need to add some index?
Ben
No the query is fine, because when I run it with the applicaiton on the same server as postgres then performance is good
Ben
but performance degrades around 30% when running on a separate server in the same local network
Ben
I think it's because there are many small queries, and the added latency to the server might add-up
Matthew
You can probably refactor it to do less queries
Jivko
Hi,
Please help with this case:
My PromaryKEY is:
"en:1:1"
"en:1:2"
"en:1:3"
"en:1:4"
"en:1:5"
"en:1:6"
"en:1:7"
"en:2:10"
"en:2:11"
"en:2:12"
"en:2:13"
"en:2:14"
"en:2:8"
"en:2:9"
If I use:
data = Data.select(lambda a: a.pointer <= 'en:2:12')
I receive only:
en:1:1
en:1:2
en:1:3
en:1:4
en:1:5
en:1:6
en:1:7
en:2:10
en:2:11
en:2:12
In the result I lost en:2:8 and en:2:9
How to have all from en:1:1 to en:2:12?
Alexander
That is true, strings are sorted alphabetically
Matthew
I would add an additional column that stores an integer based off of your primary key so that you can do comparison queries that behave how you want
Alexander
This is not related to pony or something. In almost all programming languages and databases strings are sorted alphabetically.
Ben
Hi! After a small change to my code, I'm suddenly getting a lot of "pony.orm.core.PartialCommitException" in my tests and I'm not sure what I am doing wrong. I couldn't find any documentation about this error, so I'm unclear about what could be causing it.
Ben
Any idea where the "assert local.db_session is db_session" error can come from? The traceback is less than useful, I'm guessing it's related to connection to my database failing but I'm not sure.
Alexander
Is you code async?
Ben
Yes there's GEVENT, but we're not using it for postgres
Ben
Is that the reason?
Alexander
It looks like your code suddenly switches between greenlets in the middle of db_session
Ben
Ohh thanks that's very helpful
Ben
Any idea on if it can be fixed? Or shall I just disabled GEVENT?
Alexander
You need to do the following at the start of your application:
from gevent import monkey
monkey.patch_all()
https://www.gevent.org/api/gevent.monkey.html
Alexander
Hi! After a small change to my code, I'm suddenly getting a lot of "pony.orm.core.PartialCommitException" in my tests and I'm not sure what I am doing wrong. I couldn't find any documentation about this error, so I'm unclear about what could be causing it.
PartialCommitException means that in a single db_session, you worked with entities from two different databases. In this scenario, Pony tries to imitate a distributed commit to two databases at once. At the end of db_session (or on explicit commit), Pony was able to commit successfully to the first database but failed to do it to the second database, and this resulted in ParticalCommitException, which contains the reason for the unsuccessful commit to the second database.
If you have to commit two databases separately, you can explicitly do db1.commit() and db2.commit() inside the db_session
Ben
I did the monkey.patch_all() but I did not do it for the psycogreen.gevent because I thought it didnt work with pony
Alexander
I think it should work
Alexander
At least, with it is should work better than without it
Alexander
But at the end of each greenlet thread that uses Pony, you should call db.disconnect() to avoid a huge number of unclosed connections. With normal threads, usually, the thread is reused for multiple db_sessions, and with greenlets, it is most probably not the case.
Also, suppose you have multiple greenlets that work with the database and run in parallel. In that case, each uses a separate db connection, so it may not be a good idea to have thousands of greenlets working with the database in parallel.
Ben
Thanks a lot, that's very useful!
Christian
Hi, I'm using timestamps on my models like this:
class SomeEntry(db.Entity):
created = Required(datetime, default=datetime.utcnow)
This creates the correct UTC time, e.g. 2023-05-07 15:14:01 but without a timezone. I want it like this: 2023-05-07 15:14:01+00:00
Christian
To create timezone-aware dates, I should use datetime.now(timezone.utc) but how do I pass that function without executing it? Other solutions?
Amo
AFAIK does pony not support timezone aware timestamps in the database.
My work around is to set the timezone after the read for each datetime field. And store only utc in the dv
Amo
Db
Matthew
what about default=lambda: datetime.now(timezone.utc)
Alexander
Yes, Pony does not support storing timezone-aware timestamps. You can convert it to timezone-aware datetime after retrieving from the database
Юрий
Guys, hello
I'm trying to run a parser that uses PonyOrm, but I'm getting the error "NotImplementedError: Array type is not supported for 'MySQL'". The documentation states that StrArray types (https://docs.ponyorm.org/array.html) are added in versions of 0.7.7 and only for PostgreSQL, CockroachDB and SQLite connections. I use MySQL base. How can this limitation be bypassed? The developer claims that everything works for him. I already searched how to fix it.
Alexander
Do you use MySQL or MariaDB?
Alexander
Currently Pony supports arrays in Postgres and SQLite, and for MySQL it is possible to use JSON type to store arrays. But Pony support of JSON datatype in MariaDB is limited, as MariaDB syntax for JSON queries is currently not supported
Alexander
Is the parser code publicly available?
Юрий
no, but i can send a code
Юрий
Problem Code:
class Filters(db.Entity):
zone_parsing = orm.Required(orm.StrArray)
parse_second_level_domains = orm.Required(bool)
ignore = orm.Required(orm.StrArray)
with orm.db_session:
if Filters.select().count() == 0:
Filters(zone_parsing=[], parse_second_level_domains=False, ignore=[])
Юрий
Alexander
I can guess the parser uses Postgres or SQLite, and not MySQL
Юрий
I can guess the parser uses Postgres or SQLite, and not MySQL
in code: db.bind(provider='mysql', host=DB_HOST, user=USER, passwd=PASSWORD, db=DB_NAME)
Alexander
It is strange
Alexander
In a relatively fresh post (2021) I read:
"Although an array is one of the most common data types in the world of programming, MySQL actually doesn’t support saving an array type directly. You can’t create a table column of array type in MySQL. The easiest way store array type data in MySQL is to use the JSON data type."
https://sebhastian.com/mysql-array/
So, I don't know how the parser expects to work in MySQL with this definition
Alexander
https://stackoverflow.com/questions/5541175/is-there-any-array-data-type-in-mysql-like-in-postgresql
Evgeniy
Hello! I have a really ancient project with pony ORM, and there is this line in the project: items = select(x for x in Post if x.visible and not count(y for y in x.category if y.only_for_agreeing) and not count(y for y in x.category if y.only_for_registered)).order_by(desc(Post.id)). Today I tried to run this project and got an error: arg = [code.co_names[oparg]] IndexError: tuple index out of range. As I think, this is a result of noncompatibility of an old version of the ORM and the new one. How to fix it?
Volbil
Hello! I have a really ancient project with pony ORM, and there is this line in the project: items = select(x for x in Post if x.visible and not count(y for y in x.category if y.only_for_agreeing) and not count(y for y in x.category if y.only_for_registered)).order_by(desc(Post.id)). Today I tried to run this project and got an error: arg = [code.co_names[oparg]] IndexError: tuple index out of range. As I think, this is a result of noncompatibility of an old version of the ORM and the new one. How to fix it?
You can instal older version of Pony suitable for your project and/or you may need to check if your Python version is supported by Pony
Volbil
As far as I'm aware Python 3.11 is not yet supported
SlavaMGTU
Alexander
instead of sum(a, b, c), try to use sum([a, b, c])
SlavaMGTU
Hi!
Please tell me I don't understand why they write me an error here?
Code:
class Label(db.Entity):
name = PrimaryKey(str)
path = Required(str)
parametrs = Required(str)
parametrs_ex = Required(str)
description = Required(str)
with db_session:
for line in data:
Label(name = line['name'], path = line['html'], parametrs = ' ', parametrs_ex = ' ', description=' ') # ValueError: Attribute Label.parametrs is required !!!!!
commit()
ValueError: Attribute Label.parametrs is required
SlavaMGTU
I need a empty string in this Attributes, but Python write error.
Alexander
Because Required for str means it can't be empty.
If you want to be able to store empty strings, you should declare it as Optional(str), and if you want to be able to store None, you should declare it as Optional(str, nullable=True)
https://docs.ponyorm.org/api_reference.html#optional-string-attributes
SlavaMGTU
ok. But I can't write here this string " ". Why?