Dies ist der Artikel am 14. Tag des estie Adventskalenders 2019. Bitte lesen Sie auf jeden Fall andere Artikel: Lachen:
Hallo, Sie machen Ingenieure in estie.inc, es ist Marusho. Bei estie mit dem Slogan "Die Kraft der Technologie macht die Welt frei und angenehm."
Wir versuchen, neuen Wert zu schaffen, indem wir Immobiliendaten analysieren, die täglich aktualisiert werden.
Um Daten zu analysieren und die Analyseergebnisse schnell wiederzugeben, ist es erforderlich, einfach auf die Datenbank zuzugreifen und gleichzeitig die Sicherheitskonfiguration sicherzustellen. In unserem Unternehmen wird pandas <-> DB häufig ausgetauscht, aber es braucht Zeit, um es in eine CSV-Datei zu konvertieren oder den Plattformserver einmal aufzurufen.
Damit Dieses Mal werde ich die grundlegende CRUD-Operation direkt mit Pandas versuchen und dabei die DB-Daten über die Plattform verwenden.
DB geht von einer gemeinsamen Umgebung aus, in der Sie sich in einem privaten Subnetz befinden und auf die nur über einen Plattformserver zugegriffen werden kann. Dieses Mal läuft es unter EC2 / RDS (MySQL 5.7) unter AWS.
Die lokale Umgebung ist übrigens
Da DB-Informationen von Python verarbeitet werden, wird das Standard-ORM SQL Alchemy verwendet. Außerdem werden der MySQL-Treiber und der SSH-Tunnel installiert, um SSH auf die Plattform zu bringen.
$ pip install SQLAlchemy PyMySQL sshtunnel
SSH config Um eine normale Verbindung zu ssh herzustellen, denke ich, dass Sie Host häufig in .ssh / config registrieren. Da die in der Konfiguration geschriebenen Hostinformationen auch dieses Mal im SSH-Tunnel verwendet werden, schreiben Sie die Verbindungsinformationen der Plattform wie folgt.
~/.ssh/config
Host rds_bastion
Hostname [Schrittplattform IP]
Port 22
User [UserName]
IdentityFile ~/.ssh/[KeyName]
Importieren Sie zunächst das Modul und schreiben Sie die Informationen, die zum Verbinden der Datenbank erforderlich sind
import pandas as pd
import sqlalchemy as sa
from sshtunnel import SSHTunnelForwarder
DB_USER = 'test_user' #DB-Benutzername
DB_PASS = 'db_passward' #DB-Passwort
ENDPOINT = 'hogehoge.fugafuga.ap-northeast-1.rds.amazonaws.com' #RDS-Endpunkt
PORT = 3306 #Hafen
DB_NAME = 'test_db' #DB-Name
CHARSET = 'utf8' #Zeichencode
Verwenden Sie als Nächstes den SSH-Port Forward, um über die Plattform eine Verbindung zur Datenbank herzustellen.
server = SSHTunnelForwarder(ssh_address_or_host = 'rds_bastion',
ssh_config_file = '~/.ssh/config',
remote_bind_address=(ENDPOINT,PORT))
server.start()
Lassen Sie uns schließen, wenn Sie die Verbindung schließen
server.close()
Holen Sie sich die SQLAlqhemy-Engine mit ssh verbunden.
#Generieren Sie eine SQL Alchemy-Verbindungs-URL
URL = f"mysql+pymysql://{DB_USER}:{DB_PASS}@127.0.0.1:{server.local_bind_port}/{DB_NAME}?charset={CHARSET}"
#Holen Sie sich Motor
engine = sa.create_engine(URL)
Wir werden diese Engine verwenden, um Daten in Pandas zu manipulieren
Nun, das ist das Hauptthema. Versuchen wir, Operationen mit Pandas zu erstellen, zu lesen, zu aktualisieren und zu löschen.
Erstellen Sie als Beispiel eine Mitgliedertabelle im DB-Namen "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 hier| 13 |
+----+------------------+-----+
Lesen wir zunächst die Mitgliedertabelle als DataFrame mit pandas.read_sql
Wenn Sie alle Daten in der Tabelle lesen möchten, geben Sie den Tabellennamen an.
df = pd.read_sql('members', engine)
| id | name | age | |
|---|---|---|---|
| 0 | 1 | Aoi Yukimura | 15 |
| 1 | 2 | Hinata Kurakami | 15 |
| 2 | 3 | Kaede Saito | 16 |
| 3 | 4 | Aoba hier | 13 |
Sie können es ordentlich lesen
Sie können auch die Indexspalte und den Spaltennamen angeben, den Sie in einer Liste erhalten möchten.
df= pd.read_sql('members', engine, index_col='id', columns=['name'])
| id | name |
|---|---|
| 1 | Aoi Yukimura |
| 2 | Hinata Kurakami |
| 3 | Kaede Saito |
| 4 | Aoba hier |
Natürlich ist es auch möglich, Datensätze in SQL-Abfragen anzugeben.
df= pd.read_sql('SELECT * FROM members WHERE id = 2', engine)
| id | name | age | |
|---|---|---|---|
| 1 | 2 | Hinata Kurakami | 15 |
Mit to_sql können Sie aus den Daten im DataFrame eine neue Tabelle erstellen.
Sie können auch das Vorhandensein oder Fehlen eines Index (von DataFarame) angeben und angeben, welcher als Index importiert werden soll.
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 hier| 13 |
+------+------------------+------+
Dies kann auch mit to_sql erfolgen,
Beachten Sie, dass das Verhalten abhängig von der Option if_exist unterschiedlich ist.
Wenn Sie "if_exist = append" festlegen, wird es als neuer Datensatz hinzugefügt, und wenn derselbe Datensatz vorhanden ist, tritt ein Fehler auf.
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')
| id | name | age |
|---|---|---|
| 1 | Aoi Yukimura | 15 |
| 2 | Hinata Kurakami | 15 |
| 3 | Kaede Saito | 16 |
| 4 | Aoba hier | 13 |
| 5 | Honoka Kurosaki | 14 |
Es ist das gleiche Verhalten wie bei INSERT. Es wurde richtig hinzugefügt.
Wenn Sie jedoch "if_exist = replace" setzen, ** löschen Sie alle Daten in der angegebenen Tabelle ** und fügen Sie einen DataFrame hinzu.
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')
| id | name | age |
|---|---|---|
| 5 | Honoka Kurosaki | 14 |
Bitte beachten Sie, dass es sich weder um UPDATE noch um UPSERT handelt und sich anders verhält als REPLACE!
Es scheint, dass Operationen wie das Aktualisieren nur bestimmter Datensätze in to_sql noch nicht implementiert wurden. Ich werde diesmal weglassen, aber die Use SQL Alchemy upsert Methode und Es scheint eine Möglichkeit zu geben, das Verhalten von SQL mit der Methodenoption "to_sql" zu ändern (https://stackoverflow.com/questions/34661318/replace-rows-in-mysql-database-table-with-pandas-dataframe). Also werde ich es versuchen.
Wenn ich eine Drop / Delete-Operation mit "read_sql" durchführe, erfolgt keine Rückgabe und es tritt ein Fehler auf. Tatsächlich wird der Löschvorgang auf der DB-Seite ausgeführt.
pd.read_sql('DROP TABLE members', engine)
MySQL [test_db]> SELECT * FROM members;
ERROR 1146 (42S02): Table 'test_db.members' doesn't exist
Da dies nicht die beabsichtigte Verwendung ist, wird empfohlen, die Abfrage bei der Ausführung des Löschvorgangs gehorsam mit sqlalchemy auszuführen.
engine.execute('DROP TABLE members')
Es ist attraktiv, Informationen aus Remote-DBs einfach in einen DataFrame konvertieren zu können. Die Update-Methode scheint außerhalb der Reichweite des juckenden Ortes zu liegen, daher möchte ich die zukünftige Entwicklung von Pandas im Auge behalten.
estie sucht einen Webingenieur! Wantedly Bitte besuchen Sie uns im Büro!
Recommended Posts