Abfrage SQlite Datenbank

[sveni_lee]

Ich habe mir jetzt eine SQlite datenbank angelegt

Code: Alles auswählen

        blob = {
                'id': unicode('SP.%s.%s' % (i, category)),
                'tvshow': unicode(data.tvshowname),
                'staffel': unicode(data.staffel),
                'episode': unicode(data.episode),
                'title': unicode(data.title),
                'starttime': unicode(data.tvshowstarttime),
                'date': unicode(data.date),
                'neueepisode': unicode(data.neueepisode),
                'channel': unicode(data.channel),                
                'pvrchannel': unicode(channel),
                'logo': unicode(logoURL),
                'pvrid': unicode(pvrchannelID),
                'description': unicode(details.plot),
                'rating': unicode(details.rating),
                'content_rating': unicode(details.content_rating),
                'genre': unicode(details.genre),
                'studio': unicode(details.studio),
                'status': unicode(details.status),
                'year': unicode(details.year),
                'thumb': unicode(thumbpath),
                'firstaired': unicode(details.firstaired),
                'runtime': unicode(data.runtime),
                'poster': unicode(details.posterUrl),
                'category': unicode(category),
               }


        conn = sqlite3.connect(SerienPlaner)
        cur = conn.cursor()
        cur.execute("""CREATE TABLE IF NOT EXISTS TVShowData(
            Watchtype,
            Date,
            StartTime,
            Channel,
            TVShow,
            Season,
            Episode,
            Title,
            newEpisode,
            Description,
            Rating,
            ContentRating,
            Genre,
            Studio,
            Status,
            Year,
            FirstAired,
            RunningTime,
            Thumb,
            Poster,
            ChannelID,
            ChannelLogo,
            UNIQUE(Date, StartTime, Channel)
            ON CONFLICT REPLACE);""")

        sql_command = """INSERT INTO TVShowData(
            Watchtype,
            Date,
            StartTime,
            Channel,
            TVShow,
            Season,
            Episode,
            Title,
            newEpisode,
            Description,
            Rating,
            ContentRating,
            Genre,
            Studio,
            Status,
            Year,
            FirstAired,
            RunningTime,
            Thumb,
            Poster,
            ChannelID,
            ChannelLogo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""";
        cur.execute(sql_command, (SPTranslations[blob['category']], blob['date'], blob['starttime'], blob['pvrchannel'], blob['tvshow'], blob['staffel'], blob['episode'], blob['title'], blob['neueepisode'], blob['description'], blob['rating'], blob['content_rating'], blob['genre'], blob['studio'], blob['status'], blob['year'], blob['firstaired'], blob['runtime'], blob['thumb'], blob['poster'], blob['pvrid'], blob['logo']))
        conn.commit()
        conn.close()

nun möchte ich gern diese Tabelle aulesen sortiert nach StartTime
also:

Code: Alles auswählen

        conn = sqlite3.connect(SerienPlaner)
        cur = conn.cursor()
        sql = "SELECT * FROM TVShowData Order BY StartTime"
        cur.execute(sql)

soweit komme ich noch... das ganze soll aber in einem loop laufen in dem der Datensatz immer um 1 erhöt wird bis die max. Anzahl erreicht ist... also

Code: Alles auswählen

widget = 1
for i in range
    if widget > 16
        conn. close
        break

    conn = sqlite3.connect(SerienPlaner)
    cur = conn.cursor()
    sql = "SELECT * FROM TVShowData Order BY StartTime"
    cur.execute(sql)

    
    WINDOW.setProperty('SerienPlaner.%s.Starttime' % (widget), data['starttime'])
    WINDOW.setProperty('SerienPlaner.%s.TVShow' % (offset + widget), data['tvshow'])
    usw...
    widget +=1
return widget - 1

was ich nicht verstanden habe und was auch fehlt ist, wie kann ich die data['...'] aus der datenbank auslesen mit i als Datensatz
also datensaz[1], datensatz[2] usw...

[hans]

Code: Alles auswählen

sql = "SELECT * FROM TVShowData Order BY StartTime LIMIT 10"

Liefert genau 10 Datensätze (oder eben soviel wie da sind).

*edit*
und dann fetchall() (object von cursor) aufrufen.

[sveni_lee]

ja okay... das habe ich verstanden...

aber ich möchte ja in den den Zeilen

Code: Alles auswählen

WINDOW.setProperty('SerienPlaner.%s.Starttime' % (widget), data['starttime'])
WINDOW.setProperty('SerienPlaner.%s.TVShow' % (offset + widget), data['tvshow'])

ja die daten aus den einzelnen Datensätze eintragen...

später sollen dann Datensätz StartTime außerhalb einer bestimmeten Zeitspanne lieger verworfen werden...

Limit gibt mir ja eine bestimmte anzahl Datensätzen... ich möchte immer den "i"ten Datendatz...

[Sirius3]

@sveni_lee: ich verstehe Dein Problem nicht, und Dein Code trägt nicht zum Verständnis bei. Wenn Du Datensätze mit einem bestimmten starttime haben willst, dann schreib das doch in die WHERE-Klausel von Deinem SELECT. Irgendwelche Datensätze außerhalb der Datenbank zu zählen oder zu indizieren führt nur zu fehlerhaften und unwartbaren Programmen.

Bei Deinem blob alles in unicode umzuwandeln sollte eigentlich unnötig sein. Felder wie status oder year sind garantiert Zahlen, das als Strings zu behandeln ist also ein Fehler. Was soll denn "for i in range" bedeuten, was ist range für ein Objekt? In jedem Schleifendurchgang ein connect und das selbe SELECT ist Rechenzeitverschwendung übelster Sorte. Zudem fragst Du die Datensätze ja gar nicht ab.

Also grundsätzliches Vorgehen:

Code: Alles auswählen

cursor.execute("""SELCECT [felder die ich brauch kein *] FROM tabelle WHERE [Bedingung, dass ich genau die Datensätze bekomme die ich brauch] ORDER BY starttime""")
 
 for widget, data in enumerate(cursor):
     mach was mit data

[sveni_lee]

Vorweg: Warum steige ich auf eine SQlite datenbank um...
Im Moment ist es so, das die daten von einer Internetseite geholt werden und direct in ein
WINDOW.setProperty('SerienPlaner.%s..... geschrieben werden... daraus ergeben sich dann bis zu 15 verschidene Properties für jeden Eintrag, da als max 15 eingestellt ist....
Die Serien sind in 4 verschiedene Kategorien unterschieden... was ansich erst einmal nicht das Problem darstellt...
wenn ich aber nun eine andere Karegorie wähle, dauert eine evigkeit bis alle daten von der Internetseite geholt wurde...
Mein gedanke war nun sämtliche daten in eine sqlite datenbank zu schreiben (im Hintergrund) und dan die Properties aus der Datenbank heraus zu befüllen, das solle wesentlich schneller gehen...

@sveni_lee: ich verstehe Dein Problem nicht, und Dein Code trägt nicht zum Verständnis bei. Wenn Du Datensätze mit einem bestimmten starttime haben willst, dann schreib das doch in die WHERE-Klausel von Deinem SELECT. Irgendwelche Datensätze außerhalb der Datenbank zu zählen oder zu indizieren führt nur zu fehlerhaften und unwartbaren Programmen.

warum mache ich das ganze...
In meinem Plugin ist es wählbar ob man outdatet TvShow angezeigt bekommen möchte oder nicht. das worde bisher so abgefangen...

Code: Alles auswählen

if not __showOutdated__:
    _now = datetime.datetime.now()
    try:
        _dt = '%s.%s.%s %s' % (_now.day, _now.month, _now.year, starttime)
        timestamp = date2timeStamp(_dt, '%d.%m.%Y %H:%M')
        if timestamp + 60 * int(blob['runtime']) < int(time.time()) :
            writeLog('SerienPlaner: discard blob SP.%s.%s, broadcast @%s has already finished' % (category, i, _dt), level=xbmc.LOGDEBUG)
            continue
    except ValueError:
        writeLog('Could not determine any date value, discard blob SP.%s.%s' % (category, i), level=xbmc.LOGERROR)
        continue

Bei Deinem blob alles in unicode umzuwandeln sollte eigentlich unnötig sein. Felder wie status oder year sind garantiert Zahlen, das als Strings zu behandeln ist also ein Fehler. Was soll denn "for i in range" bedeuten, was ist range für ein Objekt? In jedem Schleifendurchgang ein connect und das selbe SELECT ist Rechenzeitverschwendung übelster Sorte. Zudem fragst Du die Datensätze ja gar nicht ab.

richtig, weil ich nicht weiß wie die Abfrage ausehen muß ich will ja den Wert jeder Spalte in einer Reihe einem Property zuordnen

mit "for i in range" wollte ich einen loop erzeugen...
aber du hast recht, das würde immer wieder das öffnen der db verablassen...

Also grundsätzliches Vorgehen:

Code: Alles auswählen

cursor.execute("""SELCECT [felder die ich brauch kein *] FROM tabelle WHERE [Bedingung, dass ich genau die Datensätze bekomme die ich brauch] ORDER BY starttime""")
 
 for widget, data in enumerate(cursor):
     mach was mit data

also das wählbar machen von outdatet TVShows könnte ich auch weglassen...
dann könnte man den ja das ganze wie folgt machen...

Code: Alles auswählen

cursor.execute("""SELCECT * FROM tabelle WHERE [berechnung showoutdated] ORDER BY StartTime LIMIT 15""")

aber wie bekomme ich diese berechung dort rein....

und was ich noch nicht verstanden habe, wiekann ich dann die Werte den Property zuordnen ich muss ja dann 15x Anzahl der spalten an Properties haben....

[Sirius3]

@sveni_lee: wie man mit Datum und Zeit in SQLite rechnet steht in der Dokumentation dazu. Am einfachsten ist es wohl in Millisekunden (unixepoch) oder Tagen (julianday) zu rechnen.

und was ich noch nicht verstanden habe, wiekann ich dann die Werte den Property zuordnen ich muss ja dann 15x Anzahl der spalten an Properties haben....

Wie Du an die Daten weißt Du (execute und for-Schleife), wie Du Daten anzeigst weißt Du auch (WINDOW.setProperty). Beides kombinieren und Du hast Dein Problem gelöst.

[sveni_lee]

Wie Du an die Daten weißt Du (execute und for-Schleife), wie Du Daten anzeigst weißt Du auch (WINDOW.setProperty). Beides kombinieren und Du hast Dein Problem gelöst.

also in der Form:

Code: Alles auswählen

def refreshWidget(category, offset=0):

    conn = sqlite3.connect(SerienPlaner)
    cur = conn.cursor()
    cur.execute("""SELECT * FROM TVShowData Order BY StartTime LIMIT 15""")
    for widget, data in enumerate(cur):     

        WINDOW.setProperty('SerienPlaner.%s.TVShow' % (offset + widget), data['TVShow'])
        WINDOW.setProperty('SerienPlaner.%s.Staffel' % (offset + widget), data['Season'])
        WINDOW.setProperty('SerienPlaner.%s.Episode' % (offset + widget), data['Episode'])
        WINDOW.setProperty('SerienPlaner.%s.Title' % (offset + widget), data['Title'])
        WINDOW.setProperty('SerienPlaner.%s.Starttime' % (offset + widget), data['StartTime'])
        widget += 1

    return widget - 1

sollte ja eigendlich funktionieren...

gibt aber noch einen Fehler...

Code: Alles auswählen

TypeError: tuple indices must be integers, not str

[Sirius3]

@sveni_lee: hast Du Dir schon angeschaut, was Du als Ergebnis eines SELECT bekommst?

Wenn Du immer einen offset draufaddierst, kannst Du enumerate auch gleich einen anderen Startwert angeben, das widget += 1 zum Schluß ist unsinnig. Und vermeide diese endlosen Codewiederholungen.

Code: Alles auswählen

FIELDS = ['TVShow', 'Season', 'Episode', 'Title', 'StartTime']

cur.execute(""" SELECT %s FROM TVShowData Order BY StartTime LIMIT 15""" % ','.join(FIELDS))
for idx, data in enumerate(cur, offset):
    for field, item in zip(FIELDS, data):
        WINDOW.setProperty('SerienPlaner.%d.%s' % (idx, field), item)

[sveni_lee]

Wow... das reduziert ja den code um einiges...

das Property wird jetzt aus der Überschrift der Tabelle und dem Inhalt je Reige gebildet... richtig?

Danke, darauf wäre ich nie gekommen...

[sveni_lee]

@sveni_lee: wie man mit Datum und Zeit in SQLite rechnet steht in der Dokumentation dazu. Am einfachsten ist es wohl in Millisekunden (unixepoch) oder Tagen (julianday) zu rechnen.

wenn ich alle outdatet TVShows ausklammern möchte müsste ich wie folgt vorgehen, ichtig?

Starttime in Millisekunden + RuningTime*60*60 > currenttime in Millisekunden

also

Starttime + RunningTime*60*60 > datetime(strftime('%s','now') , 'unixepoch')

Code: Alles auswählen

datetime(strftime('%s','now') , 'unixepoch')

sollte ja eigendlich die aktuelle Zeit in Millisekunden umrechnen, oder?

nur wie kan ich die Starttime unrechnen? hier ist ja das format hh:mm. ich müsste ja den aktuellen Tag, aktuellen Monat, aktuelle Jahr zur Berechnung herranziehen...

EDIT:

mein Gedanke war:

Code: Alles auswählen

    _now = datetime.datetime.now()

    conn = sqlite3.connect(SerienPlaner)
    cur = conn.cursor()
    cur.execute(""" SELECT %s FROM TVShowData WHERE datetime(strftime('_now.day _now.month _now.year  %H:%M', Starttime), 'unixepoch') + RunningTime*60*60 > datetime(strftime('%s','now') , 'unixepoch') Order BY StartTime LIMIT 15""" % ','.join(properties))
    for idx, data in enumerate(cur, offset):
        for field, item in zip(properties, data):     
            
            WINDOW.setProperty('SerienPlaner.%d.%s' % (idx, field), item)

[Sirius3]

@sveni_lee: die eigentliche Frage ist doch, warum sind StartTime und Date zwei Spalten?

[sveni_lee]

@sveni_lee: die eigentliche Frage ist doch, warum sind StartTime und Date zwei Spalten?

weil ich das in zwei verschieden Properties haben möchte...

bei dem von mir genanten String bekomme ich immer folgende Fehlermelgung

Code: Alles auswählen

TypeError: not enough arguments for format string

[Sirius3]

@sveni_lee: die Darstellung ist erst einmal unabhängig von Datenhaltung. Nur weil Du für die Darstellung einen String brauchst ist es Quatsch alle Zahlen (Episode, Rating, Runtime) als Strings in die Datenbank zu schreiben. Genauso ist es Quatsch ein zusammengehöriges Datum als zwei Teile zu speichern, nur weil Du es für eine Darstellung brauchst. Normalerweise hat man ja nicht nur eine Darstellung sondern viele. Willst Du jetzt alles 5fach speichern nur weil sich am Ausgabeformat etwas ändert?

[sveni_lee]

das vielleicht nicht aber in diesem Fall wären es zwei anstelle von einer Spalte...
Das Datum steht mir auch als 20160408T171000 zur verfügung wäre das für
die Berechnung besser und wie sollte es dann abgespreichert werden?

[Sirius3]

@sveni_lee: das Datum sollte Dir als datetime Objekt zur Verfügung stehen, so kommt es in die Datenbank hinein, und so kommt es auch wieder raus.

[sveni_lee]

@sveni_lee: das Datum sollte Dir als datetime Objekt zur Verfügung stehen, so kommt es in die Datenbank hinein, und so kommt es auch wieder raus.

sorry aber da steh ich auf'm Schlauch... wie ist das gemeint?

soll ich schon den insert ändern, so das es gar nicht erst in unicode gewandelt wird?

[Sirius3]

@sveni_lee: dass Du nicht alles in Strings umwandeln sollst, hab ich schon im ersten Post geschrieben. Jede Größe hat einen natürlichen Typ, Zahlen sind je nachdem int oder float, Datum ist datetime. Gleich nach dem Einlesen sollte man alles in den richtigen Typ umwandeln und erst beim Rausschreiben in Strings. Datenbanken können dabei eben mit vielen Typen richtig umgehen, so dass ein Konvertieren in Strings falsch ist.

[sveni_lee]

ich habe jetzt mal das unicode entfernt und es als datetime in die db eingefügt...
und die db neu aufgebaut.

Aber ich muß doch trotzden noch alles irgendwie Millisekunden umwandeln wenn ich outdated berechnen will...
und so wie ich das verstanden habe benötige ich dafür ja einen kompletten string mit datum und uhrzeit.

[BlackJack]

@sveni_lee: Wieso musst Du wenn Du `datetime`-Objekte hast irgend etwas in Millisekunden umrechnen? Mit `datetime`-Objekten kann man rechnen. Zieh die voneinander ab und schau Dir das `timedelta`-Objekt an was Du dabei bekommst, beziehungsweise erstell ein `timedelta`-Objekt das die entsprechende Zeitspanne ”wert” ist und vergleiche auf grösser oder kleiner mit der Differenz, je nach dem was genau Du testen möchtest.

[pillmuncher]

@sveni-lee: Hier ein Beispiel:

Code: Alles auswählen

>>> import datetime as dt
>>> t1 = dt.datetime.now()                                                      
>>> delta = dt.timedelta(14)                                                    
>>> t2 = t1 - delta
>>> str(t1)                                                                     
'2016-04-08 19:51:50.968163'
>>> str(t2)                                                                     
'2016-03-25 19:51:50.968163'
>>> str(t1.date())
'2016-04-08'
>>> str(t2.date())                                                              
'2016-03-25'

Mehr dazu in der Doku.