path: root/cgi/database.py

# coding=utf-8

import threading
import MySQLdb
import MySQLdb.cursors
from settings import Settings

database_lock = threading.Lock()

try:
    # Although SQLAlchemy is optional, it is highly recommended
    import sqlalchemy.pool as pool
    _mysql = pool.manage(module=_mysql,
                         pool_size=Settings.DATABASE_POOL_SIZE,
                         max_overflow=Settings.DATABASE_POOL_OVERFLOW)
    Settings._.USING_SQLALCHEMY = True
except ImportError:
    pass


def OpenDb():
    if Settings._.CONN is None:
        Settings._.CONN = MySQLdb.connect(host=Settings.DATABASE_HOST,
                                          user=Settings.DATABASE_USERNAME,
                                          passwd=Settings.DATABASE_PASSWORD,
                                          db=Settings.DATABASE_DB,
                                          cursorclass=MySQLdb.cursors.SSDictCursor)


def FetchAll(query, params=None):
    """
    Query and fetch all results as a list
    """
    db = Settings._.CONN

    c = db.cursor()
    c.execute(query, params)
    return c.fetchall()


def FetchOne(query, params=None):
    """
    Query and fetch only the first result
    """
    db = Settings._.CONN

    c = db.cursor()
    c.execute(query, params)
    return c.fetchone()


def UpdateDb(query, params=None):
    """
    Update the DB (UPDATE/DELETE) and return # of affected rows
    """
    db = Settings._.CONN

    c = db.cursor()
    c.execute(query, params)
    return c.rowcount


def InsertDb(query, params=None):
    """
    Insert into the DB and return the primary key of new row
    """
    db = Settings._.CONN

    c = db.cursor()
    c.execute(query, params)
    return c.lastrowid


def CloseDb():
    if Settings._.CONN is not None:
        Settings._.CONN.close()
        Settings._.CONN = None