path: root/cgi/database.py

# coding=utf-8

import threading
import _mysql
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 = _mysql.connect(host=Settings.DATABASE_HOST,
                                         user=Settings.DATABASE_USERNAME,
                                         passwd=Settings.DATABASE_PASSWORD,
                                         db=Settings.DATABASE_DB)


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

    db.query(query)
    r = db.use_result()
    return r.fetch_row(0, method)


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

    db.query(query)
    r = db.use_result()
    try:
        return r.fetch_row(1, method)[0]
    except:
        return None


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

    db.query(query)
    return db.affected_rows()


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

    db.query(query)
    return db.insert_id()


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