Project

General

Profile

ormdd.py

ORM dédié - vincent.mbg, 12/25/2009 07:02 PM

 
1
# -*- coding: utf-8 -*-
2

    
3
# intégrité lors de création
4

    
5
import sqlite3 as sqlite
6

    
7
# CREATE TABLE terrasse(
8
#        id INTEGER PRIMARY KEY,
9
#        orientation TEXT)
10

    
11
# CREATE TABLE fleure(
12
#        id INTEGER PRIMARY KEY,
13
#        couleur TEXT,
14
#        id_terrasse INTEGER REFERENCES terrasse( id ))
15

    
16
BDD_SQLITE3 = "mabdd.db3"
17

    
18

    
19
class Terrasse( object ) :
20
    __CONTROLE = True
21
    __INIT = False
22

    
23
    @staticmethod
24
    def get( **args ) :
25
        out = []
26
        
27
        # Création de la requête
28
        query = 'SELECT * FROM terrasse WHERE '
29
        for field, value in args.items() :
30
            if field not in ( "id", "orientation" ) :
31
                raise AttributeError("'Terrasse' object has no attribute '%s'" % field ) 
32
            query +=  "%s = '%s'\nand " % (field, value)
33
        query = query[:-4]  
34

    
35
        con = sqlite.connect( BDD_SQLITE3 )
36
        cur = con.cursor()
37
        cur.execute( query )
38
        res = cur.fetchall()
39
        cur.close()
40
        con.close()
41

    
42
        Terrasse.__CONTROLE = False
43
        Terrasse.__INIT = True
44
        for attrs in res :
45
            out.append( Terrasse( *attrs ) )
46
        Terrasse.__CONTROLE = True
47
        Terrasse.__INIT = False
48
        return out
49

    
50
    def __init__( self, id, orientation ) :
51
        Terrasse.__INIT = True
52
        self.id = id
53
        self.orientation = orientation
54
        Terrasse.__INIT = False
55
        
56
        if Terrasse.__CONTROLE :
57
            con = sqlite.connect( BDD_SQLITE3 )
58
            cur = con.execute( 'SELECT count(*) > 0 FROM terrasse WHERE id == %s' % self.id )
59
            existe = cur.fetchone()[0]
60
            cur.close()
61
            con.close()
62

    
63
            if existe :
64
                raise sqlite.IntegrityError( "PRIMARY KEY must be unique" )
65

    
66
            con = sqlite.connect( BDD_SQLITE3 )
67
            con.execute( "insert into terrasse( id, orientation ) Values( %s, '%s' )" % ( self.id, self.orientation ) )
68
            con.commit()
69
            con.close()
70

    
71
    def __setattr__ ( self, arg, value ) :
72

    
73
            if arg in ( "id", "orientation" ):
74
                self.__dict__ [arg] = value
75

    
76
                if not Terrasse.__INIT :
77
                    con = sqlite.connect( BDD_SQLITE3 )
78
                    con.execute( "update Terrasse set %s = '%s' where id = %s" % ( arg, value, self.id ) )
79
                    existe = con.execute( 'SELECT count(*) == 0 FROM terrasse WHERE id == %s' % self.id ).fetchone()[0]
80
                    con.commit()
81
                    con.close()
82

    
83
                    if existe :
84
                        raise Exception( "'Terrasse' object is destroyed" )
85
            else :
86
                raise AttributeError("'Terrasse' object has no attribute '%s'" % arg )
87
        
88
    def destroy( self, cascade = False ) :
89

    
90
        con = sqlite.connect( BDD_SQLITE3 )
91
        cur = con.execute( 'SELECT count(*) > 0 FROM Terrasse WHERE id == %s' % self.id )
92
        existe = cur.fetchone()[0]
93
        cur.close()
94
        con.close()
95
        
96
        if existe :
97
            if not cascade :
98
                con = sqlite.connect( BDD_SQLITE3 )
99
                cur = con.execute( 'SELECT count(*) > 0 FROM fleure WHERE id_terrasse == %s' % ( self.id ) )
100
                existe = cur.fetchone()[0]
101
                cur.close(); con.close()
102
                
103
                if existe :
104
                    raise sqlite.IntegrityError("")
105

    
106
                con = sqlite.connect( BDD_SQLITE3 )
107
                con.execute( "delete from Terrasse where id = %s" %  self.id )
108
                con.commit()
109
                con.close()
110
                
111
            else :
112
                con = sqlite.connect( BDD_SQLITE3 )
113
                con.execute( "delete FROM fleure WHERE id_terrasse == %s" % ( self.id ) )
114
                con.execute( "delete FROM terrasse WHERE id = %s" % ( self.id ) )
115
                con.commit()
116
                con.close()           
117
   
118
                    
119
        else :
120
            raise Exception("'Terrasse' object is already destroyed " )
121

    
122

    
123

    
124
class Fleure( object ) :
125
    __CONTROLE = True
126
    __INIT = False
127

    
128
    @staticmethod
129
    def get( **args ) :
130
        out = []
131
        
132
        # Création de la requête
133
        query = 'SELECT * FROM fleure WHERE '
134
        for field, value in args.items() :
135
            if field not in ( "id", "couleur", "id_terrasse" ) :
136
                raise AttributeError("'Fleur' object has no attribute '%s'" % field ) 
137
            query +=  "%s = '%s'\nand " % (field, value)
138
        query = query[:-4]  
139

    
140
        con = sqlite.connect( BDD_SQLITE3 )
141
        cur = con.cursor()
142
        cur.execute( query )
143
        res = cur.fetchall()
144
        cur.close()
145
        con.close()
146

    
147
        Fleure.__CONTROLE = False
148
        Fleure.__INIT = True
149
        for attrs in res :
150
            out.append( Fleure( *attrs ) )
151
            
152
        Fleure.__CONTROLE = True
153
        Fleure.__INIT = False
154
        return out
155

    
156
    def __init__( self, id, couleur, id_terrasse ) :
157
        Fleure.__INIT = True
158
        self.id = id
159
        self.couleur = couleur
160
        self.id_terrasse = id_terrasse
161
        Fleure.__INIT = False 
162

    
163
        if Fleure.__CONTROLE :
164
            con = sqlite.connect( BDD_SQLITE3 )
165
            cur = con.execute( 'SELECT count( * ) > 0 FROM fleure WHERE id == %s' % self.id )
166
            pk_existe = cur.fetchone()[0]
167
            cur = con.execute( 'SELECT count( * ) > 0 FROM terrasse WHERE id == %s' % self.id_terrasse )
168
            fk_existe = cur.fetchone()[0]
169
            cur.close()
170
            con.close()
171

    
172
            if pk_existe :
173
                raise sqlite.IntegrityError( "PRIMARY KEY must be unique" )
174
            if not fk_existe :
175
                raise sqlite.IntegrityError()
176
            
177
            con = sqlite.connect( BDD_SQLITE3 )
178
            con.execute( "insert into Fleure( id, couleur, id_terrasse ) Values( %s, '%s', %s )" % ( self.id, self.couleur, self.id_terrasse ) )
179
            con.commit()
180
            con.close()
181

    
182
    def __setattr__ ( self, arg, value ) :
183
            if arg in ( "id", "couleur", "id_terrasse" ):
184
                self.__dict__ [arg] = value
185

    
186
                if not Fleure.__INIT :
187
                    con = sqlite.connect( BDD_SQLITE3 )
188
                    con.execute( "update Fleure set %s = '%s' where id = %s" % ( arg, value, self.id ) )
189
                    cur = con.execute( 'SELECT count( * ) == 0 FROM fleure WHERE id == %s' % self.id )
190
                    existe = cur.fetchone()[0]
191
                    con.commit()
192
                    con.close()
193

    
194
                    if existe :
195
                        raise Exception( "'Fleur' object is destroyed" )
196
            else :
197
                raise AttributeError("'Fleur' object has no attribute '%s'" % arg )
198
        
199
    def destroy( self ) :
200

    
201
        con = sqlite.connect( BDD_SQLITE3 )
202
        cur = con.execute( 'SELECT count( * ) > 0 FROM fleure WHERE id == %s' % self.id )
203
        existe = cur.fetchone()[0]
204
        cur.close()
205
        con.close()
206
        
207
        if existe :
208
            con = sqlite.connect( BDD_SQLITE3 )
209
            con.execute( "delete from Fleure where id = %s" %  self.id )
210
            con.commit()
211
            con.close() 
212

    
213
        else :
214
            raise Exception("'Fleur' object is already destroyed " )
215
        
216

    
217

    
218
try :
219
    t1 = Terrasse( 1, 'nord' )
220
except Exception as e :
221
    print e
222
    t1 = Terrasse.get( id = 1 )[0]
223

    
224
try :
225
    f1 = Fleure( 1, "red", 1 )
226
except Exception as e :
227
    print e
228
    f1 = Fleure.get( id = 1 )[0]
229

    
230
#print f1.couleur
231
#if f1.couleur == 'red' :
232
#    f1.couleur = 'green'
233
#else :
234
#    f1.couleur = 'red'
235

    
236
    
237

    
238