Hilfe bei Abfrage (if...)

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
ete
User
Beiträge: 218
Registriert: Montag 19. Februar 2007, 13:19
Kontaktdaten:

Hilfe bei Abfrage (if...)

Beitragvon ete » Montag 3. November 2008, 15:37

Hallo!

Ich brauche Hilfe für eine sqlite Abfrage, denn ich komme nicht weiter.
Ich möchte in meine Abfrage eine IF Klausel einfügen. Wie setze ich das in SQL um?
Hier ein Bsp. von 2 Tabellen, je mit name und length als Spalten:

IF length in table2 >400:
SELECT name FROM table2
ELSE:
SELECT name FROM table3 IF length_of_name_of_table3 > length_of_name_of_table2

Ich hoffe das war verständlich :?:
Wobei name in table2 und table3 identisch ist.
Kann jemand weiterhelfen, ich bin nicht so SQL bewandelt...
Danke :D
Stefanie
Pekh
User
Beiträge: 482
Registriert: Donnerstag 22. Mai 2008, 09:09

Beitragvon Pekh » Montag 3. November 2008, 16:00

In SQL heißt 'IF' 'WHERE'. Abfragen folgen der Struktur 'SELECT spalte1, spalte 2 FROM tabelle1, tabelle2 WHERE bedingung 1 AND bedingung 2 OR ....'. Gibt da ne Menge Möglichkeiten, Abfragen zu schachteln und auszuwerten. Ein entsprechendes SQL-Tutorial wird dich da weiterbringen.
querdenker
User
Beiträge: 424
Registriert: Montag 28. Juli 2003, 16:19
Wohnort: /dev/reality

Beitragvon querdenker » Montag 3. November 2008, 16:52

Hallo Stefanie,

ich stelle dir erstmal eine Gegenfrage: Was willst du damit erreichen? Besser gefragt, wie sehen die Tabellendefinitionen aus.

Hintergrund: DB-basierende Anwendungen stehen und fallen mit dem DB-Design. Und die Abfrage die du da machen möchtest, ergibt in meinen Augen keinen Sinn.
ete
User
Beiträge: 218
Registriert: Montag 19. Februar 2007, 13:19
Kontaktdaten:

Beitragvon ete » Dienstag 4. November 2008, 10:14

Pekh hat geschrieben:In SQL heißt 'IF' 'WHERE'. Abfragen folgen der Struktur 'SELECT spalte1, spalte 2 FROM tabelle1, tabelle2 WHERE bedingung 1 AND bedingung 2 OR ....'. Gibt da ne Menge Möglichkeiten, Abfragen zu schachteln und auszuwerten. Ein entsprechendes SQL-Tutorial wird dich da weiterbringen.

Gut, ich war etwas verwirrt, weil es noch CASE gibt...

Hier mal ein direktes Beispiel (ich hoffe es ist verständlich...):

Tabelle1

contig1 HS03I09_5prim
contig1 HV06Z06_5prim
contig1 HY01B05_5prim
contig1 HS03I09_3prim
contig1 HV06Z06_3prim
contig1 HY01B05_3prim
contig2 HX02N09_5prim
contig2 HO15C06_5prim
contig2 HX02N09_3prim
contig2 HO15C06_3prim
contig3 HO14H18_5prim
contig3 HO15P09_5prim
contig3 HO14H18_3prim
contig3 HO15P09_3prim

Tabelle2
HS03I09_5prim 405
HV06Z06_5prim 398
HY01B05_5prim 612
HX02N09_5prim 506
HO15C06_5prim 199
HO14H18_5prim 489
HO15P09_5prim 451

Tabelle3
HS03I09_3prim 205
HV06Z06_3prim 416
HY01B05_3prim 103
HX02N09_3prim 98
HO15C06_3prim 513
HO14H18_3prim 254
HO15P09_3prim 301

Ich möchte pro contig den längsten EST finden unter der Bedingung, bevorzugt den 5prim EST
zu nehmen, wenn er grösser als 400 ist. Ist er kleiner, so soll in Tabelle3 geschaut werden, ob der gleiche EST
(gleiche ID) länger ist. Wenn ja, wird dieser verwendet, wenn nein der 5prim EST.
Dannach soll in Tabelle1 der längste EST pro contig rausgesucht werden. Also bleibt am Ende ein EST pro contig über.

Wobei EST (http://de.wikipedia.org/wiki/Expressed_Sequence_Tag) eine Klon ID (z.B. HS03I09) ist, 5prim bzw. 3prim die Seite, von der ich eine Sequenz haben (auf gut deutsch
von links uns rechts desselben EST's) und die Zahl die Länge der Sequenz.

Das Ergebnis wäre folgendes:
contig1 HY01B05_5prim
contig2 HO15C06_3prim
contig3 HO14H18_5prim
Zuletzt geändert von ete am Dienstag 4. November 2008, 10:33, insgesamt 2-mal geändert.
Pekh
User
Beiträge: 482
Registriert: Donnerstag 22. Mai 2008, 09:09

Beitragvon Pekh » Dienstag 4. November 2008, 10:25

Gut, vielleicht fehlt mir jetzt Zeit und guter Wille, mich genau damit auseinanderzusetzen, aber es sieht mir etwas ... wirr ... aus. Wo finde ich die Spaltennamen, wo die Werte? Vielleicht könntest du das ganze auch einfach mal als DB-Dump zur Verfügung stellen, das macht das Nachvollziehen etwas leichter.

Prinzipiell glaube ich aber, daß du dir für den Anfang eine zu komplizierte SQL-Abfrage vorgenommen hast. Einfacher ist es wahrscheinlich, die Inhalte der Tabellen separat abzufragen und die Vergleichs- / Auswahllogik in deinem Python-Programm zu erledigen. Ob das dann elegant ist, kann ich so ohne weiteres nicht sagen. Mal so ganz aus dem Bauch heraus: Wenn du viele Tabellen mit immer nur ganz wenigen Spalten hast, solltest du gucken, ob du nicht einige Tabellen zusammenfassen kannst. Das erleichtert Abfragen bisweilen ungemein.
Benutzeravatar
mkesper
User
Beiträge: 919
Registriert: Montag 20. November 2006, 15:48
Wohnort: formerly known as mkallas
Kontaktdaten:

Beitragvon mkesper » Dienstag 4. November 2008, 11:51

Die Spalten enthalten nicht-atomare Daten. Das 5prim bzw. 3prim gehört nicht in die gleiche Spalte wie das EST, wenn ich das richtig sehe.
Tabellennamen sollten natürlich auch möglichst sprechend sein.
So fände ich das sinnvoller:

Code: Alles auswählen

Tabelle ESTs  #Hier haben prims nichts zu suchen
ID   contig    EST
1    1        HS03I09
2    1        HV06Z06
...

Tabelle prim_5  #Hier sind die 5er prims drin
ID   EST       length
1    HS03I09  405
...

Tabelle prim_3  #Hier nur die Dreier
ID   EST       length
1    HS03I09  205
...

Pseudo-Code (kann man bestimmt auch in purem SQL regeln, finde ich so aber einfacher zu verstehen):

Code: Alles auswählen

for contig in cursor.execute("SELECT contig FROM Tabelle1"):
    for EST in cursor.execute("SELECT EST FROM ESTs WHERE contig = ?", contig):
        Jetzt noch die beiden anderen Tabellen abfragen und Vergleiche anstellen...
ete
User
Beiträge: 218
Registriert: Montag 19. Februar 2007, 13:19
Kontaktdaten:

Beitragvon ete » Dienstag 4. November 2008, 12:43

Ja, so hab ich's gemeint.
Ich versuchs mal, danke!

Tabelle 2 und 3 zu vereinen, wird schwierig, weil ich am Ende wissen muss was 5prim und 3prim ist.
Benutzeravatar
mkesper
User
Beiträge: 919
Registriert: Montag 20. November 2006, 15:48
Wohnort: formerly known as mkallas
Kontaktdaten:

Beitragvon mkesper » Dienstag 4. November 2008, 12:56

ete hat geschrieben:Tabelle 2 und 3 zu vereinen, wird schwierig, weil ich am Ende wissen muss was 5prim und 3prim ist.

Könnte dann so aussehen:

Code: Alles auswählen

Tabelle prims
ID  EST    prim  length
1  HS03I09  5    405
2  HS03I09  3    205
...
Lonestar
User
Beiträge: 147
Registriert: Samstag 9. August 2008, 08:31

Beitragvon Lonestar » Dienstag 4. November 2008, 13:32

mkallas hat geschrieben:

Code: Alles auswählen

Tabelle ESTs  #Hier haben prims nichts zu suchen
ID   contig    EST
1    1        HS03I09
2    1        HV06Z06
...

Tabelle prim_5  #Hier sind die 5er prims drin
ID   EST       length
1    HS03I09  405
...

Tabelle prim_3  #Hier nur die Dreier
ID   EST       length
1    HS03I09  205
...



Hi, ich muss leider zugeben das ich das Beispiel von ete ganz oben nicht komplett verstanden habe. Aber da im erklärenden Text steht das EST eine ID darstellen soll haben die EST nachdem du mit ihnen eine Tabelle und eine eindeutigen ID angelegt hast in den nachfolgenden Tabellen eigentlich auch nichts mehr zu suchen. Die 2. von dir gepostete Version finde ich schon besser würde aber über die ESTID die Daten zuorden. Eine fortlaufende ID ist dann in der Tabelle eigentlich auch nicht mehr nötig so wie ich das sehe (die ESTID ist bei meinem Beispiel nur zufällig fortlaufend)

Code: Alles auswählen

ESTID    prim  length
  1      5     405
  2      3     205
...
Benutzeravatar
mkesper
User
Beiträge: 919
Registriert: Montag 20. November 2006, 15:48
Wohnort: formerly known as mkallas
Kontaktdaten:

Beitragvon mkesper » Dienstag 4. November 2008, 15:35

Lonestar hat geschrieben:Aber da im erklärenden Text steht das EST eine ID darstellen soll haben die EST nachdem du mit ihnen eine Tabelle und eine eindeutigen ID angelegt hast in den nachfolgenden Tabellen eigentlich auch nichts mehr zu suchen.

Hmm, und wie soll ich die Einträge dann verknüpfen?
Lonestar hat geschrieben:Die 2. von dir gepostete Version finde ich schon besser würde aber über die ESTID die Daten zuorden.

Verstehe ich nicht.
Lonestar hat geschrieben:Eine fortlaufende ID ist dann in der Tabelle eigentlich auch nicht mehr nötig so wie ich das sehe.

Korrekt, war wohl reine Gewohnheit.
Lonestar
User
Beiträge: 147
Registriert: Samstag 9. August 2008, 08:31

Beitragvon Lonestar » Dienstag 4. November 2008, 15:45

Die Verknüpfung findet doch durch die eindeutige ESTID schon statt die du in der Tabelle 'ESTs' zugewiesen hast. Wenn jeder Eintrag der Tabelle 'prims' einer ID zugeordnet wird brauche ich doch nicht auch noch den Namen zu der ID dazuschreiben. Oder gibt es einträge in der Tabelle 'ESTs' die nicht eindeutig sind?
querdenker
User
Beiträge: 424
Registriert: Montag 28. Juli 2003, 16:19
Wohnort: /dev/reality

Beitragvon querdenker » Dienstag 4. November 2008, 16:26

Lonestar hat geschrieben:Die Verknüpfung findet doch durch die eindeutige ESTID schon statt die du in der Tabelle 'ESTs' zugewiesen hast. Wenn jeder Eintrag der Tabelle 'prims' einer ID zugeordnet wird brauche ich doch nicht auch noch den Namen zu der ID dazuschreiben. Oder gibt es einträge in der Tabelle 'ESTs' die nicht eindeutig sind?


Jepp. ESTs gibt es für 3prim und für 5prim vorhanden sein. Sieh dir die
Daten an.


@ete: Wo kommen die Daten eigentlich her? Bekommst du die gleich in sqlite oder übernimmst du die aus einem anderen Format?
Lonestar
User
Beiträge: 147
Registriert: Samstag 9. August 2008, 08:31

Beitragvon Lonestar » Mittwoch 5. November 2008, 10:43

Ok, wenn die prim- dinger nicht eindeutig sind dann ist mein Einwand natürlich sinnlos.

Allerdings finde ich die Daten immer noch recht verwirrend :roll:
ete
User
Beiträge: 218
Registriert: Montag 19. Februar 2007, 13:19
Kontaktdaten:

Beitragvon ete » Mittwoch 5. November 2008, 11:48

Ich bin zwar noch nicht fertig aber vielleicht ist es einfacher zu verstehen, wenn ich mal code poste:

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-

import os
try:
    from pysqlite2 import dbapi2 as sqlite3
except ImportError:
    import sqlite3
try:
    os.remove("phenome.s3db")
except:
    pass

conn = sqlite3.connect("phenome.s3db")

# --------------------------------------------------------------------------------------------------------
# Tabelle 1 EST's und Sequenzlängen
sql = """
CREATE TABLE sequence (
  id INTEGER PRIMARY KEY,
  est TEXT,
  prim INTEGER,
  length INTEGER
);
"""
conn.execute(sql)
conn.commit()

data = [
    {"est": "HS03I09", "prim": "3", "length": "205"},
    {"est": "HV06Z06", "prim": "3", "length": "416"},
    {"est": "HY01B05", "prim": "3", "length": "103"},
    {"est": "HX02N09", "prim": "3", "length": "98"},
    {"est": "HO15C06", "prim": "3", "length": "513"},
    {"est": "HO14H18", "prim": "3", "length": "714"},
    {"est": "HO15P09", "prim": "3", "length": "301"},
    {"est": "HS03I09", "prim": "5", "length": "513"},
    {"est": "HV06Z06", "prim": "5", "length": "301"},
    {"est": "HY01B05", "prim": "5", "length": "561"},
    {"est": "HX02N09", "prim": "5", "length": "693"},
    {"est": "HO15C06", "prim": "5", "length": "205"},
    {"est": "HO14H18", "prim": "5", "length": "514"},
    {"est": "HO15P09", "prim": "5", "length": "554"},
]
sql = """
INSERT INTO sequence (
    est,
    prim,
    length
) VALUES (
    :est,
    :prim,
    :length
)
"""
conn.executemany(sql, data)
conn.commit()

# --------------------------------------------------------------------------------------------------------
# Tabelle 2 contigs uns EST's
sql = """
CREATE TABLE contig (
  id INTEGER PRIMARY KEY,
  contig TEXT,
  est TEXT
);
"""
conn.execute(sql)
conn.commit()

data2 = [
    {"contig":"contig1", "est": "HS03I09"},
    {"contig":"contig1", "est": "HV06Z06"},
    {"contig":"contig1", "est": "HY01B05"},
    {"contig":"contig2", "est": "HX02N09"},
    {"contig":"contig2", "est": "HO15C06"},
    {"contig":"contig3", "est": "HO14H18"},
    {"contig":"contig3", "est": "HO15P09"},
]
sql = """
INSERT INTO contig (
    contig,
    est
) VALUES (
    :contig,
    :est
)
"""
conn.executemany(sql, data2)
conn.commit()
# --------------------------------------------------------------------------------------------------------

conn.close()

conn = sqlite3.connect("phenome.s3db")

sql = """
SELECT
    id, est
FROM
    sequence
WHERE prim = 5 AND length > 400
"""
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()

for row in rows:
    print row
   
conn.close()


1) Es soll in Tabelle 1 geschaut werden ob die 5 prims > 400 ist
- wenn ja, nehme ich die EST's (auch wenn der 3 prim grösser ist, es werden 5 prims bevorzugt)
- wenn nein, suche für den gleichen EST bei den 3 prims ob diese grösser als die 5 prims sind
- wenn ja nimm die 3 prims, wenn nein die 5 prims
2) das ergibt eine Liste mit den längsten EST (nach meinen Kriterien) und von welchem prim sie kommen
3) Suche in Tabelle 2 nach dem Längsten EST pro Contig, also bleibt 1 EST pro contig über

Ich hoffe das war verständlicher.

Die Daten kommen aus einem Excel Sheet, mit dem ich gerade kämpfe :evil:
Benutzeravatar
mkesper
User
Beiträge: 919
Registriert: Montag 20. November 2006, 15:48
Wohnort: formerly known as mkallas
Kontaktdaten:

Beitragvon mkesper » Mittwoch 5. November 2008, 12:34

ete hat geschrieben:Die Daten kommen aus einem Excel Sheet, mit dem ich gerade kämpfe :evil:

D'oh! Dann hätten wir natürlich kein SQL gebraucht. :shock:
Einfach die Datei mit xlrd einlesen und in originäre Pythontypen (Dictionaries drängen sich mir hier auf) umwandeln.

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder