使用SSH将Pandas数据框写入MYSQL数据库

Question

问题

我想使用 pandas 的 to_sql 将一个 dataframe 写入 MYSQL 表中。然而，我的连接需要 SSH。

我已尝试的方法

我已经成功地建立了一个可以通过 pymysql 执行查询的连接，但是能够直接使用像 to_sql 这样的函数将数据直接推送过去会让我的生活变得更加轻松。下面是我正在使用的代码。

from sshtunnel import SSHTunnelForwarder
import pymysql as db
import pandas as pd
import numpy as np

host = 'host'
localhost = 'localhost'
ssh_username = 'ssh_username'
private_key = '/path/'

# database variables
user='user'
password='password'
database='database'

#query function that works for pulling from database
def query(q):
    with SSHTunnelForwarder(
        (host, 22),
        ssh_username=ssh_username,
        ssh_private_key=private_key,
        ssh_private_key_password="password",
        remote_bind_address=(localhost, port)
    ) as server:
        conn = db.connect(host=localhost,
                               port=server.local_bind_port,
                               user=user,
                               passwd=password,
                               db=database)

        return pd.read_sql_query(q, conn)

# What you need to for to_sql
 conn = db.connect(host=host,
                        port=port,
                        user=user, 
                        password=password,  
                        db=database)

# test df
np.random.seed(0)
number_of_samples = 10
frame = pd.DataFrame({
'feature1': np.random.random(number_of_samples),
'feature2': np.random.random(number_of_samples),
'class':    np.random.binomial(2, 0.1, size=number_of_samples),
},columns=['feature1','feature2','class'])

# to_sql
frame.to_sql(con=conn, name='test_table', if_exists='replace', flavor='mysql')

或许还有其他方法？

我在考虑将一个数据框转换为CSV文件，然后将其导入数据库。如果您知道如何使用SSH和类似to_sql的工具，请告诉我。

- Paroofkey

---

为什么你的连接需要使用 ssh 才能工作，应该可以通过使用服务器的 IP 访问你的数据库。 - gold_cy

出于安全原因，我们使用SSH。昨天我与我们的主要架构师交谈，他告诉我我们必须以某种方式通过SSH连接。_然而_，我正在研究本地端口转发或类似的解决方法，但我认为我们的管理员现在也已将其阻止了。目前我已经开发出一种将其转换为csv的解决方案。虽然不是我所期望的，但这样做可以完成工作 :/ - Paroofkey

Answer 1

我最终使用本地端口转发来解决这个问题。

以下是我在终端中使用的本地端口转发命令：

ssh -N -v SSH_user@SSH_host -L3306:127.0.0.1:3306

我使用了 sqlalchemy 进行连接：

from sqlalchemy import create_engine

engine = create_engine("mysql://user:password@127.0.0.1:3306/db?charset=utf8"
df.to_sql(con=engine, name='test_table', if_exists='replace')

- Paroofkey