Mac-Adresse als Hexadezimalzahl in MySQLdb speichern

[Dittrich]

Hallo zusammen,
ich erzeuge mir eine Tabelle in der ich mehrere Daten speichere. Die Daten bekomme ich per Select aus 2 Temporary Tables.

Nun möchte ich zusätzlich die Mac Adresse mit einer anderen Tabelle vergleichen und die zugehörige ID einfügen.
Mein Versuch war zunächst der auskommentierte Code.

Code: Alles auswählen

connection = MySQLdb.connect("*******", "*******", "","*******")
cursor = connection.cursor()
cursor.execute("""CREATE TEMPORARY TABLE get_pppoe_all (id INTEGER, einwahlid TEXT, mac VARCHAR(18))""")
cursor.execute("""CREATE TEMPORARY TABLE get_pppoe_detail (id INTEGER, einwahlid TEXT, vlan INTEGER, ip TEXT)""")

get_pppoe_all = open("C:\\Dokumente und Einstellungen\\*******\\Desktop\\get_pppoe_all.txt").readlines()
get_pppoe_detail = open("C:\\Dokumente und Einstellungen\\*******\\Desktop\\get_pppoe_detail.txt").readlines()

for i in xrange(len(get_pppoe_all)):
    cursor.execute("INSERT INTO get_pppoe_all VALUES(%s, %s, %s)", get_pppoe_all[i].strip('\n').split(';'))
for i in xrange(len(get_pppoe_detail)):
    cursor.execute("INSERT INTO get_pppoe_detail VALUES(%s, %s, %s, %s)", get_pppoe_detail[i].strip('\n').split(';')) 
#cursor.execute("""CREATE TABLE vergleich AS SELECT a.id, a.einwahlid, a.mac, b.vlan, b.ip, c.mac_id FROM get_pppoe_all AS a JOIN get_pppoe_detail AS b ON a.id = b.id AND a.einwahlid = b.einwahlid JOIN mac AS c ON c.mac = a.mac""")
cursor.execute("""CREATE TABLE vergleich AS SELECT a.id, a.einwahlid, a.mac, b.vlan, b.ip FROM get_pppoe_all AS a JOIN get_pppoe_detail AS b ON a.id = b.id AND a.einwahlid = b.einwahlid""")

cursor.execute("""DROP TEMPORARY TABLE get_pppoe_all""")
cursor.execute("""DROP TEMPORARY TABLE get_pppoe_detail""")

Soweit sollte das auch funktionieren aber die Laufzeiten sind unterirdisch(4min bei <17000 Einträge)... Die Mac-Adresse ist in der von mir erzeugten Tabelle und in der vorhandenen Tabelle varchar(18).

Nun habe ich mir überlegt die Mac Adresse anders zu speichern z.B als Hexadezimalzahl oder direkt als Integer (vllt. besser?) aber wie kann ich das bewerkstelligen?

Mfg

[Sirius3]

@Dittrich: Deine temporären Tabellen haben keinen Index, was den JOIN entsprechend langsam machen dürfte.
Öffne Dateien mit "with" damit sie auch wieder ordungsgemäß geschlossen werden. In Python iteriert man direkt über die Elemente einer Liste und nicht über ihren Index. Warum erledigst Du das Matchen der beiden Tabellen mit SQL. Eine Python-Lösung dürfte auf jeden Fall erheblich schneller sein.

[BlackJack]

@Dittrich: Die beiden temporären Tabellen haben keinen Primärschlüssel. Vielleicht liegt es ja schon da dran. Ansonsten solltest Du Dich bei MySQL in die EXPLAIN-Anweisung einlesen und wie man damit heraus bekommt, was die Datenbank mit einer gegebene Anfrage anstellt.

[Dittrich]

@Sirius hättest du mir ein Bsp.?
Das Matchen der beiden Tabellen dauert nicht so lange, erst wenn ich die Tabelle mac aus der MySQL Db mit einbeziehe. Diese ist bereits vorhanden und von dort möchte ich über die mac adresse die mac id herausfinden. Hier habe ich dann extreme Laufzeiten. Auch beim testen im MySQL Editor -> sprich wenn ich dort die Id der erzeugten Tabelle vergleich hinzufügen möchte.
Zum testen habe ich auch einen Index manuell in der Datenbank angelegt aber auch hier keine Verbesserung.

[BlackJack]

@Dittrich: Das kann trotzdem an den fehlenden Primärschlüsseln und damit Indexen auf den beiden temporären Tabellen liegen oder zumindest dadurch verstärkt werden. Durch das matchen mit jeweils linearer Laufzeit multiplizieren sich die Durchläufe ja. Wie sieht denn das mit den Anzahlen der Datensätze in den jeweiligen Tabellen aus?

Auf was hast Du denn einen Index angelegt?

Wie gesagt EXPLAIN hilft normalerweise um zu schauen was die DB-Engine mit der Anfrage macht und wo dann potentiell ungünstige Sachen passieren die man mit einem Index beschleunigen kann.

[Dittrich]

@JackBlack Den Index habe ich auf die mac gelegt.
Das Python Skript ist bereits durchgelaufen und hat die Tabelle vergleich erzeugt. Diese befindet sich nun in der Datenbank. Zum testen wollte ich nun die Tabelle mac im SQL Editor der Tabelle vergleich hinzufügen (Matchen über die Mac Adresse welche in beiden Dateien enthalten sind). Allerdings dauert das ewig. Deshalb wollte ich die Mac-Adresse in Bigint oder so Casten (Im Python Programm)(imo varchar(18)) um die Laufzeit zu verbessern. Hier komme ich nicht weiter.
Deshalb macht der EXPLAIN befehl meines Selectkommandos wenig Sinn würde ich behaupten...

Mfg

[BlackJack]

@Dittrich: EXPLAIN würde zumindest verraten warum das so langsam ist. Wie sind denn nun die Tabellengrössen und was bedeutet „ewig”? Ein Index auf die Mac-Adresse sollte das eigentlich schneller machen. Ich glaube nicht das sich durch INT statt VARCHAR so grundlegend etwas ändert, ausser das beim Eintragen und darstellen der einer Abfrage mehr Arbeit anfällt.

[Dittrich]

@BlackJack 17000 Einträge in mac und knapp 4000 in vergleich
Ewig heißt nach 10 min oder so breche ich ab...
Folgende Anweisung habe ich getestet.... und dann verschiedene Varianten mit Cast oder HEX

Code: Alles auswählen

SELECT *
FROM vergleich AS v
JOIN mac AS m ON CAST(CONV(REPLACE(m.mac,':',''),16,10)AS UNSIGNED)=CAST(CONV(REPLACE(m.mac,':',''),16,10)AS UNSIGNED);

Hatte ich nun als letztes versucht. Hier bekomme ich einen OUT OF MEMORY Error....
Wenn ich Explain davor setzte steht unter Extra Using where, Using join buffer.
Wie könnte eine Lösung meines Problems in Python aussehen? Hat da einer ein Minimalbsp oder Tutorial?

Mfg

[/me]

Code: Alles auswählen

SELECT *
FROM vergleich AS v
JOIN mac AS m ON CAST(CONV(REPLACE(m.mac,':',''),16,10)AS UNSIGNED)=CAST(CONV(REPLACE(m.mac,':',''),16,10)AS UNSIGNED);

Hatte ich nun als letztes versucht. Hier bekomme ich einen OUT OF MEMORY Error....

Mit Convert und Replace wird ein Index vermutlich nicht mehr verwendet werden können.

Du hast bei deinem angegebenen Code übrigens das Problem, dass du auf beiden Seiten des Vergleichs m.mac verwendest.

[Dittrich]

Du hast bei deinem angegebenen Code übrigens das Problem, dass du auf beiden Seiten des Vergleichs m.mac verwendest.

Oh das war nur ein Tippfehler einmal ist´s v.mac.

[Dittrich]

Mit Convert und Replace wird ein Index vermutlich nicht mehr verwendet werden können.

Danke! Hier meine Lösung mit USE INDEX läuft nun nur noch ein paar Millisekunden.

Code: Alles auswählen

SELECT * 
FROM vergleich AS v USE INDEX (mac) 
JOIN mac AS m USE INDEX (mac) ON m.mac=v.mac

Mfg

[BlackJack]

*Das* muss man MySQL *sagen*, mit Nicht-Standard-SQL! Krass…

[Dittrich]

*Das* muss man MySQL *sagen*, mit Nicht-Standard-SQL! Krass…

Ich bin mir da nicht sicher... in der iX war zufällig ein Artikel zu MySQL dort steht:
"Es gibt drei Möglichkeiten, MySQL den Weg im Umgang mit Indizes zu weisen. Dazu ergänzt man den Tabellennamen im Select- oder Join-Teil um eine Klausel. USE INDEX legt die zu nutzenden Indizes fest- gegebenenfalls auch keinen davon. ..."

Naja bei mir funktionierts =) Das komplette Programm läuft nun <8s.

Code: Alles auswählen

connection = MySQLdb.connect("***********", "***********", "","***********")
cursor = connection.cursor()
cursor.execute("""DELETE FROM get_pppoe_all""") 
cursor.execute("""DELETE FROM get_pppoe_detail""")
cursor.execute("""DELETE FROM vergleich""")  
#cursor.execute("""CREATE TABLE get_pppoe_all (id INTEGER, einwahlid VARCHAR(20), mac VARCHAR(18))""")
#cursor.execute("""CREATE TABLE get_pppoe_detail (id INTEGER, einwahlid VARCHAR(20), vlan INTEGER, ipVARCHAR(18))""")

get_pppoe_all = open("C:\\Dokumente und Einstellungen\\***********\\Desktop\\get_pppoe_all.txt").readlines()
get_pppoe_detail = open("C:\\Dokumente und Einstellungen\\***********\\Desktop\\get_pppoe_detail.txt").readlines()

for i in xrange(len(get_pppoe_all)):
    cursor.execute("INSERT INTO get_pppoe_all VALUES(%s, %s, %s)", get_pppoe_all[i].strip('\n').split(';'))
for i in xrange(len(get_pppoe_detail)):
    cursor.execute("INSERT INTO get_pppoe_detail VALUES(%s, %s, %s, %s)", get_pppoe_detail[i].strip('\n').split(';')) 

cursor.execute("""INSERT INTO vergleich 
SELECT DISTINCT a.id, a.einwahlid, a.mac, b.vlan, b.ip, m.mac_id 
FROM get_pppoe_all AS a USE INDEX (mac, id) 
JOIN get_pppoe_detail AS b USE INDEX (id) ON a.id = b.id 
AND a.einwahlid = b.einwahlid 
JOIN mac AS m USE INDEX (mac) ON m.mac=a.mac""")

Danke @ all

Mfg

[BlackJack]

@Dittrich: Anmerkungen zum Quelltext: Das einlesen der Dateien ist „unpythonisch”. Die Dateien werden nicht wieder geschlossen. Es wird alles komplett in den Speicher gelesen, obwohl nur eine Zeile pro Schleifendurchlauf benötigt wird. Die Schleife ist umständlich und unnötig über einen Index, statt einfach direkt über die Elemente zu iterieren. Der Code für beide Dateien ist nahezu identisch, wiederholt sich also — dafür kann man eine Funktion schreiben oder zumindest eine Schleife.

Bei den SQL-Anweisungen zum erzeugen der Tabellen fehlt IMHO ``PRIMARY KEY`` bei den IDs. Und den Index auf `mac` könnte man dort sinnvollerweise auch schon anlegen.

Ich sehe nirgends ein `commit()`. Vielleicht ist das auch ein Grund warum die Indexe nicht automatisch ordentlich verwendet werden. Eventuell werden die Statistiken nach denen das entschieden wird, erst aktualisiert wenn Änderungen auch tatsächlich permanent gemacht wurden.

Ungetestet:

Code: Alles auswählen

#!/usr/bin/env python
import csv
import os
from itertools import chain
import MySQLdb


def import_data(connection, filename, table_name, delimiter=';', placeholder='%s'):
    with open(filename) as data_file:
        reader = csv.reader(data_file, delimiter=delimiter)
        try:
            first_row = next(reader)
        except StopIteration:
            return
        sql = 'INSERT INTO {0} VALUES({1})'.format(
            table_name, ', '.join([placeholder] * len(first_row))
        )
        cursor = connection.cursor()
        cursor.executemany(sql, chain([first_row], reader))
        connection.commit()


def main():
    connection = MySQLdb.connect('**********', '**********', '', '**********')
    base_path = 'C:/Dokumente und Einstellungen/***********/Desktop'

    for table_name in ['get_pppoe_all', 'get_pppoe_detail']:
        import_data(
            connection, os.path.join(base_path, table_name + '.txt'), table_name
        )

    cursor = connection.cursor()
    cursor.execute(
        """INSERT INTO vergleich 
        SELECT DISTINCT a.id, a.einwahlid, a.mac, b.vlan, b.ip, m.mac_id 
        FROM get_pppoe_all AS a USE INDEX (mac, id) 
        JOIN get_pppoe_detail AS b USE INDEX (id) ON a.id = b.id 
        AND a.einwahlid = b.einwahlid 
        JOIN mac AS m USE INDEX (mac) ON m.mac=a.mac"""
    )
    connection.commit()

                        
if __name__ == '__main__':
    main()

[Dittrich]

@Back Jack vielen Dank für die Infos

Anmerkungen zum Quelltext: Das einlesen der Dateien ist „unpythonisch”.

Diese Dateien sind nur zum testen. Später erhalte ich die Daten direkt von einem Server.
Dort erhalte ich dann einen String in dem alle Daten enthalten sind.

[Dittrich]

Wenn ich nun Join durch Left Join ersetze

Code: Alles auswählen

 LEFT JOIN mac AS m USE INDEX (mac) ON m.mac=a.mac

explodiert die Laufzeit wieder... hab nach 5 min abgebrochen.
Laut Explain erhalte ich bei Join:

Code: Alles auswählen

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,a,ref,"id,mac",mac,57,func,2,"Using where"
1,SIMPLE,b,ref,id,id,68,"lokalisierung.a.id,lokalisierung.a.einwahlid",2,"Using where"
1,SIMPLE,m,ALL,NULL,NULL,NULL,NULL,17209,"Using temporary"

und bei Left Join:

Code: Alles auswählen

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,a,ALL,id,NULL,NULL,NULL,7702,"Using temporary"
1,SIMPLE,b,ref,id,id,68,"lokalisierung.a.id,lokalisierung.a.einwahlid",2,"Using where"
1,SIMPLE,m,ALL,NULL,NULL,NULL,NULL,17209,

[Sirius3]

Ich würde dennoch das meiste in reinem Python erledigen und nur das füllen der Zieltabelle SQL erledigen lassen:

Code: Alles auswählen

#!/usr/bin/env python
import csv
import os
from itertools import chain
import MySQLdb


def read_into_dict(filename, key_cols=2, delimiter=';'):
    with open(filename) as data_file:
        reader = csv.reader(data_file, delimiter=delimiter)
        return dict((tuple(row[:key_cols]),row[key_cols:]) for row in reader)


def join_dicts(*dicts):
    all_keys = set(itertools.chain(*dicts))
    for key in all_keys:
        yield (key,) + tuple(d[key] for d in dicts)


def main():
    connection = MySQLdb.connect('**********', '**********', '', '**********')
    base_path = 'C:/Dokumente und Einstellungen/***********/Desktop'

    get_pppoe_all = read_into_dict(os.path.join(base_path, 'get_pppoe_all.txt'))
    get_pppoe_detail = read_into_dict(os.path.join(base_path, 'get_pppoe_detail.txt'))
    
    cursor = connection.cursor()
    cursor.executemany(
        """INSERT INTO vergleich
        SELECT %s, %s, %s as a_mac, %s, %s, m.mac_id
        FROM mac AS m WHERE m.mac=a_mac""",
        (a_id, a_einwahlid, a_mac, d_vlan, d_id
         for (a_id, a_einwahlid), (a_mac,), (d_vlan, d_id) in join_dicts(get_pppoe_all, get_pppoe_detail))
    )
    connection.commit()
                       
if __name__ == '__main__':
    main()

Natürlich muß auf mac in der Tabelle mac ein Index existieren, obwohl hier die Zeit nur linear mit der Anzahl der Einträge in mac steigen dürfte.