Datenbanktabellen mithilfe eines scriptes anlegen

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Ich möchte 40 Datenbanktabellen anlegen. Die Tabellen haben alle die gleiche Struktur, aber verschiedene Namen. Die Datenbank in dem Beispiel heißt TESTDB und die darin enthaltenen Tabelle heißt EMPLOYEE. Weitere Tabellen sollen z.B. Frau, Mann usw. heißen.

Wie lege ich die Datenbanktabellen automatisiert an. Vielleicht ist es sinnvoll die Tabellennamen in einer Liste zu speichern, die dann abgearbeitet wird, Oder eine Schleife. Ich weiß nicht,wie ich den Tabellennamen in der Zeile sql = """CREATE TABLE EMPLOYEE gegen einen anderen Namen austauschen kann.

Hier das Beispiel

Code: Alles auswählen

#!/usr/bin/python
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object usingcursor()method
cursor = db.cursor()

# Drop table if it already exist usingexecute()method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()
Zuletzt geändert von Anonymous am Donnerstag 1. Dezember 2016, 23:36, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
Benutzeravatar
pillmuncher
User
Beiträge: 1482
Registriert: Samstag 21. März 2009, 22:59
Wohnort: Pfaffenwinkel

aaron hat geschrieben:Ich möchte 40 Datenbanktabellen anlegen. Die Tabellen haben alle die gleiche Struktur, aber verschiedene Namen. [...] die darin enthaltenen Tabelle heißt EMPLOYEE. Weitere Tabellen sollen z.B. Frau, Mann usw. heißen.
Warum dieses Design? Kann ein Employee kein Mann und keine Frau sein?
In specifications, Murphy's Law supersedes Ohm's.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Es ist nur ein Beispiel, um das Problem zu erörtern.
Zuletzt geändert von aaron am Donnerstag 1. Dezember 2016, 23:43, insgesamt 1-mal geändert.
BlackJack

@aaron: Vor allem ist für das Geschlecht ja bereits eine Spalte vorgesehen. Hätte die dann in jeder Tabelle immer den gleichen Wert? Wozu dann die Spalte? Der Datenbankentwurf ist komisch. Es ist nicht normal 40 Tabellen mit dem gleichen Schema zu haben. Da hat man normalerweise *eine* Tabelle mit einer Spalte die 40 Werte annehmen kann die aussagen zu welcher ”Tabelle” der jeweilige Datensatz gehört. Also auch wenn das nur ein Beispiel ist, riecht das sehr nach einem Entwurfsfehler.

Ansonsten ist das eine *sehr* grundlegende Frage die man eigentlich selber beantworten können sollte wenn man mit den Kontrollstrukturen und eingebauten Datenstrukturen und -typen vertraut ist. Was man sein muss wenn man *irgendwas* in Python programmieren möchte. Die Python-Dokumentation enthält beispielsweise ein Tutorial.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

einen kleinen Fortschritt habe ich schon erlangt.

Code: Alles auswählen

# Traverse the directory and process each .csv file
# Im Verzeichnis /data liegen zwei csv Dateien EURUSD.csv und EURGBP.csv
for csvfile in glob.glob('data/*.csv'):

# Remove the path and extension and use what's as a table name
    table_name = os.path.splitext(os.path.basename(csvfile))[0]

# Execute MySQL command to create specified table
    sql = """CREATE TABLE IF NOT EXISTS %s
        (id int NOT NULL AUTO_INCREMENT,
        Date date NOT NULL,
        Time time NOT NULL,
        Open decimal NOT NULL,
        High decimal NOT NULL,
        Low decimal NOT NULL,
        Close decimal NOT NULL,
        PRIMARY KEY (id))""" % table_name

    cursor.execute(sql)
Es wird leider nur die Tabelle EURUSD.csv angelegt. Auch ein zweiter Durchlauf des Scriptes legt keine zweite Tabelle an, sondern gibt lediglich die Warnung aus, dass die Tabelle EURUSD schon existiert.
Zuletzt geändert von Anonymous am Freitag 2. Dezember 2016, 18:38, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

@aaron: auch dieser Datenbankentwurf ist falsch. Statt für jede Währungskombination eine eigene Tabelle anzulegen, erzeugt man **eine** Tabelle mit zwei Spalten für die beiden Währungen. Auf diese Weise muß man nicht im vorhinein wissen, welche Währungen alle betrachtet werden sollen und kann einfach später neue Hinzufügen.
Was aber viel wichtiger ist, man kann viel mächtigere Abfragen schreiben und muß die Tabellennamen nicht händisch in SELECT-Anweisungen hineinformatieren. Sonst kommt noch jemand auf die Idee Informationen zu den Währungen MYSQL. und USERS abzufragen.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Vielen Dank für die Antwort. Ich bekomme von 40 Währungspaaren täglich Daten. Gesammelt habe ich seit dem 04.01.1999 bis heute. Die Daten werden in Form von CSV Dateien geliefert. Mittlerweile ist die Datenflut auf 16GB angewachsen.

Hier eine Beispiel für eine CSV Datei. Die anderen CSV Dateien sind genauso aufgebaut.

Folgende Aufgaben habe ich noch zu lösen:
1. wie soll der Datenbankentwurf aussehen?
2. wie importiere ich die Daten ?
3. wie geht man bei Fehlern während des Datenimportes in die Datenbank vor?
4. automatisches Versenden einer Email nach jedem Datenimport, um zu wissen, dass der Import gelaufen ist.

Vielen Dank für die Hilfe

Code: Alles auswählen

"Date","Time","Open","High","Low","Close","Total Ticks"
01.03.2016,19:30:00,1.08627,1.08627,1.08535,1.08535,1
01.03.2016,19:31:00,1.08535,1.08538,1.08535,1.08538,1
01.03.2016,19:32:00,1.08538,1.08564,1.08535,1.08564,4
01.03.2016,19:33:00,1.08564,1.08564,1.08536,1.08564,13
01.03.2016,19:35:00,1.08564,1.08564,1.08564,1.08564,2
01.03.2016,19:37:00,1.08564,1.08564,1.08561,1.08561,1
01.03.2016,19:38:00,1.08561,1.08563,1.08561,1.08562,2
01.03.2016,19:39:00,1.08562,1.08563,1.08562,1.08562,2
01.03.2016,19:40:00,1.08562,1.08568,1.08562,1.08566,3
01.03.2016,19:45:00,1.08566,1.08566,1.08551,1.08553,7
01.03.2016,19:49:00,1.08553,1.08553,1.08543,1.08543,1
01.03.2016,19:50:00,1.08543,1.08548,1.08543,1.08543,2

Das Script sieht jetzt so aus:

Code: Alles auswählen

#!/usr/bin/python
# -*- coding:utf-8 -*-

import csv
import glob
import MySQLdb
import os

# Use the absolute path
file_path = os.path.abspath(__file__)

# Traverse the directory and process each .csv file
for csvfile in glob.glob('data/*.csv'):

# Remove the path and extension and use what's as a table name
    table_name = os.path.splitext(os.path.basename(csvfile))[0]

# Open database connection
try:
    db = MySQLdb.connect(host="localhost", user="user", passwd="passwd", db="db")

# Prepare a cursor object using cursor() method
    cursor = db.cursor()

# Drop table if it already exist using execute() method
#    cursor.execute("""DROP TABLE IF EXISTS EURUSD""")

# Execute MySQL command to create specified table
    sql = """CREATE TABLE IF NOT EXISTS %s
        (id int NOT NULL AUTO_INCREMENT,
        Date date NOT NULL,
        Time time NOT NULL,
        Open decimal NOT NULL,
        High decimal NOT NULL,
        Low decimal NOT NULL,
        Close decimal NOT NULL,
        PRIMARY KEY (id))""" % table_name

    cursor.execute(sql)
# print (sql)

# Error messages
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])

# Quit ython script
    sys.exit(1)

# Disconnect from server
finally:
    if db:
        db.close()
Zuletzt geändert von Anonymous am Freitag 2. Dezember 2016, 18:42, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

@aaron: zum Datenbankentwurf hast Du ja jetzt schon mehrfach den Tipp bekommen, nur eine Tabelle zu benutzen. Dazu kommt, nur ein DATETIME-Feld zu verwenden. Wie man Daten von csv-Dateien importiert, gibt es ja schon genügend Beispiele: csv-Modul verwenden, Spalten richtig konvertieren, INSERT-Anweisung inkl. Feldnamen. Wie Du mit Fehlern umgehst, bleibt Deine Entscheidung. Entweder ignorierst Du nur die eine Zeile, die komplette Datei oder brichst ganz ab. Mit Deinem neuen Code ist auch klar, warum da nur eine Tabelle erzeugt wird. file_path wird nicht verwendet, die Kommentare sind allesamt aussagelos und können weg. try-Blöcke sollten möglichst kurz sein. Es ist nicht nötig, für jede Anweisung eine neue Datenbankverbindung aufzubauen.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

die Tabelle sieht nun wie folgt aus:

Code: Alles auswählen

sql = """CREATE TABLE IF NOT EXISTS CURRENCY
        (id int NOT NULL AUTO_INCREMENT,
        Currencypair CHAR(6) NOT NULL,
        Datetime DATETIME NOT NULL,
        Open DECIMAL NOT NULL,
        High DECIMAL NOT NULL,
        Low DECIMAL NOT NULL,
        Close DECIMAL NOT NULL,
        PRIMARY KEY (id))"""

    cursor.execute(sql)
Zuletzt geändert von Anonymous am Freitag 2. Dezember 2016, 18:43, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Ist der Datenbankentwurf jetzt richtig?
Die INSERT-Anweisung inkl. Feldnamen werde ich einbauen.
Ich habe jetzt das csv Modul eingebaut. Habe ich den csv reader an der richtigen Stelle platziert?
In der Ausgabe wird der Header nicht angezeigt und das Komma habe ich nach jeder Spalte. Ist das eine gute Idee?
Die Ausgabe sieht wie folgt aus:

Code: Alles auswählen

01.03.2016, 19:31:00, 1.08535, 1.08538, 1.08535, 1.08538
01.03.2016, 19:32:00, 1.08538, 1.08564, 1.08535, 1.08564
01.03.2016, 19:33:00, 1.08564, 1.08564, 1.08536, 1.08564
01.03.2016, 19:35:00, 1.08564, 1.08564, 1.08564, 1.08564

Code: Alles auswählen

#!/usr/bin/python
# -*- coding:utf-8 -*-

import csv
import glob
import MySQLdb
import os


for csvfile in glob.glob('data/*.csv'):

    filename = os.path.splitext(os.path.basename(csvfile))[0]

# Read data from the csv File(s)
    with open(csvfile, "rb") as data_file:
        print(' ')
        reader = csv.DictReader(data_file, delimiter = ',')
        headers = next(reader)
        for row in reader:
            print(row['Date'] + ", " + row['Time'] + ", " + row ['Open'] + ", " + row['High'] + ", " + row['Low'] + ", " + row['Close'])

try:
    db = MySQLdb.connect(host="localhost", user="usr", passwd="passwd", db="db")

    cursor = db.cursor()

# Drop table if it already exist using execute() method
#   cursor.execute("""DROP TABLE IF EXISTS CURRENCY""")

    sql = """CREATE TABLE IF NOT EXISTS CURRENCY
        (id int NOT NULL AUTO_INCREMENT,
        Currencypair CHAR(6) NOT NULL,
        Datetime DATETIME NOT NULL,
        Open DECIMAL NOT NULL,
        High DECIMAL NOT NULL,
        Low DECIMAL NOT NULL,
        Close DECIMAL NOT NULL,
        PRIMARY KEY (id))"""

    cursor.execute(sql)

except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])

    sys.exit(1)

finally:
    if db:
        db.close()
Zuletzt geändert von Anonymous am Freitag 2. Dezember 2016, 18:44, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

@aaron: in der Tabelle würde ich ja die zwei Währungen trennen. Der DictReader liest schon den Header, wenn Du das nochmal extra machst, fehlt Dir die erste Datenzeile. Die Werte auf dem Bildschirm auszugeben ist ja nicht Sinn des Programms, daher ist es ja auch irrelevant, ob Du da nun Kommas oder Smilies dazwischensetzt. Solche Ausgaben macht man dann aber mit .format:

Code: Alles auswählen

print("{Date}, {Time}, {Open}, {High}, {Low}, {Close}".format(**row))
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Vielen Dank für die Hilfe. Ich versehe diesen Satz nicht "in der Tabelle würde ich ja die zwei Währungen trennen." Meinst du damit das Kürzel von EURUSD zu EUR/USD?
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

aaron hat geschrieben:Vielen Dank für die Hilfe. Ich versehe diesen Satz nicht "in der Tabelle würde ich ja die zwei Währungen trennen." Meinst du damit das Kürzel von EURUSD zu EUR/USD?
Entschuldige bitte. Ich stand gerade neben der Spur.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Ich versuche gerade die zwei Spalten Date und Time zu einer Spalte zusammen zufassen. Da diese zwei Werte, nämlich das Datum und die Uhrzeit in zwei Spalten stecken, ist es da nicht sinnvoller den Datentyp als CHAR und nicht als datetime in der Tabelle anzugeben? Später soll mit dem Datum und der Zeit noch gerechnet werden.

Code: Alles auswählen

sql = """INSERT INTO CURRENCY(Date + Time, Open, High, Low, Close)
             Values("%s", "%s", "%s", "%s", "%s")"""

    cursor.execute(sql)
    db.commit()
    db.rollback()
BlackJack

@aaron: Gerade *weil* mit Datum/Uhrzeit gerechnet werden kann, macht es keinen Sinn CHAR sondern DATETIME zu verwenden. Mit CHAR kann man nicht rechnen. An der Stelle musst Du die Daten aus der CSV-Datei parsen und als `datetime`-Objekte aus dem `datetime`-Modul an die Datenbank übergeben.
aaron
User
Beiträge: 92
Registriert: Donnerstag 1. Dezember 2016, 23:10

Ich habe jetzt sehr lange nach einer Lösung im Internet gesucht. Ich bekomme folgende Fehlermeldung:

ValueError: time data 'Date' does not match format 'Time'

Ja, das ist richtig. weil das Format in der CSV Datei 01.03.2016, 19:30:00, (Monat, Tag, Jahr) ist. Die richtige Syntax von Time aber datetime.strptime('2012-11-14 14:32:30', '%Y-%m-%d %H:%M:%S') ist. Ich möchte das Datum und Zeitformat auf keinen Fall verändern.

Bitte schaut Euch einmal den Code an. Ist das soweit richtig? Ist der \ für den Umbruch richtig, dmit die Zeilen nicht zu lang werden?

Code: Alles auswählen

#!/usr/bin/python
# -*- coding:utf-8 -*-

import csv
import datetime
import glob
import MySQLdb
import os

# Traverse the directory and process each .csv file
for csvfile in glob.glob('data/*.csv'):

# Remove the path and extension and use what's as a table name
    table_name = os.path.splitext(os.path.basename(csvfile))[0]
    print (table_name)

# Read data from the csv File(s)
    with open(csvfile, "rb") as sourcefile:
        print(' ')
        reader = csv.DictReader(sourcefile, delimiter = ',')
        for row in reader:
            print("{Date}, {Time}, {Open}, {High}, {Low}, {Close}".format(**row))

    Datetime = datetime.datetime.strptime("Date","Time")

try:
    db = MySQLdb.connect(host="localhost", user="usr", passwd="passwd", db="db")

    cursor = db.cursor()

    sql = """INSERT INTO CURRENCY(AUDCAD, AUDJPY, AUDNZD, AUDUSD, CADJPY, EURAUD, EURCAD, EURCHF, \
                                  EURGBP, EURJPY, EURNZD, EURUSD, GBPAUD, GBPJPY, GBPUSD, NZDJPY, \
                                  USDCAD, USDCHF, USDJPY, Datetime, Open, High, Low, Close)
             Values("%s", "%s", "%s", "%s", "%s","%s", "%s", "%s", "%s", "%s","%s", "%s", "%s", \
                    "%s", "%s","%s", "%s", "%s", "%s", "%s","%s", "%s", "%s", "%s")"""

    cursor.execute(sql)
    db.commit()
    db.rollback()


except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])

finally:
    if db:
        db.close()
Zuletzt geändert von Anonymous am Samstag 3. Dezember 2016, 20:23, insgesamt 1-mal geändert.
Grund: Quelltext in Python-Codebox-Tags gesetzt.
BlackJack

@aaron: Natürlich ist die Fehlermeldung richtig, aber ich denke Du hast sie nicht wirklich verstanden. Du versuchst dort den Wert 'Date', also die Zeichenkette die aus 'D', 'a', 't', und 'e' besteht, als Datum zu parsen. Das ist schon mal falsch weil das Wort 'Date' kein Datum ist. Und die Zeichenkette 'Time' ist auch kein Muster um eine Zeichenkette mit einem tatsächlichen Datum zu parsen.

Beim Beispielaufruf von `strptime()` hat die Zeichenkette mit der Datums- und Zeitangabe ein anderes Format als Deins. Also musst Du für Dein Format auch entsprechend das Muster anpassen das als zweites Argument übergeben wird. Was die Platzhalter bedeuten und welche es noch so gibt, steht in der Python-Dokumentation.

Der Quelltext ist nicht wirklich richtig. Zum Beispiel macht Zeile 24 nicht nur von den Argumenten her keinen Sinn, sondern auch die Stelle im Programmablauf an der das steht ist nicht sinnvoll. Das gilt im Grunde auch für den ganzen Rest des Programms ab dort. Allerdings sollte man das jetzt auch nicht einfach an die richtige Stelle ”verschieben”, denn dann bist Du auf dem besten Weg einen immer überfrachteteren, unübersichtlichen Code-Klumpen zu schreiben, wo man nichts mal eben isoliert testen kann. Leg das am besten mal beiseite und arbeite ein Grundlagentutorial durch um den grundsätzlichen Programmablauf und wie man den mit Schleifen und anderen Konstrukten steuern kann, kennen zu lernen.

Und dabei wirst Du dann auch Funktionen kennen lernen, und wie man die schreibt. Denn das was Du da machst ist gross genug dass man das sinnvoll auf Funktionen aufteilen kann, damit es übersichtlicher und test- und damit auch wartbarer wird. Auf Modulebene sollte nur Code stehen der Konstanten, Funktionen, und Klassen definiert. Das Hauptprogramm steht üblicherweise in einer Funktion die `main()` heisst.

Eine übliche grobe Aufteilung ist Eingabe/Verarbeitung/Ausgabe. Du könntest also eine Funktion schreiben, die *eine* Datei einliest und die Werte entsprechend in Werte umwandeld mit denen man im Programm arbeiten möchte. Also Datums- und Zeitangaben in passende Datentypen aus dem `datetime`-Modul und Zahlen je nach Wertebereich als `int` oder `float`.

Dann kann man eine Funktion schreiben die solche eingelesenen Werte in die Datenbank schreibt.

Und dann eine die in einer Schleife die beiden Funktionen für alle Dateien anwendet.

Ein wichtiger Teil beim Programmieren ist das aufteilen vom Gesamtproblem in Teilprobleme und die wieder in weitere Teilprobleme, solange bis man welche hat, die einfach mit ein paar Zeilen Code lösbar sind. Die Teillösung testet man, und wenn sie tut was sie soll, kann man anfangen die kleineren Teillösungen in ”höheren” Teillösungen zu verwenden, und die dann auch wieder testen, bis man alles zu einer funktionierenden Gesamtlösung zusammen gesetzt hat. Du versuchst da zu viel auf einmal. Solange das einlesen einer Datei noch nicht funktioniert, braucht man sich um Code der die noch gar nicht im Programm verfügbaren Daten in die Datenbank einfügt noch nicht wirklich kümmern, denn man kann zu dem Zeitpunkt ja sowieso noch nicht testen ob der funktioniert oder nicht.

Die INSERT-Anweisung mit den ganzen Währungspaaren als *Spaltennamen* sieht komisch bis falsch aus. Die '\' am Ende sind unnötig da die """ als Begrenzer für das Zeichenkettenliteral ja schon dafür sorgen das es ein mehrzeiliges Zeichenkettenliteral ist. Die " um die Platzhalter gehören da nicht hin. Und wenn man Platzhalter in der SQLAnweisung hat, dann muss man dafür beim `execute()`-Aufruf auch die passenden Werte übergeben.

Ein `rollback()` direkt nach einem `commit()` macht keinen Sinn.

Das öffnen der Datenbankverbindung müsste vor dem ``try`` für das ``finally`` stehen. Aber in dem ``try`` für das ``except``. Also eigentlich braucht man ein ``try``/``except`` und ein ``try``/``finally`` für das was da passieren soll. Oder man verwendet ``with`` in Verbindung mit `contextlib.closing()` statt des ``try``/``finally``.

Das ``if db:`` ist sinnfrei, denn wenn der Verbindungsaufbau zu einer Ausnahme führt, dann ist `db` nicht definiert und das Programm läuft dort in einen `NameError` oder der Verbindungsaufbau hat geklappt, dann ist aber ``if db:`` auch Grundsätzlich ”wahr” weil solche Verbindungsobjekte keinen Wert annehmen der als Bedinung ”falsch” ergibt.
Antworten