{"id":7032,"date":"2021-12-14T17:48:12","date_gmt":"2021-12-14T09:48:12","guid":{"rendered":"https:\/\/www.orczhou.com\/?page_id=7032"},"modified":"2021-12-15T09:26:56","modified_gmt":"2021-12-15T01:26:56","slug":"sample-python-script-ssh-tunnel","status":"publish","type":"page","link":"https:\/\/www.orczhou.com\/index.php\/projects\/sample-python-script-ssh-tunnel\/","title":{"rendered":"Sample Python script: ssh tunnel"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Sample Python script: ssh tunnel<\/h2>\n\n\n\n<p>\u8fd9\u662f\u4e00\u4e2a\u4f7f\u7528Python\u811a\u672c\uff0c\u901a\u8fc7ssh tunnel\u6765\u8fde\u63a5\u6570\u636e\u5e93\u7684\u793a\u4f8b\u811a\u672c\uff0c\u6d4b\u8bd5\u73af\u5883\u4f7f\u7528\u7684\u662f<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">import pandas as pd\nimport pymysql\nimport logging\nimport sshtunnel\nfrom sshtunnel import SSHTunnelForwarder\n\nssh_host = 'xxxxxxx'\nssh_port=22\nssh_username = 'xxx'\nssh_password = 'xxxx'\n\ndatabase_host='xxxxxx'\ndatabase_port=3306\ndatabase_username = 'xxxxxxx'\ndatabase_password = 'xxxxxxx'\ndatabase_name = 'xxxxxx'\n'''\n-------------------------------------------------------------------\n                         |\n-------------+           |   +----------+                +---------\n local client|           |   | ECS\/CVM  |                | Database\n  web  client| &lt;== SSH ====&gt; | in VPC-A | &lt;== in VPC ==&gt; | in VPC-A\n-------------+           |   +----------+                +---------\n                         |\n-------------------------------------------------------------------\n'''\ntunnel = SSHTunnelForwarder(\n    (ssh_host, ssh_port),\n    ssh_username = ssh_username,\n    ssh_password = ssh_password,\n    remote_bind_address = (database_host, database_port),\n)\ntunnel.start()\n\nconnection = pymysql.connect(\n    host='127.0.0.1',\n    user=database_username,\n    passwd=database_password,\n    db=database_name,\n    port=tunnel.local_bind_port\n)\n\ndf = pd.read_sql_query(\"SELECT VERSION()\", connection)\nprint(df)\n\ndf = pd.read_sql_query(\"SELECT * FROM a LIMIT 10\", connection)\nprint(df)\n\ndf.head()\nconnection.close()\ntunnel.close<\/code><\/pre>\n\n\n\n<p>\u6d4b\u8bd5\u73af\u5883\u8bf4\u660e\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ python3\nPython 3.8.2 (default, Apr  8 2021, 23:19:18)\n[Clang 12.0.5 (clang-1205.0.22.9)] on darwin<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Sample Python script: ssh tunnel \u8fd9\u662f\u4e00\u4e2a\u4f7f\u7528Python\u811a\u672c\uff0c\u901a\u8fc7ssh tunnel\u6765\u8fde\u63a5\u6570\u636e\u5e93\u7684\u793a\u4f8b\u811a\u672c\uff0c\u6d4b\u8bd5\u73af\u5883\u4f7f\u7528\u7684\u662f \u6d4b\u8bd5\u73af\u5883\u8bf4\u660e\uff1a<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":117,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"class_list":["post-7032","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/pages\/7032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=7032"}],"version-history":[{"count":12,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/pages\/7032\/revisions"}],"predecessor-version":[{"id":7047,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/pages\/7032\/revisions\/7047"}],"up":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/pages\/117"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=7032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}