Python DBconnect.select_query方法代碼示例

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def save_forum(name, short_name, user):
    DBconnect.exist(entity="Users", identifier="email", value=user)
    forum = DBconnect.select_query(
        'select id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
    )
    if len(forum) == 0:
        DBconnect.update_query('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)',
                               (name, short_name, user, ))
        forum = DBconnect.select_query(
            'select id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
        )
    return forum_description(forum)

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def followers_list(email, type, params):
    DBconnect.exist(entity="Users", identifier="email", value=email)
    if type == "follower":
        where = "followee"
    if type == "followee":
        where = "follower"

    query = "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type + \
            " WHERE " + where + " = %s "

    if "since_id" in params:
        query += " AND Users.id >= " + str(params["since_id"])
    if "order" in params:
        query += " ORDER BY Users.name " + params["order"]
    else:
        query += " ORDER BY Users.name DESC "
    if "limit" in params:
        query += " LIMIT " + str(params["limit"])

    followers_ids_tuple = DBconnect.select_query(query=query, params=(email, ))

    f_list = []
    for id in followers_ids_tuple:
        id = id[0]
        f_list.append(users.details(email=id))

    return f_list

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def details(id, related):
    thread = DBconnect.select_query(
        'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
        'FROM Threads WHERE id = %s', (id, )
    )
    if len(thread) == 0:
        raise Exception('No thread exists with id=' + str(id))
    thread = thread[0]
    thread = {
        'date': str(thread[0]),
        'forum': thread[1],
        'id': thread[2],
        'isClosed': bool(thread[3]),
        'isDeleted': bool(thread[4]),
        'message': thread[5],
        'slug': thread[6],
        'title': thread[7],
        'user': thread[8],
        'dislikes': thread[9],
        'likes': thread[10],
        'points': thread[11],
        'posts': thread[12],
    }

    if "user" in related:
        thread["user"] = users.details(thread["user"])
    if "forum" in related:
        thread["forum"] = forums.details(short_name=thread["forum"], related=[])

    return thread

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def post_query(id):
    post = DBconnect.select_query('select date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
                       'isHighlighted, isSpam, likes, message, parent, points, thread, user '
                       'FROM Posts WHERE id = %s', (id, ))
    if len(post) == 0:
        return None
    return post_formated(post)

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def threads_list(entity, identifier, related, params):
    if entity == "forum":
        DBconnect.exist(entity="Forums", identifier="short_name", value=identifier)
    if entity == "user":
        DBconnect.exist(entity="Users", identifier="email", value=identifier)
    query = "SELECT id FROM Threads WHERE " + entity + " = %s "
    parameters = [identifier]

    if "since" in params:
        query += " AND date >= %s"
        parameters.append(params["since"])
    if "order" in params:
        query += " ORDER BY date " + params["order"]
    else:
        query += " ORDER BY date DESC "
    if "limit" in params:
        query += " LIMIT " + str(params["limit"])

    thread_ids_tuple = DBconnect.select_query(query=query, params=parameters)
    thread_list = []

    for id in thread_ids_tuple:
        id = id[0]
        thread_list.append(details(id=id, related=related))

    return thread_list

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def create(date, thread, message, user, forum, optional):
    DBconnect.exist(entity="Threads", identifier="id", value=thread)
    DBconnect.exist(entity="Forums", identifier="short_name", value=forum)
    DBconnect.exist(entity="Users", identifier="email", value=user)
    if len(DBconnect.select_query("SELECT Threads.id FROM Threads JOIN Forums ON Threads.forum = Forums.short_name "
                                "WHERE Threads.forum = %s AND Threads.id = %s", (forum, thread, ))) == 0:
        raise Exception("no thread with id = " + str(thread) + " in forum " + forum)
    if "parent" in optional:
        if len(DBconnect.select_query("SELECT Posts.id FROM Posts JOIN Threads ON Threads.id = Posts.thread "
                                    "WHERE Posts.id = %s AND Threads.id = %s", (optional["parent"], thread, ))) == 0:
            raise Exception("No post with id = " + optional["parent"])
    query = "INSERT INTO Posts (message, user, forum, thread, date"
    values = "(%s, %s, %s, %s, %s"
    parameters = [message, user, forum, thread, date]

    for param in optional:
        query += ", " + param
        values += ", %s"
        parameters.append(optional[param])

    query += ") VALUES " + values + ")"

    update_thread_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"

    con = DBConnection()
    con = con.connect()
    con.autocommit(False)
    with con:
        cursor = con.cursor()
        try:
            con.begin()
            cursor.execute(update_thread_posts, (thread, ))
            cursor.execute(query, parameters)
            con.commit()
        except Exception as e:
            con.rollback()
            raise Exception("Database error: " + e.message)
        post_id = cursor.lastrowid
        cursor.close()

    con.close()
    post = post_query(post_id)
    del post["dislikes"]
    del post["likes"]
    del post["parent"]
    del post["points"]
    return post

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def save_subscription(email, thread_id):
    DBconnect.exist(entity="Threads", identifier="id", value=thread_id)
    DBconnect.exist(entity="Users", identifier="email", value=email)
    subscription = DBconnect.select_query(
        'select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
    )
    if len(subscription) == 0:
        DBconnect.update_query('INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, ))
        subscription = DBconnect.select_query(
            'select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
        )

    response = {
        "thread": subscription[0][0],
        "user": subscription[0][1]
    }
    return response

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def save_user(email, username, about, name, optional):
    isAnonymous = 0
    if "isAnonymous" in optional:
        isAnonymous = optional["isAnonymous"]
    try:
        user = DBconnect.select_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
                           (email, ))
        if len(user) == 0:
            DBconnect.update_query(
                'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
                (email, about, name, username, isAnonymous, ))
        user = DBconnect.select_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
                           (email, ))
    except Exception as e:
        raise Exception(e.message)

    return user_format(user)

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def details(email):
    user = DBconnect.select_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, ))
    user = user_format(user)
    if user is None:
        raise Exception("No user with email " + email)
    user["followers"] = followers(email, "follower")
    user["following"] = followers(email, "followee")
    user["subscriptions"] = user_subscriptions(email)
    return user

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def save_thread(forum, title, isClosed, user, date, message, slug, optional):
    DBconnect.exist(entity="Users", identifier="email", value=user)
    DBconnect.exist(entity="Forums", identifier="short_name", value=forum)

    isDeleted = 0
    if "isDeleted" in optional:
        isDeleted = optional["isDeleted"]
    thread = DBconnect.select_query(
        'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
        'FROM Threads WHERE slug = %s', (slug, )
    )
    if len(thread) == 0:
        DBconnect.update_query('INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted) '
                               'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
                               (forum, title, isClosed, user, date, message, slug, isDeleted, ))
        thread = DBconnect.select_query(
            'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
            'FROM Threads WHERE slug = %s', (slug, )
        )
    thread = thread[0]
    response = {
        'date': str(thread[0]),
        'forum': thread[1],
        'id': thread[2],
        'isClosed': bool(thread[3]),
        'isDeleted': bool(thread[4]),
        'message': thread[5],
        'slug': thread[6],
        'title': thread[7],
        'user': thread[8],
        'dislikes': thread[9],
        'likes': thread[10],
        'points': thread[11],
        'posts': thread[12],
    }

    # Delete few extra elements
    del response["dislikes"]
    del response["likes"]
    del response["points"]
    del response["posts"]

    return response

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def remove_follow(email1, email2):
    follows = DBconnect.select_query(
        'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, )
    )

    if len(follows) != 0:
        DBconnect.update_query('DELETE FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
    else:
        raise Exception("No such following")

    return users.details(email1)

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def followers(email, type):
    where = "followee"
    if type == "follower":
        where = "followee"
    if type == "followee":
        where = "follower"
    f_list = DBconnect.select_query(
        "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type +
        " WHERE " + where + " = %s ", (email, )
    )
    return tuple2list(f_list)

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def details(short_name, related):
    forum = DBconnect.select_query(
        'select id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
    )
    if len(forum) == 0:
        raise ("No forum with exists short_name=" + short_name)
    forum = forum_description(forum)

    if "user" in related:
        forum["user"] = users.details(forum["user"])
    return forum

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def remove_subscription(email, thread_id):
    DBconnect.exist(entity="Threads", identifier="id", value=thread_id)
    DBconnect.exist(entity="Users", identifier="email", value=email)
    subscription = DBconnect.select_query(
        'select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
    )
    if len(subscription) == 0:
        raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
    DBconnect.update_query('DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))

    response = {
        "thread": subscription[0][0],
        "user": subscription[0][1]
    }
    return response

# 需要導入模塊: from API.tools import DBconnect [as 別名]
# 或者: from API.tools.DBconnect import select_query [as 別名]
def add_follow(email1, email2):
    DBconnect.exist(entity="Users", identifier="email", value=email1)
    DBconnect.exist(entity="Users", identifier="email", value=email2)

    if email1 == email2:
        raise Exception("User with email=" + email1 + " can't follow himself")

    follows = DBconnect.select_query(
        'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, )
    )

    if len(follows) == 0:
        DBconnect.update_query('INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (email1, email2, ))

    user = users.details(email1)
    return user