def reset(): cursor.execute( "DELETE FROM levelup.messages" ) cursor.execute( "ALTER TABLE levelup.messages AUTO_INCREMENT=1" )
def add_message(player_name, message): cursor.execute( "INSERT INTO levelup.messages (" "SENDER, MESSAGE" ") VALUES (" "%s, %s" ")", (player_name, message) )
def Aurora_InsertNote(deviceid, initials, text): # 0 = deviceid # 1 = datetime # 2 = initials # 3 = text cmd = """\ EXEC Aurora_InsertNote null, '{0}', 'I', 'Devices', 'Config Device', null, '{1}', '{2}', true """.format(deviceid, initials, text) cursor.execute(cmd)
def GetDeviceByDeviceId(deviceid): cmd = """\ EXEC GetDeviceByDeviceId {0} """.format(deviceid) cursor.execute(cmd) return_value = cursor.fetchall() return return_value
def GetLastDeviceIdByRange(bottom, top): cmd = """\ DECLARE @DeviceId int; EXEC GetLastDeviceIdByRange {0}, {1}, @DeviceId out; SELECT @DeviceId AS return_value """.format(bottom, top) cursor.execute(cmd) return_value = cursor.fetchval() return return_value
def get(self): cursor.execute(self.Q1) posts = [] for element in cursor: posts.append({ "postId": element[0], "accountId": element[1], "timestamp": element[2], "madeWith": element[3], "post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}", # This would be get url link in the future "title": element[5], "Captions": element[6], "likes": element[7], "isPrivate": element[8] }) return {"Posts": posts}
def UpdateDeviceProvisioned(datenow, deviceid, apn, server, port, script, logFileName): _LogFileName = logFileName """ 0 = deviceid 1 = apn 2 = server 3 = port 4 = firmware 5 = package 6 = script 7 = _LogFileName """ cmd = """\ EXEC UpdateDeviceProvisioned {0}, '{1}', '{2}', {3}, '{4}', '{5}', '{6}', '{7}' """.format(deviceid, apn, server, port, getFirmware(), getPackage(), script, _LogFileName) cursor.execute(cmd)
def get(self): cursor.execute(self.Q1) trending = [] others = [] count = 0 for element in cursor: trending.append({ "postId": element[0], "accountId": element[1], "timestamp": element[2], "madeWith": element[3], "post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}", # This would be get url link in the future "title": element[5], "Captions": element[6], "likes": element[7], "isPrivate": element[8] }) count += 1 if count == 6: break cursor.execute(self.Q1) for element in cursor: others.append({ "postId": element[0], "accountId": element[1], "timestamp": element[2], "madeWith": element[3], "post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}", # This would be get url link in the future "title": element[5], "Captions": element[6], "likes": element[7], "isPrivate": element[8] }) return {"Others": others, "Trending": trending}
def get(self, id): """ :param id: uesr id that referes to the database :return: Json object that has a list of posts """ cursor.execute(self.get_sql_command(id)) posts = [] for element in cursor: if element[8] == 0: posts.append({ "postId": element[0], "accountId": element[1], "timestamp": element[2], "madeWith": element[3], "post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}", # This would be get url link in the future "title": element[5], "Captions": element[6], "likes": element[7], "isPrivate": element[8] }) return {"Posts": posts}
from constants import cursor import mysql.connector Q1 = "CREATE DATABASE EnlightenmentApi" # Create a database Q2 = "CREATE TABLE Posts (postId INT PRIMARY KEY AUTO_INCREMENT, accountId INT, timestamp INT, madeWith VARCHAR(100), fileLocation VARCHAR(300), title VARCHAR(300), captions VARCHAR(500), likes INT DEFAULT 0, isPrivate BOOL)" # Table one Q3 = "CREATE TABLE Tags (postId INT, FOREIGN KEY(postId) REFERENCES Posts(postId), tag1 VARCHAR(100), tag2 VARCHAR(100), tag3 VARCHAR(100 ))" # Table two Q4 = "CREATE TABLE Accounts (accountId INT PRIMARY KEY AUTO_INCREMENT, accountName VARCHAR(100), accountPassword VARCHAR(100), posts INT DEFAULT 0)" Q5 = "DROP TABLE Accounts" cursor.execute(Q2) cursor.execute(Q3) cursor.execute(Q4) cursor.execute("DESCRIBE Posts") for x in cursor: print(x) cursor.execute("DESCRIBE Tags") for x in cursor: print(x)
def get_positions(): cursor.execute("SELECT POSITION FROM levelup.players") return [player[0] for player in cursor.fetchall()]
def get_position(player_name): cursor.execute("SELECT POSITION FROM levelup.players WHERE PLAYER_NAME=%s", (player_name, )) return cursor.fetchall()[0][0]
def get_names(): cursor.execute("SELECT PLAYER_NAME FROM levelup.players") return [player[0] for player in cursor.fetchall()]
def InsertInitialDevice(deviceid, imei, iccid): cmd = """\ EXEC InsertInitialDevice {0}, '{1}', '{2}', '{3}' """.format(deviceid, AccountID, imei, iccid) cursor.execute(cmd)
def InsertInitialAsset(deviceid): cmd = """\ EXEC InsertInitialAsset '{0}', '{1}', {2}, {3} """.format(AccountID, GroupId, deviceid, getThreshold()) cursor.execute(cmd)
def get_gamestate(): cursor.execute("SELECT * FROM levelup.gamestate") return json.loads(cursor.fetchall()[0][0])
from flask import Flask, request from flask_cors import CORS from service import chatService from constants import cursor, cnx app = Flask(__name__) CORS(app) # messages table cursor.execute("CREATE TABLE IF NOT EXISTS MESSAGES (" "ID INT AUTO_INCREMENT PRIMARY KEY, " "SENDER VARCHAR(20) NOT NULL, " "MESSAGE VARCHAR(200) NOT NULL" ")") cnx.commit() # players table cursor.execute("CREATE TABLE IF NOT EXISTS PLAYERS (" "POSITION VARCHAR(1) NOT NULL, " "PLAYER_NAME VARCHAR(20) NOT NULL" ")") cnx.commit() # gamestate table cursor.execute("CREATE TABLE IF NOT EXISTS GAMESTATE (" "GAMESTATE VARCHAR(10000) NOT NULL" ")") cnx.commit()
def _clear(): cursor.execute("DELETE FROM levelup.gamestate")
def add_player(position, player_name): cursor.execute( "INSERT INTO levelup.players (POSITION, PLAYER_NAME) VALUES (%s, %s)", (position, player_name))
def set_gamestate(gamestate): _clear() cursor.execute("INSERT INTO levelup.gamestate (GAMESTATE) VALUES (%s)", (json.dumps(gamestate), ))
def reset(): cursor.execute("DELETE FROM levelup.players")
def get_largest_id(): cursor.execute( "SELECT MAX(ID) FROM levelup.messages" ) id = cursor.fetchall()[0][0] return int(id) if id is not None else 0
def has_host(): cursor.execute("SELECT COUNT(*) FROM levelup.players WHERE POSITION='N'") return cursor.fetchall()[0][0] == 1
def get_next_messages(message_id): cursor.execute( "SELECT SENDER, MESSAGE FROM levelup.messages WHERE id>%s ORDER BY ID ASC", (message_id, ) ) return cursor.fetchall()
def get_player_name(position): cursor.execute("SELECT PLAYER_NAME FROM levelup.players WHERE POSITION=%s", (position, )) return cursor.fetchall()[0][0]