python - 在Python中使用私钥通过SSH连接并查询Mongo数据库

Question

我有一个代码文件mongo.py，如下所示，用于使用用户名 主机 密码连接n个查询mongo及其工作，但我如何使用 ssh address username 和身份验证方法 private_key 连接到 ssh ？ private_key 在另一个文件中

这是我使用 ssh 的示例配置:

sql_local = {'mongomdm':{'host':'xx.xxx.xxx.xx', 'user':'aaa', 'pkey':'/Users/satu/dua/tiga/config/settings/googlecloud_dev_rsa'}}

我已经准备好了一些引用资料，应该像这样导入库吗？

from paramiko import SSHClient, AutoAddPolicy, RSAKey
from paramiko.auth_handler import AuthenticationException
from scp import SCPClient, SCPException
from io import StringIO

这是我的代码文件mongo.py

import csv                                                                              
import pandas as pd
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import config.config as cfg
import config.modules.common as comm
from bson.objectid import ObjectId

class mongoFunc:
def __init__(self):
    self.types = 'mongodb'
    self.host = cfg.sql_local[self.types]['host']
    self.user = cfg.sql_local[self.types]['user']
    self.password = cfg.sql_local[self.types]['password']
    self.uri = 'mongodb://{user}:{password}@{host}'.format(user = self.user, password = self.password, host = self.host)

def connection(self):
    try:
        client= MongoClient(self.uri)
        client.server_info()
        print('Connection Established')
    except ConnectionFailure as err:
        raise(err)
    return client

def get_collection(self, client, database, collection):
    self.client= client
    self.database= database
    self.collection= collection

    db= self.client[self.database]
    return db[self.collection]

def get_query(self,id_data= None,start_date=None,end_date=None,query=None):
    self.id = ObjectId(id_data)
    self.start_date = start_date
    self.end_date = end_date
    self.query = query
    
    if self.end_date:
        script= {'date':{'$gt':self.start_date,'$lte':self.end_date}}
    if self.end_date is None:
        script= {'date':{'$gt':self.start_date}}
    if self.id:
        script = {'_id':{'$gt':self.id}}
    if self.query:
        script.update(self.query)
    return script

def get_data_to_pandas(self, database, collection, query, skip, limit=None):
    self.database = database
    self.collection = collection
    self.query = query
    self.limit = limit
    self.skip = skip

    self.client = self.connection()
    self.collection = self.get_collection(self.client,self.database,self.collection)

    if limit:   
        cursor = self.collection.find(self.query).skip(self.skip).limit(self.limit)
    if not limit :
        cursor = collection.find(self.query).skip(self.skip)
    df = pd.DataFrame(list(cursor))
    return df

编辑:

def connection(self):
    try:
        print('Establishing SSH Connection')
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        if (self.password == ''):
            private_key = paramiko.RSAKey.from_private_key_file(self.pkey)
            self.client.connect(hostname=self.host, port=self.port, username=self.user, pkey=private_key, allow_agent=False,look_for_keys=False)
            print("Connected to the server"),self.host
        else:
            self.client.connect(hostname=self.host, port=self.port, username=self.user, password=self.password, allow_agent=False,look_for_keys=False)    
            print("Connected to the server"),self.host
    except paramiko.AuthenticationException:
        print("Authentication failed, please verify your credentials")
        result_flag = False
    except paramiko.SSHException as sshException:
        print("Could not establish SSH connection: %s") % sshException
        result_flag = False
    else:
        result_flag = True
    return result_flag

def get_collection(self, client, database, collection):
    self.client= client
    self.database= database
    self.collection= collection

    db= self.client[self.database]
    return db[self.collection]`

我收到一个错误:

line 64, in get_collection
    db= self.client[self.database]
TypeError: 'bool' object is not subscriptable

我应该做什么？

Answer 1

这对我有用。我检查了上面的 paramiko 示例，但不明白并发现了这个。 I took the code from here并根据我的需要进行了一些修改。

def data_call(collection_name, query={}):
    from sshtunnel import SSHTunnelForwarder
    import pymongo

    MONGO_HOST = 'HOST' 
    SERVER_USER   = 'server_user'
    PRIVATE_KEY ='pem.txt'

    MONGO_USER='user_mongo'
    MONGO_PASS ='pass_mongo'

    MONGO_DB = "db"

    # define ssh tunnel
    server = SSHTunnelForwarder(
        MONGO_HOST,
        ssh_username=SERVER_USER,
        ssh_pkey=PRIVATE_KEY,
        remote_bind_address=('127.0.0.1', 27017)
    )

    # start ssh tunnel
    server.start()

    connection = pymongo.MongoClient('127.0.0.1', server.local_bind_port)
    db = connection[MONGO_DB]

    data = db[collection_name].find(query)
    return data