SQL Abfrage und regulare Expression

[ete]

Hallo!

Ich habe folgende Datenbank mit sqlite generiert:

Tabelle:
annotation
Spalten:
project_name
picture_name
haustoria_anno

Ein Beispiel sehe so aus:

Project1
01A_002_01.jpg
True

Project1
01B_004_01.jpg
False

Project1
03A_008_01.jpg
False

Project2
03A_004_01.jpg
False

Jetzt würd ich gerne folgende Abfrage machen:

Alle „picture_name“ und „haustoria_anno“ mit dem „project_name“ Project1.

Ok, das ist einfach und funktioniert auch:
"""SELECT haustoria_anno, picture_name FROM annotation
WHERE project_name = ?""",
(project)

Jetzt würde ich gerne alle „True“ der „haustoria_anno“ Zählen, wenn die Ziffern der „picture_name“ vorm ersten Unterstrich-1 (also A und B wird ignoriert) die gleichen sind. Hier mal ein Beispiel was ich meine:

Für Project1 müsste folgendes rauskommen:
Project1, 01, 1
Project1, 03, 0

weil die „picture_name“ mit Ziffern 01 zusammengefasst werden und ein True gezählt wurde. 03 gibt es nur einmal und es gibt kein True, also 0.

Würdet ihr die zweite Abfrage auch über SQL (regulare Expression) machen oder die Daten aus der ersten Abfrage zwischen speichern und über pure Python auswerten (Dict o.ä.)?

Danke für jeden Tip!
Stefanie

P.S. ich hoffe es ist verständlich erklärt

[Masaru]

Über welche Menge an Datensätzen, die zu erwarten sind, sprechen wir denn hier?

[ete]

Momentan an die 50 Projekte mit à ca. 10.000 picture_names. Stätig steigend (open end)

[Defnull]

'picture_name' ist also in Wirklichkeit kein Name, sondern ein Zusammenschluss aus Einzelinformationen. Du willst auf diesen Einzelinformationen arbeiten, also warum speicherst du sie nicht auch in einzelne Spalten?

Wenn du reguläre Ausdrücke auf Datenbanken los lassen musst (oder etwas anders tust, das eine Laufzeit von O(n) hat) machst du etwas falsch.

[ete]

"picture_name" ist schon ein Dateiname aber für den Output brauche ich nur die Ziffern vor dem ersten Unterstrich. Aber du hast recht, ich könnte noch ein zusätzlich Spalte nur mit den bennötigten Ziffern erstellen.