| [976] | 1 | ###
|
|---|
| 2 | ### SQLDict - Container access to an SQL table
|
|---|
| 3 | ###
|
|---|
| 4 |
|
|---|
| 5 | from ...builtins import *
|
|---|
| 6 | from ...parsing.autotype import *
|
|---|
| 7 |
|
|---|
| 8 | class SQLDict(object):
|
|---|
| 9 |
|
|---|
| 10 | def __init__(self, conn=None, table=None, key=None, value=None):
|
|---|
| 11 |
|
|---|
| 12 | object.__setattr__(self, 'conn', conn)
|
|---|
| 13 | object.__setattr__(self, 'table', table)
|
|---|
| 14 | object.__setattr__(self, 'key', key)
|
|---|
| 15 | object.__setattr__(self, 'value', value)
|
|---|
| 16 |
|
|---|
| 17 | def __getitem__(self, key=None):
|
|---|
| 18 |
|
|---|
| 19 | curs = self.conn.cursor()
|
|---|
| 20 | sql = "SELECT %s FROM %s WHERE %s = '%s'" % (self.value, self.table, self.key, key)
|
|---|
| 21 | curs.execute(sql)
|
|---|
| 22 | results = curs.fetchone()
|
|---|
| 23 | if results:
|
|---|
| 24 | value = autotype(results[0])
|
|---|
| 25 | return value
|
|---|
| 26 | else:
|
|---|
| 27 | return None
|
|---|
| 28 |
|
|---|
| 29 | def __getattr__(self, name):
|
|---|
| 30 |
|
|---|
| 31 | return self.__getitem__(name)
|
|---|
| 32 |
|
|---|
| 33 | def __setitem__(self, key, value):
|
|---|
| 34 |
|
|---|
| 35 | curs = self.conn.cursor()
|
|---|
| 36 |
|
|---|
| 37 | if key in self:
|
|---|
| 38 | sql = "UPDATE %s SET %s = '%s' WHERE %s = '%s'" % (self.table, self.value, value, self.key, key)
|
|---|
| 39 | else:
|
|---|
| 40 | sql = "INSERT %s (%s, %s) VALUES ('%s', '%s')" % (self.table, self.key, self.value, key, value)
|
|---|
| 41 |
|
|---|
| 42 | curs.execute(sql)
|
|---|
| 43 | self.conn.commit()
|
|---|
| 44 |
|
|---|
| 45 | def __setattr__(self, name, value):
|
|---|
| 46 |
|
|---|
| 47 | if self.__dict__.has_key(name):
|
|---|
| 48 | return object.__setattr__(self, name, value)
|
|---|
| 49 | else:
|
|---|
| 50 | return self.__setitem__(name, value)
|
|---|
| 51 |
|
|---|
| 52 | def __delitem__(self, key):
|
|---|
| 53 |
|
|---|
| 54 | curs = self.conn.cursor()
|
|---|
| 55 |
|
|---|
| 56 | sql = "DELETE FROM %s WHERE %s = '%s'" % (self.table, self.key, key)
|
|---|
| 57 |
|
|---|
| 58 | curs.execute(sql)
|
|---|
| 59 | self.conn.commit()
|
|---|
| 60 |
|
|---|
| 61 | def __delattr__(self, name):
|
|---|
| 62 |
|
|---|
| 63 | return self.__delitem__(name)
|
|---|
| 64 |
|
|---|
| 65 | def __contains__(self, item):
|
|---|
| 66 |
|
|---|
| 67 | curs = self.conn.cursor()
|
|---|
| 68 | sql = "SELECT COUNT(*) FROM %s WHERE %s = '%s'" % (self.table, self.key, item)
|
|---|
| 69 | curs.execute(sql)
|
|---|
| 70 | results = curs.fetchone()
|
|---|
| 71 | count = int(results[0])
|
|---|
| 72 |
|
|---|
| 73 | if count > 0:
|
|---|
| 74 | return True
|
|---|
| 75 | else:
|
|---|
| 76 | return False
|
|---|
| 77 |
|
|---|
| 78 | def __iter__(self):
|
|---|
| 79 |
|
|---|
| 80 | return self.iteritems()
|
|---|
| 81 |
|
|---|
| 82 | def clear(self):
|
|---|
| 83 |
|
|---|
| 84 | curs = self.conn.cursor()
|
|---|
| 85 | sql = "DELETE FROM %s" % (self.table)
|
|---|
| 86 | curs.execute(sql)
|
|---|
| 87 | self.conn.commit()
|
|---|
| 88 |
|
|---|
| 89 | return
|
|---|
| 90 |
|
|---|
| 91 | def copy(self):
|
|---|
| 92 |
|
|---|
| 93 | curs = self.conn.cursor()
|
|---|
| 94 | sql = "SELECT %s, %s FROM %s" % (self.key, self.value, self.table)
|
|---|
| 95 | curs.execute(sql)
|
|---|
| 96 |
|
|---|
| 97 | copy = {}
|
|---|
| 98 |
|
|---|
| 99 | for row in curs.fetchall():
|
|---|
| 100 | key = row[0]
|
|---|
| 101 | value = row[1]
|
|---|
| 102 | copy[key] = value
|
|---|
| 103 |
|
|---|
| 104 | return copy
|
|---|
| 105 |
|
|---|
| 106 | def has_key(self, key):
|
|---|
| 107 |
|
|---|
| 108 | return True if key in self else False
|
|---|
| 109 |
|
|---|
| 110 | def items(self):
|
|---|
| 111 |
|
|---|
| 112 | curs = self.conn.cursor()
|
|---|
| 113 | sql = "SELECT %s, %s FROM %s" % (self.key, self.value, self.table)
|
|---|
| 114 | curs.execute(sql)
|
|---|
| 115 |
|
|---|
| 116 | return [(row[0], row[1]) for row in curs.fetchall()]
|
|---|
| 117 |
|
|---|
| 118 | def keys(self):
|
|---|
| 119 |
|
|---|
| 120 | curs = self.conn.cursor()
|
|---|
| 121 | sql = "SELECT %s FROM %s" % (self.key, self.table)
|
|---|
| 122 | curs.execute(sql)
|
|---|
| 123 |
|
|---|
| 124 | return [row[0] for row in curs.fetchall()]
|
|---|
| 125 |
|
|---|
| 126 | def update(self, b):
|
|---|
| 127 |
|
|---|
| 128 | for key in b.keys():
|
|---|
| 129 | value = b[key]
|
|---|
| 130 | self[key] = value
|
|---|
| 131 |
|
|---|
| 132 | return None
|
|---|
| 133 |
|
|---|
| 134 | def fromkeys(self, seq, value=None):
|
|---|
| 135 |
|
|---|
| 136 | fk = {}
|
|---|
| 137 |
|
|---|
| 138 | for key in self.iterkeys():
|
|---|
| 139 | if key in seq:
|
|---|
| 140 | fk[key] = value
|
|---|
| 141 |
|
|---|
| 142 | return fk
|
|---|
| 143 |
|
|---|
| 144 | def values(self):
|
|---|
| 145 |
|
|---|
| 146 | curs = self.conn.cursor()
|
|---|
| 147 | sql = "SELECT %s FROM %s" % (self.value, self.table)
|
|---|
| 148 | curs.execute(sql)
|
|---|
| 149 |
|
|---|
| 150 | return [row[0] for row in curs.fetchall()]
|
|---|
| 151 |
|
|---|
| 152 | def get(self, k, x):
|
|---|
| 153 |
|
|---|
| 154 | # this could be more efficient..
|
|---|
| 155 | return self[k] if k in self else x
|
|---|
| 156 |
|
|---|
| 157 | def setdefault(self, k, x):
|
|---|
| 158 |
|
|---|
| 159 | if k in self:
|
|---|
| 160 | return self[k]
|
|---|
| 161 | else:
|
|---|
| 162 | self[k] = x
|
|---|
| 163 | return x
|
|---|
| 164 |
|
|---|
| 165 | # def pop(self, k, x)
|
|---|
| 166 |
|
|---|
| 167 | # def popitem(self)
|
|---|
| 168 |
|
|---|
| 169 | def iteritems(self):
|
|---|
| 170 |
|
|---|
| 171 | return iter(self.items())
|
|---|
| 172 |
|
|---|
| 173 | def iterkeys(self):
|
|---|
| 174 |
|
|---|
| 175 | return iter(self.keys())
|
|---|
| 176 |
|
|---|
| 177 | def itervalues(self):
|
|---|
| 178 |
|
|---|
| 179 | return iter(self.values())
|
|---|
