SQLite zu CSV, Performance Problem

[.stephan]

Hallo!

Ich habe gerade erst angefangen mich mit Python zu beschäftigen und stehe daher gerade vor einem kleinen Programmierproblem. Ich habe eine CSV Datei, welche ich mit Python via csv.reader einlese und in deine SQLite Datenbank packe. Soweit funktioniert alles perfekt. Nun möchte eine SQL Abfrage ausführen, selbige sieht so aus:

Code: Alles auswählen

nods = cursor.execute('SELECT data.*, data1.* FROM data, data as data1 WHERE data.fromnode = data1.tonode AND data.gridcode <> data1.gridcode')

Auch diese funktioniert soweit wunderbar und liefert mir das, was ich möchte. ABER....das Ergebnis soll nun in eine CSV Datei gepackt werden. Aktuell sieht mein Code dazu so aus:

Code: Alles auswählen

nods = cursor.execute('SELECT data.*, data1.* FROM data, data as data1 WHERE data.fromnode = data1.tonode AND data.gridcode <> data1.gridcode')
output = csv.writer(open(outputFile, 'w'), delimiter=";")
for row in nods:
   output.writerow(row)

Problem...das ist unglaublich langsam und meine Datenbank hat eine ganze Menge >1 Mio. Einträge. Zudem habe ich dummerweise noch nach jeder Datenzeile eine Leerzeile drin, welche ich eigentlich auch nicht möchte, da gerantiert die Software, welche die CSV Datei später wieder importieren soll, damit Probleme hat.

Nun hatte ich schon überlegt, ob es sinn macht sowas wie CREATE TABLE nodTab AS SELECT,......
ausführen zu lassen, um mir eine Tabelle Namens nodTab zu erzeugen, welche die Daten des SELECT enthält, und das dann ins csv schreiben zu lassen, was vermutlich deutlich schneller gehen würde. Allerdings macht das CREATE TABLE AS.... leider nichts. Bekomme zwar auch keinen Fehler geschmissen, aber es passiert irgendwie auch nichts. Meine Datenbank müsste ja theoretisch allein von Seiten der Dateigröße deutlich größer werden, wenn eine neue Tabelle inkl. des SELECTS darin geschrieben wird.

Wäre für Hilfe sehr dankbar, da mir ein wenig die Zeit im Nacken sitzt hier zu einer Lösung zu kommen

[Hyperion]

Hallo und Willkommen im Forum!

Für Python-Code haben wir spezielle Python-Code-Tags: [ code=python ].

Ich würde bei der SQL-Query generell `join` benutzen:

Code: Alles auswählen

SELECT data.*, data1.* 
FROM data join data as data1 on data.fromnode = data1.tonode
WHERE data.gridcode <> data1.gridcode

k.A. ob die Engine da dann besser optimiert (Ja, ich weiß, Dein Abfrage war nicht das Problem), aber es wird lesbarer

Evtl. löst das schon Dein Problem: Du musst beim CSV-Modul Dateien immer binär öffnen:

Code: Alles auswählen

with open(outputFile, 'wb') as f:
    writer = csv.writer(f, delimiter=";")
    for row in nods.fetchall():
        writer.writerow(row)

[BlackJack]

@.stephan: Bei dem Code von Hyperion könnte man noch die Schleife aus dem Python-Code verbannen:

Code: Alles auswählen

with open(outputFile, 'wb') as csv_file:
    writer = csv.writer(csv_file, delimiter=';')
    writer.writerows(nodes.fetchall())

Ist es denn tatsächlich der CSV-Teil der langsam ist? Nicht das die Abfrage tatsächlich das Kreuzprodukt der Tabelle mit sich selbst bildet, weil es keine Möglichkeit gibt das aufgrund eines oder mehrerer Indizes intelligenter zu machen.

[.stephan]

Danke für die Antworten

Die Schleife hatte ich schon rausgenommen. Ist leider trotzdem unglaublich langsam
Das es an der Abfrage selbst liegt, kann ich leider nicht ausschließen.

[Hyperion]

Das es an der Abfrage selbst liegt, kann ich leider nicht ausschließen.

Öhem... ich dachte das hättest Du schon!?

Zumal das doch einfach geht: Nimm doch alle Ausgaben raus und prüfe, wie lange die Abfrage an sich braucht!

[.stephan]

2.016 sekunden

[Hyperion]

Ist das schon eine Abfrage mit `join`? Oder noch die mit simplen Kreuzprodukt?

[.stephan]

mit join

[Hyperion]

Also lag es an der Abfrage oder sind diese 2 Sekunden nun "lang"? Ach so, ich bin eigentlich kein Taschentuch!

[.stephan]

nein diese 2 sekunden sind ganz und gar nicht lang.
wenn ich es auf beschriebene art und weise in eine csv datei schreiben möchte, dann kann ich den rechner auch 2 stunden laufen lassen, ohne das was passiert. bzw. wenn ich es einfach via

Code: Alles auswählen

    output = csv.writer(open(outputFile, "wb"), delimiter=";")
    output.writerows(nods)

mache, dann bekomme ich ja praktisch in real time mit, wie die Datei anwächst.
Der schreibt dann innerhalb von 20 Minuten vielleicht 8 kb in die Datei...und ich würde mal davon ausgehen, dass die Datei am Ende so 100 mb haben sollte.

[.stephan]

müsste mir nich

Code: Alles auswählen

cursor.execute('CREATE TABLE selection AS SELECT DATA.*, data1.* FROM DATA JOIN DATA AS data1 ON DATA.fromnode = data1.tonode WHERE DATA.gridcode <> data1.gridcode')
    verbindung.commit();

Die Abfrage praktisch direkt in eine neue SQLITE Tabelle packen?
Das dauert leider auch schon ewig - bzw. lange genug, um irgendwann den Prozess mutwillig abzubrechen.

[Hyperion]

Wie sieht denn jetzt der tatsächliche vollständige Code aus?

Sofern das `csv`-Modul Dateien nicht automatisch schließt, ist es besser die Variante mit `with` zu verwenden

Gibt es sonst noch Dinge, die für das Problem interessant sein könnten? Liegt die DB lokal vor oder greifst Du via Web oder einem verteilten Filesystem darauf zu?

Alleine aus Deinen bisherigen Beschreibungen wird man nicht viel mehr herausfinden können!

Und nur Interesse halber: Ist dei Abfrage mit `join` denn schneller als die ursprüngliche?

[BlackJack]

@.stephan: Das kann eigentlich nicht sein. Dann verschweigst Du uns noch irgend ein Detail von dem Einsatzszenario.

Sind die zwei Sekunden wirklich die Abfragezeit? Also hast Du da auch das Ergebnis abgerufen oder nur die Abfrage übermittelt? *Das* bedeutet ja noch nicht zwangsläufig, dass die Abfrage auch wirklich schon durchgeführt wurde. Das kann auch bedeuten, dass das DBMS die Anfrage in einen Ablaufplan übersetzt und alles dafür vorbereitet hat den auszuführen wenn man anfängt die Datensätze auch wirklich abzufragen.

Auf diese Möglichkeit deutet auch die Laufzeit des ``CREATE TABLE … AS SELECT`` hin. *Hast* Du denn Indizes auf den Spalten auf denen der ``JOIN`` operiert, oder muss das DBMS da stumpf das Kreuzprodukt von Millionen Datensätzen bilden?

@Hyperion: ``JOIN`` macht nichts auf magische Weise schneller. Wenn es keine Indizes gibt, könnte das DBMS ``JOIN`` als Hinweis verstehen temporär welche zu erstellen, aber darauf verlassen sollte man sich nicht.

[.stephan]

Der vollständige Code schaut aktuell so aus

Code: Alles auswählen

from __future__ import with_statement 
try:
    import csv, time, sqlite3
    import arcpy
       
    tic = time.clock()
    
    # Umgebungsvariablen werden gesetzt
    arcpy.env.workspace = (r'G:\01_arbeit\00_testraum') # @workstation
    
    # pruefen der Lizenzen
    arcpy.CheckOutExtension("Spatial")
    
    # lokale Variable
    database = "C:\\Users\\stephan\\Desktop\\testlauf\\nods.db" # @laptop
    daten = "C:\\Users\\stephan\\Desktop\\testlauf\\export.txt"
    outputFile = "C:\\Users\\stephan\\Desktop\\testlauf\\output.txt"
 
    verbindung = sqlite3.connect(database)
    cursor = verbindung.cursor()
    setStruktur = 'CREATE TABLE data (fid INTEGER, arcid INTEGER, gridcode INTEGER, fromnode INTEGER, tonode INTEGER)'
    setIndex = 'CREATE INDEX id ON data(gridcode,fromnode,tonode)'
    cursor.execute(setStruktur)
    cursor.execute(setIndex)
    # -----------------------------------------------------------------------------------------------
    reader = csv.reader(open(daten, "rb"), delimiter=";")
    for FID,ARCID,GRID_CODE,FROM_NODE,TO_NODE in reader:
        cursor.execute('INSERT INTO data (arcid,gridcode,fromnode,tonode) VALUES (?,?,?,?)',(ARCID,GRID_CODE,FROM_NODE,TO_NODE))
    verbindung.commit()
    # -----------------------------------------------------------------------------------------------
    nods = cursor.execute('SELECT DATA.*, data1.* FROM DATA JOIN DATA AS data1 ON DATA.fromnode = data1.tonode WHERE DATA.gridcode <> data1.gridcode')
    with open(outputFile, 'wb') as csv_file:
        writer = csv.writer(csv_file, delimiter=';')
        writer.writerows(nods.fetchall())
    # -----------------------------------------------------------------------------------------------
    
    verbindung.close()
    
    toc = time.clock()
    print ('Benoetigte Zeit: ' + str(toc-tic))  

except:
    print "Operation failed."
    print arcpy.GetMessages()

[BlackJack]

@.stephan: Du erstellst einen Index für (gridcode, fromnode, tonode) — das wird aber nirgends benötigt. Die drei Spalten *zusammen* werden in Deiner Abfrage nie als Schlüssel verwendet. Ich hätte da einen Index auf `fromnode` und einen weiteren auf `tonode` erstellt, weil das die beiden „Seiten” von dem JOIN sind.

[Hyperion]

Du verwendest beim Setzen des Workspace-Pfades RawStrings - danach bei den Pfaden aber nicht - das solltest Du mal ändern