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.
Install the following with conda or pip.
* Used for port forwarding.
sshtunnel
* Used to connect to mysql.
mysql.connector
pandas
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()
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
Now you can get the data directly with anaconda. You can get the data easily.
Recommended Posts