[python] Um eine XML-Zeichenfolge zu generieren, ohne to_sql () von Pandas zu verwenden, und um Daten mit der OPENXML-Funktion in der gespeicherten Prozedur von SQL Server zu aktualisieren
1. Übersicht
pandas verfügt über Methoden zum Schreiben / Lesen von Daten aus verschiedenen Datenquellen wie CSV- und Excel-Dateien, relationalen Datenbanken (RDB) und Googles BigQuery. Wenn Sie beispielsweise Daten in RDBMS-Tabellen wie SQL Server schreiben möchten. , Sie können die Daten in der Tabelle einfach mit der to_sql () -Methode von pandas aktualisieren. Wenn Sie versuchen, dieselben Daten während des zweiten Aktualisierungsvorgangs der bereits in der Tabelle gespeicherten Daten erneut in die Tabelle zu schreiben, werden die Daten dupliziert. Daher sollten die Daten in der Tabelle für solche duplizierten Daten nicht in die Tabelle geschrieben werden. Wenn Sie die Daten auf einzigartige Weise aktualisieren möchten, können Sie das Gefühl nicht leugnen, dass to_sql () von Pandas nicht wenig funktioniert. Ich kann nicht einfach to_sql () im Ersetzungsmodus ausführen ... Daher habe ich dieses Mal versucht, einen Prototyp zu erstellen / zu überlegen, wie nur die Differenzdaten in der Tabelle auf SQL Server durch den folgenden Ablauf aktualisiert werden können, ohne to_sql () von Pandas zu verwenden.
1: Generieren Sie eine XML-Zeichenfolge mit elementTree aus dem pandas df-Datenrahmen mit Python ... (1) 2: Führen Sie die gespeicherte SQL Server-Prozedur mit SQL Alchemy unter Verwendung der XML-Zeichenfolge (1) als Argument aus 3: Lesen Sie die XML-Zeichenfolge von (1) mit der OPENXML-Funktion von SQL Server und erweitern Sie die Daten ... (2) 4: Speichern Sie die Daten von (2) in der temporären Tabelle #table, verbinden Sie sich mit der realen Tabelle und aktualisieren Sie nur die Differenz
Beachten Sie, dass die Daten in diesem Bild oben unter der Voraussetzung aktualisiert werden können, dass die OPENXML-Funktion unterstützt wird, eine Funktion, die die in SQL Server, dem Microsoft-RDBMS-Produkt von Microsoft, und anderen RDBMS standardmäßig bereitgestellte XML-Zeichenfolge ausführt Ich bin nicht sicher, ob das Produkt eine Funktion zum Bearbeiten von XML-Zeichenfolgen wie die OPENXML-Funktion hat, aber was ist damit? (Hinweis: Ich bin nicht mit MySQL und Postgres vertraut. Wenn MySQL und Postgres eine Funktion zum einfachen Bedienen von XML-Zeichenfolgen in RDB haben, können diese Funktionen diese möglicherweise ersetzen. Ich habe mich gefragt, ob es welche gibt ...) Beachten Sie daher, dass dieses Problem nur für SQL Server implementiert werden kann!
2. Erste Hälfte (XML-String auf Python generieren)
file1.py
import pandas as pd
import xml.etree.ElementTree as ET
#Beispieldaten
lei = ['353800PIEETYXIDK6K51','5493006W3QUS5LMH6R84','not found']
cname = ['Toyota Motor','Polarer Ozean','Sakura Internet']
isin = [' JP3633400001' , 'JP3257200000','JP3317300006']
sic =['7203','1301','3778']
#df Generation
Pythondata={'sic': sic, 'isin': isin, 'cname': cname ,'lei': lei }
df = pd.DataFrame(data)
columns = ['sic', 'isin', 'cname', 'lei' ]
df.columns = columns
print(df)
#Generierung von XML-Zeichenfolgen
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)
Die drei oben genannten Beispieldaten (Toyota Motor, Gokuyo, Sakura Internet) finden Sie zunächst auf JPX TSE-Seite. Ich habe den Code usw. überprüft. Ich habe auch den LEI-Code auf der GLEIF-Seite nachgeschlagen. Wenn der obige Python-Code ausgeführt wird, wird zuerst der Pandas-Datenrahmen df und die XML-Zeichenfolge aus dem df generiert. Oben wird das Python-Modul elementTree zum Erstellen und Bearbeiten von XML-Objekten zum Generieren von XML-Objekten verwendet, und sequentielle XML-Tags (genauer gesagt XML-Attributinformationen) werden in einer for-Schleife aus dem Root-Tag hinzugefügt. ..
Das Folgende ist das Ausführungsergebnis des obigen Beispiel-Python-Codes ↓
- Ich habe eine einfache Skizze mit Google Colabratory erstellt, aber in Wirklichkeit gibt es einen Prozess zum Aktualisieren der Daten auf SQL Server, der später beschrieben wird. Führen Sie das Skript daher nicht in Google Colabratory, sondern in der Python-Ausführungsumgebung aus, in der Sie auf den zu verwendenden SQL Server zugreifen können. Sollte sein!
Die vom obigen Python-Beispielcode generierte XML-Zeichenfolge lautet wie folgt: ↓
'<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>'
Wenn Sie die XML-Zeichenfolge etwas lesbarer machen und mit Zeilenumbrüchen schreiben, lautet die XML-Struktur wie folgt: ↓
'<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>'
In der obigen XML wird es durch ein Attribut definiert, nicht durch ein Element. Dies wird durch ein Attribut definiert, das auf den Spezifikationen beim Lesen von XML mit der später beschriebenen SQL Server OPEN XML-Funktion basiert. Danach frage ich mich, wie ich mit NULL (NaN / None) umgehen soll, wenn ich eine XML-Zeichenfolge generiere. Ich denke, dass fehlende Werte natürlich in den von Pandas verarbeiteten Daten erscheinen, aber zu diesem Zeitpunkt kann NULL (NaN / None) nicht gut behandelt werden, wenn es als XML-Zeichenfolge definiert wird. Fügen Sie also eine Zeichenfolge hinzu. Es ist notwendig, an eine Problemumgehung wie das Geben zu denken. (Im obigen Beispiel haben beispielsweise 1301 Polar Ocean und 7203 Toyota Motor LEI-Code, während 3778 Sakura Internet [LEI-Code] hat. ](Https://www.gleif.org/ja/) hat keine und konvertiert zwangsweise NULL-Daten in Zeichen wie "nicht gefunden", um eine XML-Zeichenfolge zu generieren, und SQL Server wird später beschrieben Ist es notwendig, nach dem Lesen mit der OPENXML-Funktion in gut mit ihnen umzugehen? Hier gibt es Raum für Verbesserungen.
3. Zweite Hälfte (Lese- / Kompositionsdaten aus XML-Zeichenfolge mit OPENXML-Funktion auf 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;
Ausführungsergebnis der obigen SQL-Abfrage ↓
SQL Server unterstützt eine Funktion namens OPENXML, die XML-Zeichenfolgen als Standardausrüstung verarbeitet. Mit dieser OPENXML-Funktion wird die von Python oben generierte XML-Zeichenfolge in Daten konvertiert, die von SQL betrieben werden können. Wenn Sie dann die Daten in der temporären Tabelle #temp (SELECT * INTO ~ -Klausel) speichern, verbinden Sie die Tabelle #temp und die Speicherzieltabelle und zeichnen Sie das #temp auf, das noch nicht in der Speicherzieltabelle vorhanden ist. Wenn eine einzufügende Transaktionsabfrage in der Speicherzieltabelle als Differenzdatensatz beschrieben wird, können die Daten der Tabelle anscheinend aktualisiert werden, während die Eindeutigkeit der Daten sichergestellt wird.
Die in der ersten Hälfte generierte XML-Zeichenfolge (XML-Struktur) und die XML-Lesebeschreibung beim Lesen mit der SQL OPENXML-Funktion in der zweiten Hälfte müssen exakt identisch sein, und die zu behandelnde XML-Struktur ist die erste Hälfte. Es scheint, dass dies ein zu beachtender Punkt ist, da der Teil zum Auswerfen von XML-Zeichenfolgen und der Teil zum Lesen von XML-Zeichen in der zweiten Hälfte identisch sein müssen. (Wenn die XML-Zeichenfolge in dem von der OPENXML-Funktion von SQLServer gelesenen Teil nicht gut gelesen werden kann, wird die in Python in der ersten Hälfte des XML-Beschreibungsteils generierte Zeichenfolge normalerweise mit dem von OPENXML in SQLServer gelesenen Teil synchronisiert. Möglicherweise nicht)
4. Führen Sie die gespeicherte SQL Server-Prozedur in SQL Alchemy aus
file1.py
import sqlalchemy
#Zeichenfolge für die DB-Verbindungseinstellung
CONNECT_INFO = 'mssql+pyodbc://hoge' #hoge = ODBC-Name
engine = sqlalchemy.create_engine(CONNECT_INFO, encoding='utf-8')
#...(Unterlassung)...
##DB-Update
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
##Gespeicherte Ausführung
query = 'EXEC dbo.spUpdatehoge @prm = {0}'.format(tree)
res = session.execute(query) #sp Ausführung
print(res.fetchall())
session.commit() #verpflichten
session.close()
Wenn Sie eine gespeicherte SQL Server-Prozedur mit Python (SQL Alchemy) ausführen, möchte ich den Rückgabewert (Antwort) für die Ausführung der gespeicherten Prozedur in Python abrufen und im Teil "session.execute (query)" der obigen Abfrage anzeigen. In diesem Fall können Sie das Ergebnis der gespeicherten Ausführung auf Python sofort sehen, wenn Sie den Status des Aktualisierungsergebnisses mit der Select-Anweisung anstelle der Return-Klausel in der gespeicherten Prozedur zurückgeben. Anscheinend kann das ResultProxy-Objekt von SQLAlchemy das von der Return-Klausel in der gespeicherten Prozedur gut zurückgegebene Ergebnis nicht erhalten. Die diesmal beschriebene Aktualisierungsmethode ist unterteilt in den Teil, in dem die XML-Zeichenfolge in der ersten Hälfte vom Python-Skript generiert wird, den Teil, in dem die OPENXML-Verarbeitung in der gespeicherten Prozedur in SQL Server in der zweiten Hälfte ausgeführt wird, und die erste Hälfte / zweite Hälfte der Verarbeitung. Daher halte ich es für beruhigend, wenn die Verarbeitungsergebnisse der gespeicherten Prozedur (Erfolg und Misserfolg) von SQLAlchemy of Python erfasst und angezeigt werden können.
Recommended Posts