SQLAlchemy, one to many vs many to one

Question

Dear Everyone,

I have the following data:

CREATE TABLE `groups` (     
    `bookID` INT NOT NULL,
    `groupID` INT NOT NULL,

    PRIMARY KEY(`bookID`),
    KEY( `groupID`)
);

and a book table which basically has books( bookID, name, ... ), but WITHOUT groupID. There is no way for me to determine what the groupID is at the time of the insert for books.

I want to do this in sqlalchemy. Hence I tried mapping Book to the books joined with groups on book.bookID=groups.bookID.

I made the following:

tb_groups = Table( 'groups', metadata, 
    Column('bookID', Integer, ForeignKey('books.bookID'), primary_key=True ),
    Column('groupID', Integer),
)

tb_books = Table( 'books', metadata, 
    Column('bookID', Integer, primary_key=True), 

tb_joinedBookGroup = sql.join( tb_books, tb_groups, \
        tb_books.c.bookID == tb_groups.c.bookID)

and defined the following mapper:

mapper( Group, tb_groups, properties={
    'books': relation(Book, backref='group')
})
mapper( Book, tb_joinedBookGroup )

...

However, when I execute this piece of code, I realized that each book object has a field groups, which is a list, and each group object has books field which is a singular assigment. I think my definition here must have been causing sqlalchemy to be confused about the many-to-one vs one-to-many relationship.

Can someone help me sort this out?

My desired goal is g.books = [b, b, b, .. ] book.group = g,

where g is an instance of group, and b is an instance of book

Answer 1

Pass userlist=False to relation() to say that property should represent scalar value, not collection. This will for independent on whether there is primary key for this column, but you probably want to define unique constraint anyway.