[PonyORM-list] ponyorm - how to use

[Alexander Kozlovsky]

Hi,

Yes, it is possible to use the Set attribute without having a real foreign
key specified at the database level. But you still need to have a column at
the opposite side of the relation.

For example, you can have existing database with Groups and Students tables
as following:

create table Groups(
    id integer primary key,
    department integer not null
)

create table Students(
    id integer primary key,
    group_id integer not null,
    name varchar(100) not null,
    age integer not null
)

In this example, Students table has `group_id` column, but this column is
defined without foreign key constraint. You can define corresponding Pony
entities the following way:

db = Database()

class Group(db.Entity):
    _table_ = "Groups"
    id = PrimaryKey(int, auto=True)
    department = Required(int)
    students = Set("Student")

class Student(db.Entity):
    _table_ = "Students"
    group = Required(Group, column="group_id")
    name = Required(str)
    age = Required(int)

Here, I specified explicit table names using the `_table_` option, because
by default the table name assumed to be the same as the class name. Also I
specified column="group_id" option for the Student.group attribute, because
by default attribute name assumed to be equal to the column name.

Now you can write the following query using the association between
entities:

select(s for s in Student if s.age > 18 and s.group.department == 1)

When foreign key is defined at the database level, the database will not
allow incorrect values in the fk column. Without the foreign key it may be
possible to have incorrect values in the `group_id` column. You should
ensure that the `group_id` column does not contain incorrect values. When
Pony read value from the `group_id` column, it expects that the group
object with this id actually exists. If this turns out to be false, Pony
will throw exception "UnrepeatableReadError: Phantom object Group[123]
disappeared".

In order to have good performance, the `group_id` column should be indexed.
It will work without the index too, but some operations can be slow. If you
use MySQL innodb engine, then the index is created automatically if the
foreign key is defined. But in other cases the index should be created
explicitly. When Pony creates tables it creates all necessary indexes as
well, but if you use existing database then you should take care of it by
yourself.

Regards,
Alexander Kozlovsky


On Thu, Dec 25, 2014 at 4:58 AM, 王飞 <gf0842wf at gmail.com> wrote:

> hello
> I didn't use foreign keys, but i want to associated query.
> Because of many reasons, I cannot use foreign keys, so the 'Set' type can
> be specified in a virtual foreign keys for associated query instead of real
> foreign key?
>
> 2014-04-02 10:35 GMT+08:00 王飞 <gf0842wf at gmail.com>:
>
>> Thanks! I hope PonyOrm can develop better.
>>
>>
>> 2014-04-01 17:30 GMT+08:00 Alexey Malashkevich <
>> alexeymalashkevich at gmail.com>:
>>
>>> At this point Pony doesn't generate models from the database tables.
>>> However Pony can map entities to legacy databases.
>>> For this purpose you can use parameters "_table_", "column", "columns":
>>>
>>>     class MyEntity(db.Entity):
>>>         _table_ = "legacy_table"
>>>         attr1 = Required(unicode, column="db_column1")
>>>
>>> Check the "Mapping customization" part in the Pony documentation for an
>>> example
>>>  http://doc.ponyorm.com/entities.html#mapping-customization
>>> Let us know if you have any further questions.
>>>
>>> Regards,
>>> Alexey
>>>
>>>
>>> On Tue, Apr 1, 2014 at 8:01 AM, 王飞 <gf0842wf at gmail.com> wrote:
>>>
>>>>  Hi! How can I use pony to generate a model from a legacy database?
>>>> Look forward to your reply.Thanks!
>>>>
>>>> _______________________________________________
>>>> ponyorm-list mailing list
>>>> ponyorm-list at ponyorm.com
>>>> /ponyorm-list
>>>>
>>>>
>>>
>>> _______________________________________________
>>> ponyorm-list mailing list
>>> ponyorm-list at ponyorm.com
>>> /ponyorm-list
>>>
>>>
>>
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20141225/69c44810/attachment.html>