Grigory
Alexander
I think you can define a property:
@property
def child_id(self):
return self.child.id
Alexander
Note that getting id of related entity does not imply SQL query to load full related entity, if foreign key value of related entity id is already loaded
Grigory
And in what cases it will be loaded? Would it be loaded (cached) if it is a binary blob or a string?
Alexander
For example, you have the following entities:
class Message(db.Entity):
id = PrimaryKey(int)
text = Required(str)
owner = Required("User")
class User(db.Entity):
id = PrimaryKey(int)
name = Required(str)
age = Required(int)
messages = Set("Message")
then the underlying Message table will have owner column which is a foreign key to User table and contains id value from the User table
If you load a Message object
msg = Message.get(id=123456)
the owner column will be loaded as well. For example, it contains the value 123. Pony knows that a User object with id 123 exists in the User table. At this moment (during the loading of Message object) Pony creates User object in memory with this id value. Other attributes of this object are still unknown. We name such objects "seeds", objects for which only primary key is known.
When you access attribute owner of msg object, you will receive this seed object User[123]. If you access its id, you will get its value without a new query. However, you you write msg.owner.name Pony will issue an SQL query which load all non-lazy attributes of this object (name and age)
Alexander
That is, you if logically already know object primary key because it loaded as a foreign key of another column, Pony will not issue another query for getting this pk, it is unnecessary
Grigory
so, by default, whatever format the primary key of the related entry is, it will be cached, right?
Alexander
yes
Grigory
Thanks! Great design!
Alexander
So, when multiple Message objects are linked to the same User object, they will be all linked to the same instance of User, and if you do:
for m in messages:
print(m.owner.name)
The user will be loaded using the single query
Marat
Hi.
I try to pass raw sql update command to db.execute(raw_sql) and get following error:
pony.orm.dbapiprovider.ProgrammingError: Error: ST_GeomFromGeoJSON function does not exist.
Query:
UPDATE schema.table
SET geom = ST_GeomFromGeoJson(geo_json)
WHERE id = 123;
DB:
PostgreSQL
This query works fine via pgAdmin.
Did anyone have the same problem?
UPD:
ST_GeomFromGeoJson is PostGIS function.
Alexander
Are you sure you connect to the same database on the same server?
Alexander
Maybe with Pony you connect to older PostgreSQL version?
Alexander
or you need to install PostGIS
Alexander
https://postgis.net/install/
Marat
Are you sure you connect to the same database on the same server?
Yes, I use this Database object for other operations and it works fine
Alexander
But do you connect to the same database via PgAdmin?
Alexander
maybe you need to execute
CREATE EXTENSION postgis;
first
Marat
I guess yes, because I see changes made from the code
Marat
Actually I tried run this query through psycopg2 with the same database configs and it works fine too
Alexander
The error is generated by PostgreSQL itself, from the error text it seems it doesn't related to Pony
Alexander
I still suspect you need to check connect parameters used with Pony
Anzor
Anonymous
Hey guys!
Anonymous
Just a quick question - just starting out with PonyOrm
select(lambda: o for o in Order if o.name in ['a','b','c'])
Anonymous
ponyorm and I, we don't understand each other :D
Alexander
You can write
select(o for o in Order if o.name in ['a','b','c'])
or
Order.select(lambda o: o.name in ['a','b','c'])
Anonymous
Thank you very much! I'll try it out
Anonymous
💪
Anonymous
It works, made some error myself. Much ❤️ for ponyorm so far
Genesis Shards Community
como se actualiza con PonyORM
Genesis Shards Community
?
Matthew
https://docs.ponyorm.com/working_with_entity_instances.html#updating-an-object
Genesis Shards Community
Thanks!
Genesis Shards Community
alguien de ustedes se conecto a Postgres de Google Cloud?
Lucky
Wenn ich jetzt Deutsch spreche versteht mich auch keiner.
Lucky
Could we all speak english please?
Genesis Shards Community
How do I connect pony orm to postgres on the Google Cloud platform?
Rezha
By using cloudsql proxy, and connect it to the unix sock
Matt
Hi guys. Does anybody know how to use mixins in an entity definition? I'm trying to do something like this:
class DateMixin():
created_at = Optional(datetime)
updated_at = Optional(datetime)
class AModel(DateMixin, db.Entity):
some_field = ...
class BModel(DateMixin, db.Entity):
another_field = ...
However when the mapping is generated, it appears to skip over the fields defined in the mixin.
Rozen
This approach (when some attributes are defined inside a mixin class, but this class doesn't have a specific table and doesn't exist as a separate entity) is called abstract base classes. At this moment Pony does not support abstract base classes, but I think we should add them, it is easier than adding multiple-tables type of inheritance.
Right now you need to duplicate attribute in all classes
Matt
Thanks. That's what I was afraid of. The mixin approach would be a lot cleaner. But fortunately I think I only have ~3 properties so it's not so bad to duplicate them.
Rozen
:)
Matt
Is that also true for methods?
Matt
For example could I put a shared before_update in a mixin or so?
stsouko
Matt
Thanks @nougmanoff and @Rozzen - much appreciated
.
hello. help me please. how get table meta in pony?
Alexander
Hi! What exactly you mean?
.
I want to get the column names in the table
.
all names columns
Alexander
After db.generate_mapping is executed, you can check db.schema object which contains table definitions. You can inspect columns for a specific table:
table_name = MyEntity._table_
table = db.schema.tables[table_name]
for column in table.column_list:
print(column.name, column.sql_type)
.
oh thanks ))
.
@metaprogrammer
I have another stupid question.
I make a request and get an error
the problem is in prefetch
i can use attrgetter in prefetch
select(c for c in self.table if getattr(c, self.column) in self.args).prefetch(
attrgetter('ID', 'NAME')(self.table))[:]
Alexander
In prefetch you need to specify entity attributes, not column names:
select(s for s in Student).prefetch(Student.detailed_bio)
Alexander
What is the problem you are trying to solve?
.
I write a universal class that makes a query, deletes, insert.
I do not know in advance which table will be passed to it and which fields in it. I wanted to add a prefetch to have access to data in a place where there is no @ db_session. Well, since I don't know what columns I will have, I wanted to use attrgetter
Alexander
Maybe it is better to generate different entity for each table than to use the same universal class with different tables
Alexander
I mean, you can construct entity class dynamically using metaclass
Alexander
In simple cases (if you don't use composite keys) you can do something like that:
from pony import orm
from pony.orm import core
def define_entity(db, entity_name, attrs):
return core.EntityMeta(entity_name, (db.Entity,), attrs)
db = orm.Database()
entity = define_entity(db, 'Person', dict(
name=orm.Required(str),
age=orm.Optional(int)
))
orm.sql_debug(True)
db.bind('sqlite', ':memory:')
db.generate_mapping(create_tables=True)
.
thank. I'm going to try now
Grigory
Hi!
Is it possible to expose the special ROWID SQLite column to Pony objects?
We use big BLOBs as primary keys, so we need an 'alternative primary key' to address stuff from other tables.
.
In simple cases (if you don't use composite keys) you can do something like that:
from pony import orm
from pony.orm import core
def define_entity(db, entity_name, attrs):
return core.EntityMeta(entity_name, (db.Entity,), attrs)
db = orm.Database()
entity = define_entity(db, 'Person', dict(
name=orm.Required(str),
age=orm.Optional(int)
))
orm.sql_debug(True)
db.bind('sqlite', ':memory:')
db.generate_mapping(create_tables=True)
Sorry, only now I could see the code. I do not create a table through a pony. I make a request to an already created oracle table
Alexander
> We use big BLOBs as primary keys, so we need an 'alternative primary key' to address stuff from other tables.
You can try something like:
class MyEntity(db.Entity):
id = PrimaryKey(int, column='ROWID')
blob = Required(buffer, unique=True)
The idea to use binary BLOBs for primary keys was suspicious to me from the beginning :)
Alexander
> I do not create a table through a pony. I make a request to an already created oracle table
You can define specific entities anyway, just don't specify create_tables=True in generate_mapping
.
when I make such a request, then it is successful
select(c for c in self.table if getattr(c, self.column) in self.args).prefetch(
attrgetter('ID')(self.table))[:]
but when i pass in attrgetter 'ID.NAME' get an error
select(c for c in self.table if getattr(c, self.column) in self.args).prefetch(
attrgetter('ID.NAME')(self.table))[:]
error:
AttributeError: 'Required' object has no attribute 'NAME'
.
> I do not create a table through a pony. I make a request to an already created oracle table
You can define specific entities anyway, just don't specify create_tables=True in generate_mapping
I created an object using your method. but still when I make a prefetch request and try to get data after the session is open, I get an error
the database session is over
the only thing that helps me is to pass it to
prefetch (attrgetter ('ID') (self.table))
but I cannot transfer a few 'ID.NAME' fields. Although it should work
Muhammed
I'm new at ponyorm. I want to create two tables associated with each other. I try with online editor for ponyorm.
For example (PostgreSQL):
CREATE TABLE WEBUSER (
WEBUSER_ID SERIAL PRIMARY KEY,
NAME VARCHAR(40)
);
CREATE TABLE MEMBER (
MEMBER_ID SERIAL PRIMARY KEY,
WEBUSER_ID INTEGER REFERENCES WEBUSER(WEBUSER_ID),
STATUS BOOLEAN
)
How create these table on online editor?
Alexander
You need to create entity WebUser with webuser_id and name attributes, entity Member with member_id and status, and then create one-to-one relation WebUser.member <-> Member.web_user with column webuser_id on Member.web_user side. If you need all-uppercase column names, you can maually set column names for all attributes, or just name all attributes uppercase
Muhammed
You need to create entity WebUser with webuser_id and name attributes, entity Member with member_id and status, and then create one-to-one relation WebUser.member <-> Member.web_user with column webuser_id on Member.web_user side. If you need all-uppercase column names, you can maually set column names for all attributes, or just name all attributes uppercase
Muhammed
Muhammed
You need to create entity WebUser with webuser_id and name attributes, entity Member with member_id and status, and then create one-to-one relation WebUser.member <-> Member.web_user with column webuser_id on Member.web_user side. If you need all-uppercase column names, you can maually set column names for all attributes, or just name all attributes uppercase
And it is not required that uppercase column names. Thanks
Matt
@metaprogrammer you are awesome for being so active and helpful in Telegram, really great to see such a community around a tool
Alexander
Thanks Matt
Alexander
This is not tables, but entities, similar to ER-diagrams wth Crow's Foot notation.
https://www.researchgate.net/profile/Marina_Siplivaya/publication/304413446/figure/fig2/AS:388326621237249@1469595689335/ER-diagram-of-subject-domain-Crows-Foot-notation.png
Alexander
Tables may be generated from entities. On ER-diagram you don't add webuser_id attribute to Member, because this attribute is assumed by reationship between these two entities, and webuser_id column will be generated from relationship. By default in Pony this column will be named just webuser, so to name it webuser_id you need to specify correct column name in relationship
Muhammed
OK, I think I understand. Thanks
Grigory
Hi! Is it possible to update a bunch of Pony object attributes at once? Something like:
s = Student['Petrov']
s(grade='C', group=1)
Alexander
s.set(grade='C', group=g1)
Grigory
ow!