python3 sqlite3 数据库连接

python3 sqlite3 数据库创建 & 连接 脚本

# -*- coding:utf-8 -*-
import traceback
import sqlite3
import re
import os

class DB(object):
    def __init__(self, dbname, autocommit = True):        
        self.dbname = dbname
        self.cursor = None
        self.connected = False
        self.autocommit = autocommit
        
    def connect(self):
        if self.autocommit:
            self.conn = sqlite3.connect(self.dbname,isolation_level=None)
        else:
            self.conn = sqlite3.connect(self.dbname)
        self.connected = True
    
    def startTransaction(self):
        if not self.connected:
            self.connect()
    
    def commitTransaction(self):
        self.cursor.close() 
        self.conn.commit()
    
    def endTransaction(self):
        pass
    
    def rollbackTransaction(self):
        self.cursor.close()  
        self.conn.rollback()
        
    def dict_factory(self, cursor, row):
        d = {}
        for index, col in enumerate(cursor.description):
            d[col[0]] = row[index]
        return d
    
    # 正则表达式
    def regexp(self, expr, item):
        '''
        正则表达式函数
        @params expr: 需要搜索的正则表达式
        @params item: sqlite 传入的需要搜索的内容
        '''
        if type(item).__name__ == "bytes":
            item = item.decode()
        reg = re.compile(expr)
        return reg.search(item) is not None
    
    # bytes 转 str
    def bytes2Str(self, expr):
        '''
        bytes 转 str
        在sqlite3 中, blob中获取到的值不能直接和字符串比, 需要先转换成字符串
        @params expr: 需要转换的字符串
        '''
        if expr:
            if type(expr).__name__ == "bytes":
                return expr.decode()
        return expr
        
    def query(self, sql, params=()):
          try:
               if self.connected == False:
                   self.connect()
               self.conn.row_factory = self.dict_factory
               self.conn.create_function("regexp", 2, self.regexp)
               self.conn.create_function("bytes2Str", 1, self.bytes2Str)
               self.cursor = self.conn.cursor()
               self.cursor.execute(sql, params)
               if not self.autocommit:
                   self.conn.commit()
          except (AttributeError, sqlite3.OperationalError) as e:
               self.connect()
               self.conn.row_factory = self.dict_factory
               self.conn.create_function("regexp", 2, self.regexp)
               self.conn.create_function("bytes2Str",1, self.bytes2Str)
               self.cursor = self.conn.cursor()
               self.cursor.execute(sql, params)
               if not self.autocommit:
                   self.conn.commit()
          except sqlite3.Error as e:
              print("Error {0}, sql:({1})".format(e,sql))
              if not self.autocommit:
                self.rollbackTransaction()
              print("{0}".format(e))
              return False
          return self.cursor

    def script(self, SQLScriptStr):
          """执行SQL脚本"""
          try:
               if self.connected == False:
                   self.connect()
               self.conn.row_factory = self.dict_factory
               self.conn.create_function("regexp", 2, self.regexp)
               self.conn.create_function("bytes2Str", 1, self.bytes2Str)
               self.cursor = self.conn.cursor()
               self.cursor.executescript(SQLScriptStr)
               if not self.autocommit:
                   self.conn.commit()
          except (AttributeError, sqlite3.OperationalError) as e:
               self.connect()
               self.conn.row_factory = self.dict_factory
               self.conn.create_function("regexp", 2, self.regexp)
               self.conn.create_function("bytes2Str",1, self.bytes2Str)
               self.cursor = self.conn.cursor()
               self.cursor.executescript(SQLScriptStr)
               if not self.autocommit:
                   self.conn.commit()
          except sqlite3.Error as e:
              print("Error {0}, sql:({1})".format(e,SQLScriptStr))
              if not self.autocommit:
                self.rollbackTransaction()
              print("{0}".format(e))
              return False
          return self.cursor

    def getInsertId(self):
          """"获取最近插入记录的rowid"""
          returnid = None
          try:
               returnid = self.query("select last_insert_rowid()").fetchone().get("last_insert_rowid()")
          except (AttributeError, sqlite3.OperationalError):
               self.connect()
               returnid =self.query("select last_insert_rowid()").fetchone().get("last_insert_rowid()")
          except sqlite3.Error as e:
               print("{0}".format(e))
          return returnid
      
    def close(self):
        if  hasattr(self, 'conn') and self.conn:
            self.conn.close()


def check_db(dbname, create_sql):
    """检查数据库，如果没有就创建"""
    print("start check db")
    local_path = os.path.dirname(os.path.abspath(__file__))
    db_path = os.path.join(local_path, dbname)
    dir_path = os.path.dirname(db_path)
    
    if not os.path.exists(db_path):
        print("not find db: {0}, start create".format(db_path))
        
        if not os.path.exists(dir_path):
            print("not find dir: {0}, start make it".format(dir_path))
            try:
                os.makedirs(dir_path)
            except  Exception as e:
                print("make dir: {0} failed:{1}".format(dir_path, traceback.format_exc()))
                print("check db finished")
                return False
        try:
            db = DB(dbname=dbname)
            db.script(create_sql)
            if os.path.exists(db_path):
                print("create db: {0} success".format(db_path))
            else:
                print("create db: {0} failed".format(db_path))
        except Exception as e:
            print("create db: {0} failed, error: {1}".format(db_path, traceback.format_exc()))
            return False
            
    """ 暂时不需要更新数据库表
    else:
        print("start run update_sql")
        try:
            db =  DB(dbname=dbname)
            db.script(update_sql)
        except Exception as e:
            print("run update_sql failed: {0}".format(traceback.format_exc()))
    print("check db finished")
    """
    return True

if __name__ == "__main__":
    dbname = "test.db"
    create_sql = """
        CREATE TABLE IF NOT EXISTS "user_info" (
            "id"	INTEGER,
            "name" char(64),
            PRIMARY KEY("id" AUTOINCREMENT)
    );
    """
    check_db(dbname=dbname, create_sql=create_sql)
    db = DB(dbname=dbname)
    insert_sql = """insert into user_info(name) values('John');"""
    db.query(insert_sql)
    
    get_sql = """select * from user_info;"""
    user_info = db.query(get_sql).fetchone()
    print(user_info)
    
    db.close()