[?]sqlite3 Abfrage mit mehreren Werten

[lackschuh]

Hallo

Ich bräuchte mal Hilfe bzw. ein paar Tipps. Und zwar habe ich eine Datenbank zur Verwaltung von Büchern bestehend aus 6 Tabelen:
Buch(id, titel, standort, verlag, kategorie)
Autor(id, vorname, nachname)
Buchautor(buchid, autorid)
Kategorie(id, bezeichnung)
...

Eine normale bzw einfache Suche funktioniert mittels LIKE problemlos.

Code: Alles auswählen

sql = """SELECT DISTINCT buch.id, buch.titel, autor.vorname, autor.nachname,  verlag.bezeichnung, kategorie.bezeichnung
       FROM buch 
       LEFT OUTER JOIN buchautor ON buchautor.buchid = buch.id 
       LEFT OUTER JOIN kategorie ON buch.kategorieid = kategorie.id 
       LEFT OUTER JOIN autor ON autor.id = buchautor.autorid 
       LEFT OUTER JOIN verlag ON buch.verlagid = verlag.id 
       WHERE buch.titel LIKE ? OR autor.nachname LIKE ? OR kategorie.bezeichnung LIKE ? OR verlag.bezeichnung LIKE ? OR buch.id LIKE ? OR buch.standort LIKE ?"""

Meine Frage ist nun:

wie kann ich zwei oder mehr Suchbegriffe eingeben zB Beton+Holz? Also, wenn ich ins Eingabefeld folgendes Eintrage:
begriff1+begriff2, dann sollen mir alle Tupels die begriff1 beinhalten als auch alle Tupels die begriff2 beinhalten ausgegeben werden.
Das "+" muss ich irgendwie definieren, nur hänge ich da momentan fest.

mfg

[BlackJack]

@lackschuh: Da wirst Du pro Suchbegriff ein ``OR feldname LIKE ?`` benötigen. Also dynamisch erstellen. Das wäre so der Punkt an dem ich spätestens auf SQLAlchemy setzen würde.

[deets]

Indem du AND verwendest.

Code: Alles auswählen

select buch.id from buch left outer join buchautor on buchautor.buchid = buch.id
where
(buch.titel like %(a)s or autor.name like %(a)s) and (buch.titel like %(b)s or autor.name like %(b)s)

Und hier solltest du ueber die verwendung von SQLAlchemy nachdenken. Denn das hilft immens, solche komplexen Queries zusammenzubauen, weil es die Verwaltung von Tabellen macht usw.

Wichtig in diesem Zusammenhang: alle Begriffe die ohne Plus sind gehoeren verodert in eine Klausel. Alle verundeten jeweils als Teilausdruck mit den ganzen ORs verundet mit den anderen Klauseln.

[lackschuh]

Hallo

Nach deets Hilfe geht nun mal die Abfrage mit nEingaben.

Anbei der komplette Code:

Code: Alles auswählen

@post('/search')
def search_submit():
    name     = request.forms.get('name')
    con = sqlite3.connect('test.db')
    cur = con.cursor()
    sl = name.split("+")
    sl = [u"%%%s%%" % item.decode("utf-8") for item in sl]
    sql =""
    
    
    if len(sl) == 1:
        for s in sl:
            sql += "buch.titel LIKE '%s' OR " %s
            sql += "autor.nachname LIKE '%s' OR " %s
            sql += "verlag.bezeichnung LIKE '%s' OR " %s
            sql += "kategorie.bezeichnung LIKE '%s' OR " %s
            sql += "buch.standort LIKE '%s' " %s
    
    elif len(sl)>1:
        for s in sl:
            sql += "buch.titel LIKE '%s' OR " %s
            sql += "autor.nachname LIKE '%s' OR " %s
            sql += "verlag.bezeichnung LIKE '%s' OR " %s
            sql += "kategorie.bezeichnung LIKE '%s' OR " %s
            sql += "buch.standort LIKE '%s' " %s
            sql += "AND "
    
    sql2 = """SELECT DISTINCT buch.id AS ID, buch.titel, autor.vorname, autor.nachname, autortyp.bezeichnung, verlag.bezeichnung AS verlag, buch.jahr, buch.standort, kategorie.bezeichnung AS kategorie \
           FROM buch \
           LEFT OUTER JOIN buchautor ON buchautor.buchid = buch.id \
           LEFT OUTER JOIN kategorie ON buch.kategorieid = kategorie.id \
           LEFT OUTER JOIN autor ON autor.id = buchautor.autorid \
           LEFT OUTER JOIN autortyp ON autor.autortypid = autortyp.id \
           LEFT OUTER JOIN verlag ON buch.verlagid = verlag.id \
           WHERE """
    
    sql3 = sql2+sql
    if sql3.endswith("AND "):
        sql3 = sql3[:-4]
    print sql3
    
    
    cur.execute(sql3)
    
    result = cur.fetchall()
    cur.close()
    output = template('make_table', rows=result)
    return output

Die letzte Frage ist nun, ob man diesen Bastel so stehen lassen kann oder ob es Verbesserungen gibt. In der Doku steht ja folgendes:

# Never do this -- insecure!

Code: Alles auswählen

symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead

Code: Alles auswählen

t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

[deets]

Ich wuerde das wegschmeissen + auf SQLAlchemy setzen. Denn damit hast du den grossen Vorteil, dass du

- dich nicht um SQL-injection kuemmern musst (das ist das "unsichere" an dem %-Vorgehen)
- du komplexe Queries leicht zusammenbauen kannst, mit automatischem nachhalten von zB join-tabellen usw.

[lackschuh]

Ok, danke. Ich werde mich mal über SQLAlchemy schlau machen. Kleine Frage noch Vorweg: ist meine aktuelle sqlite DB (also die test.db) kompatibel zu SQLAlchemy oder muss ich da wieder umbauen?

[sparrow]

SQLAlchemy kann mit verschiedenen Datenbanken umgehen, unter anderem auch mit SQLite. Du kannst aber deine Anwendung hinterher recht problemlos auf ein anderes DBMS wie postgresql oder mySQL loslassen.

Wenn du dir zudem noch anschaust, wie man damit Modelle deklariert, wirst du die direkte Pflege ohne Mapper für eine Plage aus der Hölle halten. Insgesamt wird es dir viel Zeit und Sorgen sparen.

[lackschuh]

Hallo

Ich bräuchte noch einen Tippp und zwar hab ich folgende Abfrage:

Code: Alles auswählen

    sql ="""SELECT id,nachname, vorname FROM autor ORDER BY nachname ASC"""
    sql2 = """SELECT id,titel FROM buch ORDER BY id ASC"""

Template:

Code: Alles auswählen

    <label>Buch:
      <select name="buchID" id="buchD">
   %for row in rows2:
   %txt = u"%s %s" % (row[0],row[1])
        <option>{{txt}}</option>
    %end
      </select>
      Autor:
      <select name="autorID" id="autorID">
   %for row in rows1:
   %txt = u"%s %s" % (row[0],row[1])
        <option>{{txt}}</option>
    %end
      </select>
      <br>
      <br>
    </label>
    <label>
      <input type="submit" name="save" value="Speichern">
    </label>

Soweit so gut, aber das eigentliche Problem ist nun, dass dich nun das Ausgewählte in eine Zwischentabelle "buchautor" speichern muss (also nur die ID).

Code: Alles auswählen

            buchID = request.GET.get('buchID').strip()
            autorID = request.GET.get('autorID').strip()
            con.text_factory = str
            sql3="""INSERT INTO buchautor VALUES (?,?)"""

Die Frage ist nun, wie kann ich in der Combobox/Drop Down nur den Text bzw die Beizeichnung anzeigen lassen (ok mit row[1]) aber schlussendlich soll/muss ich die IDs in die Tabelle "buchautor" schreiben.

Ich hoffe, ihr wisst ungefähr was ich meine

PS: In SqlAlchemy bin ich mich zZ am einlesen. Aber das obige muss a.s.a.p. gehen...

[lackschuh]

Hallo

Die obige Frage hat sicher erledigt. Durch probieren statt studieren bin ich auf die Lösung gekommen. Ich hab einfach die Auswahl mit einem value versehen:

Code: Alles auswählen

      <select name="kategorieID" id="kategorieID">
   %for row in rows1:
   %txt = u"%s" % (row[1])
        <option value ="{{row[0]}}">{{txt}}</option>
    %end

Somit hat sich auch die PN an Leonidas erledigt.