[python] Autour de la génération d'une chaîne de caractères XML sans utiliser to_sql () de pandas et de la mise à jour des données à l'aide de la fonction OPENXML dans la procédure stockée de SQL Server
1. Vue d'ensemble
pandas dispose de méthodes pour écrire / lire des données à partir de diverses sources de données telles que des fichiers csv et Excel, des bases de données relationnelles (RDB) et BigQuery de Google. Par exemple, si vous souhaitez écrire des données dans des tables SGBDR telles que SQL Server, , Vous pouvez facilement mettre à jour les données de la table en utilisant la méthode to_sql () de pandas. Si vous essayez d'écrire à nouveau les mêmes données dans la table pendant le deuxième processus de mise à jour des données déjà stockées dans la table, les données seront dupliquées, donc n'écrivez pas dans la table pour ces données dupliquées. Si vous souhaitez mettre à jour les données d'une manière unique, vous ne pouvez pas nier le sentiment que to_sql () des pandas ne fonctionne pas un peu. Je ne peux pas simplement exécuter to_sql () en mode remplacement ... Donc, cette fois, j'ai essayé de prototyper / considérer comment mettre à jour uniquement les données de différence dans la table sur SQL Server par le flux suivant sans utiliser to_sql () de pandas.
1: Générer une chaîne de caractères XML avec elementTree à partir du cadre de données pandas df avec python ... (1) 2: Exécuter la procédure stockée SQL Server avec SQL Alchemy en utilisant la chaîne de caractères XML de (1) comme argument 3: Lisez la chaîne de caractères XML de (1) avec la fonction OPENXML de SQL Server et développez les données ... (2) 4: Stockez les données de (2) dans la table temporaire #table, JOIN avec la table réelle et ne mettez à jour que la différence
Notez que les données peuvent être mises à jour dans cette image ci-dessus en partant du principe qu'il existe une prise en charge de la fonction OPENXML, qui est une fonction qui exploite la chaîne de caractères XML fournie en standard dans SQL Server, qui est le produit SGBDR de Microsoft, et d'autres SGBDR. Je ne sais pas si le produit a une fonction pour manipuler les chaînes XML comme la fonction OPENXML, mais qu'en est-il? (Remarque: je ne suis pas familier avec MySQL et postgres, mais en tant que direction, si MySQL et postgres ont une fonction pour faire fonctionner facilement des chaînes de caractères XML sur RDB, ces fonctions peuvent être en mesure de le remplacer. Je me demandais s'il y en avait ...) Par conséquent, veuillez noter que ce problème est une méthode qui ne peut être implémentée que pour SQL Server!
2. Première moitié (générer une chaîne XML sur python)
file1.py
import pandas as pd
import xml.etree.ElementTree as ET
#Exemple de données
lei = ['353800PIEETYXIDK6K51','5493006W3QUS5LMH6R84','not found']
cname = ['Moteur Toyota','Océan polaire','Internet Sakura']
isin = [' JP3633400001' , 'JP3257200000','JP3317300006']
sic =['7203','1301','3778']
#génération df
Pythondata={'sic': sic, 'isin': isin, 'cname': cname ,'lei': lei }
df = pd.DataFrame(data)
columns = ['sic', 'isin', 'cname', 'lei' ]
df.columns = columns
print(df)
#Génération de chaînes XML
roots = ET.Element('root')
for i in range(len(df)):
f0 = ET.SubElement(roots, 'sb')
f1 = ET.SubElement(f0, 'hoge')
f1.set('sic', df.iloc[i,0])
f1.set('isin', df.iloc[i,1])
f1.set('cname', df.iloc[i,2])
f1.set('lei', df.iloc[i,3])
tree = ET.tostring(roots)
tree = tree.decode()
tree = "'" + tree + "'"
print(tree)
Tout d'abord, les trois exemples de données ci-dessus (Toyota Motor, Gokuyo, Sakura Internet) peuvent être trouvés sur la page JPX TSE. J'ai vérifié le code, etc. J'ai également recherché le code LEI sur la page GLEIF. Lorsque le code python ci-dessus est exécuté, la trame de données pandas df est générée en premier et la chaîne de caractères XML est générée à partir du df. Dans ce qui précède, le module python elementTree pour créer et manipuler des objets XML est utilisé pour générer des objets XML, et des balises XML séquentielles (pour être exact, des informations d'attribut XML) sont ajoutées dans une boucle for à partir de la balise racine. ..
Voici le résultat de l'exécution de l'exemple de code python ci-dessus ↓
- J'ai pris un simple croquis avec Google Colabratory, mais en réalité, il existe un processus de mise à jour des données vers SQL Server, qui sera décrit plus tard, alors exécutez le script non pas dans Google Colabratory mais dans l'environnement d'exécution python où vous pouvez accéder au serveur SQL à utiliser. Devrait être!
La chaîne de caractères XML générée par l'exemple de code python ci-dessus est la suivante ↓
'<root><sb><hoge cname="トヨタ自動車" isin=" JP3633400001" lei="353800PIEETYXIDK6K51" sic="7203" /></sb><sb><hoge cname="極洋" isin="JP3257200000" lei="5493006W3QUS5LMH6R84" sic="1301" /></sb><sb><hoge cname="さくらインターネット" isin="JP3317300006" lei="not found" sic="3778" /></sb></root>'
Si vous rendez la chaîne de caractères XML un peu plus lisible et l'écrivez avec des sauts de ligne, la structure XML sera la suivante ↓
'<root>
<sb>
<hoge cname="トヨタ自動車" isin="JP3633400001" lei="353800PIEETYXIDK6K51" sic="7203" />
</sb>
<sb>
<hoge cname="極洋" isin="JP3257200000" lei="5493006W3QUS5LMH6R84" sic="1301" />
</sb>
<sb>
<hoge cname="さくらインターネット" isin="JP3317300006" lei="not found" sic="3778" />
</sb>
</root>'
Dans le XML ci-dessus, il est défini par un attribut, pas par un élément. Ceci est défini par un attribut basé sur les spécifications lors de la lecture de XML avec la fonction XML OPEN SQL Server décrite plus loin. Après cela, je me demande comment gérer NULL (NaN / None) lors de la génération d'une chaîne de caractères XML. Je pense que les valeurs manquantes apparaîtront naturellement dans les données gérées par les pandas, mais à ce moment-là, NULL (NaN / None) ne peut pas être bien géré lors de sa définition en tant que chaîne de caractères XML, alors ajoutez une chaîne de caractères. Il est nécessaire de penser à une solution de contournement telle que donner. (Par exemple, dans l'exemple ci-dessus, 1301 Polar Ocean et 7203 Toyota Motor ont Code LEI, tandis que 3778 Sakura Internet a [Code LEI]. ](Https://www.gleif.org/ja/) n'a pas, et convertit de force les données NULL en caractères tels que "introuvable" afin de générer une chaîne de caractères XML, et SQL Server décrit plus loin Est-il nécessaire de bien les gérer après avoir lu avec la fonction OPENXML dans? Il y a place à amélioration ici)
3. Seconde moitié (lecture / composition des données à partir d'une chaîne XML avec la fonction OPENXML sur SQL Server)
file2.sql
DECLARE @idoc INT
DECLARE @xml AS NVARCHAR(MAX)
SET @xml = '<root><sb><hoge cname="トヨタ自動車" isin=" JP3633400001" lei="353800PIEETYXIDK6K51" sic="7203" /></sb><sb><hoge cname="極洋" isin="JP3257200000" lei="5493006W3QUS5LMH6R84" sic="1301" /></sb><sb><hoge cname="さくらインターネット" isin="JP3317300006" lei="not found" sic="3778" /></sb></root>'
--SELECT @xml
DROP TABLE IF EXISTS #temp;
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;
SELECT *
INTO #temp
FROM OPENXML(@idoc, '/root/sb/hoge',1)
WITH (SIC varchar(50) './@sic'
, ISIN varchar(50) './@isin'
, CNAME varchar(50) './@cname'
, LEI varchar(50) './@lei'
);
SELECT *
FROM #temp;
DROP TABLE IF EXISTS #temp;
Résultat de l'exécution de la requête SQL ci-dessus ↓
SQL Server prend en charge une fonction appelée fonction OPENXML qui exploite les chaînes de caractères XML en tant qu'équipement standard. À l'aide de cette fonction OPENXML, la chaîne de caractères XML générée par python ci-dessus est convertie en données qui peuvent être exploitées par SQL. Ensuite, si vous stockez les données dans la table temporaire #temp (clause SELECT * INTO ~), rejoignez le #temp et la table de destination de stockage, et enregistrez le #temp qui n'existe pas encore dans la table de destination de stockage. Si une requête de transaction à insérer est décrite dans la table de destination de stockage comme un enregistrement de différence, il semble que les données de la table puissent être mises à jour tout en garantissant l'unicité des données.
La chaîne de caractères XML (structure XML) générée dans la première moitié et la description de lecture XML lors de la lecture avec la fonction SQL OPENXML dans la seconde moitié doivent être exactement identiques et la structure XML à gérer est la première moitié. Il semble que ce soit un point à noter car la partie d'éjection de chaîne de caractères XML de ce qui précède et la partie de lecture XML de la dernière moitié doivent être identiques. (Si la chaîne de caractères XML ne peut pas être bien lue dans la partie lue par la fonction OPENXML de SQLServer, la chaîne de caractères générée sur python dans la première moitié de la partie de description XML est généralement synchronisée avec la partie lue par OPENXML sur SQLServer. N'est peut être pas)
4. Exécutez la procédure stockée SQL Server dans SQL Alchemy
file1.py
import sqlalchemy
#Chaîne de caractères du paramètre de connexion à la base de données
CONNECT_INFO = 'mssql+pyodbc://hoge' #hoge = nom ODBC
engine = sqlalchemy.create_engine(CONNECT_INFO, encoding='utf-8')
#...(Omission)...
##Mise à jour de la base de données
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
##Exécution stockée
query = 'EXEC dbo.spUpdatehoge @prm = {0}'.format(tree)
res = session.execute(query) #exécution sp
print(res.fetchall())
session.commit() #commettre
session.close()
Lors de l'exécution d'une procédure stockée SQL Server avec python (SQL Alchemy), je souhaite obtenir la valeur de retour (réponse) de l'exécution de la procédure stockée sur python et l'afficher dans la partie session.execute (query) de la requête ci-dessus. Dans ce cas, si vous renvoyez l'état du résultat de la mise à jour avec l'instruction Select au lieu de la clause Return dans la procédure stockée, vous pouvez voir immédiatement le résultat de l'exécution stockée sur python. Apparemment, l'objet ResultProxy de SQLAlchemy ne peut pas bien obtenir le résultat renvoyé par la clause Return dans la procédure stockée.
La méthode de mise à jour décrite cette fois-ci est divisée en la partie où la chaîne de caractères XML est générée par le script Python dans la première moitié, la partie où le traitement OPENXML est effectué dans la procédure stockée dans SQL Server dans la seconde moitié et la première moitié / seconde moitié du traitement. Par conséquent, je pense qu'il serait rassurant si les résultats du traitement de la procédure stockée (succès et échec) peuvent être acquis et affichés par SQLAlchemy de Python.
Recommended Posts