Nhiều cơ sở dữ liệu MySQL được cấu hình để chấp nhận kết nối từ các máy chủ khác trên mạng cục bộ và sẽ từ chối kết nối từ các máy từ xa. Thông thường, bạn có thể giải quyết vấn đề này bằng cách tạo kết nối đường hầm SSH để đăng nhập vào máy, sau đó mở kết nối cơ sở dữ liệu và ánh xạ cổng cơ sở dữ liệu tới cổng trên máy cục bộ của bạn. Nhưng làm thế nào bạn có thể làm điều đó từ tập lệnh Python?
Chà, nhờ các gói Python sshtunnel
và
ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
0, bạn có thể tạo kết nối SSH bên trong tập lệnh Python của mình và kết nối với máy chủ MySQL từ xa một cách an toàn và trả về kết quả truy vấn của bạn trong khung dữ liệu Pandas. Đây là cách nó được thực hiệnTải các gói
Mở tài liệu Python hoặc sổ ghi chép Jupyter và tải các gói
ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
1, ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
0, ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
3 và sshtunnel
, bao gồm mô-đun ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
5 từ sshtunnel
. Bạn có thể cài đặt chúng bằng cách sử dụng ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
7 nếu chúng chưa có trên máy của bạnimport pandas as pd
import pymysql
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
Định cấu hình thông tin đăng nhập của bạn
Tiếp theo, tạo một số biến cấu hình để giữ thông tin đăng nhập của bạn. Bạn sẽ cần địa chỉ IP của máy chủ SSH, cùng với tên người dùng và mật khẩu. Bạn cũng sẽ cần tên người dùng, mật khẩu và tên cơ sở dữ liệu MySQL
ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
Mở một đường hầm SSH
Để làm cho mã cuối cùng sạch hơn và cho phép chúng tôi sử dụng lại mã đó, chúng tôi sẽ tạo một số chức năng để xử lý từng tác vụ. Chức năng đầu tiên của chúng tôi sử dụng mô-đun sshtunnel
gói
ssh_host = '1.2.3.4'
ssh_username = 'bob'
ssh_password = 'SecretPassword'
database_username = 'bob_smith'
database_password = 'SecretDBPassword'
database_name = 'ecommerce'
localhost = '127.0.0.1'
5 để tạo một đường hầm SSH và cổng chuyển tiếp nó tới một cổng trên máy cục bộ, sau đó nó bắt đầu kết nối. Bạn có thể sử dụng cờ def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
0 để bật gỡ lỗi để kiểm tra mọi thứ đang hoạt độngdef open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
Kết nối với MySQL qua đường hầm SSH
Chức năng tiếp theo sử dụng PyMySQL để tạo kết nối đến máy chủ MySQL. Điều này sẽ được chạy sau khi đường hầm SSH đã được mở. Cổng MySQL sẽ được ánh xạ tới
def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
1 từ đối tượng def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
2 và được lưu trữ trong toàn cầu def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
3def mysql_connect[]:
"""Connect to a MySQL server using the SSH tunnel connection
:return connection: Global MySQL database connection
"""
global connection
connection = pymysql.connect[
host='127.0.0.1',
user=database_username,
passwd=database_password,
db=database_name,
port=tunnel.local_bind_port
]
Chạy truy vấn SQL của bạn
Để chạy một truy vấn cơ sở dữ liệu, chúng ta có thể tạo một trình bao bọc cho hàm Pandas
def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
4. Điều này nhận một truy vấn SQL và def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
3 toàn cầu và trả về kết quả MySQL được đặt trong khung dữ liệu Pandasdef run_query[sql]:
"""Runs a given SQL query via the global database connection.
:param sql: MySQL query
:return: Pandas dataframe containing results
"""
return pd.read_sql_query[sql, connection]
Ngắt kết nối và đóng đường hầm
Cuối cùng, chúng tôi cần một số chức năng để dọn dẹp khi chúng tôi hoàn thành. Cái đầu tiên ngắt kết nối khỏi máy chủ MySQL bằng cách sử dụng chức năng
def open_ssh_tunnel[verbose=False]:
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder[
[ssh_host, 22],
ssh_username = ssh_username,
ssh_password = ssh_password,
remote_bind_address = ['127.0.0.1', 3306]
]
tunnel.start[]
6, trong khi cái thứ hai đóng đường hầm SSHdef mysql_disconnect[]:
"""Closes the MySQL database connection.
"""
connection.close[]
def close_ssh_tunnel[]:
"""Closes the SSH tunnel connection.
"""
tunnel.close
Chạy mã
Cuối cùng, chúng ta có thể chạy các lệnh theo thứ tự sau. Đầu tiên, chúng tôi sẽ kết nối với máy chủ từ xa thông qua SSH và tạo một đường hầm SSH, sau đó chúng tôi sẽ kết nối với cơ sở dữ liệu MySQL trên máy chủ từ xa bằng đường hầm, sau đó chúng tôi sẽ chạy truy vấn và trả về một khung dữ liệu Pandas chứa kết quả
Khi điều đó chạy, chúng tôi sẽ ngắt kết nối khỏi MySQL và đóng đường hầm SSH. Bạn cũng có thể sử dụng mã tương tự để chèn dữ liệu vào cơ sở dữ liệu MySQL bằng PyMySQL