[PYTHON] J'ai essayé le Sql Upsert de Pandas

introduction

Il s'agit de Miyano (@estie_mynfire) d'Estie CTO.

Depuis estie construit des données immobilières à partir de diverses ressources, les pandas sont utilisés pour la mise en forme des données. pour cette raison ** Données créées avec pandas-> DataBase ** Il est important d'effectuer le flux en douceur.

Jusqu'à présent, il n'y avait que remplacer et ajouter, donc je devais faire de mon mieux du côté des pandas pour mouler puis mettre à jour. (C'est très ennuyeux)

Pendant ce temps, l'autre jour, les pandas avaient la [Pull request pour ajouter la fonction Sql Upsert] tant attendue (https://github.com/pandas-dev/pandas/pull/29636), alors j'ai essayé de la déplacer. Cela devrait faire exploser votre efficacité au travail ...! !!

Qu'est-ce qu'Upsert en premier lieu?

Cela signifie insérer et mettre à jour. Il existe deux fonctions principales d'Upsert dans Sql.

Basé sur la clé primaire, rien n'existe et Insert (** upsert_keep **) n'existe pas.
Sur la base de la clé primaire, si elle existe, mettez-la à jour et, si elle n’existe pas, insérez (** upsert_overwrite **)

Pourquoi êtes-vous heureux de pouvoir le faire avec les pandas

Difficile de gérer les valeurs manquantes dans Sql
Seule la chaîne de caractères NULL est considérée comme une valeur manquante
Python nan et None-> Entrez le tableau sous forme de chaîne tel quel
Reconnu comme une chaîne vide-> 0
cracher csv est gênant
Même si je l'ai écrit en Python, il est inefficace de le recracher en csv puis de le plonger dans un autre wrapper.

Par conséquent, les ingénieurs Python souhaitent mettre à jour les tables en toute confiance ** directement depuis les pandas.

Conclusion

Pratique car les opérations SQL peuvent être effectuées presque exclusivement avec des pandas. Et simple et facile à utiliser
Il semble que upsert_keep ne se comporte pas comme prévu, alors recherchez la cause

'''
Attributes:
    df (pd.DataFrame): any DataFrame
    tablename (str):nom de la table
'''
import pandas as pd
from sqlalchemy import create_engine

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

# upsert_keep ->En gros, ne rien faire(?)Il y a une possibilité de bogue, je vais donc l'examiner à l'avenir.
df.to_sql(tablename, con, if_exist='upsert_keep', index=False)

# upsert_overwrite ->Mettez à jour ceux qui existent et insérez-les s'ils n'existent pas. Comme prévu
df.to_sql(tablename, con, if_exist='upsert_overwrite', index=False)

Env De là, j'écrirai une histoire concrète. L'environnement est le suivant.

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

Setting

Environnement

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

git rev-parse HEAD #Valeur de hachage de validation actuelle
# d0eb251075883902280cba6cd0dd9a1a1c4a69a4

Installation from sources

pip install cython
# Successfully installed cython-0.29.14
python setup.py build_ext --inplace --force #Ça prend beaucoup de temps. Attendons patiemment.

Mysql server

Pour les tests locaux, configurez un serveur mysql et créez une table users.

table des utilisateurs (** la colonne id est la clé primaire **)

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


mysql.server start --skip-grant-tables #Démarrez un serveur mysql qui vous permet de vous connecter sans mot de passe
mysql #S'identifier

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);

Sujet principal

De là, j'essaierai Sql Upsert en utilisant des pandas et sqlalchemy.

Db connect Tout d'abord, de la connexion avec DB. sqlalchemy est requis, donc si vous ne l'avez pas, veuillez faire pip install sqlalchemy

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

Tout d'abord, réécrivez le DataFrame

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

La syntaxe de base de la méthode to_sql est

df.to_sql(tablename, con, if_exist, index=False)
# df: pd.DataFrame
# tablename:nom de la table

# 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.

(Vérifiez pandas / core / generic.py pour d'autres arguments!)

upsert_keep

Ne rien faire (?) -> Voir ci-dessous
Je pensais que ça insérerait 3 shiro 28, mais ça n'a pas été fait

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

upsert_overwrite

clé primaire (id dans ce cas) Insérez si vous ne portez pas, mettez à jour l'enregistrement d'usure
Comme prévu

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

en conclusion

Problèmes avec upsert_keep ne se comportant pas comme prévu

Il semble nécessaire de regarder autour de la méthode _upsert_keep_processing de pandas / io / sql.py. Dès que la cause est connue, nous mettrons à jour la pull request et l'article.

À propos d'Estie

Chez estie, nous sommes toujours à la recherche d'ingénieurs passionnés par les nouvelles technologies et d'ingénieurs full-stack! https://www.wantedly.com/companies/company_6314859/projects

estie -> https://www.estie.jp estiepro -> https://pro.estie.jp Site de la société-> https://www.estie.co.jp