dao.py 1.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
  1. import sqlite3
  2. import queue
  3. def singleton(cls):
  4. instances = {}
  5. def _singleton(*args, **kw):
  6. if cls not in instances:
  7. instances[cls] = cls(*args, **kw)
  8. return instances[cls]
  9. return _singleton
  10. @singleton
  11. class SQLiteUtil(object):
  12. __queue_conn = queue.Queue(maxsize=1)
  13. __path = None
  14. def __init__(self, path):
  15. self.__path = path
  16. print('path:', self.__path)
  17. self.__create_conn()
  18. def __create_conn(self):
  19. conn = sqlite3.connect(self.__path, check_same_thread=False)
  20. self.__queue_conn.put(conn)
  21. def __close(self, cursor, conn):
  22. if cursor is not None:
  23. cursor.close()
  24. if conn is not None:
  25. cursor.close()
  26. self.__create_conn()
  27. def execute_query(self, sql, params):
  28. conn = self.__queue_conn.get()
  29. cursor = conn.cursor()
  30. try:
  31. if not params is None:
  32. records = cursor.execute(sql, params).fetchall()
  33. else:
  34. records = cursor.execute(sql).fetchall()
  35. field = [i[0] for i in cursor.description]
  36. value = [dict(zip(field, i)) for i in records]
  37. finally:
  38. self.__close(cursor, conn)
  39. return value
  40. def execute(self, sql, params):
  41. conn = self.__queue_conn.get()
  42. cursor = conn.cursor()
  43. try:
  44. if not params is None:
  45. cursor.execute(sql, params)
  46. else:
  47. cursor.execute(sql)
  48. conn.commit()
  49. except Exception:
  50. conn.rollback()
  51. finally:
  52. self.__close(cursor, conn)
  53. def executescript(self, sql):
  54. conn = self.__queue_conn.get()
  55. cursor = conn.cursor()
  56. try:
  57. cursor.executescript(sql)
  58. conn.commit()
  59. except Exception:
  60. conn.rollback()
  61. raise
  62. finally:
  63. self.__close(cursor, conn)