Tabellen aus CSV Dateien in SQLite Datenbanken importieren

[BlackJack]

@ThoSchoo: Das kann nicht sein.

[ThoSchoo]

Code: Alles auswählen

import csv
import sqlite3


con = sqlite3.connect("LGORT")
cur = con.cursor()
cur.execute("CREATE TABLE invoice (LGORT_ID INTEGER, City TEXT, WERKS_ID INTEGER);")

with open('tbl_LGORT.csv') as input_file:
        reader = csv.DictReader(input_file)
        to_db = [(i["LGORT_ID"], i["City"], i["WERKS_ID"]) for i in reader]

cur.executemany("Insert into invoice (LGORT_ID, City, WERKS_ID) Values (?, ?, ?);", to_db)
con.commit()

to_db = [(i["LGORT_ID"], i["City"], i["WERKS_ID"]) for i in reader]
KeyError: 'LGORT_ID'

Leider doch ..

[pillmuncher]

@ThoSchoo:

1) Setz bitte den Code in Zukunft in CodeBox-Tags. Direkt über dem Eingabefenster ist eine Dropdown-Box ("Code auswählen"). Da drauf klicken, die Buchstaben p und y eingeben und Enter drücken, schon hast du ein Paar CodeBox-Tags. Da kommt der Code rein.

2) Kann es sein, dass in deiner Datei tbl_LGORT.csv in der ersten Zeile etwas anderes steht, als LGORT_ID etc? Stehen da überhaupt Spaltennamen? Oder gleich in der ersten Zeile ein Datensatz? Falls letzteres, musst du die gewünschten Spaltennamen beim Aufruf von csv.DictReader() als Argument (zB. in einer Liste) übergeben. Wie das genau geht, steht in der Doku.

[Sirius3]

@ThoSchoo: laut Deinem Beispiel müßte der delimiter ';' sein.

[ThoSchoo]

@Sirius3
JAAA.. das scheint das Problem gewesen zu sein ..

Code: Alles auswählen

con = sqlite3.connect("LGORT")
cur = con.cursor()
cur.execute("CREATE TABLE invoice (LGORT_ID INTEGER, City TEXT, WERKS_ID INTEGER);")

with open('tbl_LGORT.csv') as input_file:
        reader = csv.DictReader(input_file, delimiter = ";")
        to_db = [(i["LGORT_ID"], i["City"], i["WERKS_ID"]) for i in reader]

cur.executemany("Insert into invoice (LGORT_ID, City, WERKS_ID) Values (?, ?, ?);", to_db)
con.commit()

Nun gibt er mir allerdings beim auslesen ein "u" vor der City !?

• (1, u'Bramsche', 1)
  (2, u'Bramsche', 1)
  (3, u'Bramsche', 1)

[BlackJack]

@ThoSchoo: Das macht nichts.

[ThoSchoo]

Wie könnte ich denn nun das Programm variabler machen, so dass es die Spaltennamen eigenständig erkennt ?

[BlackJack]

@ThoSchoo: Die Spaltennamen der CSV-Datei kann man vom `DictReader`-Objekt abfragen. Die Spaltennamen einer Datenbanktabelle kann man über das `description`-Attribut auf Cursor-Objekten abfragen wenn man eine Abfrage aller Spalten einer Tabelle macht. Datenbankspezifisch gibt es in der Regel noch andere Wege an diese Information zu kommen, ohne eine Abfrage der Daten selbst zu machen. An der Stelle würde ich dann aber auf eine Abstraktion wie SQLAlchemy ausweichen.

Datenbankspalten würde ich übrigens in Kleinbuchstaben bennenen. Eigentlich sollten die „case insensitive“ sein, allerdings machen das nicht alle Datenbanken so oder lassen sich zumindest auch anders konfigurieren. Genau wie bei Bezeichnern in einem Programm sollte man sich auch auf eine natürliche Sprache beschränken und nicht eine Spalte in deutsch und eine andere in englisch benennen. Da kommt man schnell durcheinander.

[ThoSchoo]

@BlackJack Danke aber ich hab leider nicht sehr viel verstanden, von dem was du mir mitteilen wolltest


Da es mehrere CSV Dateien sind, die alle in einem Verzeichnis liegen und alle als einzelne Tabellen in eine Datenbank eingetragen werden sollen, habe ich mal das hier zusammengesucht und -gesetzt:

Code: Alles auswählen

import csv
import sqlite3
import os
import glob



db = ("Mockupdata")
con = sqlite3.connect(db)
cur = con.cursor()

# traverse the directory and process each .csv file
for csvfile in glob.glob(os.getcwd("", "*.csv")):
    # remove the path and extension and use what's left as a table name
    tablename = os.path.splitext(os.path.basename(csvfile))[0]

    with open(csvfile, "rb") as inputfile:
        reader = csv.reader(inputfile)
 
        header = True
        for row in reader:
            if header:
                # gather column names from the first row of the csv
                header = False
 
                sql = "DROP TABLE IF EXISTS %s" % tablename
                cur.execute(sql)
                sql = "CREATE TABLE %s (%s)" % (tablename,
                          ", ".join([ "%s text" % column for column in row ]))
                cur.execute(sql)
                       
                insertsql = "INSERT INTO %s VALUES (%s)" % (tablename,
                            ", ".join([ "?" for column in row ]))
 
                rowlen = len(row)
            else:
                # skip lines that don't have the right number of columns
                if len(row) == rowlen:
                    cur.execute(insertsql, row)
 
        con.commit()
 
cur.close()
con.close()

Hier scheitert es allerdings schon bei dem durchsuchen des Ordners :

Code: Alles auswählen

    for csvfile in glob.glob(os.getcwd("", "*.csv")):
TypeError: getcwd() takes no arguments (2 given)

Ich arbeite gerade auf einem Mac .. das Ganze soll allerdings am besten überall anwendbar sein

[cofi]

Schau dir mal die Fehlermeldung an und dann die Klammern in der betreffenden Zeile.

[BlackJack]

@ThoSchoo: Der `os.getcwd()`-Aufruf ist hier sowieso überflüssig.

Das Programm würde ja nun sämtliche Felder in der Datenbank vom Typ TEXT anlegen. Und Constraints wie NOT NULL oder PRIMARY KEY gehen auf diese Art und Weise ja auch nicht.

Warum gibt es in der Datei überhaupt Zeilen mit der falschen Länge?

Statt *in* der Schleife ständig zu prüfen ob man die erste Zeile vor sich hat oder nicht, würde man die erste Zeilen einfach *vor* der Schleife mit `next()` vom Reader-Objekt abfragen.

Das ganze ist mittlerweile komplex genug um es sinnvoll auf Funktionen aufzuteilen.

Bei der Gelegenheit kann man dann auch gleich das Hauptprogramm in eine Funktion stecken (üblicherweise `main()` genannt) und auf Modulebene damit dann keinen Code mehr haben der nicht Konstanten, Funktionen, oder Klassen definiert.

[ThoSchoo]

@cofi

wenn ich es so schreibe :

Code: Alles auswählen

csvfile in glob.glob(os.getcwd(""), "*.csv"):

gibt er mir das :

Code: Alles auswählen

 for csvfile in glob.glob(os.getcwd(""), "*.csv"):
TypeError: getcwd() takes no arguments (1 given)

[ThoSchoo]

@BlackJack

mit dem `os.getcwd()` will ich ja mein Arbeitsverzeichnis aufrufen in mein die Dateien vorhanden sind
Wie gelange ich denn sonst in das Verzeichnis?

Es dürfte eigentlich keine Daten mit einer falschen länge geben

[BlackJack]

@ThoSchoo: Die Fehlermeldung ist doch ziemlich deutlich. Die Funktion erwartet keine Argumente, Du übergibst aber ein Argument. Lass das sein.

Zum aktuellen Arbeitsverzeichnis muss man nicht gelangen, da ist man bereits. Das ist ja gerade die Bedeutung von „aktuellem Arbeitsverzeichnis“, das von dort aus alle relativen Pfadangaben gesucht werden.

Wenn es keine Zeilen mit falscher Anzahl von Feldern geben kann, warum ist dann eine Prüfung darauf im Programm?

[ThoSchoo]

So funktioniert es :

Code: Alles auswählen

import csv
import sqlite3
import os
import glob



db = ("Mockupdata")
con = sqlite3.connect(db)
cur = con.cursor()

# traverse the directory and process each .csv file
for csvfile in glob.glob( "*.csv"):
    # remove the path and extension and use what's left as a table name
    tablename = os.path.splitext(os.path.basename(csvfile))[0]

    with open(csvfile, "rb") as inputfile:
        reader = csv.reader(inputfile, delimiter = ";")
 
        header = True
        for row in reader:
            if header:
                # gather column names from the first row of the csv
                header = False
 
                sql = "DROP TABLE IF EXISTS %s" % tablename
                cur.execute(sql)
                sql = "CREATE TABLE %s (%s)" % (tablename,
                          ", ".join([ "%s text" % column for column in row ]))
                cur.execute(sql)
                       
                insertsql = "INSERT INTO %s VALUES (%s)" % (tablename,
                            ", ".join([ "?" for column in row ]))
 
                rowlen = len(row)
 
        con.commit()
 
cur.close()
con.close()

allerdings ist es so alles in TEXT

wie könnte ich das denn umschrieben damit es den jeweiligen DatenTypen erkennt ?

[BlackJack]

@ThoSchoo: In einen DB-Entwurf gehört ja noch mehr als der Tabellenname und die Spaltennamen und -typen, das lässt sich nicht so einfach in einer CSV-Kopfzeile abbilden. Dementsprechend würde ich das dort auch nicht speichern oder von dort ermitteln wollen. Üblicherweise verwendet man SQL für das Schema oder ein ORM und lässt sich von dem die Tabellen erstellen. SQLAlchemy erwähnte ich ja schon mal…

[ThoSchoo]

Uff ja .. In SQLAlchemy müsste ich mir mal einlesen ..
Hab es nun allerdings nun zunächst mal ehr primitiv gelöst.
Nun bekomme ich allerdings bei einer Zeile ein Problem ..

Code: Alles auswählen

con = sqlite3.connect("Mockup_Data.db")
cur = con.cursor()
cur.execute( "DROP TABLE IF EXISTS tbl_Supplier_Class")
cur.execute("CREATE TABLE tbl_Supplier_Class (Supplier_Class_ID INTEGER, Supplier_Class_Name TEXT, Share REAL, Avg_DPO INTEGER, Saisonality_Factor REAL, Invoice_Share REAL);")

with open('tbl_Supplier_Class.csv', "rb") as input_file:
        reader = csv.DictReader(input_file, delimiter = ";")
        to_db = [(i["Supplier_Class_ID"], i["Supplier_Class_Name"], i["Share"], i["Avg_DPO"], i["Saisonality_Factor"], i["Invoice_Share"]) for i in reader]

cur.executemany("Insert into tbl_Supplier_Class (Supplier_Class_ID, Supplier_Class_Name, Share, Avg_DPO, Saisonality_Factor, Invoice_Share) Values (?, ?, ?, ?, ?, ?);", to_db)
con.commit()

Da wird mir nun das hier als Fehler gegeben und ich versteh beim besten Willen nicht warum

Code: Alles auswählen

    to_db = [(i["Supplier_Class_ID"], i["Supplier_Class_Name"], i["Share"], i["Avg_DPO"], i["Saisonality_Factor"], i["Invoice_Share"]) for i in reader]
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/csv.py", line 107, in next
    self.fieldnames
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/csv.py", line 90, in fieldnames
    self._fieldnames = self.reader.next()
_csv.Error: new-line character seen in unquoted field - do you need to open the file in universal-newline mode?

Die Datei sieht so aus :

Code: Alles auswählen

Supplier_Class_ID;Supplier_Class_Name;Share;Avg_DPO;Saisonality_Factor;Invoice_Share
1;A;0,1;90;0,15;0.5
2;B;0,35;60;0,05;0.3
3;C;0,55;30;0,01;0.2

[BlackJack]

@ThoSchoo: Die Fehlermeldung enthält ja eine Frage. Die was mit `open()` und „universal-newline mode“ zu tun hat. Man könnte ja mal in der Dokumentation von `open()` nachlesen ob da vielleicht etwas über einen solchen Modus steht.

[ThoSchoo]

Danke @BlackJack

Nun steht ich leider vor dem nächsten Problem und sitze seit Stunden davor und finde den Fehler einfach nicht

Code: Alles auswählen

con = sqlite3.connect("Mockup_Data.db")
cur = con.cursor()
cur.execute( "DROP TABLE IF EXISTS tbl_Material_Class")
cur.execute("CREATE TABLE tbl_Material_Class (Material_Class_ID INTEGER, Share REAL, Mean_Price REAL, Deviation_Price REAL, Mean_Ordersize INTEGER);")

with open('tbl_Material_Class.csv', "rb") as input_file:
        reader = csv.DictReader(input_file, delimiter = ";")
        to_db = [(i["Material_Class_ID"], i["Share"], i["Mean_Price"], i["Deviation_Price"], i["Mean_Ordersize"]) for i in reader]

cur.executemany("Insert into tbl_LGORT (Material_Class_ID, Share, Mean_Price, Deviation_Price, Mean_Ordersize) Values (?, ?, ?, ?, ?);", to_db)
con.commit()

und er gibt mir nur einen Keyerror aus

Code: Alles auswählen

   to_db = [(i["Material_Class_ID"], i["Share"], i["Mean_Price"], i["Deviation_Price"], i["Mean_Ordersize"]) for i in reader]
KeyError: 'Mean_Ordersize'

Das ist die dazugehörige Tabelle:

Code: Alles auswählen

Material_Class_ID;Share;Mean_Price;Deviation_Price; Mean_Ordersize
1;0,4;5;0,5;500
2;0,2;60;3;200
3;0,15;150;10;100
4;0,15;300;20;50
5;0,1;500;100;10

[BlackJack]

@ThoSchoo:

Code: Alles auswählen

In [1]: ' Mean_Ordersize' == 'Mean_Ordersize'
Out[1]: False