[PYTHON] Ich habe Pandas 'Sql Upsert ausprobiert

Einführung

Dies ist Miyano (@estie_mynfire) von estie CTO.

Da estie Immobiliendaten aus verschiedenen Ressourcen erstellt, werden Pandas zur Datenformung verwendet. aus diesem Grund ** Mit pandas-> DataBase erstellte Daten ** Es ist wichtig, den Fluss reibungslos durchzuführen.

Bisher gab es nur Ersetzen und Anhängen, daher musste ich auf der Pandaseite mein Bestes geben, um zu formen und dann zu aktualisieren. (Das ist sehr nervig)

In der Zwischenzeit hatten Pandas neulich die lang erwartete Pull-Anfrage zum Hinzufügen der SQL Upsert-Funktion, also habe ich versucht, sie zu verschieben. Dies sollte Ihre Arbeitseffizienz explodieren lassen ...! !!

Was ist Upsert überhaupt?

Es bedeutet Einfügen und Aktualisieren. Es gibt zwei Hauptfunktionen von Upsert in SQL.

Basierend auf dem Primärschlüssel existiert nichts und Insert (** upsert_keep **) existiert nicht.
Wenn vorhanden, aktualisieren Sie ihn basierend auf dem Primärschlüssel. Wenn er nicht vorhanden ist, fügen Sie ihn ein (** upsert_overwrite **).

Warum bist du froh, es mit Pandas machen zu können?

Es ist schwierig, fehlende Werte in SQL zu behandeln
Nur die NULL-Zeichenfolge wird als fehlender Wert beurteilt
Python nan und None-> Geben Sie die Tabelle so wie sie ist als Zeichenfolge ein
Als leere Zeichenfolge erkannt-> 0
CSV-Spucken ist mühsam
Obwohl ich es in Python geschrieben habe, ist es ineffizient, es an csv auszuspucken und es dann in einen anderen Wrapper zu tauchen.

Daher möchten Python-Ingenieure Tabellen mit Vertrauen ** direkt von Pandas aktualisieren.

Fazit

Praktisch, da SQL-Operationen fast ausschließlich mit Pandas ausgeführt werden können. Und einfach und leicht zu bedienen
Es scheint, dass sich upsert_keep nicht wie erwartet verhält. Untersuchen Sie daher die Ursache

'''
Attributes:
    df (pd.DataFrame): any DataFrame
    tablename (str):Tabellenname
'''
import pandas as pd
from sqlalchemy import create_engine

con = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]')

# upsert_keep ->Grundsätzlich nichts tun(?)Es besteht die Möglichkeit eines Fehlers, daher werde ich ihn mir in Zukunft ansehen.
df.to_sql(tablename, con, if_exist='upsert_keep', index=False)

# upsert_overwrite ->Aktualisieren Sie die vorhandenen und fügen Sie sie ein, wenn sie nicht vorhanden sind. Wie beabsichtigt
df.to_sql(tablename, con, if_exist='upsert_overwrite', index=False)

Env Von hier aus werde ich eine konkrete Geschichte schreiben. Die Umgebung ist wie folgt.

macOS Mojave
- 10.14.5
Python -V
- 3.6.8
package version
- SQLAlchemy 1.3.11

Setting

Umgebung

git clone https://github.com/V0RT3X4/pandas.git
cd pandas
git checkout -b sql-upsert
git pull origin  sql-upsert

git rev-parse HEAD #Aktueller Commit-Hash-Wert
# d0eb251075883902280cba6cd0dd9a1a1c4a69a4

Installation from sources

pip install cython
# Successfully installed cython-0.29.14
python setup.py build_ext --inplace --force #Es benötigt viel Zeit. Warten wir geduldig.

Mysql server

Richten Sie für lokale Tests einen MySQL-Server ein und erstellen Sie eine Benutzertabelle.

Benutzertabelle (** ID-Spalte ist der Primärschlüssel **)

id	name	age
0	taro	20
1	jiro	17
2	saburo	18


mysql.server start --skip-grant-tables #Starten Sie einen MySQL-Server, auf dem Sie sich ohne Kennwort anmelden können
mysql #Einloggen

mysql> CREATE DATABASE testdb;
mysql> USE testdb;
mysql> CREATE TABLE users (
         id INT NOT NULL,
         name VARCHAR(256),
         age INT
       );
mysql> ALTER TABLE users ADD PRIMARY KEY (id);
mysql> INSERT INTO users (id, name, age) VALUES
       (0, 'taro', 20),
       (1, 'jiro', 19),
       (2, 'saburo', 18);

Hauptthema

Von hier aus werde ich SQL Upsert mit Pandas und SQLalchemie ausprobieren.

Db connect Zunächst aus der Verbindung mit der DB. sqlalchemy ist erforderlich. Wenn Sie es nicht haben, führen Sie bitte pip install sqlalchemy durch

from sqlalchemy import create_engine
import pandas as pd

con = create_engine('mysql+mysqlconnector://@localhost/testdb')
# format: 'mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]'
users = pd.read_sql('SELECT * FROM users;', con)
users
#   id    name  age
#0   0    taro   20
#1   1    jiro   19
#2   2  saburo   18

Upsert

Schreiben Sie zuerst den DataFrame neu

users.loc[users.id==0, 'name'] = 'syo'
users = pd.concat([users, pd.DataFrame({'id': [3], 'name': ['shiro'], 'age': [28]})])
#   id    name  age
#0   0     syo   20
#1   1    jiro   19
#2   2  saburo   18
#0   3   shiro   28

Die grundlegende Syntax der to_sql-Methode lautet

df.to_sql(tablename, con, if_exist, index=False)
# df: pd.DataFrame
# tablename:Tabellenname

# if_exist: {'fail', 'replace', 'append', 'upsert_overwrite', 'upsert_keep'}

# * fail: Raise a ValueError. 
# * replace: Drop the table before inserting new values. 
# * append: Insert new values to the existing table. 
# * upsert_overwrite: Overwrite matches in database with incoming data. 
# * upsert_keep: Keep matches in database instead of incoming data.

(Weitere Argumente finden Sie unter pandas / core / generic.py!)

upsert_keep

Nichts tun (?) -> Siehe unten
Ich dachte, es würde "3 Shiro 28" einfügen, aber es wurde nicht getan

users.to_sql('users', con, if_exists='upsert_keep', index=False)
pd.read_sql('SELECT * FROM users;', con) #Bestätigung
#   id    name  age
#0   0     taro   20
#1   1    jiro   19
#2   2  saburo   18

upsert_overwrite

Primärschlüssel (in diesem Fall ID) Einfügen, wenn nicht getragen, aktualisieren Sie den Trageprotokoll
Wie beabsichtigt

users.to_sql('users', con, if_exists='upsert_overwrite', index=False)
pd.read_sql('SELECT * FROM users;', con) #Bestätigung
#   id    name  age
#0   0     syo   20
#1   1    jiro   19
#2   2  saburo   18
#3   3   shiro   28

abschließend

Probleme mit upsert_keep verhalten sich nicht wie beabsichtigt

Es scheint notwendig, sich um die _upsert_keep_processing-Methode von [pandas / io / sql.py] zu kümmern (https://github.com/V0RT3X4/pandas/blob/d0eb251075883902280cba6cd0dd9a1a1c4a69a4/pandas/io/sql). Sobald die Ursache bekannt ist, werden wir die Pull-Anfrage und den Artikel aktualisieren.

Über estie

Bei estie sind wir immer auf der Suche nach Ingenieuren, die von neuen Technologien und Full-Stack-Ingenieuren begeistert sind! https://www.wantedly.com/companies/company_6314859/projects

estie -> https://www.estie.jp estiepro -> https://pro.estie.jp Unternehmensseite-> https://www.estie.co.jp