1 #!/bin/python
2 #_*_coding:utf8_*_
3
4 from sqlalchemy import *
5 from sqlalchemy.orm import *
6 engine=create_engine("sqlite:///./car.db")
7 metadata=MetaData()
8
9 car=Table('car',metadata,
10 Column('carID',Integer,primary_key=True),
11 Column('tyreID1',Integer,ForeignKey('tyre.tyreID'),nullable=False),
12 Column('tyreID2',Integer,ForeignKey('tyre.tyreID'),nullable=False),
13 Column('tyreID3',Integer,ForeignKey('tyre.tyreID'),nullable=False),
14 Column('tyreID4',Integer,ForeignKey('tyre.tyreID'),nullable=False),
15 )
16
17 tyre=Table('tyre',metadata,
18 Column('tyreID',Integer,primary_key=True),
19 Column('tyreSize',Integer,nullable=False),
20 Column('tyreMaterial',String(50),nullable=False),
21 )
22
23 metadata.drop_all(engine)
24 metadata.create_all(engine)
25
26 class Car(object):
27 pass
28 class Tyre(object):
29 pass
30
31 mapper(Car,car,properties={
32 'x1':relationship(Tyre, primaryjoin=and_(car.c.tyreID1==tyre.c.tyreID),backref=backref("car1")),
33 'x2':relationship(Tyre, primaryjoin=and_(car.c.tyreID2==tyre.c.tyreID),backref=backref("car2")),
34 'x3':relationship(Tyre, primaryjoin=and_(car.c.tyreID3==tyre.c.tyreID),backref=backref("car3")),
35 'x4':relationship(Tyre, primaryjoin=and_(car.c.tyreID4==tyre.c.tyreID),backref=backref("car4"))
36 })
37 mapper(Tyre,tyre)
38 #mapper(Tyre,tyre)
39
40 Session=sessionmaker(bind=engine)
41 s=Session()
42
43 t1=Tyre()
44 t1.tyreSize=1
45 t1.tyreMaterial="pi"
46 t1.tyreID=1
47
48 t2=Tyre()
49 t2.tyreSize=2
50 t2.tyreMaterial="pi2"
51 t2.tyreID=2
52
53 t3=Tyre()
54 t3.tyreSize=3
55 t3.tyreMaterial="pi3"
56 t3.tyreID=3
57
58 t4=Tyre()
59 t4.tyreSize=4
60 t4.tyreMaterial="pi4"
61 t4.tyreID=4
62
63
64 c=Car()
65 c.tyreID1=1
66 c.tyreID2=2
67 c.tyreID3=3
68 c.tyreID4=4
69
70 s.add(t1)
71 s.add(t2)
72 s.add(t3)
73 s.add(t4)
74 s.add(c)
75 s.commit()
76
77 for r in s.query(Car).all():
78 print r.tyreID1,r.x1.tyreMaterial
79
80 print t1.tyreID,t1.car1.carID
