Try to get data while port forwarding to RDS with anaconda.

Introduction

When you analyze data, you may find that the data is in the database in the first place. In that case, it would be nice if it was local, but if it is in AWS RDS, it is necessary to connect remotely and acquire the data. In this case, one method is to get it by port forwarding locally and remotely. This time I would like to do this using anaconda and see.

Preparation

Install the following with conda or pip.

* Used for port forwarding.
sshtunnel
* Used to connect to mysql.
mysql.connector
pandas

Port forwarding

Import sshtunnel and port forward.

* Import what you need
from sshtunnel import SSHTunnelForwarder
* Port forwarding settings
host = 'Remote host'
localhost = '127.0.0.1'
ssh_username = 'username'
ssh_private_key = 'Private key location'
server = SSHTunnelForwarder(
          (host, 22),
          ssh_private_key_password="Private key password",
          ssh_username=ssh_username,
          ssh_private_key=ssh_private_key,
          local_bind_address=('0.0.0.0',Local port to bind),
          remote_bind_address=(localhost,Remote port to bind))

server.start()
* Stop when you want to finish port forwarding.
server.stop()

Port forwarding to get data from the database

With port forwarding done with the above source It connects to the database, gets the data, and displays the data in the data frame.

import mysql.connector
import pandas as pd

#Database connection information
connect = mysql.connector.connect(user='DB user name', password='DB password', host='127.0.0.1',port='Port forwarding destination', database='DB name', charset='utf8')
cursor = connect.cursor()

cursor.execute("select * from table WHERE id = 1")
result = cursor.fetchall()
dataframe = pd.DataFrame(result)
dataframe

At the end

Now you can get the data directly with anaconda. You can get the data easily.

Recommended Posts

Try to get data while port forwarding to RDS with anaconda.
Try to get CloudWatch metrics with re: dash python data source
Try converting to tidy data with pandas
Try to aggregate doujin music data with pandas
I tried to get CloudWatch data with Python
[First API] Try to get Qiita articles with Python
Get Amazon RDS (PostgreSQL) data using SQL with pandas
How to get more than 1000 data with SQLAlchemy + MySQLdb
Try to extract Azure SQL Server data table with pyodbc
Try to process Titanic data with preprocessing library DataLiner (Append)
Try to process Titanic data with preprocessing library DataLiner (Encoding)
Get additional data to LDAP with python (Writer and Reader)
Try using folium with anaconda
[Introduction to Python] How to get data with the listdir function
Try to process Titanic data with preprocessing library DataLiner (conversion)
Try to extract the features of the sensor data with CNN
Try to solve the shortest path with Python + NetworkX + social data
Try to process Titanic data with preprocessing library DataLiner (Drop edition)
Link to get started with python
Try to operate Facebook with Python
How to deal with imbalanced data
How to deal with imbalanced data
[Python] Get economic data with DataReader
Try to profile with ONNX Runtime
Try to put data in MongoDB
How to get started with Scrapy
How to get started with Python
How to get started with Django
How to Data Augmentation with PyTorch
Try to get statistics using e-Stat
Try and learn iptablse, port forwarding
Try to output audio with M5STACK
Manage your data with AWS RDS
Try data parallelism with Distributed TensorFlow
How to install Anaconda with pyenv
Try to image the elevation data of the Geographical Survey Institute with Python
Try to get the road surface condition using big data of road surface management
Get additional data in LDAP with python
Step notes to get started with django
Try to reproduce color film with Python
Try logging in to qiita with Python
Change Python 64bit environment to 32bit environment with Anaconda
[Note] Get data from PostgreSQL with Python
How to get parent id with sqlalchemy
Try working with binary data in Python
I tried to get started with Hy
Get data from Cloudant with Bluemix flask
Convert Excel data to JSON with python
Get replies to specific tweets with tweepy
Send data to DRF API with Vue.js
Convert FX 1-minute data to 5-minute data with Python
Quickly try to visualize datasets with pandas
First YDK to try with Cisco IOS-XE
Overwrite data in RDS with AWS Glue
Get data from an oscilloscope with pyVISA
Try to generate an image with aliasing
How to read problem data with paiza
How to get started with laravel (Linux)
[Introduction to Python] How to get the index of data with a for statement