import psycopg2 as pg2 from config import config import logging logger = logging.getLogger() logger.setLevel(logging.INFO) formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') file_handler = logging.FileHandler('/var/log/desktop.log') file_handler.setFormatter(formatter) logger.addHandler(file_handler) db_name = config['db']['db_name'] db_user = config['db']['db_user'] db_password = config['db']['db_password'] db_port = config['db']['db_port'] def setconn(u_id, vm_num, vm_port,vm_host): conn = pg2.connect("host = localhost dbname={} user={} password={} port={}".format(db_name,db_user,db_password,db_port)) conn.autocommit = True cur = conn.cursor() cur.execute("SELECT entity_id FROM guacamole_entity WHERE name = '{}'".format(u_id)) row = cur.fetchone() if row == None: cur.execute("INSERT INTO guacamole_entity (name, type) VALUES ('{}','USER')".format(u_id)) cur.execute("SELECT entity_id FROM guacamole_entity WHERE name = '{}'".format(u_id)) row = cur.fetchone() en_id = row[0] cur.execute("INSERT INTO guacamole_user(entity_id, password_hash, password_date) VALUES ('{}', '\x74657374', now())".format(en_id)) print("create user : " , u_id) else: en_id = row[0] cur.execute("SELECT password_hash FROM guacamole_user WHERE entity_id = '{}'".format(en_id)) row = cur.fetchone() if row == None: cur.execute("INSERT INTO guacamole_user(entity_id, password_hash, password_date) VALUES ('{}', '\x74657374', now())".format(en_id)) print("user exsit") cn = "{}{}".format(u_id,vm_num) cur.execute("SELECT connection_id FROM guacamole_connection WHERE connection_name = '{}'".format(cn)) row = cur.fetchone() if row == None: #create connection cur.execute("INSERT INTO guacamole_connection (connection_name, protocol) VALUES ('{}', 'vnc')".format(cn)) cur.execute("SELECT MAX(connection_id) FROM guacamole_connection WHERE connection_name = '{}' AND parent_id IS NULL".format(cn)) temp_cn_id = cur.fetchone() cn_id = temp_cn_id[0] cur.execute("INSERT INTO guacamole_connection_parameter VALUES ('{}','hostname','{}')".format(cn_id, vm_host)) cur.execute("INSERT INTO guacamole_connection_parameter VALUES ('{}','port','{}')".format(cn_id,vm_port)) #connection permission cur.execute("INSERT INTO guacamole_connection_permission(entity_id, connection_id, permission) VALUES ('{}', '{}', 'READ')".format(en_id,cn_id)) #clipboard-encoding cur.execute("INSERT INTO guacamole_connection_parameter VALUES ('{}','clipboard-encoding','UTF-8')".format(cn_id)) print("create connection") log = "create connection : " + cn logging.info(log) else: cur.execute("SELECT MAX(connection_id) FROM guacamole_connection WHERE connection_name = '{}' AND parent_id IS NULL".format(cn)) temp_cn_id = cur.fetchone() cn_id = temp_cn_id[0] cur.execute("UPDATE guacamole_connection_parameter SET parameter_value='{}' where connection_id='{}' and parameter_name='hostname'".format(vm_host,cn_id)) cur.execute("UPDATE guacamole_connection_parameter SET parameter_value='{}' where connection_id='{}' and parameter_name='port'".format(vm_port,cn_id)) #cur.execute("UPDATE guacamole_connection_parameter SET parameter_value='UTF-8' where connection_id='{}' and parameter_name='clipboard-encoding'".format(cn_id)) print("no connection") conn.close() return None def delconn(u_id, vm_num): conn2 = pg2.connect("host = localhost dbname={} user={} password={} port={}".format(db_name,db_user,db_password,db_port)) conn2.autocommit = True cur2 = conn2.cursor() cur2.execute("SELECT entity_id FROM guacamole_entity WHERE name = '{}'".format(u_id)) row2 = cur2.fetchone() if row2 == None: print("no user : " , u_id) else: cn2 = "{}{}".format(u_id,vm_num) cur2.execute("SELECT connection_id FROM guacamole_connection WHERE connection_name = '{}'".format(cn2)) row2 = cur2.fetchone() if row2 != None: print("cn_id : ", row2[0]) #delete connection cur2.execute("SELECT connection_id from guacamole_connection_permission where connection_id = '{}'".format(row2[0])) row2 = cur2.fetchone() if row2 != None: print("delete connection : ",row2[0]) cur2.execute("delete from guacamole_connection_permission where connection_id = '{}'".format(row2[0])) log = "delete connection : " + cn2 logging.info(log) conn2.close()