def chain(self, keys=None):
if self._key_list is None and keys is None:
raise RuntimeError('No key found')
if keys is None:
keys = self._key_list
sql = 'select `{0}` from `{1}` where {2}'.format(
'`,`'.join(self._columns), self._table_name,
' and '.join(['`{0}`=%({0})s'.format(key) for key in keys]))
if self._order_by is not None:
sql = sql + ' order by `{0}`'.format('`,`'.join(self._order_by))
sql = sql + ' limit 1 '
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True)
c.execute(sql, (self.get_keys(keys)))
r = c.fetchone()
if r == None:
return False
self.set_value(r)
return True
except Exception as e:
print e
return False
finally:
c.close()
dbc.close()
def __iter__(self, keys=None):
sql = 'select `{0}` from `{1}`'.format('`,`'.join(self._columns),
self._table_name)
if self._key_list is not None or keys is not None:
if keys is None:
keys = self._key_list
if len(keys) > 0:
sql = sql + ' where {0}'.format(' and '.join(
['`{0}`=%({0})s'.format(key) for key in keys]))
if self._order_by is not None:
sql = sql + ' order by `{0}`'.format('`,`'.join(self._order_by))
if self._page_size is not None:
if self._current_page is None:
self._current_page = 1
sql = sql + ' limit {0}, {1}'.format(
(self._current_page - 1) * self._page_size, self._page_size)
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True, buffered=True)
print 'mysql status', dbc.is_connected()
c.execute(sql, (self.get_keys(keys)))
print 'mysql status', dbc.is_connected()
for r in c:
rec = self.__class__(self._table_name, self._columns,
self._unique_keys)
rec.set_value(r)
if rec.lock():
yield rec
except Exception as e:
print e
raise StopIteration
finally:
c.close()
dbc.close()
def execute(sql):
try:
dbc = DBConnector.connect()
c = dbc.cursor()
c.execute(sql)
return c
except Exception as e:
print e
return None
finally:
c.close()
dbc.close()
def write(self, data):
sql = 'insert into %(table)s(%(column)s) value(%(data)s)'
sql_data = {'table' : self.table, 'column' : '', 'data' : ''}
sql_data['column'] = ','.join([k for k, v in data.items()])
sql_data['data'] = ','.join(['%({0})s'.format(k) for k, v in data.items()])
sql = sql % sql_data
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql, (data))
except Exception, e:
raise e
def delete(self, key={}):
sql = 'delete from %(table)s %(where)s'
sql_data = {'table' : self.table, 'where' : ''}
if key != {}:
sql_data['where'] = 'where ' + ' and '.join(['{0}=%({0})s'.format(k) for k, v in key.items()])
sql = sql % sql_data
#print sql
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql, (key))
except Exception, e:
raise e
def write(self):
sql = 'insert into `{0}`(`{1}`) values({2})'.format(
self._table_name, '`,`'.join(self._columns),
','.join(['%({0})s'.format(column) for column in self._columns]))
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True)
c.execute(sql, (self.get_dict()))
return True
except Exception as e:
print e
return False
finally:
c.close()
dbc.close()
def update(self, data, key={}):
sql = 'update %(table)s set %(data)s %(where)s'
sql_data = {'table' : self.table, 'data' : '', 'where' : ''}
sql_data['data'] = ','.join(['{0}=%(d{0})s'.format(k) for k, v in data.items()])
data = {'d'+k : v for k, v in data.items()}
if key != {}:
sql_data['where'] = 'where ' + ' and '.join(['{0}=%(k{0})s'.format(k) for k, v in key.items()])
key = {'k'+k : v for k, v in key.items()}
sql = sql % sql_data
data.update(key)
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql, (data))
except Exception, e:
raise e
def counts(self, keys=None):
sql = 'select count(*) as `num` from `{0}`'.format(self._table_name)
if self._key_list is not None or keys is not None:
if keys is None:
keys = self._key_list
sql = sql + ' where {0}'.format(' and '.join(
['`{0}`=%({0})s'.format(key) for key in keys]))
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True)
c.execute(sql, (self.get_keys(keys)))
r = c.fetchone()
return r['num']
except Exception as e:
print e
return 0
finally:
c.close()
dbc.close()
def delete(self, keys=None):
sql = 'delete from `{0}`'.format(self._table_name)
if self._key_list is not None or keys is not None:
if keys is None:
keys = self._key_list
sql = sql + ' where {0}'.format(' and '.join(
['`{0}`=%({0})s'.format(key) for key in keys]))
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True)
c.execute(sql, (self.get_keys(keys)))
print c.fetchall()
return True
except Exception as e:
print e
return False
finally:
c.close()
dbc.close()
def update(self, keys=None):
if self._key_list is None and keys is None:
raise RuntimeError('No key found')
if keys is None:
keys = self._key_list
counts = self.counts(keys)
if counts > 0:
sql = 'update `{0}` set {1} where {2}'.format(
self._table_name, ','.join([
'`{0}`=%({0})s'.format(column) for column in self._columns
]),
' and '.join(['`{0}`=%({0})s'.format(key) for key in keys]))
try:
dbc = DBConnector.connect()
c = dbc.cursor(dictionary=True)
c.execute(sql, (self.get_dict()))
except Exception as e:
print e
finally:
c.close()
dbc.close()
return counts
def read(self, key={}, orderby=[], lock=False, iteration=False):
sql = 'select * from %(table)s %(where)s %(order)s %(option)s'
sql_data = {'table' : self.table, 'where' : '', 'order' : '', 'option' : ''}
if lock:
if not dbc.in_transaction():
raise RuntimeError('Not in transaction but trying to use lock')
sql_data['option'] = 'for update'
if key != {}:
sql_data['where'] = 'where ' + ' and '.join(['{0}=%({0})s'.format(k) for k, v in key.items()])
if orderby != []:
sql_data['order'] = 'order by ' + ','.join(orderby)
sql = sql % sql_data
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql, key)
rt = c.fetchall()
if iteration:
self.rt = rt
return self
return rt
except Exception, e:
raise e
def read_in(self, keys, orderby=[], lock=False, iteration=False):
if keys == []:
return None;
sql = 'select * from %(table)s %(where)s %(order)s %(option)s'
sql_data = {'table' : self.table, 'where' : '', 'order' : '', 'option' : ''}
if lock:
if not dbc.in_transaction():
raise RuntimeError('Not in transaction but trying to use lock')
sql_data['option'] = 'for update'
in_list = []
one_key = '(%s)' % ','.join(['%({0})s'.format(k) for k in keys[0].keys()])
for key in keys:
d = {}
for k, v in key.items():
conv = v
conv = self.converter.to_mysql(conv)
conv = self.converter.escape(conv)
conv = self.converter.quote(conv)
d[k] = bytes(conv)
in_list.append(one_key % d)
sql_data['where'] = 'where (%s) in (%s)' % (','.join(keys[0].keys()), ','.join(in_list))
if orderby != []:
sql_data['order'] = 'order by ' + ','.join(orderby)
sql = sql % sql_data
print sql
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql)
rt = c.fetchall()
if iteration:
self.rt = rt
return self
return rt
except Exception, e:
raise e
sql = sql % sql_data
#print sql
try:
c = None
c = dbc.connect().cursor(buffered=True, dictionary=True)
c.execute(sql, (key))
except Exception, e:
raise e
finally:
if c is not None: c.close()
def __iter__(self):
for rt in self.rt:
yield rt
if __name__ == '__main__':
dbc.connect()
for i in range(0, 5):
sql().table('images_unit_test').write({
'id': uuid.uuid1().get_hex(),
'name': 'test{0}.jpg'.format(i),
'path': '/test/test{0}.jpg'.format(i),
'ctime': datetime.datetime.now(),
})
in_list = []
for i in range(0, 5):
in_list.append({
'name': 'test{0}.jpg'.format(i),
'path': '/test/test{0}.jpg'.format(i),
})
sql().table('images_unit_test').update({'path': 'f**k you'}, {'name': 'test1.jpg'})
for i in sql().table('images_unit_test').read_in(in_list, iteration=True):
