Das deutsche Python-Forum

Hy,
ich möchte 2 Zeilen aus 2 verschiedenen PostgreSQL-Tabellen vergleichen, das heißt :

Telefonnummer von Winfried: 004908001101

Nummer von Deutschland: 0049

Diese beiden Informationen stehen in verschiedenen Tabellen.

Ich will lesen:

Winfried Deutschland

Wie kann ich mit einer SQL-Methode oder einer anderen Methode in Zope das erreichen?

Oder sollte ich vieleicht eine neue Tabelle anlegen, dass würde einiges erleichtern.

Wie schreibe ich einen Pythonscript, dass die beiden daten aus der Anrufe tabelle mit der Ländertabelle vergleicht, und mir dann in eine neue Tabelle schreib, wer aus welchem Land mit welcher Nummer angerufen hat.

also die Informationen :
Name, Rufnummer, Land aus den Tabellen zusammen in eine neue Tabelle kommen?.

Oder kann ich einfach eine Procedure verwenden?

Jedoch gestaltet sich noch zusätzlich schwierig, dass ich zum beispielt auch die nummer:004916 für T mobile Deutschland vermerkt habe, und dann auch stehen haben möchte Winfried Tmobile Deutschland 4 mal
aber Winfried Deutschland 0 mal
weil er ja immer vom Handy angerufen hat.
Also müsstte man, immer alle Übereinstimmungen die länger sind, von den niedrigeren abziehen:
Anrufe aus Deutschland - Anrufe von T-Mobile, Anrufe von D2 usw.

Hab leider keinen richtigen Plan wie das gehen soll

Gruß Tim

ps: Bin leider noch nicht so geübt im Umgang mit Python und SQL.

Vielen Dank =)

Hi BoOnOdY,

es gibt viele Möglichkeiten Dein Problem zu lösen und vor allem kommt es
darauf an, wie Du den Datenvergleich weiterverarbeiten möchtest. Ob als
Daten in der DB oder als Pythonstruktur zum direkten (HTML) ausgeben...

Ich hab das ganze mal als relativ komplexe SQL Query abgebildet. Ich ging
mal davon aus, dass Du noch eine dritte Tabelle hast, in der die calls abgelegt
sind, sonst wüsstest Du ja auch nicht, ob Winfried 3x oder nur 1x angerufen
hat.

Da ich mich nicht mit Zope auskenne, habe ich hier den direkten Weg für
PostgreSQL beschrieben.

Hier die einfachen Tabellen:

Code: Alles auswählen

CREATE TABLE phones (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)

CREATE TABLE countries (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)

CREATE TABLE calls (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)

Damit man die 0049, 004916, etc. Nummern auseinander halten kann,
musste ich einen Trick anwenden. Durch regular expressions kann man
hier die Nummern genau voneinander trennen.

Code: Alles auswählen

Deutschland,              ^0049[2-8]
TMobile Deutschland,  ^004916

Durch entsprechende subselects, regex und count geschichten ergibt
sich dann das gewünschte Ergebnis (ja, PostgreSQL kann das alles )...

Code: Alles auswählen

SELECT p.nr,p.name, 
(
SELECT c.name
FROM countries c
WHERE p.nr ~ c.nr
) AS trunk,
(
SELECT count(calls.nr)
FROM calls
WHERE calls.nr = p.nr
) AS calls

FROM phones p
ORDER BY calls DESC

Ergebnis:

Code: Alles auswählen

"0049160012345"; "Winfried";  "TMobile Deutschland"; 3
"00498001101";    "Winfried";  "Deutschland";             1
"004312345678";  "BoOnOdY"; "";                              0

Tabellar

*g*
das probier ich gleihc morgen mal aus, sieht ganz plausiebel aus

danke schon mal.

Will das alles mit Zope ausgeben, daher kommt das in ne normale SQL methode mit nem searchinterface.

Aber es gibt leider 3040 Vorwahlen auf dieser Welt

und ich bekomm anrufe von überall, geht da was mit variablen? weil ich muss ja d1 0049160 auch von d2 0049172 auseinander halten und so...

Hab 2 Tabellen,
in einer steht, Hans mit der nummer 0049800342 hat angerufen und in der anderen
0049= Deutschland

BoOnOdY hat geschrieben:*g*
... Aber es gibt leider 3040 Vorwahlen auf dieser Welt und ich bekomm anrufe von überall, geht da was mit variablen? weil ich muss ja d1 0049160 auch von d2 0049172 auseinander halten und so...

Hab 2 Tabellen,
in einer steht, Hans mit der nummer 0049800342 hat angerufen und in der anderen
0049= Deutschland

Du scheinst ja ne nette PBX zu haben, die Dir Deine DB mit calls zumüllt

.
Wie schon oben beschrieben, habe ich in der Tabelle countries die Provider
(trunks) drinnen:

Du musst die Tabelle eben noch ergänzen:

Code: Alles auswählen

------------------------------------------------
NAME                          ,   NR
------------------------------------------------
Deutschland,               ^0049[2-8]
d1,                        ^0049160
d2,                        ^0049172
------------------------------------------------

Wo willst Du hier Variablen verwenden?
Edit:
Das ^-Zeichen kann man ev. auch weglassen und oben im select
mit dem || Operator "verketten". Aber die 0049[2-8] - das bedeutet
00492..., 00493..., - 00498... - das muss sein, denn sonst hast Du keinen
eindeutigen Treffer bei der Suche! Oder Du baust die Nummern eben
manuell mehrfach ein. Bei 3040 Vorwahlen kommt es dann auch nicht
mehr drauf an ...

Tabellar

Hy, also such PostgreSQL immer nach den größten übereinstimmungen? weil ´d1 und d2 haben ja 00491 gemeinsam.
Ich geh erst ma ne Runde Joggen und dann meld ich mich noch ma hier

befass mich noch ma intensiv damit,
bis dahin, vielen dank

bis nachher

Also ich glaube, das wird etwas schwieriger, denn das hat keine feste logig dahinter, man müsste also sagen:

Vorwahldatensatz mit Telefonnummerdatensatz vergleichen, der mit den meisten Übereinstimmungen wird benutzt

Denke mal am einfachsten klappt das mit ner neuen Tabelle, speicher is egal, hab ich genug

Tabelle 1.

primarykey;10.05.2006;16:00:44:485;5;1;441582526619;2306745425;;CAU_NUNSP

an der roten Zahl sieht man von wo mein anrufer mich anruft.
Tabelle2.
Vorwahl;Land

und die neue Tabelle soll dann einfach
primarykey;10.05.2006;16:00:44:485;5;1;441582526619;2306745425;10;CAU_NUNSP;Land

Gibts da auch nen Weg ? von wegen meiste übereinstimmung und so?

Gruß Tim

Ich hätte ne idee, müsst ich aber was zu wissen.

Einfach ein Tool schreiben, dass aus der Tabelle mit den Ländercodes, eine abfrage genneriert.

Die dann so aussehen muss, dass er zuerst sucht, mit welchen nummern er übereinstimmt, also

Suchen nach Deutschland, und wenn call aus deutschland, gucken, ob anderer eintrag auch mit dem übereinstimmt, und wenn dem nicht der fall in tabelle 3 eintragen, er kommt aus Deutschland, ansonsten eintragen wo er sonst hekommt.
also so ne abfrage in 2 ebenen.

Wäre das irgendwie ermöglichbar?
Weiß ja net ob das jetzt ne total hirnrissige idee is sich einfach einen so langen quelltext generiern zu lassen, aber vieleicht gehts ja irgendwie

Würd mich tierisch freun.

hab mir überlegt das müsste ja dann so aussehen

Select* from call, destination
where call.anrufernummer=destination.vowahl(aktuelle in der gerade durchlaufenen spalte)%
select* from destination
where destination.vorwahl=destination.vowahl(aktuelle in der gerade durchlaufenen spalte)
[außer die schon gefundene]

(vergleichen von allen gefunden vorwahlen mit der Telefonummer und dann nimmt er die, bei der er, wenn er die Suche macht, nur noch eine Möglichkeit findet)

Also müsste da ne schleife drumm "mach es so lange, bis du deine Spalte aus der Anruferliste einer Spalte aus der destinantionsliste zuordnen kannst"

und dann schreibe alle daten aus der ersten tabelle, zusammen mit dem Land in eine zweite Tabelle.

weui0ßt net ob der denkansatz was bringt, hoffe schon

Gruß Tim

Hallo Tim!

Die Anzahl der Stellen grenzt normalerweise eine Telefonnummer ein. "0043" bedeutet immer "Österreich". Es gibt keine Telefonnummer, die so beginnt und nicht für Österreich steht. So ist es auch mit den Vorwahlen. Diese sind wahrscheinlich so gewählt, dass sie sich nicht überschneiden können. Also ist es so, dass wenn ich mehrere Übereinstimmungen mit dem Suchstring habe, dann ist die Übereinstimmung mit dem **längsten** Suchstring auch die **genaueste**. Anders kann ich mir das bei Telefonnummern im Moment nicht vorstellen.

Code: Alles auswählen

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

import psycopg2


# Verbinden
conn = psycopg2.connect(
    user = "testuser", password = "testuser", database = "testdb"
)
cur = conn.cursor()

# locations-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'locations')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE locations CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE locations (
  searchstring TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()

# calls-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'calls')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE calls CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE calls (
  id SERIAL PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  number TEXT NOT NULL
);

CREATE INDEX ix_calls_number ON calls(number);
"""
cur.execute(sql)
conn.commit()

# locations-Tabelle mit Demodaten füllen
sql = """
INSERT INTO locations (searchstring, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("", u"Unbekannt"),
        ("0049", u"Deutschland"),
        ("0043", u"Österreich"),
        ("00435262", u"Telfs, Pfaffenhofen, Oberhofen im Inntal; Tirol; Österreich"),
        ("0043512", u"Innsbruck; Tirol; Österreich"),
        ("0043699", u"ONE-Mobil; Österreich"),
    )
)
conn.commit()

# calls-Tabelle mit Demodaten füllen
sql = """
INSERT INTO calls (name, number) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        (u"Peter Müller", "0043512552233"), # Innsbruck
        (u"Walter Müller", "0043699123456"), # ONE-Mobil
        (u"Gertraud Holly", "0049888888"), # Deutschland
        (u"Gertraud Wolli", "00435262665544"), # Telfs
        (u"Thomas Wolli", "00435262445566"), # Oberhofen
        (u"Thomas Hauser", "00445262445566"), # keine Ahnung
        (u"Peter Müller", "0043512552233"), # Innsbruck
        (u"Peter Müller", "0043699556688"), # ONE-Mobil
        (u"Gertraud Holly", "0049888888"), # Deutschland
        (u"Gertraud Holly", "0049888888"), # Deutschland
    )
)
conn.commit()

# VIEW "calls_view" (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT viewname FROM pg_views WHERE viewname = 'calls_view')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP VIEW calls_view"
    cur.execute(sql)
    conn.commit()

sql = """
CREATE VIEW calls_view AS
  SELECT
    name,
    number,
    (
      SELECT
        name
      FROM
        locations
      WHERE
        grouped_calls.number like(locations.searchstring || '%')
      ORDER BY
        char_length(locations.searchstring) DESC
      LIMIT 1
    ) as location,
    calls_count
  FROM
    (
      SELECT
        name,
        number,
        count(*) as calls_count
      FROM
        calls
      GROUP BY
        name,
        number
    ) AS grouped_calls
"""
cur.execute(sql)
conn.commit()

# Anzeigen
sql = """
SELECT 
  calls_count, name, number, location
FROM
  calls_view
ORDER BY
  calls_count, name, number
"""
cur.execute(sql)
for row in cur.fetchall():
    print row[0], repr(row[1]), row[2], repr(row[3]) # repr() wegen den Umlauten

cur.close()
conn.close()

mfg
Gerold

Hy, ich probier das gleich morgen mal aus. Meld mich dann noch mal,

Ja es gibt immer nur eine Vorwahl die Stimmt

es soll halt bei 004916 anzeigen T-Mobile
und nicht Deutschland, das war mir nur wichtig.

Weil ich halt den eintrag
0049; Deutschland
und
004916 T-Mobile habe
und da soll dann in der neunen tabelle nur stehen "T-Mobile" und nicht Deutschland, weil ja in Deutschland auch 0049 drinne is, aber bei T-Mobile ist halt Deutschland und T-mobile drinne .. und ich will nur das eindeutige Ergebnis wissen, also wo genau der anruf her kommt, also wo die meisten übereinstimmungen sind, der rest ist mir egal
sol nur T-Mobile stehen und nichts anderes.

Aber schon ma vielen dank

ich meld mich dan noch ma deswegen, guck mir das ma an, wenn meine beschreibung jetzt was gändert hat, verstehste vieleicht jetzt meine "hersauforderung"

Wenn das so in einen SQL-Befehl passt, dann is das ja perfekt, dann kann nich das ja auch in Zope benutzen

Werd mich ma mit dem "view" beschäftigen.
Aber ich habe richtig verstanden, dass man eigentlich gar keine 3te Tabelle braucht um diesen Vergleich auszuführen `? oder habe ich was falsch verstanden?

Viele Grüße Tim

BoOnOdY hat geschrieben:...Jedoch gestaltet sich noch zusätzlich schwierig, dass ich zum beispielt auch die nummer:004916 für T mobile Deutschland vermerkt habe, und dann auch stehen haben möchte Winfried Tmobile Deutschland 4 mal
aber Winfried Deutschland 0 mal
weil er ja immer vom Handy angerufen hat.

Wenn Du die Information "Winfried Deutschland - 0 mal" haben möchtest,
dann brauchst Du eine Telefonnummerntabelle, die Du mit der "calls" Tabelle vergleichen kannst. Das GROUP BY statement in der "calls"
Tabelle von Gerold kann nur Zahlen >= 1 ergeben! Ausserdem wird die
Ausgabe Winfried - Deutschland und Winfried - D1 mit einem GROUP
calls.name auch schwierig ...

BoOnOdY hat geschrieben:...Also ich glaube, das wird etwas schwieriger, denn das hat keine feste logig dahinter, man müsste also sagen:
Vorwahldatensatz mit Telefonnummerdatensatz vergleichen, der mit den meisten Übereinstimmungen wird benutzt

Nochmals, befass Dich mal bitte mit dem Thema "Reguläre Ausdrücke" im
Allgemeinen und in Postgres. Die Logik ist doch in den Telefonnummern
an sich gegeben. Deutsche Festnetznummern sind 00492xxx, 00493xxx, etc.
Anbieter im deutschen Mobilnetzen sind 0049160xxx D1, 0049172xxx - D2, etc.
In Österreich gilt ein ähnliche Logik. Durch entsprechende "Text-
muster" kann man so den Provider herausfiltern (s.Beitrag oben). Es gibt
entsprechende PBXe, z.B. Asterisk, da läuft das ganze Routing so ...

Tabellar

BoOnOdY hat geschrieben:habe richtig verstanden, dass man eigentlich gar keine 3te Tabelle braucht um diesen Vergleich auszuführen?

Hi Tim!

Wenn bei jedem Anruf der Name mitgespeichert wird, dann braucht es die dritte Tabelle nicht. Wie Tabellar bereits aufgezeigt hat, gibt es dann aber die Information, wer nie angerufen hat (0 mal), auch nicht. Ich kenne deine Anwendung nicht, deshalb gehe ich vorerst mal davon aus, dass diese Information auch nicht wichtig ist.

Aber, um auch diese Information zurück zu geben, habe ich hier noch ein Beispiel. Dieses Beispiel geht **NICHT** davon aus, dass jeder Anruf von einem registrierten Anrufer kommt. Dadurch wird zwar auch diese Information aufgezeigt, aber die Datenbank muss dadurch auch mehr arbeiten um diese Information herauszubekommen.

Bei mehreren Tausend Anrufen pro Tag, sollte man sich eine bessere Struktur oder einen Archivierungsplan überlegen. PostgreSQL bietet zum Glück alles was man dazu braucht. (Auch wenn es ein wenig komplizierter wird.)

Code: Alles auswählen

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

import psycopg2


# Verbinden
conn = psycopg2.connect(
    user = "testuser", password = "testuser", database = "testdb"
)
cur = conn.cursor()

# locations-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'locations')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE locations CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE locations (
  searchstring TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()

# locations-Tabelle mit Demodaten füllen
sql = """
INSERT INTO locations (searchstring, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("", u"<UNKNOWN LOCATION>"),
        ("0049", u"Deutschland"),
        ("0043", u"Österreich"),
        ("00435262", u"Telfs, Pfaffenhofen, Oberhofen im Inntal; Tirol; Österreich"),
        ("0043512", u"Innsbruck; Tirol; Österreich"),
        ("0043699", u"ONE-Mobil; Österreich"),
    )
)
conn.commit()

# calls-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'calls')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE calls CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE calls (
  id SERIAL PRIMARY KEY NOT NULL,
  number TEXT NOT NULL
);

CREATE INDEX ix_calls_number ON calls(number);
"""
cur.execute(sql)
conn.commit()

# calls-Tabelle mit Demodaten füllen
sql = """
INSERT INTO calls (number) VALUES (%s)
"""
cur.executemany(
    sql,
    (
        ("0043512552233",), # Innsbruck
        ("0043699123456",), # ONE-Mobil
        ("0049888888",), # Deutschland
        ("00435262665544",), # Telfs
        ("00435262445566",), # Oberhofen
        ("00445262445566",), # keine Ahnung
        ("0043512552233",), # Innsbruck
        ("0043699556688",), # ONE-Mobil
        ("0049888888",), # Deutschland
        ("0049888888",), # Deutschland
        ("004977668855",), # Deutschland
        ("004977668855",), # Deutschland
        ("004977668855",), # Deutschland
        ("004977668855",), # Deutschland
        ("004978688858",), # Deutschland
        ("004978688858",), # Deutschland
        ("004978688858",), # Deutschland
    )
)
conn.commit()

# contacts-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'contacts')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE contacts CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE contacts (
  number TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()

# contacts-Tabelle mit Demodaten füllen
sql = """
INSERT INTO contacts (number, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("0043512552233", u"Peter Müller"), # Innsbruck
        ("0043699123456", u"Walter Müller"), # ONE-Mobil
        ("0049888888", u"Gertraud Holly"), # Deutschland
        ("00435262665544", u"Gertraud Wolli"), # Telfs
        ("00435262445566", u"Thomas Wolli"), # Oberhofen
        ("00445262445566", u"Thomas Hauser"), # keine Ahnung
        ("0043699556688", u"Peter Müller"), # ONE-Mobil
        ("0043699556644", u"Walter Nase"), # ONE-Mobil
        ("0043699556633", u"Sabine Muster"), # ONE-Mobil
    )
)
conn.commit()

# VIEW "calls_view" (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT viewname FROM pg_views WHERE viewname = 'calls_view')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP VIEW calls_view"
    cur.execute(sql)
    conn.commit()

sql = """
CREATE VIEW calls_view AS
  SELECT
    grouped_contacts.name,
    grouped_contacts.number,
    count(calls.*) as calls_count,
    (
      SELECT
        name
      FROM
        locations
      WHERE
        grouped_contacts.number like(locations.searchstring || '%')
      ORDER BY
        char_length(locations.searchstring) DESC
      LIMIT 1
    ) as location
  FROM
    (
      SELECT DISTINCT
        name,
        number
      FROM
        (
          SELECT
            name,
            number
          FROM
            contacts
          UNION ALL
          SELECT
            '<UNKNOWN CONTACT>' as name,
            calls.number
          FROM
            calls
          WHERE
            (calls.number NOT IN (SELECT contacts.number FROM contacts))
        ) united_contacts
    ) grouped_contacts
  LEFT OUTER JOIN
    calls
  ON
    grouped_contacts.number = calls.number
  GROUP BY
    grouped_contacts.name,
    grouped_contacts.number
"""
cur.execute(sql)
conn.commit()


# Anzeigen
sql = """
SELECT 
  calls_count, name, number, location
FROM
  calls_view
ORDER BY
  calls_count DESC, name, number
"""
cur.execute(sql)
for row in cur.fetchall():
    print row[0], repr(row[1]), row[2], repr(row[3]) # repr() wegen den Umlauten

cur.close()
conn.close()

mfg
Gerold

Wenn man davon ausgehen kann, dass jeder Anrufer in die Tabelle ``contacts`` eingetragen wird, dann kann man sich einen Teil der Abfrage sparen und die Anwendung wird schneller.

Code: Alles auswählen

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

import psycopg2


# Verbinden
conn = psycopg2.connect(
    user = "testuser", password = "testuser", database = "testdb"
)
cur = conn.cursor()

# locations-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'locations')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE locations CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE locations (
  searchstring TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()

# locations-Tabelle mit Demodaten füllen
sql = """
INSERT INTO locations (searchstring, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("", u"<UNKNOWN LOCATION>"),
        ("0049", u"Deutschland"),
        ("0043", u"Österreich"),
        ("00435262", u"Telfs, Pfaffenhofen, Oberhofen im Inntal; Tirol; Österreich"),
        ("0043512", u"Innsbruck; Tirol; Österreich"),
        ("0043699", u"ONE-Mobil; Österreich"),
    )
)
conn.commit()

# contacts-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'contacts')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE contacts CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE contacts (
  number TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()

# contacts-Tabelle mit Demodaten füllen
sql = """
INSERT INTO contacts (number, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("0043512552233", u"Peter Müller"), # Innsbruck
        ("0043699123456", u"Walter Müller"), # ONE-Mobil
        ("0049888888", u"Gertraud Holly"), # Deutschland
        ("00435262665544", u"Gertraud Wolli"), # Telfs
        ("00435262445566", u"Thomas Wolli"), # Oberhofen
        ("00445262445566", u"Thomas Hauser"), # keine Ahnung
        ("0043699556688", u"Peter Müller"), # ONE-Mobil
        ("0043699556644", u"Walter Nase"), # ONE-Mobil
        ("0043699556633", u"Sabine Muster"), # ONE-Mobil
    )
)
conn.commit()

# calls-Tabelle (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'calls')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP TABLE calls CASCADE"
    cur.execute(sql)
    conn.commit()
   
sql = """
CREATE TABLE calls (
  id SERIAL PRIMARY KEY NOT NULL,
  number TEXT NOT NULL REFERENCES contacts (number) /* Beziehung zur Tabelle 'contacts' */
);

CREATE INDEX ix_calls_number ON calls(number);
"""
cur.execute(sql)
conn.commit()

# calls-Tabelle mit Demodaten füllen
sql = """
INSERT INTO calls (number) VALUES (%s)
"""
cur.executemany(
    sql,
    (
        ("0043512552233",), # Innsbruck
        ("0043699123456",), # ONE-Mobil
        ("0049888888",), # Deutschland
        ("00435262665544",), # Telfs
        ("00435262445566",), # Oberhofen
        ("00445262445566",), # keine Ahnung
        ("0043512552233",), # Innsbruck
        ("0043699556688",), # ONE-Mobil
        ("0049888888",), # Deutschland
        ("0049888888",), # Deutschland
    )
)
conn.commit()

# VIEW "calls_view" (neu) erstellen
sql = """
SELECT
  EXISTS(SELECT viewname FROM pg_views WHERE viewname = 'calls_view')
"""
cur.execute(sql)
if cur.fetchone()[0]:
    sql = "DROP VIEW calls_view"
    cur.execute(sql)
    conn.commit()

sql = """
CREATE VIEW calls_view AS
  SELECT
    contacts.name,
    contacts.number,
    count(calls.*) as calls_count,
    (
      SELECT
        name
      FROM
        locations
      WHERE
        contacts.number like(locations.searchstring || '%')
      ORDER BY
        char_length(locations.searchstring) DESC
      LIMIT 1
    ) as location
  FROM
    contacts
  LEFT OUTER JOIN
    calls
  ON
    contacts.number = calls.number
  GROUP BY
    contacts.name,
    contacts.number
"""
cur.execute(sql)
conn.commit()


# Anzeigen
sql = """
SELECT 
  calls_count, name, number, location
FROM
  calls_view
ORDER BY
  calls_count DESC, name, number
"""
cur.execute(sql)
for row in cur.fetchall():
    print row[0], repr(row[1]), row[2], repr(row[3]) # repr() wegen den Umlauten

cur.close()
conn.close()

Man achte auf die Beziehung zwischen der ``contacts``-Tabelle und der ``calls``-Tabelle (REFERENCES). Diese stellt sicher, dass kein Anruf eingetragen werden kann, der nicht vorher als Kontakt eingetragen wurde. So spart man sich beim Abfragen aber auch die nicht als Kontakt eingetragenen Anrufe extra zu behandeln. --> schneller!

Wobei ich aber auch bemerken muss, dass man große Geschwindigkeitsunterschiede wahrscheinlich erst ab ein paar Millionen Datensätzen bemerken wird.

mfg
Gerold

Hy,

also ich werd das morgen früh alles genaustens durchugcken, damit ichs kapiere, aber wie müsste der View aussehen, wenn ich die beidne tabellen schon habe, und die felder wie folgt lauten:

calls:
identify;datum;uhrzeit;eingangsport;ausgangsport;eingangsnummer;ausgangsnummer;dauer;fehlercode
destination:
dialcode;name

wie schreib ich dann die funktion die mir sagt, zu welchem Land die Ausgangsnummer gegangen sind?

Ich setzt mich morgen ma drann, und frag dann noch ma,
=)

Und davon, diesen View dann in ne neune Tabelle zu schreiben, davon hältst du nichts? Weil dadurch könnte man Zeit sparen, weil dass dann nur einma durchgerechnet werden muss, der view aber durch ein einfachen group by befehl immer wieder schnell gemacht werden könnte.

Ich rechne jede Nacht meine Anrufe durch, da wärs doch lohnenswert die einfach in ne dritte tabelle einzufügen, oder einfach nur den "identify" also den primary key+das Land in eine neue Tabelle zu schreiben, dann könnte man doch auch mit inner joy Zeit sparen und so?
Oder was meint ihr??

aber shcon ma taudend dank für die schnellen antworten ich guck mir das alels ma an

is echt super

Gruß Tim

BoOnOdY hat geschrieben:Und davon, diesen View dann in ne neune Tabelle zu schreiben, davon hältst du nichts? Weil dadurch könnte man Zeit sparen, weil dass dann nur einma durchgerechnet werden muss, der view aber durch ein einfachen group by befehl immer wieder schnell gemacht werden könnte.

Hi Tim!

Kommt darauf an, mit wieviel GB Daten du rechnest.

Es gibt viele Methoden eine Datenbank schnell zu machen. Eine davon ist, alle Daten in einer Datenbank zu sammeln und in einer anderen Datenbank die "Echtdaten" aufbereitet abzuspeichern.
(=Data Warehouse) Das hat den Vorteil, dass die Aufbereitung der Daten auch auf einen anderen Computer ausgelagert werden kann. So wird die Echtdaten-Datenbank nicht damit belastet.

Natürlich kannst du das so machen. Allerdings rentiert sich das erst wenn die Datenbank wirklich groß (=mehrere GB) wird.

Wenn du nie mit so viel Daten rechnest, dann kann ich mir nicht denken, dass PostgreSQL dir die Daten nicht schnell genug zur Verfügung stellen kann.

Wenn du Views und Prozeduren verwendest, oder wenn du einfach nur nomale SELECT-Abfragen verwendest, dann kannst du dir sicher sein, immer mit den aktuellen Daten zu arbeiten.
Ist das nicht wichtig und wenn du mit vielen Daten rechnest, dann spricht nichts dagegen, dir die aufbereiteten Daten auch irgendwo zwischenzuspeichern. Der Vorteil muss aber gegenüber der Verwendung von Echtzeitdaten wirklich **überwiegen**, sonst bringt es nichts.

mfg
Gerold

Hy,
gut also ich lese eh nur einmal am Tag meine Daten ein, von daher wäre diese Data Warehouse methode für mich optimal. Denn ein "group by" befehl funktionert um einiges schneller als so ein ganz langer vergleichsoperator

Wie würde denn die Data Warehouse Version aussehen?

3 Datenbanken:
Calls
Destinationen
Calls+Länderzeile

Dadurch, dass ich diese neue Tabelle immer gleich mit Daten füttere, wenn ich die anderen Daten einfüge, geht das ja dann bei ner Abfrage viel schneller

Ich importiere die Daten jeden Tag aus ner .CSV in die Tabelle. Kann man da vieleicht gleich beim Importieren den Vergleich machen?, So dass man nur 2 Tabellen braucht weil die tabelle mit Calls gleich beim Importieren mit der Tabelle Destinationen ergänzt wird? Weil für das Importieren hab ich Zeit, das mach ich automatisch Nachts.

Vielen Dank

Tim

PS: Wo find ich denn gute Tutorials, von mir aus auch ein Buch, in dem ich so was alles lerne? Weil ich habe im moment nur das Einstieg in SQL von Galileo Computing. Möchte das aber mir gerne selbst beibringen.

BoOnOdY hat geschrieben:Ich importiere die Daten jeden Tag aus ner .CSV in die Tabelle. Kann man da vieleicht gleich beim Importieren den Vergleich machen?
[...]
Wo find ich denn gute Tutorials, von mir aus auch ein Buch, in dem ich so was alles lerne?

Hallo Tim!

- Da stimme ich dir zu. Der Beste Zeitpunkt, um die Daten aufzubereiten ist der Datenimport.

- Bücher bei Amazon

mfg
Gerold

Bei dem Quelltext hattest du mir ja schon ma stark unter die arme gegriffen, außer der Benutzereingabe stammt ja alles von dir, aber ich hab den Quelltext jetzt verstanden

Code: Alles auswählen

import psycopg2 
import csv 
import datetime 


# Datenbank-Connection und -Cursor erstellen 
conn = psycopg2.connect(user = "Import", password = "import", database = "GBC") 
cur = conn.cursor() 

dateiname= raw_input("bitte geben sie den dateinamen ein: ")
# CSV-Datei öffnen 
f = file(dateiname, "r") 

# CSV-Datei dem Parser übergeben 
csvdata = csv.reader(f, delimiter = ';') 

# Jede, nicht leere, Zeile durchlaufen 
for row in ( row for row in csvdata if row ): 
    # 
    # Daten vorbereiten (in den korrekten Datentyp umwandeln) 
    # 
    data = []
    data.append(row[0]) 
    # Datum 
    d, m, y = ( int(val) for val in row[1].split(".") ) 
    data.append(datetime.date(y, m, d)) 
    # Uhrzeit 
    H, M, S, ms = ( int(val) for val in row[2].split(":") ) 
    data.append(datetime.time(H, M, S)) 
    # Eingangsport und Ausgangsport, Eingangsnummer und Ausgangsnummer 
    data += [int(row[3]), int(row[4]), row[5] or None, row[6] or None] 
    # Dauer 
    try: 
        data.append(int(row[7])) 
    except ValueError: 
        data.append(None) 
    # Fehler 
    data.append(row[8].strip()) 
    
    # 
    # Daten in DB schreiben 
    # 
    sql = """ 
    INSERT INTO call ( 
        callrefnummer,
        datum, 
        uhrzeit, 
        eingangsport, 
        ausgangsport, 
        eingangsnummer, 
        ausgangsnummer, 
        dauer, 
        releasecode 
    ) VALUES ( 
        %s,%s, %s, %s, %s, %s, %s, %s, %s 
    ) 
    
     
      """ 
  
        cur.execute(sql, data) 
    
    conn.commit() 

# CSV-Datei schließen 
f.close() 
 
# Datenbankverbindung trennen 
cur.close() 
conn.close()

Wie macht man das, dass die daten gleich bei diesem Prozess ergänzt werden?, wie stelle ich denn da so eine Abfrage rein, die mir die Daten gleich ergänzt? Hab da ga keine Ahung, wie ich das anstellen soll.
Wie verpacke ich die Abfrage da rein?

Würd mich sehr über ein bisschen Quelltext freuen

Viele Grüße Tim

PS: Dann werd ich mir ma so ein Buch Python und PostgreSQL bestellen. Dann kann ich das endlich richtig lernen

Hy,
ich hab mir mal die Bücher angeguckt, das sind ja alles nur so allgemeine Pyhton Bücher ,,, welche über Python mit PostrgreSQL anbindung hab ich net gefunden ... muss ich mir da 2 Bücher kaufen?
Wär mir irgendwie lieber wenn das irgendwie irgendwo zusammen stehen würde.. wie das in Verbindung funktioniert.

Gruß Tim

Das sind ja im Grunde auch zwei komplett verschiedene und umfangreiche Welten die nur durch die DB-API 2 zusammengehalten werden. Irgend etwas spezielles gibt's da nicht was nicht auch für andere Datenbanken gelten würde.

Also ist es schon sinnvoller ein reines Python Buch und ein reines PostgreSQL Buch zu haben als eine Kombi. Mit der Kombi könnte jemand der Firebird oder MySQL einsetzen will nichts anfangen, die Python Informationen wären aber die gleichen. Umgekehrt soll es auch Leute geben, die PostgreSQL mit Java einsetzen wollen. Es gibt wahrlich komische Leute.

Je nachdem wie viel Du im DB Bereich machen möchtest, wäre zusätzlich ein Buch über SQL und Datenbankentwurf sinnvoll. Da gibt's sicher Kombinationen, also PostgreSQL Bücher die auch den Entwurf einer DB mit abhandeln.

Also ich habe im moment dieses buch
http://www.galileocomputing.de/katalog/ ... itelID-747

Aber da stehen ja nur Grundlagen drinne.. vieleicht sollte ich mir noch mal eins über PostgreSQL kaufen ?? ( Will nur PostgreSQL benutzen)
und eins über Pyhton ...
gibts da irgend nen besonders berühmten oder guten Lehrfaden?

Das deutsche Python-Forum

Vergleichen von 2 Spalten

Vergleichen von 2 Spalten

Re: Vergleichen von 2 Spalten