[SOLVED] PonyORM many-to-many relationship on defined schema in postgreSQL

PonyORM many-to-many relationship on defined schema in postgreSQL

I have question regarding ponyORM and many-to-many relationship. I'm using python3.7, ponyORM and PostgreSQL Db.

Setup: I have two schemas in my database, let's say schema 'A' and schema 'B'. In schema 'A' there are two entities: 'Car' and 'Driver' and many-to-many relationship between them. I also created table 'driver_car' (with columns: driver, car) in schema 'A':

from datetime import datetime
from pony.orm import *

db = Database()

class Car(db.Entity):
    _table_ = ('A', 'car')
    id_car = PrimaryKey(int, auto=True)
    name = Required(str, 45)
    driver = Set('Driver')

class Driver(db.Entity):
    _table_ = ('A', 'driver')
    id_driver = PrimaryKey(int, auto=True)
    name = Required(str, 45)
    surname = Required(str, 45)
    car = Set(Car)

Let's say I have two instances of Car and two instances of Driver. When I wan't to relate driver with car, in python with ponyORM I need something like this:

db.bind(PARAMETERS)
with db.session:
    Car[1].driver.add(Driver[1])

I'm getting the error:

pony.orm.dbapiprovider.ProgrammingError: relation "car_driver" does not exist
LINE 1: INSERT INTO "car_driver" ("car", "driv...

As I understand, problem is that ponyORM tries to create table "car_driver" instead of "A"."car_driver"? So, how to handle this problem? Hope I was clear enough.

Solution

You can specify the intermediate's table name from one of Set attribute.

class Car(db.Entity):
    _table_ = ('A', 'car')
    id_car = PrimaryKey(int, auto=True)
    name = Required(str, 45)
    driver = Set('Driver', table=('A', 'car_driver'))  # like this

class Driver(db.Entity):
    _table_ = ('A', 'driver')
    id_driver = PrimaryKey(int, auto=True)
    name = Required(str, 45)
    surname = Required(str, 45)
    car = Set(Car)