1  
   2  # table schema
   3  # CREATE TABLE person (id COUNTER, name VARCHAR)
   4  
   5  db = e32db.Dbms()
   6  db.begin()  # begin transaction (lock other inserts)
   7  db.execute(u"INSERT INTO person(name) VALUES 'korakot' ") # insert a new row
   8  
   9  dbv = e32db.Db_view()
  10  dbv.prepare(db, u"SELECT id FROM person ORDER BY id DESC")
  11  dbv.first_line()
  12  dbv.get_line()
  13  last_id = dbv.col(1)  # get it!
  14  
  15  db.commit()  # commit the transaction

   1  
   2  CREATE TABLE person (id COUNTER, name VARCHAR)
   3  CREATE UNIQUE INDEX id_index ON person(id)

   1  
   2  from __future__ import generators
   3  import e32db, re
   4  db = e32db.Dbms()
   5  dbv = e32db.Db_view()
   6  db.open(u'C:\\test.db')
   7  
   8  # Some helping classes (need more in next version)
   9  class String:
  10      pass
  11  
  12  class Integer:
  13      pass
  14  
  15  class Float:
  16      pass
  17  
  18  class column:
  19      def __init__(self, coltype):
  20          self.coltype = coltype

   1  
   2  class Mapper(object):
   3      def __init__(self, id=None, **kw):
   4          if id is None:
   5              self.id = self._insert(**kw)
   6          else:
   7              self.id = id
   8      def _insert(self, **kw):
   9          names = ','.join(kw.keys())
  10          values = ','.join([self.quote(k,v) for k,v in kw.items()])
  11          tablename = self.__class__.__name__
  12          q = u"INSERT INTO %s(%s) VALUES (%s)" % (tablename, names, values)
  13          db.execute(q)
  14          # get last insert ID
  15          dbv.prepare(db, u'SELECT id FROM '+tablename+' ORDER BY id DESC')
  16          dbv.first_line()
  17          dbv.get_line()
  18          return dbv.col(1)
  19      def __getattr__(self, name):
  20          if name in self.mapping.__dict__:
  21              q = 'SELECT '+name+' FROM '+self.__class__.__name__
  22              q += ' WHERE id='+str(self.id)
  23              dbv.prepare(db, unicode(q))
  24              dbv.first_line()
  25              dbv.get_line()
  26              return dbv.col(1)
  27          else:
  28              return self.__dict__[name]
  29      def __repr__(self):
  30          return '<%s id=%d>' % (self.__class__.__name__, self.id)
  31      def quote(self, name, value):
  32          if self.mapping.__dict__[name].coltype == String:
  33              return "'%s'" % value.replace("'", "''")  # encode single quote
  34          else:
  35              return str(value)
  36      def __setattr__(self, name, value):
  37          if name in self.mapping.__dict__:
  38              q = 'UPDATE '+self.__class__.__name__+' SET '+name+'='
  39              q += self.quote(name, value) + " WHERE id=" + str(self.id)
  40              db.execute(unicode(q))
  41          else:
  42              self.__dict__[name] = value
  43      def set(self, **kw):
  44          q = "UPDATE "+self.__class__.__name__+" SET "
  45          for k, v in kw.items():
  46              q += k+'='+self.quote(k,v)+','
  47          q = q[:-1]+" WHERE id=%s" % self.id
  48          db.execute(unicode(q))
  49      def delete(self):
  50          q = 'DELETE FROM '+self.__class__.__name__+" WHERE id=" + str(self.id)
  51          db.execute(unicode(q))
  52          self.id = None
  53      def dict(self):
  54          names = [k for k in self.mapping.__dict__.keys() if not k.startswith('__')]
  55          q = 'SELECT '+','.join(names)+' FROM '+self.__class__.__name__
  56          q += ' WHERE id=' + str(self.id)
  57          dbv.prepare(db, unicode(q))
  58          dbv.first_line()
  59          dbv.get_line()
  60          dct = {'id': self.id}
  61          for i in range(dbv.col_count()):
  62              dct[names[i]] = dbv.col(i+1)
  63          return dct
  64      def select(cls, where=None, orderby=None):
  65          q = 'SELECT id FROM '+cls.__name__
  66          if where:
  67              q += ' WHERE '+where
  68          if orderby:
  69              q += ' ORDER BY '+orderby
  70          dbv = e32db.Db_view()  # need its own cursor
  71          dbv.prepare(db, unicode(q))
  72          dbv.first_line()
  73          for i in range(dbv.count_line()):
  74              dbv.get_line()
  75              yield cls(dbv.col(1))
  76              dbv.next_line()
  77      select = classmethod(select)

   1  
   2  class Person(Mapper):
   3      class mapping:
   4          # doesn't need id = column(Integer)
   5          name = column(String)
   6          age = column(Integer)

   1  
   2  >>> import e32db
   3  >>> db = e32db.Dbms()
   4  >>> dbv = e32db.Db_view()
   5  >>> db.open(u'C:\\test.db')  # might need db.create(...)

   1  
   2  def Q(sql):
   3      if sql.upper().startswith('SELECT'):
   4          dbv.prepare(db, unicode(sql))
   5          dbv.first_line()
   6          rows = []
   7          maxlen = [0] * dbv.col_count()
   8          for i in range(dbv.count_line()):
   9              dbv.get_line()
  10              result = []
  11              for i in range(dbv.col_count()):
  12                  try:
  13                      val = dbv.col(i+1)
  14                  except:    # in case coltype 16
  15                      val = None
  16                  result.append(val)
  17                  maxlen[i] = max(maxlen[i], len(str(val)))
  18              rows.append(result)
  19              dbv.next_line()
  20          fmt = '|'+ '|'.join(['%%%ds' % n for n in maxlen]) + '|'
  21          for row in rows:
  22              print fmt % tuple(row) 
  23      else:
  24          n = db.execute(unicode(sql))
  25          print '%d rows affected' % n

   1  
   2  >>> Q("CREATE TABLE person (id COUNTER, name VARCHAR)")
   3  0 rows affected
   4  >>> Q("INSERT INTO person(name) VALUES ('Korakot')")
   5  1 rows affected
   6  >>> Q("INSERT INTO person(name) VALUES ('morning_glory')")
   7  1 rows affected
   8  >>> Q("SELECT * from person")
   9  |0|      Korakot|
  10  |1|morning_glory|

   1  
   2  import e32db
   3  db = e32db.Dbms()
   4  dbv = e32db.Db_view()
   5  db.open(u'C:\\system\\data\\bookmarks1.db')
   6  
   7  def select_row(query):
   8      dbv.prepare(db, unicode(query))
   9      dbv.first_line()
  10      dbv.get_line()
  11      result = []
  12      for i in range(dbv.col_count()):
  13          try:
  14              result.append(dbv.col(i+1))
  15          except:    # in case coltype 16
  16              result.append(None)
  17      return result
  18  
  19  def select_all(query):
  20      dbv.prepare(db, unicode(query))
  21      dbv.first_line()
  22      rows = []
  23      for i in range(dbv.count_line()):
  24          dbv.get_line()
  25          result = []
  26          for i in range(dbv.col_count()):
  27              try:
  28                  result.append(dbv.col(i+1))
  29              except:    # in case coltype 16
  30                  result.append(None)
  31          rows.append(result)
  32          dbv.next_line()
  33      return rows

   1  
   2  >>> select_row('SELECT * FROM Favourites')  # 1 row
   3  >>> select_all('SELECT * FROM Favourites')  # all rows

   1  
   2  >>> def print_db(arg, dirname, names):
   3  ...   for name in names:
   4  ...     if name.endswith('.db'):
   5  ...       print dirname + '\\' + name
   6  ...
   7  >>> os.path.walk('C:\\', print_db, None)
   8  C:\\SportsDiary.db
   9  C:\\test.db
  10  C:\system\Data\Bookmarks1.db
  11  C:\system\Data\lcscfg.db
  12  C:\system\Data\lcs.db
  13  C:\system\Data\NSmlDSSettings.db
  14  C:\system\Data\SavedDecks1.db
  15  C:\system\Data\midp2\systemams\MIDP2SystemAMSDynamic.db
  16  C:\system\Data\midp2\systemams\MIDP2SystemAMSStatic.db
  17  C:\system\Data\srsplugin\SDDatabase.db
  18  C:\system\Data\vasdb\vasdatabase.db
  19  >>>

   1  
   2  def _sign(val):
   3      if val:
   4          if val > 0: return 1
   5          else: return -1
   6      else:
   7          return val
   8  
   9  #get your db connection, conn
  10  conn.create_function("sign", 1, _sign)
  11  
  12  ...
  13  
  14  >>cur = c.conn.cursor()
  15  >>cur.execute("select test, val from test")
  16  >>cur.fetchall()
  17  [(u'a', None)]
  18  
  19  >>cur.execute("select sign(test), sign(val), sign(0), sign(-99), sign(99) from test")
  20  >>cur.fetchall()
  21  [(1, None, 0, -1, 1)

   1  
   2  import contacts
   3  db = contacts.open()
   4  
   5  all_ids = db.keys() # [159, 161, 273, ...]
   6  c = db[159]  # first contact
   7  found = db.find('jim')  # search in name, email, etc.
   8  jim = found[0]  # first one found
   9  
  10  jim_id = jim.id  # 819
  11  mobile = jim.find('mobile_number')[0].value  # first only
  12  firstname = jim.find('first_name')[0].value
  13  # other fields: email_address, url, company_name, job_title, 
  14  # phone_number, fax_number, note, etc.
  15  
  16  # to add new contact
  17  newc = db.add_contact()
  18  newc.add_field('first_name', 'Korakot')
  19  newc.add_field('mobile_number', '017337330')
  20  newc.commit()

   1  
   2  import e32db
   3  db = e32db.Dbms()
   4  dbv = e32db.Db_view()
   5  db.open(u'C:\\System\\Data\\Contacts.cdb ')  # open database file
   6  
   7  # search and retrieve from a row
   8  def select_row(query):
   9    dbv.prepare(db, unicode(query))
  10    dbv.first_line()
  11    dbv.get_line()
  12    result = []
  13    for i in range(dbv.col_count()):
  14      result.append(dbv.col(i+1))
  15    return result
  16  
  17  # search and retrieve from a column
  18  def select_col(query):
  19    dbv.prepare(db, unicode(query))
  20    dbv.first_line()
  21    result = []
  22    for i in range(dbv.count_line()):
  23      dbv.get_line()
  24      result.append(dbv.col(1))
  25      dbv.next_line()
  26    return result
  27  
  28  # now it's quite easy to query anything, for example
  29  # get the id of my friend "Jakapong"
  30  id, = select_row("select parent_cmid from identitytable where cm_firstname='Jakapong' ")