[PYTHON] Les données RDS via la plate-forme pas à pas sont envoyées aux Pandas

Ceci est l'article sur le 14ème jour du calendrier de l'Avent Estie 2019. S'il vous plaît voir d'autres articles par tous les moyens: rire:

introduction

Bonjour, vous faites des ingénieurs en estie.inc, c'est marusho. Chez estie, avec le slogan "Avec la puissance de la technologie, rendez le monde libre et agréable", afin d'éliminer les "problèmes" dans le domaine immobilier

--Service de recherche de bureau estie --Service de visualisation de données immobilières estiepro

Nous essayons de créer une nouvelle valeur en analysant des données immobilières mises à jour quotidiennement.

Afin d'analyser les données et de refléter rapidement les résultats de l'analyse, il est nécessaire d'accéder facilement à la base de données tout en assurant la configuration de la sécurité. Dans notre entreprise, pandas <-> DB est fréquemment échangé, mais il faut du temps pour le convertir en fichier csv ou entrer une seule fois sur le serveur de la plateforme.

Alors Cette fois, je vais essayer le fonctionnement CRUD de base directement avec les pandas, en utilisant les données DB via la plate-forme.

environnement

DB suppose un environnement commun dans lequel vous vous trouvez dans un sous-réseau privé et n'est accessible que via un serveur de plateforme. Cette fois, il s'exécute sur EC2 / RDS (MySQL 5.7) sur AWS.

À propos, l'environnement local est

• MacOS Mojave
• Python 3.6.8
• Pandas 0.24.1

Installez les packages requis

Étant donné que les informations de la base de données sont gérées par Python, l'ORM standard SQL Alchemy est utilisé. Il installe également le pilote MySQL et le tunnel SSH pour mettre SSH sur la plate-forme.

$ pip install SQLAlchemy PyMySQL sshtunnel

SSH config Afin de vous connecter normalement à ssh, je pense que vous enregistrez souvent Host dans .ssh / config. Puisque les informations d'hôte écrites dans la configuration sont également utilisées dans le tunnel ssh cette fois, écrivez les informations de connexion de la plate-forme comme suit.

~/.ssh/config

Host rds_bastion
    Hostname [IP de la plateforme de progression]
    Port 22
    User [UserName]
    IdentityFile ~/.ssh/[KeyName]

Connectez-vous à RDS

Tout d'abord, importez le module et écrivez les informations nécessaires à la connexion du DB

import pandas as pd
import sqlalchemy as sa
from sshtunnel import SSHTunnelForwarder

DB_USER = 'test_user' #Nom d'utilisateur DB
DB_PASS =  'db_passward' #Mot de passe DB
ENDPOINT = 'hogehoge.fugafuga.ap-northeast-1.rds.amazonaws.com' #Point de terminaison RDS
PORT = 3306 #Port
DB_NAME = 'test_db' #Nom de la base de données
CHARSET = 'utf8' #Code de caractère

Ensuite, utilisez le transfert de port SSH pour vous connecter à la base de données sur la plate-forme.

server = SSHTunnelForwarder(ssh_address_or_host = 'rds_bastion',
                            ssh_config_file = '~/.ssh/config',
                            remote_bind_address=(ENDPOINT,PORT))
server.start()

Fermons lors de la fermeture de la connexion

server.close()

Obtenez le moteur SQLAlqhemy avec ssh connecté.

#Générer l'URL de connexion SQL Alchemy
URL = f"mysql+pymysql://{DB_USER}:{DB_PASS}@127.0.0.1:{server.local_bind_port}/{DB_NAME}?charset={CHARSET}"

#Obtenir le moteur
engine = sa.create_engine(URL)

Nous utiliserons ce moteur pour manipuler les données dans Pandas

Aller à Pandas

Eh bien, c'est le sujet principal. Essayons de créer, lire, mettre à jour, supprimer des opérations avec des pandas.

À titre d'exemple, créez une table des membres dans le nom de base de données test_db

MySQL [test_db]> SELECT * FROM members;
+----+------------------+-----+
| id | name             | age |
+----+------------------+-----+
|  1 |Aoi Yukimura| 15  |
|  2 |Hinata Kurakami| 15  |
|  3 |Kaede Saito| 16  |
|  4 |Aoba ici| 13  |
+----+------------------+-----+

Lire lire

Tout d'abord, lisons la table des membres comme DataFrame en utilisant pandas.read_sql

Si vous souhaitez lire toutes les données de la table, spécifiez le nom de la table.

df = pd.read_sql('members', engine)

Vous pouvez le lire proprement

Vous pouvez également spécifier la colonne d'index et le nom de la colonne que vous souhaitez obtenir dans une liste.

df= pd.read_sql('members', engine, index_col='id', columns=['name'])

Bien entendu, il est également possible de spécifier des enregistrements dans les requêtes SQL.

df= pd.read_sql('SELECT * FROM members WHERE id = 2', engine)

Créer: créer une table

Vous pouvez utiliser to_sql pour créer une nouvelle table à partir des données du DataFrame. Vous pouvez également spécifier la présence ou l'absence d'index (de DataFarame) et celui à importer comme index.

df = pd.read_sql('SELECT * FROM members WHERE age < 14', engine)
df.to_sql('jc_members', engine, index=False, index_label='id')

MySQL [test_db]> select * from jc_members;
+------+------------------+------+
| id   | name             | age  |
+------+------------------+------+
|    4 |Aoba ici| 13   |
+------+------------------+------+

Insérer: insérer / mettre à jour l'enregistrement

Cela peut aussi être fait avec to_sql, Notez que le comportement est différent avec l'option ʻif_exist`.

Si vous définissez ʻif_exist = append`, il sera ajouté en tant que nouvel enregistrement, et si le même enregistrement existe, une erreur se produira.

insert_df = pd.DataFrame({'id':['5'],'name' : ['Honoka Kurosaki'],'age':['14']})
insert_df.to_sql('members', engine, index=False, index_label='id', if_exists='append')

C'est le même comportement que INSERT. Il a été ajouté correctement.

Cependant, si ʻif_exist = replace`, ** supprimez toutes les données de la table spécifiée ** et ajoutez un DataFrame.

insert_df = pd.DataFrame({'id':['5'],'name' : ['Honoka Kurosaki'],'age':['14']})
insert_df.to_sql('members', engine, index=False, index_label='id', if_exists='replace')

Veuillez noter que ce n'est ni UPDATE ni UPSERT, et qu'il se comporte différemment de REPLACE!

Il semble que des opérations telles que la mise à jour uniquement d'enregistrements spécifiques n'aient pas encore été implémentées dans to_sql. Je vais omettre cette fois, mais la méthode Use SQL Alchemy upsert et Il semble y avoir un moyen de changer le comportement de SQL avec l'option de méthode de to_sql (https://stackoverflow.com/questions/34661318/replace-rows-in-mysql-database-table-with-pandas-dataframe) Alors je vais essayer.

Supprimer: supprimer l'enregistrement / la table

Lorsque j'effectue une opération de suppression / suppression avec read_sql, il n'y a pas de retour et une erreur se produit. En fait, l'opération de suppression sera exécutée du côté DB.

pd.read_sql('DROP TABLE members', engine)

MySQL [test_db]> SELECT * FROM members;
ERROR 1146 (42S02): Table 'test_db.members' doesn't exist

Comme ce n'est pas l'utilisation prévue, il est recommandé d'exécuter la requête avec sqlalchemy docilement lors de l'exécution de l'opération de suppression.

engine.execute('DROP TABLE members')

en conclusion

Il est intéressant de pouvoir convertir facilement les informations des bases de données distantes en DataFrame. La méthode de mise à jour semble être hors de portée de l'endroit qui démange, alors je voudrais garder un œil sur le développement futur des pandas.

estie recherche un ingénieur web! Wantedly N'hésitez pas à venir nous rendre visite au bureau!