address.py 5.73 KB
Newer Older
ymir's avatar
ymir committed
1
2
# coding=utf-8
# import
ymir's avatar
ymir committed
3
4
from sqlite3 import connect
from dummypythongraphqlserver.dataAccess import DB
ymir's avatar
ymir committed
5
from dummypythongraphqlserver.entities import Address
ymir's avatar
ymir committed
6
7
8
# data access object
class AddressDAO():
    @staticmethod
ymir's avatar
ymir committed
9
    def create(addresses):
ymir's avatar
ymir committed
10
11
        statement = "INSERT INTO ADDRESS (HOUSE_NUMBER,STREET,ZIPCODE,CITY) VALUES "
        parameters = list()
ymir's avatar
ymir committed
12
        # fill each address
ymir's avatar
ymir committed
13
14
15
16
17
        for address in addresses :
            statement += "(?,?,?,?),"
            parameters += [address.houseNumber,address.street,address.zipCode,address.city]
            pass
        statement = statement[:-1]
ymir's avatar
ymir committed
18
        connection = connect(DB)
ymir's avatar
ymir committed
19
20
        cursor = connection.cursor()
        raisedExeption = None
ymir's avatar
ymir committed
21
22
        # INFO : we can update input addresses in dictionary keys
        newAddresses = list()
ymir's avatar
ymir committed
23
        try:
ymir's avatar
ymir committed
24
25
            # insert data
            cursor.execute(statement,parameters)
ymir's avatar
ymir committed
26
            connection.commit()
ymir's avatar
ymir committed
27
28
29
30
            # retrieve IDs
            for originalAddress in addresses:
                newAddress = AddressDAO.read(houseNumber=originalAddress.houseNumber, street=originalAddress.street, zipCode=originalAddress.zipCode, city=originalAddress.city)[0]
                newAddresses.append(newAddress)
ymir's avatar
ymir committed
31
32
33
34
35
36
37
            pass
        except Exception as exeception:
            raisedExeption = exeception
        finally:
            cursor.close()
            connection.close()
            if raisedExeption : raise raisedExeption
ymir's avatar
ymir committed
38
        return newAddresses
ymir's avatar
ymir committed
39
    @staticmethod
40
    def read(ids=None, houseNumber=None, street=None, zipCode=None, city=None, clientsIds=None, limit=None, offset=None):
ymir's avatar
ymir committed
41
        statement = "SELECT A.HOUSE_NUMBER, A.STREET, A.ZIPCODE, A.CITY, A.ID FROM ADDRESS A"
ymir's avatar
ymir committed
42
        parameters = list()
ymir's avatar
ymir committed
43
        # joins
44
        if clientsIds:
ymir's avatar
ymir committed
45
            statement += " JOIN CLIENT C ON C.ADDRESS_ID = A.ID"
ymir's avatar
ymir committed
46
47
        # where
        statement += " WHERE 0=0"
ymir's avatar
ymir committed
48
        if ids:
ymir's avatar
ymir committed
49
            statement += " AND A.ID IN ("+','.join(['?']*len(ids))+")"
ymir's avatar
ymir committed
50
            parameters += ids
ymir's avatar
ymir committed
51
        if houseNumber:
ymir's avatar
ymir committed
52
            statement += " AND A.HOUSE_NUMBER LIKE ?"
ymir's avatar
ymir committed
53
54
            parameters.append('%'+str(houseNumber)+'%')
        if street:
ymir's avatar
ymir committed
55
            statement += " AND UPPER(A.STREET) LIKE UPPER(?)"
ymir's avatar
ymir committed
56
57
            parameters.append('%'+str(street)+'%')
        if zipCode:
ymir's avatar
ymir committed
58
            statement += " AND A.ZIPCODE LIKE ?"
ymir's avatar
ymir committed
59
60
            parameters.append('%'+str(zipCode)+'%')
        if city:
ymir's avatar
ymir committed
61
            statement += " AND UPPER(A.CITY) LIKE UPPER(?)"
ymir's avatar
ymir committed
62
            parameters.append('%'+str(city)+'%')
63
64
65
        if clientsIds:
            statement += " AND C.ID IN ("+','.join(['?']*len(clientsIds))+")"
            parameters += clientsIds
ymir's avatar
ymir committed
66
67
        # order
        statement += " ORDER BY A.ID ASC"
ymir's avatar
ymir committed
68
        # shift
ymir's avatar
ymir committed
69
70
71
        if limit:
            statement += " LIMIT ?"
            parameters.append(limit)
ymir's avatar
ymir committed
72
73
74
        if offset:
            statement += " OFFSET ?"
            parameters.append(offset)
ymir's avatar
ymir committed
75
        connection = connect(DB)
ymir's avatar
ymir committed
76
77
78
79
        cursor = connection.cursor()
        raisedExeption = None
        addresses = list()
        try:
ymir's avatar
ymir committed
80
            results = cursor.execute(statement,parameters)
ymir's avatar
ymir committed
81
82
83
84
85
86
87
88
89
90
91
92
            for result in results:
                address = Address(*result)
                addresses.append(address)
                pass
            pass
        except Exception as exeception:
            raisedExeption = exeception
        finally:
            cursor.close()
            connection.close()
            if raisedExeption : raise raisedExeption
        return addresses
ymir's avatar
ymir committed
93
    @staticmethod
ymir's avatar
ymir committed
94
95
96
    def update(addresses):
        statement = "WITH TMP(ID,HOUSE_NUMBER,STREET,ZIPCODE,CITY) AS (VALUES"
        parameters = list()
ymir's avatar
ymir committed
97
        # fill each address
ymir's avatar
ymir committed
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
        for address in addresses :
            statement += "(?,?,?,?,?),"
            parameters += [address.id,address.houseNumber,address.street,address.zipCode,address.city]
        statement = statement[:-1]
        statement += """)
UPDATE ADDRESS
SET HOUSE_NUMBER = (SELECT HOUSE_NUMBER FROM TMP WHERE ADDRESS.ID=TMP.ID),
STREET = (SELECT STREET FROM TMP WHERE ADDRESS.ID=TMP.ID),
ZIPCODE = (SELECT ZIPCODE FROM TMP WHERE ADDRESS.ID=TMP.ID),
CITY = (SELECT CITY FROM TMP WHERE ADDRESS.ID=TMP.ID)
WHERE ID IN (SELECT ID FROM TMP)"""
        connection = connect(DB)
        cursor = connection.cursor()
        raisedExeption = None
        try:
            cursor.execute(statement,parameters)
            connection.commit()
            pass
        except Exception as exeception:
            raisedExeption = exeception
        finally:
            cursor.close()
            connection.close()
            if raisedExeption : raise raisedExeption
        pass
ymir's avatar
ymir committed
123
    @staticmethod
ymir's avatar
ymir committed
124
    def delete(ids=None,houseNumber=None,street=None,zipCode=None,city=None):
ymir's avatar
ymir committed
125
126
127
128
129
130
131
132
133
        statement = "DELETE FROM ADDRESS WHERE 0=0"
        parameters = list()
        if ids:
            statement += " AND ID IN ("+','.join(['?']*len(ids))+")"
            parameters += ids
        if houseNumber:
            statement += " AND HOUSE_NUMBER LIKE ?"
            parameters.append('%'+str(houseNumber)+'%')
        if street:
ymir's avatar
ymir committed
134
            statement += " AND UPPER(STREET) LIKE UPPER(?)"
ymir's avatar
ymir committed
135
136
137
138
139
            parameters.append('%'+str(street)+'%')
        if zipCode:
            statement += " AND ZIPCODE LIKE ?"
            parameters.append('%'+str(zipCode)+'%')
        if city:
ymir's avatar
ymir committed
140
            statement += " AND UPPER(CITY) LIKE UPPER(?)"
ymir's avatar
ymir committed
141
142
143
144
145
146
147
148
149
150
151
152
153
            parameters.append('%'+str(city)+'%')
        connection = connect(DB)
        cursor = connection.cursor()
        raisedExeption = None
        try:
            cursor.execute(statement,parameters)
            connection.commit()
        except Exception as exeception:
            raisedExeption = exeception
        finally:
            cursor.close()
            connection.close()
            if raisedExeption : raise raisedExeption
ymir's avatar
ymir committed
154
155
156
    pass
pass