def find_one(self, name, fields=None, kwargs={}):
if not objIsEmpty(fields):
fields_str = string.join(fields, ',')
else:
fields_str = '*'
sql = 'select %s from %s' % (fields_str, name)
if not objIsEmpty(kwargs):
conditions = []
for k, v in kwargs.iteritems():
condition = '%s=:%s' % (k, k)
conditions.append(condition)
#sqlite3.ProgrammingError:
# You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
# (like text_factory = str).
# It is highly recommended that you instead just switch your application to Unicode strings.
if isinstance(v, basestring):
kwargs[k] = _2unicode(v)
conditions_str = string.join(conditions, ' and ')
sql += ' where %s' % conditions_str
self.conn.row_factory = sqlite.Row
self.conn.text_factory = sqlite.OptimizedUnicode
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql, kwargs)
row = self.cursor.fetchone()
detail = dict(row)
return detail
except:
print 'failed to %s, %r' % (sql, tb.format_exc())
def find(self, name, spec=None, fields=None, other=None):
if not objIsEmpty(fields):
fields_str = string.join(fields, ',')
else:
fields_str = '*'
sql = 'select %s from %s' % (fields_str, name)
if not objIsEmpty(spec):
sql += ' where %s' % spec
if not objIsEmpty(other):
sql += ' %s' % other
self.conn.row_factory = sqlite.Row
self.conn.text_factory = sqlite.OptimizedUnicode
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
res = self.cursor.fetchall()
colls = []
for row in res:
detail = dict(row)
colls.append(detail)
return colls
except:
print 'failed to %s, %r' % (sql, tb.format_exc())
#保证返回值为list
return []
def remove(self, name, spec=None):
self.cursor = self.conn.cursor()
try:
sql = 'delete from %s' % name
if not objIsEmpty(spec):
sql += ' where %s' % spec
self.cursor.execute(sql)
except:
print 'failed to %s, %r' % (sql, tb.format_exc())
return False
print '%s is removed done' % name
return True
def update(self, name, spec=None, args=''):
sql = 'update %s set %s' % (name, args)
if not objIsEmpty(spec):
sql += ' where %s' % spec
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
return True
except:
print 'failed to %s, %r' % (sql, tb.format_exc())
return False
def insert(self, name, dData, fields=[]):
if objIsEmpty(dData):
return False
if not isinstance(dData, list):
dData = [dData]
self.conn.text_factory = sqlite.OptimizedUnicode
self.cursor = self.conn.cursor()
if self.table_exist(name) is False:
self.create_table(name, fields)
keys = []
fmts = []
try:
sql = 'select * from %s limit 1' % name
self.cursor.execute(sql)
for col in self.cursor.description:
key = col[0]
keys.append(key)
fmts.append('?')
except:
print 'IGNORE: failed to %s, %r' % (sql, tb.format_exc())
oneData = dData[0]
for key in oneData.iterkeys():
keys.append(key)
fmts.append('?')
manyValues = []
for oneData in dData:
values = []
for key in keys:
value = oneData.get(key)
if value is None:
keyU = _2unicode(key)
value = oneData.get(keyU)
if isinstance(value, list) or isinstance(value, dict):
save_v = json.dumps(value)
#sqlite3.ProgrammingError:
# You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
# (like text_factory = str).
# It is highly recommended that you instead just switch your application to Unicode strings.
elif isinstance(value, basestring):
save_v = _2unicode(value)
else:
save_v = value
values.append(save_v)
manyValues.append(tuple(values))
try:
sql = 'insert into %s values(%s)' % (name, string.join(fmts, ','))
self.cursor.executemany(sql, manyValues)
except:
print 'failed to %s, %r' % (sql, tb.format_exc())
return False
print '%s(%d) is inserted done' % (name, len(manyValues))
return True