Mapping customization

When Pony creates tables from entity definitions, it uses the name of entity as the table name and attribute names as the column names, but you can override this behavior.

The name of the table is not always equal to the name of an entity: in MySQL, PostgreSQL and CockroachDB the default table name generated from the entity name will be converted to the lower case, in Oracle - to the upper case. You can always find the name of the entity table by reading the _table_ attribute of an entity class.

If you need to set your own table name use the _table_ class attribute:

class Person(db.Entity):
    _table_ = "person_table"
    name = Required(str)

Also you can set schema name:

class Person(db.Entity):
    _table_ = ("my_schema", "person_table")
    name = Required(str)

If you need to set your own column name, use the option column:

class Person(db.Entity):
    _table_ = "person_table"
    name = Required(str, column="person_name")

Also you can specify the _table_options_ for the table. It can be used when you need to set options like ENGINE or TABLESPACE. See Entity options part of the API reference for more detail.

For composite attributes use the option columns with the list of the column names specified:

class Course(db.Entity):
    name = Required(str)
    semester = Required(int)
    lectures = Set("Lecture")
    PrimaryKey(name, semester)
class Lecture(db.Entity):
    date = Required(datetime)
    course = Required(Course, columns=["name_of_course", "semester"])

In this example we override the column names for the composite attribute Lecture.course. By default Pony will generate the following column names: "course_name" and "course_semester". Pony combines the entity name and the attribute name in order to make the column names easy to understand to the developer.

If you need to set the column names for the intermediate table for many-to-many relationship, you should specify the option column or columns for the Set attributes. Let’s consider the following example:

class Student(db.Entity):
    name = Required(str)
    courses = Set("Course")
class Course(db.Entity):
    name = Required(str)
    semester = Required(int)
    students = Set(Student)
    PrimaryKey(name, semester)

By default, for storing many-to-many relationships between Student and Course, Pony will create an intermediate table "Course_Student" (it constructs the name of the intermediate table from the entity names in the alphabetical order). This table will have three columns: "course_name", "course_semester" and "student" - two columns for the Course’s composite primary key and one column for the Student. Now let’s say we want to name the intermediate table as "Study_Plans" which have the following columns: "course", "semester" and "student_id". This is how we can achieve this:

class Student(db.Entity):
    name = Required(str)
    courses = Set("Course", table="Study_Plans", columns=["course", "semester"]))
class Course(db.Entity):
    name = Required(str)
    semester = Required(int)
    students = Set(Student, column="student_id")
    PrimaryKey(name, semester)

You can find more examples of mapping customization in an example which comes with Pony ORM package