Daten aus Excel in Sqlite schreiben

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

Hallo Zusammen,

ich möchte gerne Daten aus diversen Excel Dateien in eine Sqlite Datenbank schreiben um dort mit diesen dann weiter zu arbeiten.

Mein Problem ist, dass die Excel Files je Thema (Umsatzzahlen, Mitarbeiterzahlen, ...) die gleiche Struktur haben, die Spaltenüberschriften aber unterschiedlich sind.
Wie kann ich das lösen?

Mit Pandas habe ich es hin bekommen, die Files mit Hilfe eines DataFrame in die Tabelle zu schreiben. Das klappt aber nur, wenn die Spaltennamen gleich sind.
Anbei der Code:

Code: Alles auswählen

import pandas as pd
import sqlite3
con = sqlite3.connect("test.db")

test_table = "t_test"

cur = con.cursor()

# drop table
cur.execute("""DROP TABLE IF EXISTS """+ test_table +""";""")

sql_command = """
CREATE TABLE """+ test_table +""" ( 
datum DATE
,niederlassung VARCHAR2(50)
,anzahl_mitarbeiter INT
);"""

cur.execute(sql_command)

df_test = pd.read_excel("C:/Data/test.xlsx")

df_test.to_sql(test_table, con, if_exists="append", index=False)

con.commit()

con.close()
Könnt ihr mir einen Tipp geben, wie ich unabhängig von den Spaltennamen die Tabelle befüllt bekomme?

Herzlichen Dank und Grüße

André
__deets__
User
Beiträge: 14545
Registriert: Mittwoch 14. Oktober 2015, 14:29

Du wirst nicht darum herum kommen, die Daten aus Pandas in einen neuen Frame zu schreiben, der die Spalten korrekt benannt hat. Du kannst dazu auf die Ursprungsdaten auch per Index statt Namen zugreifen.
Benutzeravatar
__blackjack__
User
Beiträge: 14047
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

Wobei man beim vorhandenen DataFrame auch einfach die Spaltennamen ändern kann, so dass sie zur DB-Tabelle passen, bevor man den DataFrame in die Datenbank schreibt.
“Vir, intelligence has nothing to do with politics!” — Londo Mollari
__deets__
User
Beiträge: 14545
Registriert: Mittwoch 14. Oktober 2015, 14:29

Oder das. Mit Pandas geht's ja immer auf tausend und eine Art.
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

Moin,
ich war gedanklich dabei, wie ich ein INSERT Statement generieren könnte, aufs umbenennen der Spalten bin ich nicht gekommen. Danke.

Macht es überhaupt Sinn ein INSERT Statement zu generieren? Das erledigt ja Pandas für mich oder müsste ich Pandas dann gar nicht nutzen und könnte einen anderen Weg gehen?
Sirius3
User
Beiträge: 18270
Registriert: Sonntag 21. Oktober 2012, 17:20

@wenckman: Pandas macht es halt sehr bequem ein Excel zu lesen und in eine Datenbank zu schreiben. Das sind zwei Zeilen. Wenn es funktioniert, gibt es eigentlich keinen effizienteren Weg. Warum willst Du also einen anderen suchen?

Da die Felder in den Tabellen unterschiedlich sind, warum hast Du den Tabellennamen als Variable?
Benutzeravatar
__blackjack__
User
Beiträge: 14047
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@wenckman: Noch ein paar Anmerkungen zum Quelltext: Variablennamen sollte man nicht abkürzen. Statt `con` und `cur` besser `connection` und `cursor`.

Da man hier fest auf `sqlite3` festgelegt ist, kann man die Besonderheiten von dem Modul nutzen.

Zum Beispiel sind `sqlite3`-Verbindungsobjekte Kontextmanager, können also mit ``with`` verwendet werden, womit man sich die Aufrufe von `close()` und `commit()` sparen kann.

Des weiteren haben sie eine `execute()`-Methode so dass man sich nicht selbst den `Cursor` erstellen muss.

Zeichenketten und Werte mit ``+`` zusammenstückeln ist eher BASIC als Python. In Python gibt es dafür Zeichenkettenformatierung mit der `format()`-Methode oder ab Python 3.6 auch f-Zeichenkettenliterale.

Ungetestet:

Code: Alles auswählen

#!/usr/bin/env python3
import sqlite3

import pandas as pd


def main():
    table_name = 't_test'
    with sqlite3.connect('test.db') as connection:
        connection.execute('DROP TABLE IF EXISTS {};'.format(table_name))
        connection.execute(
            'CREATE TABLE {} ('
            ' datum DATE,'
            ' niederlassung VARCHAR(250),'
            ' anzahl_mitarbeiter INT'
            ');'.format(table_name)
        )
        data = pd.read_excel('C:/Data/test.xlsx')
        data.columns = ['datum', 'niederlassung', 'anzahl_mitarbeiter']
        data.to_sql(table_name, connection, if_exists='append', index=False)


if __name__ == '__main__':
    main()
“Vir, intelligence has nothing to do with politics!” — Londo Mollari
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

@Sirius:
Mir geht es darum zu schauen, welche Möglichkeiten mir zur Verfügung stehen. Wenn das für meinen Fall mit Pandas der effizienteste Weg ist, nehme ich diesen gerne.
Variable habe ich, weil ich sie mehrfach verwende und nicht an allen Stellen händisch ändern möchte.
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

@__blackjack__:
Danke für den Hinweis bezüglich des Quelltexts. Merke ich mir.
Dein Code sieht sehr übersichtlich aus, danke für das Beispiel. Ich merke schon, ich habe noch eine große Lernkurve vor mir.

Bezüglich sqlite3 werde ich mich noch intensiver mit der Doku beschäftigen um die Besonderheiten nutzen zu können.

Vielleicht nochmal eine grundsätzliche Frage zur Datenbank. Für die jetzige Stand-Alone Lösung fühle ich mich mit Sqlite gut aufgestellt.
Wenn ich das ganze auf einen Server bringe, würde ich auf mySQL wechseln, es könnte aber auch sein, dass ich Oracle nutzen muss. Zu Oracle habe ich kaum etwas gefunden.
Sollte ich dann mySQL fokusieren?
Benutzeravatar
__blackjack__
User
Beiträge: 14047
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@wenckman: Wenn das DBMS nicht fest steht solltest Du nicht das `sqlite3`-Modul direkt verwenden, das ist ja von Pandas an der Stelle sowieso nur etwas was auch historischen Gründen unterstützt wird. Zum zweiten Argument von `to_sql()` sagt die Pandas-Dokumentation ja unter anderem „Legacy support is provided for sqlite3.Connection objects.“

Es wäre dann also sinnvoller gleich auf SQLAlchemy zu setzen, denn eine `Engine` von SQLAlchemy ist da das was Pandas dort eigentlich erwartet. Neben SQLite kann man dann auch MySQL oder Oracle verwenden, und was SQLAlchemy noch so unterstützt.

Edit: Wegen der Variable für den Tabellennamen: Das ist halt komisch weil Tabellen in einem DB-Entwurf fest und nicht variabel sind, und mehrere Tabellen mit dem gleichen Schema aus denen dann per Variablen Tabellennamen ausgesucht wird, nach einem Entwurfsfehler aussehen.
“Vir, intelligence has nothing to do with politics!” — Londo Mollari
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

Bezüglich des DB Entwurfs gebe ich dir recht, sieht nach einem Fehler aus. Ich bin noch am probieren und testen und somit steht da noch gar nichts. Möchte erstmal ein paar Grundlagen haben, wie ich meine Aufgabenstellung lösen kann.

Danke für den Tipp bezüglich des DBMS. Dann schaue ich mit SQLAlchemy an und versuche den Code dementsprechend umzustellen.
Benutzeravatar
__blackjack__
User
Beiträge: 14047
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@wenckman: Jetzt hatte ich in der Zwischenzeit das Beispiel schon auf SQLAlchemy umgeschrieben. 😎

Ungetestet:

Code: Alles auswählen

#!/usr/bin/env python3
import pandas as pd
from sqlalchemy import (
    Column, create_engine, DATE, INT, MetaData, Table, VARCHAR
)


def main():
    engine = create_engine('sqlite:///test.db')
    metadata = MetaData(engine)
    table = Table('t_test', metadata,
        Column('datum', DATE),
        Column('niederlassung', VARCHAR(255)),
        Column('anzahl_mitarbeiter', INT),
    )
    table.drop(checkfirst=True)
    table.create()

    data = pd.read_excel('C:/Data/test.xlsx')
    data.columns = ['datum', 'niederlassung', 'anzahl_mitarbeiter']
    data.to_sql(table.name, engine, if_exists='append', index=False)


if __name__ == '__main__':
    main()
Statt `drop()`/`create()` auf dem `Table`-Objekt könnte man auch `drop_all()`/`create_all()` auf dem `MetaData`-Objekt verwenden wenn man mehr als eine Tabelle definiert und die alle neu erstellen möchte.

Was beim DB-Entwurf noch ”komisch” ist, ist ein fehlender Primärindex und das alle Spalten per Default ”nullable” sind, was soweit ich das sehe bei keiner Sinn macht. Und das `niederlassung` eine Zeichenkette ist, sieht nach Verletzung der Normalform aus. Du solltest nicht den Fehler begehen Datenbanktabellen als zweidimensionale Tabellen wie beispielsweise Tabellen aus einer Tabellenkalkulation zu sehen. Das heisst zwar beides ”Tabelle” ist aber doch sehr verschieden.
“Vir, intelligence has nothing to do with politics!” — Londo Mollari
wenckman
User
Beiträge: 6
Registriert: Mittwoch 3. Juli 2019, 16:17

cool, du bist ja fix. Herzlichen Dank :)

Ich merke schon, auch wenn ich teste, sollte ich das ganze schon etwas mehr durchstrukturieren. Im geplanten Modell wird es für alle Stammdaten eine separate Tabelle mit ID und Attributen geben, welche ich dann wiederverwende. Daraus ergeben sich dann auch für jede Tabelle die entsprechenden Primärschlüssel.
Antworten