Re: [Sqlalchemy-users] intended way to create a record for one to many relation

[Shuo Yang]

On 2/2/06, Wade Leftwich <[EMAIL PROTECTED]> wrote:

Shuo Yang wrote:
> If I had:
>
> class Class(object):
>     pass
>
> class Student(object):
>     def __init__(self, class):
>         self.class = class
>
> What's the intended way to add a student record that associates to a
> class?  Could I do something like this?
> class = Class('english')
> student = Student(class=class)
>
> It doesn't work when I do objectstore.commit().  Only a "none" is
> inserted for foreign key on student table.
>
> --
> ---------------------------------------------------------------------------------------------------
>
> John S. Yang

Hello John,

I am also just getting into Sqlalchemy, and in my first project I will
be doing something like your example, so I worked through it:

First, I suggest you never use a Python reserved word like 'class' as an
identifier. Even though it might be legal in the place you used it, it
is going to create confusion down the road.

I don't actually use it, just quickly typed up something :)

####

from sqlalchemy import *
engine = create_engine('mysql',
                       {'db':'sc',
                        'user':'root'},
                       echo=True
                       )

klasses = Table('klasses', engine,
                Column('klass_id', Integer, primary_key=True),
                Column('teacher_name', String(50), nullable=False)
               )

students = Table('students', engine,
                 Column('student_id', Integer, primary_key=True),
                 Column('student_name', String(16), nullable=False),
                 Column('klass_id', Integer,
                                   ForeignKey(' klasses.klass_id'))
)

class Klass(object):
    pass

class Student(object):
    def __init__(self, klass=None):
        self.klass = klass

Klass.mapper = mapper(Klass, klasses)

Student.mapper = mapper(Student, students,
                        properties = {
                            'klass' : relation( Klass.mapper)

                            }
                        )

or if you want to have a dual reference (this is aparently what I missed again in my mapper):
Student.mapper = mapper(Student, students)
Klass.mapper = mapper(Klass, klasses, properties = {'students': relation( Student.mapper, backref='klass')})

output:
>>> k = Klass()
>>> k.teacher_name = "Elephant"
>>> s = Student(klass=k)
>>> s.klass
<__main__.Klass object at 0xb79e81ec>
>>> k.students
[<__main__.Student object at 0xb79e84cc>]
>>>

## ipython session
In [2]: k = Klass()

In [3]: k.teacher_name = 'Elephant'

In [4]: s = Student(klass=k)

In [5]: s.student_name = 'Marin'

In [6]: objectstore.commit()
INSERT INTO klasses (teacher_name) VALUES (%s)
['Elephant']
INSERT INTO students (student_name, klass_id) VALUES (%s, %s)
['Marin', 1L]

In [7]: s.klass.klass_id
Out[7]: 1L

In [8]: s.klass.teacher_name
Out[8]: 'Elephant'

###

-- Wade Leftwich
Ithaca, NY

--
---------------------------------------------------------------------------------------------------
John S. Yang