1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253#!/usr/bin/env python
#coding=utf-8
# Modify the following to support other databases:
import MySQLdb
dbmod = MySQLdb
get_tables = "show tables;" # how to get a list of tables
row_id = "_rowid" # name of the row ID keyword
import types
class table:
"""Emulates a list of DB rows, where each row is a tuple.
May also be accessed via a string, to pull entire columns.
Examples:
t = table(db, "users")
### select and organize data
t.search("id<100")
t.sort("lastname, firstname")
### retrieve data
rows = t[3:12] # get 9 records, from the 4th to the 12th
record = t[-5] # get the 5th record from the endd
names = t["firstname, lastname"] # return a list of all firstname-
lastname tuples
### iterate over data
for row in t: print row
### add data
t.insert('', 'Bob', 'Barker', 'bbarker')
### remove data
t.__delitem__(58)
### update data
t.update('clientes', cells, dates, "id=20") # where cells and dates
are tuples
"""
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = None
self.debug = 1
self._styles = ["Cursor", "SSCursor"]
self._new_cursor()
self._sort = ""
self._search = ""
# detect whether we are accessing more than one table
def ordenar(self, name, condition):
q = "select * from %s order by %s" % (self.name, condition)
self._query(q)
return self.dbc.fetchall()
def sort(self, method):
self._sort = ""
if method: self._sort = "order by %s" % (method)
# def search(self, method):
# self._search = ""
# if method: self._search = "where %s" % (method)
def busqueda(self, name, condition):
q = "select * from %s where %s" % (self.name, condition)
self._query(q)
return self.dbc.fetchone()
def search(self, name, condition):
q = "select * from %s where %s" % (self.name, condition)
self._query(q)
return self.dbc.fetchall()
def _new_cursor(self):
"ensure we have a fresh working cursor.(improves support for SSCursors)"
if self.dbc:
self.dbc.close()
for style in self._styles:
if hasattr(dbmod.cursors, style):
print style
self.dbc = self.db.obj.cursor(getattr(dbmod.cursors, style))
break
def _query(self, q, data=None):
if not self.dbc:
self._new_cursor()
if self.debug: print "Query: %s (%s)" % (q, data)
self.dbc.execute(q, data)
def __getitem__(self, item):
q = "select * from %s %s %s" % (self.name, \
self._search, self._sort)
if isinstance(item, types.SliceType):
q = q + " limit %s, %s" % (item.start, item.stop - item.start)
self._query(q)
return self.dbc.fetchall()
elif isinstance(item, types.StringType):
q = "select %s from %s %s %s" % (item, self.name, \
self._search, self._sort)
self._query(q)
return self.dbc.fetchall()
elif isinstance(item, types.IntType):
if item < 0: # add support for negative (from the end) indexing
item = len(self) + item
if item < 0:
raise IndexError, "index too negative"
q = q + " limit %s, 1" % (item)
self._query(q)
return self.dbc.fetchone()
else:
raise IndexError, "unsupported index type"
def __setitem__(self, key, value):
"Not yet implemented."
if isinstance(key, types.IntType):
pass
else:
raise IndexError, "index not a number"
def __delitem__(self, item):
# the method described in the article:
#q = "select %s from %s %s" % ("_rowid", self.name, self._search)
#self._query(q)
#rid = self.dbc.fetchone()[0]
q = "delete from %s where %s" % (self.name, item)
self._query(q)
# a simpler method:
#rid = self[item][-1]
#q = "delete from %s where %s=%s" % (self.name, row_id, rid)
#self._query(q)
def time(self):
q = "select curtime()"
self._query(q)
return self.dbc.fetchone()[0]
def date(self):
q = "select curdate()"
#q = "select date_format(curdate(), '%d-%m-%Y')"
self._query(q)
return self.dbc.fetchone()[0]
def filter(self, name, values, condition):
q = "select * from %s where %s like '%s'" % (self.name, values, condition)
self._query(q)
return self.dbc.fetchall()
def insert(self, *row):
fmt = ("%s," * len(row))[:-1]
q = "insert into %s values (%s)" % (self.name, fmt)
self._query(q, row)
def update(self, name, cells, values, condition):
cells = cells.split(",")
count = 0
string = ""
for val in values:
if count>0:
string+=","
string += """%s="%s" """ %(cells[count],values[count])
count = count+1
string +=" where %s" %condition
q = "update %s set %s" %(self.name, string)
self._query(q)
def __iter__(self):
self._new_cursor()
q = "select * from %s %s %s" % (self.name, \
self._search, self._sort)
self._query(q)
return self
def next(self):
r = self.dbc.fetchone()
if not r:
self._new_cursor()
raise StopIteration
return r
def __len__(self):
self._query("select count(*) from %s %s" % (self.name, self._search))
r = int(self.dbc.fetchone()[0])
return r
def count(self, name):
self._query("select count(*) from %s" % (name))
r = int(self.dbc.fetchone()[0])
return r
def inner(self, id):
q = """select articulos.id, articulos.familia_FK_Id,
articulos.descripcion, articulos.stock, articulos.stock_minimo,
articulos.precio_venta, articulos.imagen from articulos, botonera
where articulos.id = %s""" % (id)
self._query(q)
return self.dbc.fetchone()
def max_value(self, cell, name):
q = "SELECT MAX(%s) FROM %s" % (cell, name)
self._query(q)
r = int(self.dbc.fetchone()[0])
return r
def multiplicar(self, name):
q = "select cantidad * precio_venta as importe from ticket_linea" + \
"where id = %s" % (name)
self._query(q)
r = float(self.dbc.fetchone()[0])
return r
class db:
"""
A basic wrapper for databases. Usage is as follows:
d = db(user="user", passwd="password", db="database")
table_name = d.tables()[0]
t = d.table(table_name)
The parameters for connect() and __init__() are keyword arguments, given
directly to your database module.
If you access the same table from several places in your code, there is no
need to pass the table object around. This class will keep track of them
for you and provide the existing copy of a table, if one already exists.
"""
def __init__(self, **args):
self._tables = {}
if args:
self.connect(**args)
def tables(self):
q = get_tables
c = self.obj.cursor()
a = c.execute(q)
ts = []
for row in c.fetchall():
#print row
ts.append(row[0])
return ts
def table(self, name):
try:
return self._tables[name]
except:
self._tables[name] = table(self, name)
return self._tables[name]
def connect(self, **args):
self.obj = dbmod.connect(**args)
if __name__ == "__main__":
print "this file should not be executed"