Python SQLite operation example (supporting simultaneous multi-process/thread operations)

Python SQLite operation exampleCode:

 Import timeimport threadingimport sqlite3def nomal_producer (conn ):  '''  @ Summary: Producer defination  ''' Counter = 0  Conn. isolation_level = None conn. row_factory = Sqlite3.row  While  True: # insert to DB cur = Conn. cursor () cur.exe cute ( "  Insert into Datas (content, flag) values (?, ?);  " ,( "  Content % s  " % Counter, false) Counter = Counter + 1  # Conn. Commit () Time. Sleep (  0.1  ) Def nomal_consumer (conn ):  '''  @ Summary: Consumer defination  ''' Conn. isolation_level = None conn. row_factory = Sqlite3.row  While  True :#  Select  Data cur = Conn. cursor () cur.exe cute (  "  Select * From datas order by ID limit 10;  "  ) Records = Cur. fetchall ()  If Len (Records)>0  : Print  "  Begin to delete:  "  Print records # delete records  For R In  Records: conn.exe cute (  "  Delete from datas where id = ?;  " , (R [ "  ID  " ],) Time. Sleep (  0.5  )  If _ Name _ = "  _ Main __  "  : # Init dB Conn = Sqlite3.connect ( '  ./DB. SQLite  ' , Check_same_thread = False) # Conn = Sqlite3.connect ( '  ./DB. SQLite '  ) # Init thread producer = Threading. Thread (target = nomal_producer, argS = (Conn,) Consumer = Threading. Thread (target = nomal_consumer, argS = (Conn,) # Start threads producer. Start () Consumer. Start () 

In the sample code for multi-process SQLite operations, the producer and consumer modes are used for processing. Nothing special is found, but note that when establishing a connection to sqlite3, you need to set check_same_thread = false.
In addition, to achieve real thread-safe, you can further encapsulate sqlite3 of python to achieve that there is only one thread operating SQLite. The principle is very simple, it is to use queue to process all operation requests and return the results to another queue at the same time. The sample code is as follows:

 Import sqlite3  From  Queue import queue  From Threading import thread  Class  Sqlitemultithread (thread ):  """ Wrap SQLite connection In A way that allows concurrent requests From  Multiple Threads. This  Is  Done by internally queueing the requests and processing them sequentially  In A separate thread ( In  The same order they arrived ). """  Def _ init _ (self, filename, autocommit, journal_mode): Super (sqlitemultithread, self). _ init _ () self. filename = Filename self. autocommit = Autocommit self. journal_mode = Journal_mode self. reqs = Queue () # Use Request queue of unlimited size self. setdaemon (true) # python2.  5 - Compatible self. Start () def run (Self ):  If  Self. autocommit: Conn = Sqlite3.connect (self. filename, isolation_level = none, check_same_thread = False)  Else  : Conn = Sqlite3.connect (self. filename, check_same_thread = False) conn.exe cute (  '  Pragma journal_mode = % s  ' % Self. journal_mode) Conn. text_factory = STR cursor = Conn. cursor () cursor.exe cute (  ' Pragma synchronous = off  '  )  While  True: req, ARG, res = Self. reqs. Get  ()  If Req = '  -- Close --  '  :  Break  Elif req = ' -- Commit --  '  : Conn. Commit ()  Else  : Cursor.exe cute (req, ARG)  If  Res:  For REC In  Cursor: res. Put (REC) res. Put (  '  -- No more --  '  ) If  Self. autocommit: conn. Commit () Conn. Close () def execute (self, req, ARG = None, Res = None ):  """ 'Execute 'callare non-blocking: Just queue up the request and Return  Immediately.  """  Self. reqs. Put (req, Arg or tuple (), Res) def executemany (self, req, items ):  For Item In  Items: self.exe cute (req, item) def Select (Self, req, Arg = None ):  """ Unlike SQLite '  S native select, this select doesn  '  T handle iteration efficiently. The result'  Select 'Starts filling up with values As Soon As  The request  Is Dequeued, and although you can iterate over the result normally ('  For Res In Self. Select ():... '), The entire result will be In  Memory.  """ Res = Queue () # results of Select Will appear As Items In   This  Queue self.exe cute (req, ARG, Res) While  True: REC = Res. Get  ()  If Rec = '  -- No more --  '  :  Break              Yield  REC def select_one (self, req, ARG = None ):  """ Return only the first row of the select, or none if there are no matching rows.  """          Try  :  Return ITER (self. Select  (Req, ARG). Next () Doesn't stopiteration:  Return  None def commit (Self): self.exe cute (  '  -- Commit --  '  ) Def close (Self): self.exe cute ( '  -- Close --  '  ) # Endclass sqlitemultithread