Matthew
I want the “y” with the largest id (primary key) as a subquery
Matthew
What am I forgetting?
Matthew
select((x, max(y.id for y in Y if y.x == x)) for x in X)
Matthew
That works, but gets me the ID, not the full object
Alexander
select((x, y) for x in X for y in x.y_set if y.id == max(y.id for y in x.y_set))
Matthew
great, thank you
Lucky
How can I make a reference to a field not being the primary key?
Matthew
Can you explain more what you mean?
Alexander
If you mean adding foreign keys constraints to non-primary keys, Pony does not support it, but you can add such foreign key constraint manually. The entity attribute will not have an object type, it will be just id, and you need to fetch related object manually
If you mean just using it in query, it looks like:
select(x for x in X for y in Y if x.y_id == y.secondary_id)
Lucky
Ah, bummer.
Evgeniy
Hello everyone! Can I inherit the model class not from db.Entity (as specified in the documentation), but directly from EntityMeta? Do I have any problems with this?
Alexander
Hi Evgeniy! Why do you want to do this?
Evgeniy
I am writing my own small library based on your ORM and it seems inconvenient to instantiate Database() just to inherit the database entity from it. I wanted to make a connection to the database through my class.
Alexander
Usually Database instance is a convenient way to define multple entities that are related to the same physical database. Also it allows to specify options to connect to the database.
EntityMeta is not a class, but a metaclass. You can't directly inherit from it, so, I think, it will not work. Probably you mean Entity class in pony.orm module.
When db instance is created, it creates yet another Entity class (inherited from the base pony.orm.Entity class) which becomes db.Entity and has additional field _database_. This field is used in many important methods.
If you make a subclass of pony.orm.Entity and specify _database_ in it it probably may work, but is very brittle and not officially supported.
Evgeniy
Thank you very much for your response! Please tell me how to better organize the code? I don't like the idea of combining declarative code (for example, an entity model in a database) and executable code (creating an instance of the Database class) in a single file. This also makes it more difficult to pass parameters to the bind() method. Do you have any code examples where this is implemented well and beautifully?
Alexander
Do you plan to define all entity models in a single file?
Evgeniy
Yes, there is only one model.
Lucky
This is about specifying the database at a later time, yes?
Alexander
I consider database variable as a way to organize entities and group them together, so from my point of view it is a part of declarative code, especially if you perform db.bind later
So usually it looks like
# models.py - declarative code
db = Database()
class MyEntity(db.Entity):
...
# main.py - executable code
from models import db, MyEntity
db.bind(**settings.db_params)
db.generate_mapping(create_tables=True)
with db_session:
select(x for x in MyEntity)
You can also create entity model inside a function
# models.py
def define_models(db):
class MyEntity(db.Entity):
...
# main.py - executable code
from models import define_models
db = Database()
define_models(db)
db.bind(**settings.db_params)
db.generate_mapping(create_tables=True)
with db_session:
select(x for x in db.MyEntity)
The drawback is IDEs don't autocomplete entity attributes for entities defined inside a function
Note that you can access entity class as db attribute db.MyEntity and it is not necessary to return it directly from the function
Evgeniy
This is about specifying the database at a later time, yes?
Yes, that's what I wanted to do. I used to do this in Django.
Lucky
Yes, that's what I wanted to do. I used to do this in Django.
That would be a feature I request for years :/
Lucky
Sadly the core of pony isn't written in a way so one could simply add it.
Lucky
The crulpit is EntityMeta, from what I've seen.
Lucky
if there'd be an kind of api which you'd can use to register a table, or register a variable to a table, I think we could do a Blueprint like approach.
Lucky
Like basic idea is if you have a class doing stuff you collect what it would do in a list, and just run that at a later time
Lucky
So take flask for example, it does something like this for @route decorators
class Blueprint(objec):
later: List[Callable]
def route(self, *args, **kwargs):
self.later.append(lambda app: app.route(*args, **kwargs)
def register_to_app(self, app):
for later in self.later:
later(app)
Evgeniy
Like basic idea is if you have a class doing stuff you collect what it would do in a list, and just run that at a later time
This does not look like a simple and beautiful solution to my problem...
Evgeniy
But thank
Lucky
Anyway, I think ponyorm would benefit in a few places if entity definition could be an api.
Evgeniy
Another small question about the internal implementation of pony. I'm writing a small library for logging, and I don't want to slow down the main application by waiting for a database entry. To do this, I thought to use a separate thread for writing, which would accept tasks and write them to the database. But then I thought: isn't there a similar mechanism inside pony already? Does it make sense to go to a separate stream for writing? Logging can be quite stressful.
Alexander
Yes, it totally make sense. You can communicate with logging thread using a queue, and store each portion of log records in a separate db_session. You probably need some additional logic to group log records together to reduce the number of transaction: you can do something like
while True:
records_to_save = []
record = queue.get()
records_to_save.append(record)
try:
while True:
record = queue.get_nowait()
records_to_save.append(record)
except Empty:
pass
with db_session:
save_log_records(records_to_save)
Evgeniy
Can I work with the same Database() object from different threads?
Alexander
yes
Alexander
each thread has separate database connection
Evgeniy
Thank!
Lucky
I'm trying to insert into the database with random token generated by posgres:
https://www.dbrnd.com/2016/04/postgresql-how-to-generate-a-random-token-string/
But using
network = Network(
identifier = orm.raw_sql(f"array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1,{token_length})), '')"),
title=text
)
fails with
TypeError: Value type for attribute Network.identifier must be str. Got: <class 'pony.orm.ormtypes.RawSQL'>
Alexander
Pony doesn't support raw_sql outside of select queries and raw SQL queries.
As a workaround you can do
db.execute("""
insert into network (identifier, title) values (
array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1, $token_length)), ''),
$text
)
""")
Lucky
Okey, thanks!
Lucky
<Lazy QueryResult object at 0x7f503bd9f650> .get() is not supported, isn't it?
Lucky
I'm getting a error:
pony.orm.core.UnexpectedError: Object JoiningUser[new:3] cannot be stored in the database. DataError: integer out of range
Lucky
class JoiningUser(db.Entity):
join_link = Required('JoinLink')
user = Required(User)
chat_to_join = Optional(ChatPartOfNetwork)
referrer_chat_id = Optional(int, size=64)
referrer_external = Optional(str, nullable=True, sql_default='NULL', default=None)
joined_at = Optional(datetime, sql_default='NULL')
invite_link_message_id = Optional(int)
Lucky
Which is strange as it is size=64 already.
Lucky
It is happening when calling orm.flush()
Lucky
The database was dropped before, so pony created the tables freshly.
Lucky
Full model is https://editor.ponyorm.com/user/luckydonald/channel_network/snapshots/121/designer
Lucky
Interestingly the workaround is adding id = PrimaryKey(int, auto=True)
Lucky
I believe that's a bug though.
Alexander
If I understand correctly, the problem was with the value of referrer_chat_id or invite_link_message_id. Maybe the value was too big indeed?
Lucky
I don't think so. Seems to be the missing id field.
Alexander
CREATE TABLE "joininguser" (
"id" SERIAL PRIMARY KEY,
...
It's serial, PostgreSQL should provide it automatically
Lucky
because nvite_link_message_id is null, and referrer_chat_id = already is size=64
Alexander
It is signed 64, maybe the value was unsigned 64 and was out of the range?
Lucky
After I changed
class JoiningUser(db.Entity):
join_link = Required('JoinLink')
to
class JoiningUser(db.Entity):-
id = PrimaryKey(int, auto=True)
join_link = Required('JoinLink')
it started to work.
Alexander
With the same values?
Lucky
at least referrer_chat_id was the same
Lucky
and invite_link_message_id was null in both cases.
Lucky
Or actually unset.
Anonymous
Can i ask
Anonymous
class notification(db.Entity):
chat_id = PrimaryKey(int, auto=False, size=64)
user_id = Optional(int, default='', auto=False, size=64)
full_name = Optional(str, default='')
Anonymous
Anonymous
About optional in user_id
Anonymous
Idk what does auto= do here
Lucky
Autoincrement. If you don't add a id it will do 1, 2, 3, ...
Anonymous
user_id = Optional(int, default='', auto=False, size=64)
Anonymous
Any wrong?
Anonymous
Or its fine?
Lucky
Looks good to me
Lucky
probably default='' is wrong
Lucky
either do default=1234
Lucky
or leave it out, to have it default to null/None.
Lucky
With the same values?
Here is how that looks like:
chat_network_1 | DEBUG 2020-07-05 12:26:04 chat_network.main.cmd_start:
chat_network_1 | JoiningUser(
chat_network_1 | user=User[10717954], chat_to_join=ChatPartOfNetwork[UUID('dd46c1d1-9adc-47cb-9d0d-e99e257c2ff6')], join_link=JoinLink[1],
chat_network_1 | referrer_chat_id=-1001032895287, referrer_external=None,
chat_network_1 | )
Anonymous
or leave it out, to have it default to null/None.
user_id = Optional(int, default=None, auto=False, size=64)
Anonymous
What is auto there?
Lucky
What is auto there?
Autoincrement. If you don't add a id it will do 1, 2, 3, ... automatically
Anonymous
Autoincrement. If you don't add a id it will do 1, 2, 3, ... automatically
But it only works in primarykey?
Lucky
yes.
Anonymous
Or also in optional?