使用 Python 中的私钥通过 SSH 连接和查询 Mongo 数据库 [英] Connect and query Mongo database over SSH with private key in Python

问题描述

我有一个像下面这样的代码文件 mongo.py 用 username host password 连接 n 个查询 mongo及其工作，但是我如何使用 ssh address username 和 auth 方法 private_key 连接到 ssh?private_key 在另一个文件中

这是我使用 ssh 的示例 config:

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

我已经准备好了一些参考资料，应该像这样导入库吗?

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

这是我的代码文件 mongo.py

导入csv将熊猫导入为 pd从 pymongo 导入 MongoClient从 pymongo.errors 导入 ConnectionFailure将 config.config 导入为 cfg将 config.modules.common 导入为 comm从 bson.objectid 导入 ObjectId类 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)定义连接(自我):尝试:客户端 = MongoClient(self.uri)client.server_info()print('连接建立')除了 ConnectionFailure 作为错误:提高(错误)回头客def get_collection(self, client, database, collection):self.client=客户self.database= 数据库self.collection= 集合db= self.client[self.database]返回数据库[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_dateself.end_date = end_dateself.query = 查询如果 self.end_date:script= {'date':{'$gt':self.start_date,'$lte':self.end_date}}如果 self.end_date 是 None:script= {'date':{'$gt':self.start_date}}如果 self.id:脚本 = {'_id':{'$gt':self.id}}如果 self.query:script.update(self.query)返回脚本def get_data_to_pandas(self, database, collection, query, skip, limit=None):self.database = 数据库self.collection = 收藏self.query = 查询self.limit = 限制self.skip = 跳过self.client = self.connection()self.collection = self.get_collection(self.client,self.database,self.collection)如果限制:游标 = self.collection.find(self.query).skip(self.skip).limit(self.limit)如果没有限制:游标 = collection.find(self.query).skip(self.skip)df = pd.DataFrame(list(cursor))返回 df

def connection(self):尝试:print('建立SSH连接')self.client = paramiko.SSHClient()self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())如果(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)打印(连接到服务器")，self.host别的:self.client.connect(hostname=self.host, port=self.port, username=self.user, password=self.password, allow_agent=False,look_for_keys=False)打印(连接到服务器")，self.host除了 paramiko.AuthenticationException:打印(身份验证失败，请验证您的凭据")result_flag = False除了 paramiko.SSHException 作为 sshException:打印(无法建立 SSH 连接:%s")% sshExceptionresult_flag = False别的:result_flag = 真返回 result_flagdef get_collection(self, client, database, collection):self.client=客户self.database= 数据库self.collection= 集合db= self.client[self.database]返回数据库[self.collection]`

我有一个错误:

第 64 行，在 get_collection 中db= self.client[self.database]类型错误:bool"对象不可下标

我该怎么办?

解决方案

除非 MongoClient 模块本身支持 SSH 隧道(我不知道)，否则你必须明确实现端口转发.

修改 使用 Python Paramiko 的嵌套 SSH 中的代码进行数据库隧道，您会得到如下代码:

ssh_client = paramiko.SSHClient()# ...ssh_client.connect(hostname=ssh_hostname, username=ssh_username, key_filename=ssh_key)传输 = ssh_client.get_transport()dest_addr = (mongo_host, mongo_port)local_unique_port = 4000 # 任何未使用的本地端口local_host = '本地主机'local_addr = (local_host, local_unique_port)vmchannel = vmtransport.open_channel(direct-tcpip", dest_addr, local_addr)self.uri = 'mongodb://{user}:{password}@{local_host}:{port}'.格式(用户=mongo_user，密码=mongo_password，local_host=local_host，端口=local_unique_port)

如果 Mongo 数据库在 SSH 服务器本身上运行，那么它通常只会在环回接口上侦听.在这种情况下，mongo_host 应该设置为 localhost.

---

关于 PostgreSQL 的相同问题:使用 Paramiko 设置 SSH 隧道以访问 PostgreSQL.

i have a code file mongo.py like below to connect n query mongo with username host password and its work, but how i can connect to ssh with ssh address username and auth method private_key ? private_key in the other file

here is my sample config with ssh:

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

i have ready some references and should import library like this ?

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

here is my code file 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

Edit:

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]`

I got an error:

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

What should I do?

解决方案

Unless MongoClient module supports SSH tunneling on its own (I do not know), you have to implement port forwarding explicitly.

Modifying the code from Nested SSH using Python Paramiko for database tunneling, you get a code like this:

ssh_client = paramiko.SSHClient()
# ...

ssh_client.connect(hostname=ssh_hostname, username=ssh_username, key_filename=ssh_key)

transport = ssh_client.get_transport()
dest_addr = (mongo_host, mongo_port)
local_unique_port = 4000 # any unused local port
local_host = 'localhost'
local_addr = (local_host, local_unique_port)
vmchannel = vmtransport.open_channel("direct-tcpip", dest_addr, local_addr)

self.uri = 'mongodb://{user}:{password}@{local_host}:{port}'.
    format(user=mongo_user, password=mongo_password, local_host=local_host,
           port=local_unique_port)

If the Mongo database runs on the SSH server itself, then it will typically listen on the loopback interface only. In that case mongo_host should be set to localhost.

---

Same question about PostgreSQL: Setup SSH tunnel with Paramiko to access PostgreSQL.

这篇关于使用 Python 中的私钥通过 SSH 连接和查询 Mongo 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！