Wie mache ich das? sqlparse => json

[aisberg]

Ich möchte sqlparse verwenden, um "create VIEW" statements für SQL Server Sichten zu parsen und das Ergebnis des Parsers soll zurück in den SQL Server, als JSON, damit ich dann im SQL Server damit arbeiten kann. Insbesondere will ich daraus Spaltenabhängigkeiten zwischen Sichten extrahieren, die ich aus den System-Sichten nicht rausbekomme. Dort bekomme ich nur Spaltenabhängigkeiten zu Quell-Spalten in Tabellen extrahiert.

Warum will ich das Ergebnis des Parsers im SQL Server verwenden? Dort ist mein Zuhause und mit TSQL kenne ich mich aus. Mit Python habe ich leider zu wenig Erfahrung, sodass es für mich wahrscheinlich einfacher wird, das Ergebnis des Parsers mit TSQL zu verarbeiten.

Der Parser liefert eine geschachtelte Token-Liste aus einzelnen Token, und die will ich in JSON convertieren, und zwar nicht flach (das funktioniert schon mit jsonpickle), sondern als geschachtelte Liste.

Code: Alles auswählen

# https://github.com/andialbrecht/sqlparse
import sqlparse

# # https://docs.python.org/3/library/json.html
import json

# import simplejson as json

# https://github.com/jsonpickle/jsonpickle
# https://jsonpickle.github.io/
# https://jsonpickle.readthedocs.io/en/latest/api.html
import jsonpickle

# Split a string containing two SQL statements:
# raw = 'select * from foo; select * from bar;'
# statements = sqlparse.split(raw)
# print(statements)
# # Get the first statement
# stmt = statements[0]

stmt = """
CREATE VIEW a.c AS
SELECT
[Date] = [T1].[Date],
aaa = /*Comment line 1
Comment line 2*/
bbb,
ccc as ccc1,
ddd as ddd1,
eee = /*Comment line 1
Comment line 2*/
eee,
fff = fff
FROM
a.b T1 (NOLOCK)
LEFT JOIN c.d T2
ON T1.jjj = T2.jjj
"""

stmt_format1 = sqlparse.format(stmt, strip_comments=False, use_space_around_operators=False, comma_first=True, reindent=True, keyword_case='upper')
## remove block comments in the first step, otherwise some unwanted indents are created
stmt_format2 = sqlparse.format(stmt_format1, strip_comments=True, use_space_around_operators=False, comma_first=True, reindent=True, keyword_case='upper')
## reformat the statement with removed comments to get results without unwanted indents
stmt_format3 = sqlparse.format(stmt_format2, strip_comments=True, use_space_around_operators=False, comma_first=True, reindent=True, keyword_case='upper')

print(stmt_format3)

# Parsing a SQL statement:
# the result is fine and nested, there is an IdentifierList which I would like to keep as one group
parsed = sqlparse.parse(stmt_format3)[0]

# # json.dumps doesn't work, the object is to complex
# jsonStr = json.dumps(dict(parsed.tokens))

# I found jsonpickle
# it can convert also complex objects into json
frozen = jsonpickle.encode(parsed.tokens,keys=True,indent=1)

print(frozen)

# # the object is not restored as the original because the json represenation lost information
# thawed = jsonpickle.decode(frozen)

# # I dont get nested tokens exported into json, instead I get only the token type, but not the content:

#  {
#   "py/object": "sqlparse.sql.IdentifierList"
#  },

#  {
#   "py/object": "sqlparse.sql.Identifier"
#  },


# to get all tokens with content, I can flatten the "parsed" and convert to json
flattened = list(parsed.flatten())

print (flattened)

frozen2 = jsonpickle.encode(flattened,keys=True,indent=1)

print(frozen2)

# But now they are in a flat list and I lost information about parents and nested structures

# print(parsed.tokens)

[narpfel]

Moin,

du hast eine rekursive Datenstruktur, also ist es am einfachsten, eine rekursive Funktion zu benutzen, um die Datenstruktur als JSON zu serialisieren.

Pseudocode:

Code: Alles auswählen

def serialise(tokens):
    for token in tokens:
        if isinstance(token, sqlparse.sql.TokenList):
            yield {"type": token.type, "str": str(token), "children": serialise(token.tokens), ...}
        else:
            yield {"type": token.type, "str": str(token), "children": None, ...}


print(json.dumps(serialise(sqlparse.parse(...)[0])))

[Sirius3]

Und um kopierten Code zu vermeiden:

Code: Alles auswählen

def serialise(tokens):
    for token in tokens:
        if isinstance(token, sqlparse.sql.TokenList):
            children = serialise(token.tokens)
        else:
            children = None
        yield {"type": token.type, "str": str(token), "children": children, ...}

[__blackjack__]

Statt Typtest würde ich das `is_group`-Attribut testen. Das macht `sqlparse` selbst auch. Dann ist das auch kurz genug um es als bedingten Ausdruck in einer Zeile zu schreiben:

Code: Alles auswählen

def serialise(tokens):
    for token in tokens:
        children = serialise(token.tokens) if token.is_group else None
        yield {"type": token.type, "str": str(token), "children": children}

Oder Alternativ als ein Generatorausdruck:

Code: Alles auswählen

def serialise(tokens):
    return (
        {
            "type": token.type,
            "str": str(token),
            "children": serialise(token.tokens) if token.is_group else None,
        }
        for token in tokens
    )

[aisberg]

Danke, die Idee habe ich verstanden.

Allerdings muss ich das Ergebnis noch in json bekommen, dazu muss ich wohl erst mal das Ergebnis des Generators in irgendwas (dict?) bekommen, bevor es dann in JSON konvertiert werden kann.
Und mir ist noch nicht klar, wie ich das Ergebnis der Rekursion auch als JSON-fertigen Text bekomme, statt nur das Objekt.

Sorry für meine Anfänger-Fragen, aber ich habe bisher nur ganz wenig in Python programmiert, und dann auch meistens nur Beispiele angepasst.

Code: Alles auswählen

# def serialise(tokens):
#     for token in tokens:
#         children = serialise(token.tokens) if token.is_group else None
#         if not token.is_whitespace:
#             yield {"ttype": token.ttype, "str": str(token), "normalized": token.normalized, "is_group": token.is_group, "is_keyword": token.is_keyword, "is_whitespace": token.is_whitespace, "children": children}

def serialise(tokens):
    return(
        {
            "ttype": token.ttype,
            "str": str(token),
            "normalized": token.normalized,
            "is_group": token.is_group,
            "is_keyword": token.is_keyword,
            "is_whitespace": token.is_whitespace,
            "children": serialise(token.tokens) if token.is_group else None,
        }
        for token in tokens
    )

...

parsed = sqlparse.parse(stmt_format3)[0]
my_tokens = (parsed.tokens)
serialized = serialise(my_tokens)

# druckt nur den Namen des Objects aus: wie
# <generator object serialise at 0x000001FB530E39E0>
print(serialized)

# Das funktioniert schon fast, bis auf die Rekursion, die wird auch nur als Objekt ausgedruckt:
# {'type': None, 'str': 'a.c', 'normalized': 'a.c', 'is_group': True, 'is_keyword': False, 'is_whitespace': False, 'children': <generator object serialise at 0x000001FB530D1740>}
# ich muss es also noch schaffen, dass auch da statt des Objekts der Inhalt kommt
for i in serialized:
    print(i)

# Das funktioniert nicht, weil ich ja erst mal ein dict (oder was auch immer) brauche, in das ich das Ergebnis von serialized reinschreibe
# Object of type generator is not JSON serializable
jsonStr = json.dumps(serialise(my_tokens))
# oder
jsonStr = json.dumps(serialized)

[aisberg]

und noch eine ergänzende Frage:
Manche Token haben "function variables" wie get_alias, get_name, usw.
Kann man noch irgendwie so was machen? Wobei mich natürlich nur einige Funktionen interessieren, die ich fest angegeben will.

Code: Alles auswählen

wenn die Funktion xyz für den token existiert, dann: "xyz": "Rückgabewert der wie auch immer aufzurufenden Funktion xyz"

[aisberg]

Für die Konvertierung in JSON habe ich jetzt eine Lösung gefunden. Damit funktioniert auch die Rekursion.
Wie das ohne simplejson geht, weiß ich nicht, irgendwie scheint ja simplejson was anderes als json zu sein

Code: Alles auswählen

import simplejson as json

# Hier der andere Code

#  simplejson
# Changed in version 3.8.0: iterable_as_array is new in 3.8.0.
jsonStr = json.dumps(serialise(my_tokens), iterable_as_array=True, indent=1)
print(jsonStr)

Bleibt noch die Frage, ob und wie ich den Inhalt der Funktionen in das json bekomme

[Sirius3]

`serialise` ist in Generator, und json.dump braucht eine Liste, also mußt Du das Ergebnis des Generators in eine Liste umwandeln:

Code: Alles auswählen

json_data = json.dumps(list(serialise(tokens)))

[__blackjack__]

Erst einmal das serialisieren ein kleines bisschen umgestellt, so das die Funktion nur noch ein Token als Argument nimmt:

Code: Alles auswählen

#!/usr/bin/env python3
import json

import sqlparse

STATEMENT = """\
CREATE VIEW a.c AS
SELECT
[Date] = [T1].[Date],
aaa = /*Comment line 1
Comment line 2*/
bbb,
ccc as ccc1,
ddd as ddd1,
eee = /*Comment line 1
Comment line 2*/
eee,
fff = fff
FROM
a.b T1 (NOLOCK)
LEFT JOIN c.d T2
ON T1.jjj = T2.jjj
"""


def serialise(token):
    return {
        "ttype": token.ttype,
        "str": str(token),
        "normalized": token.normalized,
        "is_group": token.is_group,
        "is_keyword": token.is_keyword,
        "is_whitespace": token.is_whitespace,
        "children": list(map(serialise, token)) if token.is_group else [],
    }


def main():
    statement = sqlparse.format(
        STATEMENT, strip_comments=True, strip_whitespace=True
    )
    print(statement)
    print(json.dumps(serialise(sqlparse.parse(statement)[0]), indent=2))


if __name__ == "__main__":
    main()

[aisberg]

Super, das funktioniert jetzt wie erwartet, und mit dem normalen json Modul. Und ich lerne wieder sehr gut dazu.
Vielen Dank!

Hoffentlich sind meine Ergänzungen OK
- ist das OK, dass es nach dem "if" auch mal kein return gibt, oder ist das ein schlechter Stil?
- kann man die "class" auch auf elegantere Art und Weise extrahieren?

Code: Alles auswählen

def serialise(token):
    if not token.is_whitespace:
      return {
          "ttype": token.ttype,
          "str": str(token),
          "normalized": token.normalized,
          "is_group": token.is_group,
          "is_keyword": token.is_keyword,
          "is_whitespace": token.is_whitespace,
          # "<class 'sqlparse.sql.Identifier'>"
          # How to get only 'sqlparse.sql.Identifier'?
          # substring or are there other ways?
          "class": str(token.__class__)[8:-2],
          "children": list(map(serialise, token)) if token.is_group else [],
      }

[Jankie]

Du könntest die class mit regulären Ausdrücken herausfiltern.

[narpfel]

@aisberg:

Code: Alles auswählen

type(token).__qualname__

Edit: Wenn eine Funktion ohne `return` endet, wird implizit `None` zurückgegeben. Das will man meistens nicht, weil man dann bei jedem Funktionsaufruf prüfen müsste, ob man einen sinnvollen Wert oder `None` zurückbekommen hat. In deinem Fall bekommst du dadurch ein `null` für jedes Whitespace-Token in dein JSON.

[Sirius3]

Die Stringrepräsentation ist nicht zum Weiterverarbeiten gedacht, sondern nur für Debugzwecke.
Hier willst Du die Klasse aus Modul und Namen zusammensetzen:

Code: Alles auswählen

f"{token.__class__.__module__}.{token.__class__.__name__}"

[__blackjack__]

Wenn filtern von Whitespace-Token nur bei Kindern (rekursiv) ausreicht, dann lässt sich das so filtern:

Code: Alles auswählen

def serialise(token):
    return {
        "ttype": token.ttype,
        "str": str(token),
        "normalized": token.normalized,
        "is_group": token.is_group,
        "is_keyword": token.is_keyword,
        "is_whitespace": token.is_whitespace,
        "class": token.__class__.__name__,
        "children": (
            [serialise(child) for child in token if not child.is_whitespace]
            if token.is_group
            else []
        ),
    }

[aisberg]

"class": token.__class__.__name__,
=> das funktioniert super

Mit den vielen zurückgegebenen NULL das ist mir auch aufgefallen, wenn der return Block fehlt.
Falls sie bei der Weiterverarbeitung nicht stören, kann ich sie lassen. Zumindest ist es ja valides JSON. Falls die NULL doch stören, dann kann ich ja auch wieder auf die Variante mit yield umstellen, um nur dann was zu bekommen, wenn ich es brauche.

Die Variante mit dem return statt den yield schien mir "schöner" zu sein, ist so selbsterklärend. Wobei die Frage ist, ob Code auch immer schön sein muss.

[__blackjack__]

@aisberg: Whitespace filtert mein Codebeispiel doch auch raus, wo kommen denn da noch ``null``-Werte her?

Klar muss Code immer schön sein. Problem: die Definition von „schön“. Ich weiss das meine Definition richtig ist — das wollen leider nicht alle wahrhaben.

[aisberg]

@__blackjack__
Mathematik, Programmieren, Datenbank-Architektur - das hat für mich immer auch etwas mit Ästhetik zu tun
Dein Beispiel filtert die Einträge bei den Kindern weg. Und wenn ich mir das richtig überlege, reicht das auch, weil der übergeordnete Knoten ein "Statement" ist mit genau einem Kind. Und somit sollte das passen. Ich werde das gleich noch mit einbauen.
Andererseits stören mich die NULL aber auch nicht, da ich die JSON Objekte ja im SQL Server mit TSQL weiterverarbeite und so, wie ich das mache, fallen sie da dann auch raus.
Aber da es ja auch um Ästhetik und Perfektionismus geht, werde ich es einbauen.

Ich bin positiv überrascht, wie gut und schnell hier geholfen wird. Und ich habe heute einige Einblicke in Python gewonnen.

Ursprünglich war ja eine wichtige Motivation, dass Python auch direkt im SQL Server unterstützt wird (was ich aber bei mir nicht hinbekomme und das jetzt aufgebe). Python ist eine schöne Sprache, wobei ich mich noch mit den verschiedenen Klammern und deren Bedeutung befassen muss. Meine letzten Programme habe ich ja noch Ende der 80er und Beginn der 90er mit Turbo Pascal geschrieben. Seit 1999 nur noch TSQL. Aber es macht Spaß, sich doch etwas intensiver mit Python zu beschäftigen.