Das deutsche Python-Forum

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""")

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);

Code: Alles auswählen

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

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""")

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()

Code: Alles auswählen

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

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"

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,

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()