SQLAlchemy通过Paramiko SSH

Question

我有一个数据库存储在服务器上，需要通过SSH访问。目前，我使用命令行获取数据来处理数据库。

import paramiko
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='XX.XX.XX', username='user', password='pass', port = YYY)
query = "mysql -u " + username_sql + " -p" + password_sql +" dbb -e \"" + sql_query + "\""
ssh.exec_command(query.decode('string_escape'))
ssh.close()

是否有一种使用SQLAlchemy更高效的方法，以便我可以直接使用pandas数据框架进行操作？

from sqlalchemy import create_engine
engine = create_engine(
       "mysql://username_sql:password_sql@localhost/dbb")

- Alexis G

Answer 1

如果有任何人希望通过SSH连接到远程的Postgresql数据库，并想将数据加载到pandas DataFrame中，以下是如何操作：

假设我们已经在远程服务器上安装了postgresql数据库，并可以通过以下参数进行ssh。

SSH参数：

• 服务器IP：10.0.0.101
• SSH端口号：22（SSH的默认端口号）
• 用户名：my_username
• 密码：my_password

数据库参数：

• 端口号：5432（postgresql的默认端口号）
• 数据库名称：db
• 数据库用户：postgres_user（默认用户名为postgres）
• 数据库密码：postgres_pswd（默认密码为空字符串）
• 包含数据的表格：MY_TABLE

现在，我们想要在本地连接到该数据库并将数据加载到pandas DataFrame中：

from sshtunnel import SSHTunnelForwarder
from sqlalchemy import create_engine
import pandas as pd

server = SSHTunnelForwarder(
    ('10.0.0.101', 22),
    ssh_username="my_username",
    ssh_password="my_password",
    remote_bind_address=('127.0.0.1', 5432)
    )

server.start()
local_port = str(server.local_bind_port)
engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format("postgres_user", "postgres_pswd", "127.0.0.1", local_port, "db"))

dataDF = pd.read_sql("SELECT * FROM \"{}\";".format("MY_TABLE"), engine)

server.stop()

- Matin Kh

Answer 2

最简单的方法是在远程主机上运行一个SSH隧道到mysql端口。 例如:

ssh -f [e-mail] -L 3307:mysql1.example.com:3306 -N

然后使用SQLAlchemy在本地连接：

engine = create_engine("mysql://username_sql:password_sql@localhost:3307/dbb")

如果你真的想使用paramiko，可以尝试在paramiko库中这个演示代码或使用sshtunnel模块。不过，ssh命令可能是最简单的方法...如果隧道断开了，你可以使用autossh重新启动隧道。

- Steven Kryskalla

Answer 3

您可以按照以下方式使用SSHTunnel库：

您可以使用SSHTunnel库如下：

from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy

with SSHTunnelForwarder(
    ('10.160.1.24', 22), #Remote server IP and SSH port
    ssh_username = "<usr>",
    ssh_password = "<pwd>",
    remote_bind_address=('127.0.0.1', 5432)
    ) as server:

    server.start() #start ssh sever
    print 'Server connected via SSH'

    #connect to PostgreSQL
    local_port = str(server.local_bind_port)
    engine = create_engine('postgresql://<db_user>:<db_pwd>@127.0.0.1:' + local_port +'/<db_name>')

    Session = sessionmaker(bind=engine)
    session = Session()

    print 'Database session created'

    #test data retrieval
    test = session.execute("SELECT * FROM <table_name>")

- Pradeep Vairamani

Answer 4

只需将服务器的（主机，端口）与postgres交换：

from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel

server = SSHTunnelForwarder(
    (<'your host'>, <host port>),
    ssh_username=<"os remote username">,
    ssh_pkey=<'path/to/key.pem'>,  # or ssh_password.
    remote_bind_address=(<'postgres db host'>, <'postgres db port'>))

server.start()

connection_data = 'postgresql://{user}:{password}@{host}:{port}/{db}'.format(user=<'postgres user'>,
                                                                             password=<'postgres password'>,
                                                                             host=server.local_bind_host,
                                                                             port=server.local_bind_port,
                                                                             db=<'postgres db name'>)

engine = create_engine(connection_data)

# Do your queries

server.stop()

- juan Isaza

Answer 5

我将使用非PostgreSQL数据库MySQL，并在Pythonanywhere.com上进行操作，以下是代码。该代码将把一个表格转换为Excel文件。

import sshtunnel
import sqlalchemy
import pymysql
import pandas as pd
from pandas import ExcelWriter
import datetime as dt
from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='username', 
    ssh_password='password',
    remote_bind_address=('username.mysql.pythonanywhere-services.com', 3306) ) 

server.start()
local_port = str(server.local_bind_port)
db = 'username$database'
engine = sqlalchemy.create_engine(f'mysql+pymysql://username:password@127.0.0.1:{local_port}/{db}')

print('Engine Created')

df_read = pd.read_sql_table('tablename',engine)
print('Grabbed Table')
writer = ExcelWriter('excelfile.xlsx')
print('writer created')
df_read.to_excel(writer,'8==D') # '8==D' specifies sheet
print('df to excel')
writer.save()
print('saved')
server.stop()

- Brad123