PyOdbc verbunden mit MSSql. MSSql liefert json mit Binärdaten

[rbaert]

Hallo Python Experten

Ich erstelle zur Zeit eine API mit Fast-API. Die Daten kommen aus Prozeduren von einem MSSql Server. Die API soll dann eine React-Native-App mit Daten versorgen.

Also ich sende einfach den Befehl "Execute dbo.irgendeineProzedur @parameter1 = 'hallo'".

Soweit so gut. Die Prozedur liefert mir ein JSON Objekt. Sehr vereinfacht sieht diese so aus:

Code: Alles auswählen

	SELECT
		itm.[ID]
		,itm.[ItemNumber]
		,itm.[Description]
		,itm.Attachment 
	FROM dbo.Items itm							
	WHERE itm.itmID = @itmID
	FOR JSON AUTO

Wichtig dabei ist, dass es sich bei der Column Attachment um eine varbinary(max) Spalte handelt.

Der relevante Teil in Python, sieht so aus:

Code: Alles auswählen

    def execute_sql(self, query: str) -> dict:
        cursor = self.cnxn.cursor()
        cursor.execute(query)
        for row in cursor.fetchall():
            for value in row:
                if isinstance(value, str):
                    json_data = json.loads(value)
        return json_data

Wenn ich dies nun ausführe, bekomme ich den Fehler:

Code: Alles auswählen

json.decoder.JSONDecodeError: Unterminated string starting at: line 1 column 139

Macht Sinn, da es sich dabei handelt es sich sicher um ein Konvertierungs-Problem. Sinn macht auch, dass der Code funktioniert, sobald ich die binary-Daten in der Prozedur entferne.

So nun meine Fragen:
1. Wie bringe ich dies zum funktionieren und zwar so, dass ich die Binary (ist ein Bild) in der React-Native-App noch anzeigen kann?
2. Bin ich auf dem Holzweg? Sollte ich dies generell anders lösen? (z.B. einen eigenen Endpunkt für binarys in FastAPi?)
3. Wie löst ihr so ein Problem, Empfehlungen?

Danke schon mal für eure Antworten.
Gruss Roland

[__deets__]

Da JSON prinzipbedingt keine Binaerdaten enkodieren kann, hast du zwei Moeglichkeiten:

1) das Binary wird base64 oder vergleichbar als String kodiert, und muss dann client-seitig rekodiert werden.
2) was du selbst angedacht hast, Binaerdaten explizit auszuliefern.

Moeglichkeit 1 ist aufwendig und erhoeht die Datenmenge um ~25%. Darum wuerde ich zu zwei greifen.

[Sirius3]

Offensichtlich kann MSSQL keine JSON-Daten liefern. Denn sonst würde Python keinen Dekodierungsfehler werfen. Warum überhaupt Json? Mach doch einfach eine ganz normale SQL Abfrage und dann hast du mit Binärdaten auch kein Problem.

[__blackjack__]

@rbaert: Nur um sicherzugehen, dass das Problem hier richtig lokalisiert wurde: Welches ist denn das Feld mit JSON? Oder anders: Welche Felder sind denn Zeichenketten? Nicht dass Du hier versuchst Description als JSON zu dekodieren, das JSON aber tatsächlich in Attachment steckt. Und welchen Typ hat Attachment auf Python-Seite? Eventuell ist das ja auch gar keine Zeichenkette sondern ein `bytes`-Objekt.

[sparrow]

@rbaert: Es wirkt auch irgendwie komisch jedes Feld als JSON zu lesen. Muss das so? Ist nicht der komplette Response nicht ein JSON Objekt?

[rbaert]

Da JSON prinzipbedingt keine Binaerdaten enkodieren kann, hast du zwei Moeglichkeiten:

1) das Binary wird base64 oder vergleichbar als String kodiert, und muss dann client-seitig rekodiert werden.
2) was du selbst angedacht hast, Binaerdaten explizit auszuliefern.

Moeglichkeit 1 ist aufwendig und erhoeht die Datenmenge um ~25%. Darum wuerde ich zu zwei greifen.

Das mit der Convertierung mit base64 habe ich versucht. Hättest du da Beispiel-Code? Bei mir wollte dies nicht funktionieren. Eigentlich würde ich dies gerne machen, da ich die API noch für andere Anwendungen nutzen möchte. Leider habe ich es nicht geschafft, das ganze zum fliegen zu kriegen.
Die Binärdaten explizit ausliefern ist auch eine Möglichkeit. Ich werde es versuchen, allerdings habe ich dann zwei Arten von Endpunkten und ich muss immer unterscheiden. Wenn die API grösser wird, könnten dadurch Fehler entstehen. Deshalb favorisiere ich trotz deinem Abraten aktuell noch Möglichkeit 1.

P.S. im ursprünglichen Versuch konvertiere ich auf der SQL Seite das varbinary in varchar. Nun sollte es eigentlich reiner Text sein. Leider kommt die Fehlermeldung dann immer noch....

Offensichtlich kann MSSQL keine JSON-Daten liefern. Denn sonst würde Python keinen Dekodierungsfehler werfen.

Da muss ich dir Recht geben. MSSql kann nur die Struktur des JSON imitieren. Wenn also Binary daherkommt, wird dieses einfach abgefüllt wie es in der Datenbank abgelegt ist. Es ist nicht eine Konvertierung zu JSON, sondern nur die Struktur. Die Konvertierung müsste man selber machen. Leider habe ich noch keine schlaue Antwort gefunden, auf was ich das varbinary convertieren soll, damit es JSON kompatibel ist. Ich habe auf SQL-Seite einiges versucht. Das Problem blieb aber bestehen.

Warum überhaupt Json? Mach doch einfach eine ganz normale SQL Abfrage und dann hast du mit Binärdaten auch kein Problem.

JSON weil ich im echten leben noch verschiedene Lagerorte mitgebe. Da können pro Item mehrere vorhanden sein. Ich habe das SQL-Statement stark vereinfacht für diesen Eintrag. Ich muss aber eine Datenstruktur haben, die "Arrays" aufnehmen kann. Da aber MSSql mit JSON offensichtlich sehr schwach ist, muss ich dies vielleicht nochmal überdenken.

@rbaert: Nur um sicherzugehen, dass das Problem hier richtig lokalisiert wurde: Welches ist denn das Feld mit JSON? Oder anders: Welche Felder sind denn Zeichenketten? Nicht dass Du hier versuchst Description als JSON zu dekodieren, das JSON aber tatsächlich in Attachment steckt. Und welchen Typ hat Attachment auf Python-Seite? Eventuell ist das ja auch gar keine Zeichenkette sondern ein `bytes`-Objekt.

Wenn ich das richtig verstehe sind in der Description die Spaltennamen, etc. Da kommt von SQL ein kryptischer Feldnamen daher (weil ich ja auch keinen Namen mitgebe). Deshalb ignoriere ich den Feldnamen und nehme nur den Inhalt. Description ist das nicht, denn ich muss zugeben, dass ich dies am Anfang versucht habe. Aktuell bekomme ich die richtigen Werte zurück, wenn ich Attachment rausnehme funktioniert alles wunschgemäß. Attachment wäre innerhalb des JSON strings. Und nein, es ist kein 'bytes' es ist ein String, wenn auch mit kryptischen Zeichen darin.

@rbaert: Es wirkt auch irgendwie komisch jedes Feld als JSON zu lesen. Muss das so? Ist nicht der komplette Response nicht ein JSON Objekt?

Ich habe mich am Anfang entschieden, dass alle Prozeduren aus SQL nur einen Wert und zwar ein JSON zurückgeben sollen. Faktisch bekomme ich eine Tabelle mit nur einer Spalte und nur einer Zeile zurück. Allerdings zweifle ich während ich diese Zeile schreibe auch daran ob dies der richtige Weg ist. Evtl. kann ich in der Prozedur kein Select ausführen, sondern ein Return. Das ist ein guter Input, danke. Bleibt aber immer noch das Problem mit der Konvertierung.

[sparrow]

Warum soll denn der SQL-Server überhaupt JSON zurückgeben? Das kann man ja selbst generieren oder gleich ein ORM.

[__deets__]

Ich habe keinen beispielcode für base64 aus deiner DB. Ich nutze die nicht. In Python ist das mit dem base64-Modul trivial, den Einzeiler bekommst du hin.

Du scheinst aber etwas zu sehr an einmal getroffenen Entscheidungen zu hängen. Neue Anforderungen erfordern regelmäßig die Anpassung einer API oder allgemeiner Vorgehens. Statt dir das Leben schwer zu machen, Bau einfach mehrere Endpunkte. Die Begründung das wäre Fehleranfälliger kann ich nicht nachvollziehen.

[grubenfox]

im echten leben noch verschiedene Lagerorte mitgebe. Da können pro Item mehrere vorhanden sein. Ich habe das SQL-Statement stark vereinfacht für diesen Eintrag.

Das klingt für mich erst mal nach einem SQL-Join...('Left outer join' möglicherweise) je nach Anzahl der Lagerorte erscheint das jeweilige Item in der Ergebnismenge dann in 1-n Zeilen. das muss man dann außerhalb der DB wieder zusammenfassen (und/oder in JSON konvertieren).

[sparrow]

@rbaert: Nur um das noch einmal zu unterstreichen, was Sirius3 bereits deutlich gesagt hat: Das Problem ist, fas du da windigen Kram machst um JSON aus der Datenbank zu bekommen. Weder zwingt dich dazu jemand, noch verfängt ein Argument, dass das für die API von Bedeutung wäre. Dem Client ist egal, ob das JSON aus der Datenbank kommt, die Oma dss unterm Küchentisch klöppelt oder du das im View baust.