CSV in MySQL schreiben

[snatch]

Hallo,
ich muss ein kleines Programm schreiben mit dem ich eine CSV Datei auslesen muss um die Daten aus der CSV Datei dann in eine MySQL DB zu schreiben.
Das klappt auch schon.
Nun ist es aber so das die CSV alle 15 Minuten aktualisiert wird und die Daten in der DB auch aktuell gehalten werden müssen.
Das Programm muss also alle 15 Minuten die CSV Datei checken und die Datensätze die noch nicht in der DB stehen in die DB schreiben.
Wie mache ich das am besten?

mfg
snatch

[veers]

Legst eine InMemory Tabelle an, sperrst die jeweils, machst ein DROP und dann wieder einen INSERT.

Nur... wozu das ganze?

[snatch]

Nur... wozu das ganze?

Verstehe die Frage nicht.

Die Daten immer komplett neu einzulesen wäre dumm.
Es sind nämlich jetzt schon knapp 17000 Datensätze. Es soll ganz einfach immer die DB mit den neuen Datensätzen aus der CSV Datei aktualisiert werden.

[BlackJack]

Ob das wirklich dumm wäre kommt auf den Aufwand an. Die Daten komplett neu einlesen ist einfach und kann mit `executemany()` effizient gemacht werden.

Ansonsten musst Du für jeden Datensatz prüfen, ob er schon in der DB ist und ihn gegebenfalls eintragen. Das sieht für mich nach wesentlich mehr Kommunikationsaufwand zwischen Programm und DB aus.

[gerold]

Es sind nämlich jetzt schon knapp 17000 Datensätze. Es soll ganz einfach immer die DB mit den neuen Datensätzen aus der CSV Datei aktualisiert werden.

Hallo snatch!

Ich fasse mal zusammen:

1. Möglichkeit: Du läufst alle Zeilen der CSV-Datei durch und fügst diese mit ``INSERT`` und ``execute`` zur SQL-Datenbank hinzu. Auf die Fehler, wenn ein Datensatz bereits existiert, musst du entsprechend reagieren. Nach jedem Datensatz die Transaktion commiten (conn.commit()) und bei Fehler mit einem conn.rollback() antworten. (langsam, umständlich, verbraucht unanständig viel Ressourcen)

2. Möglichkeit: Du erstellst dir eine temporäre Tabelle und schiebst alle Zeilen der CSV-Datei in einem Rutsch (mit executemany()) in die Datenbank. Danach kannst du die fehlenden Datensätze in einem Rutsch von der temporären Tabelle zur Datentabelle kopieren. Das wäre dann so eine Abfrage:

Code: Alles auswählen

INSERT INTO <zieltabelle>  (
  <zielfelder>
) SELECT <quellfelder>
FROM <quelltabelle>
WHERE <primary key> NOT IN ...<zieltabelle>...

Leider weiß ich nicht, ob MySQL damit klar kommt. <ironisch>Ich verwende nur richtige Datenbanken.</ironisch>
Danach löscht du die temporäre Tabelle wieder.

3. Du liest alle Primärschlüssel der Datentabelle aus und speicherst das Ergebnis in einem Dictionary oder einem Set. Ein Dictionary lässt sich von Python schneller durchsuchen als eine Liste. Danach läufst du die CSV-Datei Zeile für Zeile durch. Prüfst zuerst, ob die Zeile (der Primärschlüssel) im Dictionary enthalten ist oder nicht. Wenn nicht, dann fügst du die Zeile mit einem INSERT zur Datentabelle hinzu. Das geht so lange gut und sauschnell, so lange die Liste der Primärschlüssel im Speicher platz hat. Wenn das nicht mehr der Fall ist, dann kannst du statt dem Dictionary ein "Shelve" verwenden.

4. Eine Andere Lösung... aber ich mag jetzt nicht mehr weiter schreiben, da mir die Lösung 3 eh schon recht gut gefällt.

Es wird natürlich schwieriger, wenn du nicht nur fehlende Datensätze eintragen, sondern auch geänderte Datenätze überschreiben möchtest...

mfg
Gerold

[snatch]

Ich danke dir für deine Ideen.
Nur was verstehst du unter einer richtigen Datenbank?
Was nutzt du und worin bestehen die Vor- und Nachteile?

Du schreibst das du deine dritte Idee am besten findest.
Wenn ich das richtig verstehe meinst du das so:
Die Primärschlüssel aus der DB sollen ausgelesen und in ein Dict geschrieben werden. Da die Zeilennummer der CSV Datei quasi der Primärschlüssel ist, wird überprüft bis zu welcher Zeile die Primärschlüssel (Zeilennummer) aus der DB reichen. Ist die Anzahl der Zeilen in der CSV höher als der höchste Primärschlüssel, werden die Zeilen die eine höhere Zeilennummer als der Primärschlüssel haben in die DB eingelesen.
Habe ich das so richtig verstanden?

mfg

[gerold]

Da die Zeilennummer der CSV Datei quasi der Primärschlüssel ist
[...]
Habe ich das so richtig verstanden?

Hallo snatch!

Warum hast du uns die Tatsache, dass die Zeilennummer der Primärschlüssel ist, verschwiegen?

Ich klinke mich aus der Diskussion aus.

mfg
Gerold

[lunar]

Da die Zeilennummer der CSV Datei quasi der Primärschlüssel ist
[...]
Habe ich das so richtig verstanden?

Warum hast du uns die Tatsache, dass die Zeilennummer der Primärschlüssel ist, verschwiegen?

Ich denke, dass hat er nicht "verschwiegen", vielmehr kam ihm diese Idee wahrscheinlich erst beim Lesen deines Postings.

[gerold]

Ich denke, dass hat er nicht "verschwiegen", vielmehr kam ihm diese Idee wahrscheinlich erst beim Lesen deines Postings.

Hallo lunar!

Das kann natürlich sein.

lg
Gerold

[snatch]

Ich denke, dass hat er nicht "verschwiegen", vielmehr kam ihm diese Idee wahrscheinlich erst beim Lesen deines Postings.

Genau so war es.

gerold:
Wie war es denn genau von dir gedacht?
So wie ich es nämlich selbst beschrieben habe habe ich deine Idee auch verstanden.

[gerold]

So wie ich es nämlich selbst beschrieben habe habe ich deine Idee auch verstanden.

Hallo snatch!

Wenn du eine Datei hast, in der wirklich immer nur Zeilen dazu kommen können, dann ist es einfach. Dann brauchst du dir nicht alle Primärschlüssel aus der Datenbank holen. Du holst dir einfach den größten Primärschlüssel und kannst von diesem aus gehen. Die Ganze Arbeit mit dem Auslesen aller Primärschlüssel und zwischenspeichern in ein Dictionary fällt weg, da du nicht prüfen musst, ob der Datensatz schon in der Datenbank ist.

Du holst dir den größten Primärschlüssel aus der Datenbank. ``SELECT MAX(id) from zieltabelle``

Dann öffnest du die Datei zum Lesen.

Code: Alles auswählen

f = file("quelle.csv", "rU")

Dann überspringst du alle Zeilen, die bereits in der Datenbank sind.

Code: Alles auswählen

f.readlines(<max primärschlüssel in db>)

Danach kannst du mit csv durch die Zeilen iterieren und schön gemütlich jede zeile einzeln in die DB schreiben.
Einzeln läuft das dann so:

Code: Alles auswählen

import csv
my_csv = csv.reader(f)
for row in my_csv:
    conn.execute("INSERT INTO <tabellenname> (<felder>) VALUES (?, ?, ?,...)", row)
conn.commit()

Alles in einem Rutsch könnte vielleicht funktionieren, wenn "mysql-python" bei ``conn.executemany()`` das CSV-Objekt akzeptieren würde. Aber das weiß ich nicht und kann es auch nicht nachprüfen. Das würde dann so laufen:

Code: Alles auswählen

import csv
my_csv = csv.reader(f)
conn.executemany("INSERT INTO <tabellenname> (<felder>) VALUES (?, ?, ?,...)", my_csv)
conn.commit()

mfg
Gerold

[snatch]

Danke, das hat mir sehr geholfen!

Aber mich würde trotzdem noch interessieren welches Datenbanksystem du nutzt und was für dich ein "richtiges" Datenbanksystem ist?

[Leonidas]

Aber mich würde trotzdem noch interessieren welches Datenbanksystem du nutzt und was für dich ein "richtiges" Datenbanksystem ist?

Da ich Gerold schon seit, hmm, einigen Jahren kenne kann ich das beantworten: Gerold nutzt PostgreSQL. Was das angeht, stimme ich Gerold zu, für größere Sachen ist PostgreSQL ziemlich gut, kann eine Menge, verhält sich so wie man es wünschen würde, etc.

[gerold]

mich würde trotzdem noch interessieren welches Datenbanksystem du nutzt und was für dich ein "richtiges" Datenbanksystem ist?

Liebe Forenmitglieder! Bitte verzeiht mir, wenn ich jetzt nichts positives über MySQL schreibe.

Hallo Snatch!

Das kommt immer darauf an, was man vom Datenbanksystem erwartet. Für viele Webprogrammierer/-designer, genügte es schon, wenn sie ihre dynamisch erlangten Daten irgendwie abspeichern konnten. Und da diese Leute nicht anspruchsvoll waren oder es nicht besser wussten, genügte ihnen MySQL. So hat sich MySQL (mit den MyISAM-Tabellen) im Internetbereich stark verbreitet. Aber viel mehr als Daten in eine Tabelle legen und wieder auslesen kann man damit nicht. Da dieser Tabellentyp nicht viel kann, ist er aber auch recht schnell.

Es gibt Datebanksysteme, die sich *auch* um die Integrität der Daten kümmern. Auch MySQL besitzt (inzwischen) Tabellentypen, die so etwas können. Aber wann man welche Tabellentypen einsetzen "darf" und wann nicht, liegt irgendwo in den Lizenzbestimmungen verborgen.

Dann gibt es zwischendurch wieder mal Lizenzansprüche von irgendwoher oder eine Firma die sich einmischt oder zurück zieht... und irgendwie führt das dazu, dass man als Programmierer wieder nicht weiß, welchen Tabellentyp man verwenden kann/soll.

Ansprochsvollere Programmierer fragten immer mehr nach "Referenzieller Integrität", nach "Gespeicherten Prozeduren" und "Triggern". Diese Features wurden dann im Nachhinein in das System rein gequetscht. Wobei sie irgendwie die "Gespeicherten Prozeduren" vergessen haben.

Darf ich ein kommerziell vermarktetes Programm schreiben und die Installation von MySQL mit in die Setuproutine meines Programmes einbeziehen? Oder muss ich dafür schon eine kommerzielle Lizenz von der Firma "MySQL AB" kaufen?

Es gibt ein Datenbanksystem das komplett frei verwendet werden darf, schon viele Jahre Entwicklung hinter sich hat und immer Wert darauf gelegt hat, den zur damaligen und heutigen Zeit gültigen SQL-Standards so nahe wie möglich zu kommen. Dieses Datenbanksystem hat eine wirklich gute und schnelle Python-Schnittstelle. Es ist flexibel. Es können sogar direkt (intern) Prozeduren in Python programmiert werden. Es hat ein ausgeklügeltes Berechtigungssystem. Es kann sogar Signale (vom Server aus) an die Clients schicken. Seit einigen Versionen gibt es sogar eine native Windows-Version mit einfacher Installationsroutine. Und nennt sich PostgreSQL.

Zum Abschluss noch: Natürlich gibt es auch noch andere "gscheide" Datenbanksysteme. Einige davon sind sogar richtig gut, aber die kann man sich kaum leisten.

mfg
Gerold

[gerold]

Ich muss noch etwas los werden.

Manch einer mag fragen, wozu diese ganze Datenbankintegrität und warum soll sich denn das Datenbanksystem darum kümmern, dass die Daten zusammen passen?

Erst gestern passiert:
Ein Freund von mir übersiedelt mit einem Webforum (so etwas ähnliches wie unser Python-Forum) auf einen neuen Server, den er sich angemietet hat. Ich habe ihm den Server aufgesetzt und wollte gestern die Datenbank vom alten Server übernehmen.

Ich machte einen Dump der alten MySQL-Datenbank, kopierte diesen Dump zum neuen Server und wollte diesen Dump mit dem Kommandozeilentool "mysql" zum neuen Server übernehmen.

Schon nach wenigen Minuten brach das Programm mit der Meldung ab, dass es doppelte Zeilen/Schlüssel in einer Tabelle geben würde. Den genauen Wortlaut weiß ich jetzt nicht mehr. Aber, was bedeutet das?
Es gibt also in irgendeiner Tabelle einen Datensatz oder zumindest einen Primärschlüssel **doppelt**.

Das bedeutet auch, dass sich die Datenbank nicht darum gekümmert hat, dass so etwas nicht passieren kann. Das bedeutet aber auch, dass ich mindestens einen Datensatz verliere. Denn einer dieser beiden Datensätze kann nie von außen angesprochen werden. Das sind Zustände, die ich mir nicht erlauben kann, wenn ich ein Kassenprogramm, eine Warenwirtschaft, eine Buchhaltung, eine Lagerverwaltung, usw. programmieren will.

Noch etwas:
Aber, eines haben die von MySQL richtig gut gemacht. Sie zeigen den anderen, wie wichtig hochwertige Administrationsprogramme für ein Datenbanksystem sind um beliebt zu werden. Das hat bei phpMyAdmin angefangen. Die sind den anderen Web-Administrationssystemen immer drei Schritte voraus. Und das endet bei den gelungenen, neuen MySQL-Administrationsprogrammen.

@snatch: Das soll jetzt aber nicht bedeuten, dass MySQL deiner Aufgabe nicht gewachsen wäre. Im Gegenteil. Für solche Kleinigkeiten eignet sich MySQL hervorragend.

lg
Gerold