Python SSHTunnelForwarder.start方法代码示例

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
 def on_Btn_cups_clicked(self, widget, ip, usuario, password, puerto, notebook, spinner, estado):
     ssh_path = os.environ['HOME'] + '/.ssh/id_rsa'
     spinner.start()
     estado.set_text("Creando tunel...")
     puerto = int(puerto)
     try:
         #Borra en el archivo cupsd.conf la autentificacion
         with settings(host_string=ip, port=puerto, password=password, user=usuario):
             sudo('sed -i."old" "/Require user @SYSTEM/d" /etc/cups/cupsd.conf;sed -i."old2" "/AuthType Default/d" /etc/cups/cupsd.conf;/etc/init.d/cups reload')
         server = SSHTunnelForwarder((ip, puerto), ssh_username=usuario, ssh_private_key=ssh_path, remote_bind_address=('127.0.0.1', 631))
         server.start()
         puerto_local = str(server.local_bind_port)
         scrolledwindow = Gtk.ScrolledWindow()
         scrolledwindow.set_hexpand(True)
         scrolledwindow.set_vexpand(True)
         page = WebKit.WebView()
         page.set_border_width(10)
         page.open("http://127.0.0.1:" + puerto_local)
         scrolledwindow.add(page)
         tab_label = tablabel.TabLabel("CUPS " + ip, Gtk.Image.new_from_file("/usr/share/grx/icons/cups32.png"))
         tab_label.connect("close-clicked", tablabel.on_close_clicked, notebook, page)
         notebook.append_page(scrolledwindow, tab_label)
         self.show_all()
     except:
         self.mensaje("No se ha podido ejecutar cups", "Atencion", atencion)
     spinner.stop()
     estado.set_text("")

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class SSHTunnel(object):
    class TunnelException(Exception):
        pass

    def __init__(self, host, username, key_file, remote_port, host_port=nat_ssh_port_forwarding):
        """
        Returns tuple consisting of local port and sshtunnel SSHTunnelForwarder object.
        Caller must call stop() on object when finished
        """
        logger = logging.getLogger('sshtunnel')
        logger.setLevel(logging.ERROR)

        try:
            self._server = SSHTunnelForwarder((host, host_port),
                    ssh_username=username, ssh_private_key=key_file,
                    remote_bind_address=('127.0.0.1', remote_port), logger=logger)
        except sshtunnel.BaseSSHTunnelForwarderError as e:
            raise self.TunnelException(e)


    def connect(self):
        self._server.start()
        self.local_port = self._server.local_bind_port

    def close(self):
        self._server.stop()

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, type, value, traceback):
        self.close()

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
def sshtunnel():
    server = SSHTunnelForwarder(
            (current_app.config['SSH_HOST'],current_app.config['SSH_PORT']),
            ssh_username=current_app.config['SSH_USER'],
            ssh_password=current_app.config['SSH_PASSWORD'],
            remote_bind_address=(current_app.config['SSH_REMOTE_HOST'], current_app.config['SSH_REMOTE_PORT'])
            )
    server.start()

    return server.local_bind_port

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class TunelSSH():
    def __init__(self, ssh_address, ssh_port, ssh_username, ssh_password, remote_bind_address, remote_bind_port):
        self.server = SSHTunnelForwarder(ssh_address=(ssh_address, ssh_port), ssh_username=ssh_username, 
            ssh_password=ssh_password, remote_bind_address=(remote_bind_address, remote_bind_port))

    def Iniciar(self):
        self.server.start()
        return self.server.local_bind_port

    def Cerrar(self):
        self.server.stop()

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
    def on_Btn_vncviewer_clicked(self, widget, ip, usuario, puerto, password, clave_remoto, spinner, estado):
        spinner.start()
        estado.set_text("Conectando con el equipo")
        ssh_path=os.environ['HOME'] + '/.ssh/id_rsa'

        try:
            puerto = int(puerto)
            server = SSHTunnelForwarder((ip, puerto), ssh_username=usuario,ssh_private_key=ssh_path,remote_bind_address=(ip, 5900))
            server.start()
            puerto_local = str(server.local_bind_port)
            msg = local('echo "' + clave_remoto + '" | vncviewer -autopass -compresslevel 9 -bgr233 127.0.0.1:' + puerto_local + ' &')
            #msg=local('echo "'+clave_remoto+'" | vinagre -autopass -compresslevel 9 -bgr233 127.0.0.1:'+puerto_local+' &')
        except:
            self.mensaje("No se ha podido ejecutar 'vncviewer' en el equipo remoto", "Atencion", atencion)
        spinner.stop()
        estado.set_text("")

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
def dal_connect():
    server = SSHTunnelForwarder(
             (settings.ssh_host, settings.ssh_port),
             ssh_password=settings.ssh_password,
             ssh_username=settings.ssh_username,
             remote_bind_address=('127.0.0.1', 3306))

    server.start()

    uri = 'mysql://{username}:{password}@127.0.0.1:{port}/{db}'.format(
           username = settings.mysql_username,
           password = settings.mysql_password,
           db = settings.mysql_dbname,
           port = server.local_bind_port
        )

    db = DAL(uri, migrate = False)
    return db

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class DatabaseWrapper(MysqlDatabaseWrapper):

    def __init__(self, *args, **kwargs):
        super(DatabaseWrapper, self).__init__(*args, **kwargs)
        self.tunnel = None

    def get_connection_params(self):
        kwargs = super(DatabaseWrapper, self).get_connection_params()
        host = kwargs['host']
        port = kwargs['port']
        config = self.settings_dict["TUNNEL_CONFIG"]
        config['remote_bind_address'] = (host, port)
        self.tunnel = SSHTunnelForwarder(**config)
        self.tunnel.daemon_forward_servers = True
        self.tunnel.daemon_transport = True
        self.tunnel.start()
        kwargs["host"] = '127.0.0.1'
        kwargs['port'] = self.tunnel.local_bind_port
        return kwargs

    def _close(self):
        super(DatabaseWrapper, self)._close()
        if self.tunnel is not None:
            self.tunnel.stop()

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class DB:
    _db_connection = None
    _db_cur = None

    def __init__(self):
        ssh_tunnel = Credentials.ssh_tunnel
        db_config = Credentials.vicnode_db
        self._server = SSHTunnelForwarder(
            ((ssh_tunnel['host']), (int(ssh_tunnel['port']))),
            ssh_password=ssh_tunnel['ssh_password'],
            ssh_username=(ssh_tunnel['username']),
            ssh_pkey=(ssh_tunnel['private_key_file']),
            remote_bind_address=(db_config['host'], 5432),
            allow_agent=False
        )
        self._server.start()
        # we are about to bind to a 'local' server by means of an ssh tunnel
        # ssh tunnel: which will be seen as a local server...
        # so replace the loaded config host
        db_config['host'] = 'localhost'
        db_config['port'] = self._server.local_bind_port
        self._db_connection = psycopg2.connect(**db_config)
        self._db_cur = self._db_connection.cursor(
            cursor_factory=psycopg2.extras.RealDictCursor)
        self.test_connection()

    def __del__(self):
        self.close_connection()

    def close_connection(self):
        if self._server:
            self._db_connection.close()
            self._server.stop()
            self._server = None

    def test_connection(self):
        self._db_cur.execute("SELECT * FROM applications_suborganization;")
        rows = self._db_cur.fetchall()

    def get_contacts(self):
        """
        Returns: every project name and it's chief investigator (can be more
        than one?).
        """
        query = """
            SELECT
              collection.id,
              contacts_contact.email_address,
              contacts_contact.business_email_address
            FROM applications_project AS collection
              LEFT JOIN applications_custodian
                ON collection_id = collection.id
              LEFT JOIN contacts_contact
                ON applications_custodian.person_id = contacts_contact.id
            WHERE applications_custodian.role_id = 293
            ORDER BY id;
        """
        self._db_cur.execute(query)
        return self._db_cur.fetchall()

    def get_sub_organizations(self):
        """
        Returns: all the projects
        """
        query = """
            SELECT
              applications_request.project_id,
              contacts_organisation.short_name,
            CASE
              WHEN applications_suborganization.id = 1
                THEN 'ABP'
              WHEN applications_suborganization.id = 2
                THEN 'FBE'
              WHEN applications_suborganization.id = 3
                THEN 'FoA'
              WHEN applications_suborganization.id = 4
                THEN 'MGSE'
              WHEN applications_suborganization.id = 5
                THEN 'MSE'
              WHEN applications_suborganization.id = 6
                THEN 'MLS'
              WHEN applications_suborganization.id = 7
                THEN 'MDHS'
              WHEN applications_suborganization.id = 8
                THEN 'FoS'
              WHEN applications_suborganization.id = 9
                THEN 'VAS'
              WHEN applications_suborganization.id = 10
                THEN 'VCAMCM'
              WHEN applications_suborganization.id = 11
                THEN 'External'
              ELSE 'Unknown' END AS faculty
            FROM applications_request
              LEFT JOIN applications_suborganization
                ON applications_request.institution_faculty_id =
                   applications_suborganization.id
              LEFT JOIN contacts_organisation
                ON applications_request.institution_id = contacts_organisation.id
            WHERE -- institution_id = 2
              -- don't worry about this, as only UoM projects are assigned
#.........这里部分代码省略.........

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
    exit(1)

ser = serial.Serial(config.get('Serial', 'Port'),
                    config.getint('Serial', 'Baud'))

forwarder = SSHTunnelForwarder(
        config.get('SSH', 'Address'),
        ssh_username=config.get('SSH', 'Username'),
        ssh_password=config.get('SSH', 'Password'),
        remote_bind_address=('127.0.0.1', config.getint('SSH', 'ServerPort')),
        local_bind_address=('127.0.0.1', config.getint('SSH', 'ClientPort')),
        set_keepalive=5
    )

if config.getboolean('SSH', 'Enable'):
    forwarder.start()
    print('[SSH] Opened SSH tunnel with %s:%d' % (forwarder.ssh_host, forwarder.ssh_port))

engine = create_engine(
    config.get('Database', 'ConnectionString'),
    pool_recycle=3600,
    pool_size=5
)
Base = declarative_base()


class Member(Base):
    __tablename__ = 'members'

    mid = Column(String(length=40), primary_key=True)
    name = Column(String(length=100))

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class CdhConfExtractor(object):
    def __init__(self, config):
        self._logger = logging.getLogger(__name__)
        self._hostname = config['machines']['cdh-launcher']['hostname']
        self._hostport = config['machines']['cdh-launcher']['hostport']
        self._username = config['machines']['cdh-launcher']['username']
        key_path = config['machines']['cdh-launcher']['key_filename']
        self._key = os.path.expanduser(key_path)
        self._key_password = config['machines']['cdh-launcher']['key_password']
        self._is_openstack = config['openstack_env']
        self._is_kerberos = config['kerberos_used']
        self._cdh_manager_ip = config['machines']['cdh-manager']['ip']
        self._cdh_manager_user = config['machines']['cdh-manager']['user']
        self._cdh_manager_sshtunnel_required = config['machines']['cdh-manager']['sshtunnel_required']
        self._cdh_manager_password = config['machines']['cdh-manager']['password']

    def __enter__(self):
        extractor = self
        try:
            if self._cdh_manager_sshtunnel_required:
                self._logger.info('Creating tunnel to CDH-Manager.')
                extractor.create_tunnel_to_cdh_manager()
                extractor.start_cdh_manager_tunneling()
                self._logger.info('Tunnel to CDH-Manager has been created.')
            else:
                self._logger.info('Connection to CDH-Manager host without ssh tunnel.')
                self._local_bind_address = self.extract_cdh_manager_host()
                self._local_bind_port = 7180
            return extractor
        except Exception as exc:
            self._logger.error('Cannot creating tunnel to CDH-Manager machine.')
            raise exc

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            if self._cdh_manager_sshtunnel_required:
                self.stop_cdh_manager_tunneling()
                self._logger.info('Tunelling to CDH-Manager stopped.')
        except Exception as exc:
            self._logger.error('Cannot close tunnel to CDH-Manager machine.')
            raise exc

    # Cdh launcher methods
    def create_ssh_connection(self, hostname, username, key_filename, key_password):
        try:
            self._logger.info('Creating connection to remote host {0}.'.format(hostname))
            self.ssh_connection = paramiko.SSHClient()
            self.ssh_connection.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            self.ssh_connection.connect(hostname, username=username, key_filename=key_filename, password=key_password)
            self._logger.info('Connection to host {0} established.'.format(hostname))
        except Exception as exc:
            self._logger.error('Cannot creating connection to host {0} machine. Check your settings '
                               'in fetcher_config.yml file.'.format(hostname))
            raise exc

    def close_ssh_connection(self):
        try:
            self.ssh_connection.close()
            self._logger.info('Connection to remote host closed.')
        except Exception as exc:
            self._logger.error('Cannot close connection to the remote host.')
            raise exc

    def ssh_call_command(self, command, subcommands=None):
        self._logger.info('Calling remote command: "{0}" with subcommands "{1}"'.format(command, subcommands))
        ssh_in, ssh_out, ssh_err = self.ssh_connection.exec_command(command, get_pty=True)
        if subcommands != None:
            for subcommand in subcommands:
                ssh_in.write(subcommand + '\n')
                ssh_in.flush()
        return ssh_out.read() if ssh_out is not None else ssh_err.read()

    def extract_cdh_manager_host(self):
        self._logger.info('Extracting CDH-Manager address.')
        if self._cdh_manager_ip is None:
            self.create_ssh_connection(self._hostname, self._username, self._key, self._key_password)
            if self._is_openstack:
                ansible_ini = self.ssh_call_command('cat ansible-cdh/platform-ansible/inventory/cdh')
            else:
                ansible_ini = self.ssh_call_command('cat ansible-cdh/inventory/cdh')
            self._cdh_manager_ip = self._get_host_ip('cdh-manager', ansible_ini)
            self.close_ssh_connection()
        self._logger.info('CDH-Manager adress extracted: {}'.format(self._cdh_manager_ip))
        return self._cdh_manager_ip

    # Cdh manager methods
    def create_tunnel_to_cdh_manager(self, local_bind_address='localhost', local_bind_port=7180, remote_bind_port=7180):
        self._local_bind_address = local_bind_address
        self._local_bind_port = local_bind_port
        self.cdh_manager_tunnel = SSHTunnelForwarder(
            (self._hostname, self._hostport),
            ssh_username=self._username,
            local_bind_address=(local_bind_address, local_bind_port),
            remote_bind_address=(self.extract_cdh_manager_host(), remote_bind_port),
            ssh_private_key_password=self._key_password,
            ssh_private_key=self._key
        )

    def start_cdh_manager_tunneling(self):
        try:
#.........这里部分代码省略.........

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
    def DatabaseReachPasswordTimeout(self,p_database_index):
        if not self.v_databases[p_database_index]['prompt_password']:
            return { 'timeout': False, 'message': ''}
        else:
            #Create tunnel if enabled
            if self.v_databases[p_database_index]['tunnel']['enabled']:
                v_create_tunnel = False
                if self.v_databases[p_database_index]['tunnel_object'] != None:
                    try:
                        result = 0
                        v_tunnel_object = tunnels[self.v_databases[p_database_index]['database'].v_conn_id]
                        if not v_tunnel_object.is_active:
                            v_tunnel_object.stop()
                            v_create_tunnel = True
                    except Exception as exc:
                        v_create_tunnel = True
                        None

                if self.v_databases[p_database_index]['tunnel_object'] == None or v_create_tunnel:
                    try:
                        if self.v_databases[p_database_index]['tunnel']['key'].strip() != '':
                            v_file_name = '{0}'.format(str(time.time())).replace('.','_')
                            v_full_file_name = os.path.join(settings.TEMP_DIR, v_file_name)
                            with open(v_full_file_name,'w') as f:
                                f.write(self.v_databases[p_database_index]['tunnel']['key'])
                            server = SSHTunnelForwarder(
                                (self.v_databases[p_database_index]['tunnel']['server'], int(self.v_databases[p_database_index]['tunnel']['port'])),
                                ssh_username=self.v_databases[p_database_index]['tunnel']['user'],
                                ssh_private_key_password=self.v_databases[p_database_index]['tunnel']['password'],
                                ssh_pkey = v_full_file_name,
                                remote_bind_address=(self.v_databases[p_database_index]['database'].v_active_server, int(self.v_databases[p_database_index]['database'].v_active_port)),
                                logger=logger
                            )
                        else:
                            server = SSHTunnelForwarder(
                                (self.v_databases[p_database_index]['tunnel']['server'], int(self.v_databases[p_database_index]['tunnel']['port'])),
                                ssh_username=self.v_databases[p_database_index]['tunnel']['user'],
                                ssh_password=self.v_databases[p_database_index]['tunnel']['password'],
                                remote_bind_address=(self.v_databases[p_database_index]['database'].v_active_server, int(self.v_databases[p_database_index]['database'].v_active_port)),
                                logger=logger
                            )
                        server.set_keepalive = 120
                        server.start()

                        s = SessionStore(session_key=self.v_user_key)
                        tunnels[self.v_databases[p_database_index]['database'].v_conn_id] = server

                        self.v_databases[p_database_index]['tunnel_object'] = str(server.local_bind_port)
                        self.v_databases[p_database_index]['database'].v_connection.v_host = '127.0.0.1'
                        self.v_databases[p_database_index]['database'].v_connection.v_port = server.local_bind_port

                        #GO OVER ALL TABS CONNECTION OBJECTS AND UPDATE HOST AND PORT FOR THIS CONN_ID
                        try:
                            for k in list(self.v_tab_connections.keys()):
                                if self.v_tab_connections[k].v_conn_id == p_database_index:
                                    self.v_tab_connections[k].v_connection.v_host = '127.0.0.1'
                                    self.v_tab_connections[k].v_connection.v_port = server.local_bind_port
                        except Exception:
                            None
                        s['omnidb_session'] = self
                        s.save()

                    except Exception as exc:
                        return { 'timeout': True, 'message': str(exc)}
            #Reached timeout, must request password
            if not self.v_databases[p_database_index]['prompt_timeout'] or datetime.now() > self.v_databases[p_database_index]['prompt_timeout'] + timedelta(0,custom_settings.PWD_TIMEOUT_TOTAL):
                #Try passwordless connection
                self.v_databases[p_database_index]['database'].v_connection.v_password = ''
                v_test = self.v_databases[p_database_index]['database'].TestConnection()

                if v_test=='Connection successful.':
                    s = SessionStore(session_key=self.v_user_key)
                    s['omnidb_session'].v_databases[p_database_index]['prompt_timeout'] = datetime.now()
                    s['omnidb_session'].v_databases[p_database_index]['database'].v_connection.v_password = ''
                    s.save()
                    return { 'timeout': False, 'message': ''}
                else:
                    return { 'timeout': True, 'message': v_test}
            #Reached half way to timeout, update prompt_timeout
            if datetime.now() > self.v_databases[p_database_index]['prompt_timeout'] + timedelta(0,settings.PWD_TIMEOUT_REFRESH):
                s = SessionStore(session_key=self.v_user_key)
                s['omnidb_session'].v_databases[p_database_index]['prompt_timeout'] = datetime.now()
                s.save()
            return { 'timeout': False, 'message': ''}

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
	def getRemoteDB(self):
		# TODO: This needs to add new rows since the last update, rather than replace everything.
		'''
		# Get the local database last record timestamp
		conn = pymysql.connect(host='127.0.0.1', port=3306, user=self.config['localusername'], passwd=self.config['localpassword'], db=self.config['localdbname'])
		cur = conn.cursor()
		cur.execute('SELECT Time_uploaded_to_server FROM remotedata ORDER BY Time_uploaded_to_server ASC;')
		lts = cur.fetchone()
		lastTimestamp = lts[0]

		cur.close()
		conn.close()
		'''

		# The database query
		sql = """select enc.dateTime, enc.pushedToServerDateTime, enc.howFeeling, enc.takenMedsToday+0,
				 MAX(IF(Observation.question_id = "20140544-1bee-4d02-b764-d80102437adc", Observation.valueNumeric, NULL)) AS Nose,
				 MAX(IF(Observation.question_id = "22d7940f-eb30-42cd-b511-d0d65b89eec6", Observation.valueNumeric, NULL)) AS Eyes,
				 MAX(IF(Observation.question_id = "2cd9490f-d8ca-4f14-948a-1adcdf105fd0", Observation.valueNumeric, NULL)) AS Breathing,
				 demo.birthYear, demo.gender, demo.allergiesDivulged+0, demo.hayFever+0, demo.asthma+0, demo.otherAllergy+0,
				 demo.unknownAllergy+0, loc.latitude, loc.longitude, loc.accuracy, loc.whenObtained
				 from Encounter as enc inner join Observation on Observation.encounter_id = enc.id
				 inner join Question on Observation.question_id = Question.id join Demographics as demo on demo.id = enc.demographics_id
				 join LocationInfo as loc on loc.id = enc.locationInfo_id
				 group by enc.id;"""

		# Open SSH tunnel
		server = SSHTunnelForwarder(
			(self.config['remotehostname'], 1522),
			ssh_username=self.config['remoteusername'],
			ssh_password=self.config['remotepassword'],
			remote_bind_address=('127.0.0.1', 3306)
		)

		# Select data from remote database
		server.start()
		#print(server.local_bind_port)
		#print(server.tunnel_is_up)
		#print(server.local_is_up(('127.0.0.1', 3306)))

		if server.is_alive:
			print('Connection up...executing sql...')
			#print(os.system('mysql -h 127.0.0.1 --port='+str(server.local_bind_port)+' -u'+self.config['remoteusername']+' -p'))

			try:
				conn = pymysql.connect(host='127.0.0.1', port=server.local_bind_port, user=self.config['remoteusername'], passwd=self.config['remotedbpassword'], db=self.config['remotedbname'])
				cur = conn.cursor()
				cur.execute(sql)
				cur.close()
				conn.close()

			except pymysql.err.OperationalError:
				print('MySQL failed...exiting.')
				server.stop()
				sys.exit(0)

		# Close the ssh tunnel
		server.stop()

		# Update local database
		lconn = pymysql.connect(host='127.0.0.1', port=3306, user=self.config['localusername'], passwd=self.config['localpassword'], db=self.config['localdbname'], autocommit=True)
		lcur = lconn.cursor()
		lcur.execute('TRUNCATE TABLE remotedata;')

		rowcount = 0
		for row in cur:
			#print(row[0])
			sql = """INSERT INTO remotedata (id, Time_answered_on_phone, Time_uploaded_to_server, How_feeling, Taken_meds_today, Nose, Eyes, Breathing, Year_of_Birth, Gender, Optional_data_shared, hay_fever, asthma, other_allergy, unknown, latitude, longitude, accuracy, time_of_location_fix) VALUES ('', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');
			""".format(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])

			# Update the remotedata table
			lcur.execute(sql)

			# Update the row count
			rowcount = rowcount+1

		print('Rows processed: ', rowcount)

		# Done
		lcur.close()
		lconn.close()

		# DB sync complete, update the postcodes
		self.addPostcodesToDB()

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]

#.........这里部分代码省略.........
                                "Could not find the specified database."
                            ))

        if database is None:
            # Check SSH Tunnel is alive or not.
            if self.use_ssh_tunnel == 1:
                self.check_ssh_tunnel_alive()
            else:
                raise ConnectionLost(self.sid, None, None)

        my_id = (u'CONN:{0}'.format(conn_id)) if conn_id is not None else \
            (u'DB:{0}'.format(database))

        self.pinged = datetime.datetime.now()

        if my_id in self.connections:
            return self.connections[my_id]
        else:
            if async_ is None:
                async_ = 1 if conn_id is not None else 0
            else:
                async_ = 1 if async_ is True else 0
            self.connections[my_id] = Connection(
                self, my_id, database, auto_reconnect, async_,
                use_binary_placeholder=use_binary_placeholder,
                array_to_string=array_to_string
            )

            return self.connections[my_id]

    def _restore(self, data):
        """
        Helps restoring to reconnect the auto-connect connections smoothly on
        reload/restart of the app server..
        """
        # restore server version from flask session if flask server was
        # restarted. As we need server version to resolve sql template paths.
        from pgadmin.browser.server_groups.servers.types import ServerType

        self.ver = data.get('ver', None)
        self.sversion = data.get('sversion', None)

        if self.ver and not self.server_type:
            for st in ServerType.types():
                if st.instanceOf(self.ver):
                    self.server_type = st.stype
                    self.server_cls = st
                    break

        # Hmm.. we will not honour this request, when I already have
        # connections
        if len(self.connections) != 0:
            return

        # We need to know about the existing server variant supports during
        # first connection for identifications.
        self.pinged = datetime.datetime.now()
        try:
            if 'password' in data and data['password']:
                data['password'] = data['password'].encode('utf-8')
            if 'tunnel_password' in data and data['tunnel_password']:
                data['tunnel_password'] = \
                    data['tunnel_password'].encode('utf-8')
        except Exception as e:
            current_app.logger.exception(e)

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class StorageDB:
    _db_connection = None
    _db_cur = None

    def __init__(self):
        ssh_tunnel = Credentials.ssh_tunnel
        db_config = Credentials.vicnode_db
        self._server = SSHTunnelForwarder(
            ((ssh_tunnel['host']), (int(ssh_tunnel['port']))),
            ssh_password=ssh_tunnel['ssh_password'],
            ssh_username=(ssh_tunnel['username']),
            ssh_pkey=(ssh_tunnel['private_key_file']),
            remote_bind_address=(db_config['host'], 5432),
            allow_agent=False
        )
        self._server.start()
        # we are about to bind to a 'local' server by means of an ssh tunnel
        # ssh tunnel: which will be seen as a local server...
        # so replace the loaded config host
        db_config['host'] = 'localhost'
        db_config['port'] = self._server.local_bind_port
        self._db_connection = psycopg2.connect(**db_config)
        self._db_cur = self._db_connection.cursor(
            cursor_factory=psycopg2.extras.RealDictCursor)
        self.test_connection()

    def __enter__(self):
        return self

    def __exit__(self, type, value, traceback):
        self.close_connection()

    def __del__(self):
        self.close_connection()

    def close_connection(self):
        if self._server:
            self._db_connection.close()
            self._server.stop()
            self._server = None

    def test_connection(self):
        self._db_cur.execute("SELECT * FROM applications_suborganization;")
        rows = self._db_cur.fetchall()

    def get_contacts(self):
        """
        Returns: every project name and it's chief investigator (can be more
        than one).
        """
        query = """
            SELECT
              collection.id,
              contacts_contact.first_name,
              contacts_contact.last_name,
              contacts_contact.email_address,
              contacts_contact.business_email_address
            FROM applications_project AS collection
              LEFT JOIN applications_custodian
                ON collection_id = collection.id
              LEFT JOIN contacts_contact
                ON applications_custodian.person_id = contacts_contact.id
            WHERE applications_custodian.role_id = 293
            ORDER BY id;
        """
        self._db_cur.execute(query)
        return self._db_cur.fetchall()

    def get_sub_organizations(self):
        """
        Returns: all the projects that have a suborganization
        """
        query = """
            SELECT
              applications_allocation.collection_id AS project_id,
              CASE
              WHEN applications_suborganization.id = 1
                THEN 'ABP'
              WHEN applications_suborganization.id = 2
                THEN 'FBE'
              WHEN applications_suborganization.id = 3
                THEN 'FoA'
              WHEN applications_suborganization.id = 4
                THEN 'MGSE'
              WHEN applications_suborganization.id = 5
                THEN 'MSE'
              WHEN applications_suborganization.id = 6
                THEN 'MLS'
              WHEN applications_suborganization.id = 7
                THEN 'MDHS'
              WHEN applications_suborganization.id = 8
                THEN 'FoS'
              WHEN applications_suborganization.id = 9
                THEN 'VAS'
              WHEN applications_suborganization.id = 10
                THEN 'VCAMCM'
              WHEN applications_suborganization.id = 11
                THEN 'Services'
              ELSE 'Unknown' END AS faculty
            FROM applications_allocation
#.........这里部分代码省略.........

# 需要导入模块: from sshtunnel import SSHTunnelForwarder [as 别名]
# 或者: from sshtunnel.SSHTunnelForwarder import start [as 别名]
class DB(object):
    """
    Read: https://colinnewell.wordpress.com/2016/01/21/hand-coding-sql-with-psycopg2-for-odoo/
    """
    _db_connection = None
    _db_cur = None
    _server = None

    def __init__(self):
        db_config = Configuration.get_vicnode_db()
        ssh_intermediate = Configuration.get_ssh_tunnel_info()

        self._server = SSHTunnelForwarder(
            ((ssh_intermediate['host']), (int(ssh_intermediate['port']))),
            ssh_password=ssh_intermediate['ssh_password'],
            ssh_username=(ssh_intermediate['username']),
            ssh_pkey=(ssh_intermediate['private_key_file']),
            remote_bind_address=(db_config['host'], 5432),
            allow_agent=False
        )
        self._server.start()
        # we are about to bind to a 'local' server by means of an ssh tunnel
        # ssh tunnel: which will be seen as a local server...
        # so replace the loaded config host
        db_config['host'] = 'localhost'
        db_config['port'] = self._server.local_bind_port
        self._db_connection = psycopg2.connect(**db_config)
        self._db_cur = self._db_connection.cursor(
            cursor_factory=psycopg2.extras.RealDictCursor)
        self.test_connection()

    def __del__(self):
        self.close_connection()

    def close_connection(self):
        if self._server:
            logging.info("Closing the VicNode DB connection")
            # if the connection was not established, there will be no close()
            # attribute...
            self._db_connection.close()
            logging.info("Stopping the ssh tunnel")
            self._server.stop()
            logging.info("The VicNode DB connection is closed")
            self._server = None

    @staticmethod
    def get_product_code(product):
        products = settings.STORAGE_PRODUCT_CODES
        if product == COMPUTATIONAL:
            products = settings.COMPUTE_PRODUCT_CODES
        elif product == MARKET:
            products = settings.MARKET_PRODUCT_CODES
        elif product == VAULT:
            products = settings.VAULT_MARKET_CODES
        return products

    @connection_required
    def test_connection(self):
        self._db_cur.execute("SELECT * FROM applications_suborganization;")
        rows = self._db_cur.fetchall()
        # print(rows)

    @connection_required
    def get_allocated(self, day_date):
        """
        :param self:
        :param day_date:
        :return:
        """
        q_allocated = """
            SELECT
              sum(size),
              CASE
              WHEN storage_product_id IN %(compute)s
                THEN 'computational'
              WHEN storage_product_id IN %(market)s
                THEN 'market'
              ELSE 'vault' END AS product
            FROM applications_allocation
            WHERE storage_product_id IN %(all_types)s
                AND COALESCE(applications_allocation.creation_date, '2014-11-14' :: DATE) <
                      (%(day_date)s :: DATE + '1 day' :: INTERVAL)
            GROUP BY storage_product_id;
        """
        self._db_cur.execute(q_allocated, {
            'compute': settings.COMPUTE_PRODUCT_CODES,
            'market': settings.MARKET_PRODUCT_CODES,
            'all_types': settings.STORAGE_PRODUCT_CODES,
            'day_date': day_date
        })
        return self._db_cur.fetchall()

    @connection_required
    def get_allocated_by_faculty(self, day_date, product='all'):
        """
        :param product:
        :param self:
        :param day_date:
        :return:
        """
#.........这里部分代码省略.........