Python-сообщество

users (
id Integer, primary-key, auto-inc
fio string 150
region_id integer
)

regions(
id integer primary-key, auto-inc,
val string 100
)

# -*-coding: utf-8; show-trailing-whitespace: t -*-

import sqlite3
from sqlalchemy import Table, ForeignKey, Unicode, Integer, create_engine, MetaData, Column
from sqlalchemy.orm import mapper, sessionmaker, scoped_session, relation

engine = create_engine("sqlite:///memory")
Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=engine))
session = Session()
metadata = MetaData(bind=engine)



tbl_regions = Table("regions", metadata,
                    Column("id", Integer, primary_key=True),
                    Column("reg_name", Unicode(100)))

tbl_person = Table("persons", metadata,
                   Column("id", Integer, primary_key=True),
                   Column("fio", Unicode(150)),
                   Column("region_id", Integer, ForeignKey("regions.id")))

class Person(object):
    def __init__(self, fio=u""):
        self.fio = fio

    def repr(self):
        return "<Person(id:{%d}, fio:{%s})>" % (self.id, self.fio)

class Region(object):
    def __init__(self, reg_name=u""):
        self.reg_name = reg_name

    def repr(self):
        return "<Region(id:{%d}, reg_name:{%s})>" % (self.id, self.reg_name)

mapper(Region, tbl_regions)
mapper(Person, tbl_person,
       properties={"regions": relation(Region, uselist=True)})


############################################################################
#                                                                          #
#                                 Testing block                            #
#                                                                          #
############################################################################

import unittest

class TestApp(unittest.TestCase):
    def setUp(self):
        metadata.create_all()
    def tearDown(self):
        metadata.drop_all()

    def test_main(self):
        person = Person(u"Test fio")
        person.regions.append(Region(u"This is the test region"))
        session.save(person)
        session.commit()
        self.assertEqual(person.regions[0].reg_name, u"This is the test region")

if __name__ == "__main__":
    unittest.main()