python記憶體資料庫pydblite

標籤：

 Pure-Python engine

最近由於項目開發中發現python informixDB模組對多線程的支援非常不好，當開啟兩個線程同時串連informix資料庫的時候，資料庫會報錯，顯示SQL process進行中當中，根據python 多線程的機制我們懷疑是串連資料庫時，informix將兩個線程的cursor識別為同一個，故資料庫報錯。通過python中multiprocess模組講所有關於資料庫的操作全部改為多進程。

但是這就帶來另外一個問題，在之前多線程的情況下，項目中維護著一個Queue，裡面儲存著若干已經建立好的informix tenant pool 的instance資訊，用於客戶快速擷取可用資料庫資源。但是有了多進程的存在，每次客戶取一個instance，主進程都需要從Queue中取take一個出來，與此同時take()操作會觸發建立一個tenant instance，裡邊包含一個進程將instance資訊再次儲存到Queue中。這裡會涉及到Queue的進程間通訊的問題。需要將Queue改為multiprocess.queue才能避免資料丟失。這裡我想嘗試一下用記憶體資料庫來試著簡化進程通訊的步驟。

• import class Base from module PyDbLite : from PyDbLite import Base
• create a database instance, passing it a path in the file system : db = Base(‘dummy‘)
• for a new database, define the field names : db.create(‘name‘,‘age‘,‘size‘) 
  You don‘t have to define the field types. PyDbLite will accept any value that can be serialized by the cPickle module : strings, Unicode strings, integers, floats, dates and datetimes (instances of the date and datetime classes in the datetime module), user-defined classes, etc
• if the base exists, open it : db.open()
• you can pass a parameter "mode" to the create() method, to specify what you want to do if the base already exists in the file system
  • mode = "open" : db.create(‘name‘,‘age‘,‘size‘,mode="open") opens the database and ignores the field definition
  • mode = "override" : db.create(‘name‘,‘age‘,‘size‘,mode="override") erases the existing base and creates a new one with the field definition
  • if mode is not specified and the base already exists, an IOError is raised
• insert a new record
  • by keywords : db.insert(name=‘homer‘,age=23,size=1.84) 
    If some fields are missing, they are initialized with the value None
  • by positional arguments : db.insert(‘homer‘,23,1.84) 
    The arguments must be provided in the same order as in the create() method
• save the changes on disk : db.commit() 
  If you don‘t commit the changes, the insertion, deletion and update operations will not be saved on disk. To return to the previous version, just open() it again (this is equivalent to rollback in transactional databases)
• besides the fields passed to the create() method, an internal field called __id__ is added. It is a integer which is guaranteed to be unique and unchanged for each record in the base, so that it can be used as the record identifier
• another internal field called __version__ is also managed by the database engine. It is a integer which is set to 0 when the record is created, then incremented by 1 each time the record is updated. This is used to detect concurrency control, for instance in a web application where 2 users select the same record and want to update it at the same time
• the selection of records uses Python list comprehension syntax : 
  recs = [ r for r in db if 30 > r[‘age‘] >= 18 and r[‘size‘] < 2 ] 
  returns the records in the base where the age is between 18 and 30, and size is below 2 meters. The record is a dictionary, where the key is the field name and value is the field value
• Python generator expression syntax can also be used : 
  for r in (r for r in db if r[‘name‘] in (‘homer‘,‘marge‘) ):
      do_something_with(r) 
  iterates on the records where the name is one of ‘homer‘ or ‘marge‘
• to iterate on all the records : 
  for r in db:
      do_something_with(r)
• a record can be accessed by its identifier : record = db[rec_id] returns the record such that record[‘__id__‘] == rec_id
• finally, a shortcut can be used for simple selections : db(key1=val1,key2=val2) returns the list of records where the keys take the given value. It is equivalent to [ r for r in db if r["key1"]==val1 and r["key2"]==val2], but much more concise
• to speed up selections, an index can be created on a field : db.create_index(‘age‘) 
  When an index is created, the database instance has an attribute (here _age : note the heading underscore, to avoid name conflicts with internal names). This attribute is a dictionary-like object, where keys are the values taken by the field, and values are the records whose field values are egal to the key : 
  records = db._age[23] returns the list of records with age == 23 
  If no record has this value, lookup by this value returns an empty list 
  The index supports iteration on the field values, and the keys() method returns all existing values for the field
• number of records in the base : len(db)
• to delete a record : db.delete(record) or, if you know the record identifier : del db[rec_id]
• to delete a list of records : db.delete(list_of_records) 
  list_of_records can be any iterable (list, tuple, set, etc) yielding records
• to update a record : db.update(record,age=24)
• to add a new field to an existing base and specify a default value : db.add_field(‘new_field‘[,default=v]). If no default is provided, the field value is None
• to drop an existing field : db.drop_field(‘name‘)
• to get the list of fields : db.fields

import pydblite# 使用記憶體資料庫pydb = pydblite.Base("address")# 建立a,b,c三個欄位pydb.create(‘a‘, ‘b‘, ‘c‘)# 為欄位a,b建立索引pydb.create_index(‘a‘, ‘b‘)# 插入一條資料pydb.insert(a=0, b=0, c=1)pydb.insert(a=1, b=0, c=1)pydb.insert(a=1, b=0, c=1)pydb.insert(a=1, b=0, c=1)pydb.update(records=pydb[1],a=2,c="li")pydb.delete(pydb[0])# 查詢符合特定要求的資料results = pydb(a=2)for i in  results:    print results[i]

 

python記憶體資料庫pydblite