python 与sqlite3

主旨

本文主要是为了加强大家对sqlite3数据库在多线程使用环境下理解

正确的使用方法

1.最简单在python 多线程中使用sqlite3的方法是每个线程创建一个数据库的连接。

2.参考SQLAIchemy一样弄个连接池,或者直接使用它。

3.其他方式

写本文的原因

cannot start a transaction within a transaction 或者 no transaction is active,解决方法就是一个连接不能在多个线程中同时使用.

database is locked暂时不在本文的讨论范围

经仔细查证，发现此处代码如下(此处是仿照的代码)：

# 生成数据库

conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)

c = conn.cursor()

c.execute("create table firms (founded, hq, name, rev, size, type)")

c.execute("insert into firms ( name ) values (?) ", ("bar", ))

conn.commit()

bug代码

#coding: utf-8

import sqlite3

import time

import thread

import threading



def update_f1(information):

    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])

    where = ' WHERE name == :name'

    values = information

    query = 'UPDATE firms SET ' + updates + where

    print query

    try:

        c.execute(query, values)

        time.sleep(3)

        conn.commit()

    except Exception, e:

        print e

    print "commit"



lock = threading.Lock()



conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)

c = conn.cursor()



thread.start_new_thread(update_f1, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))

print c.execute('select * from firms').fetchall()

conn.close()

time.sleep(1000)

看如上代码，发现在主线程和其他线程都有对数据库的操作,而且使用同一个连接，于是很容易猜测出估计是多线程中commit的问题。

正确的方法

每个线程一个连接

这个方法是正确的，但是丑陋的方法.

import sqlite3

import time

import thread

import threading

def update_normal(information):

    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])

    where = ' WHERE name == :name'

    values = information

    query = 'UPDATE firms SET ' + updates + where

    print query

    conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)

    c = conn.cursor()

    print "exec"



    try:

        c.execute(query, values)

        time.sleep(3)

        conn.commit()

    except:

        conn.rollback()

        raise

    finally:

        conn.close()

    print "commit"



for i in range(10):

    thread.start_new_thread(update_normal, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))



update(dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'))

print c.execute('select * from firms').fetchall()



conn.close()

time.sleep(1000)

连接池

一个连接只能同时被一个地方获取

class DbPool(object):                                                              

    """docstring for DbPool"""                                                     

    # TODO: may be need auto auto creat                                            



    def __init__(self, count=20, auto_get=True):                                   

        # count = 20                                                               

        db_queue = Queue.Queue(count)                                              

        for i in range(count):                                                     

            db = get_db_instance()                                                 

            db_queue.put(db)                                                       

        self._queue = db_queue                                                     

        self.item = self._queue.get() if auto_get else None                        



    def __enter__(self):                                                        

        if self.item is None:                                                   

            self.item = self._queue.get()                                       

        return self.item                                                        



    def __exit__(self, Type, value, traceback):                                 

        if self.item is not None:                                               

            self._queue.put(self.item)                                          

            self.item = None                                                    



    def __del__(self):                                                          

        if self.item is not None:                                               

            self._queue.put(self.item)                                          

            self.item = None                     



if __name__ == '__main__':                                                                                                              

    db_pool = DbPool(count=20)                                                  

    with db_pool as db:                                                         

        print db                                                                

    for i in range(40):                                                         

        with db_pool as db:                                                     

            print db    

连接池2

每个线程使用thread.local去实现不并发同一个连接的写操作

threading_data = threading.local() 
    def get_db():                                                               
        db_instance = getattr(threading_data, 'db_instance', None)              
        if db_instance is None:                                                 
            threading_data.db_instance = 你的连接数据库             
        return threading_data.db_instance

错误的方法

单纯共享连接

这个是最容易出错的类型

import sqlite3

import time

import thread

import threading



lock = threading.Lock()



conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)

c = conn.cursor()



def update_f2(information):

    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])

    where = ' WHERE name == :name'

    values = information

    query = 'UPDATE firms SET ' + updates + where

    print query

    try:

        time.sleep(3)

        c.execute(query, values)

         time.sleep(3)

        conn.commit()

    except Exception, e:

        print e

    print "commit"



for i in range(10):

    thread.start_new_thread(update_f2, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))



update(dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'))

print c.execute('select * from firms').fetchall()

conn.close()

time.sleep(1000)

其他资料

python 官方 多线程sqlite3