-
Notifications
You must be signed in to change notification settings - Fork 0
/
speedyimport.py
executable file
·110 lines (88 loc) · 4.41 KB
/
speedyimport.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
#!/usr/bin/python3
#Copyright (C) 2013 Federico Tabbò
#This program is free software; you can redistribute it and/or
#modify it under the terms of the GNU General Public License
#as published by the Free Software Foundation; either version 2
#of the License, or (at your option) any later version.
#This program is distributed in the hope that it will be useful,
#but WITHOUT ANY WARRANTY; without even the implied warranty of
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#GNU General Public License for more details.
#You should have received a copy of the GNU General Public License
#along with this program; if not, write to the Free Software
#Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
import csv
import glob
import sqlite3
axlsql = open("import.xml","w")
def saninitse(input):
input=input.replace("&","&").replace("'","'").replace("<","<").replace(">",">").replace('"','"')
return input
def personaladdressbook(enduser, firstname, lastname, nickname, email):
enduser =saninitse(enduser)
firstname =saninitse(firstname)
lastname =saninitse(lastname)
nickname =saninitse(nickname)
email =saninitse(email)
query='INSERT into personaladdressbook (fkenduser, firstname, lastname, nickname, email) VALUES ((select pkid from enduser where userid = "'+enduser+'"),"'+firstname+'","'+lastname+'","'+nickname+'","'+email+'")'
row="<sql update='"+query+"'/>\n"
axlsql.write(row)
def personalphonebook(enduser, nickname, tkpersonalphonenumber, phonenumber):
enduser =saninitse(enduser)
nickname =saninitse(nickname)
query='INSERT into personalphonebook (fkenduser, fkpersonaladdressbook, tkpersonalphonenumber, phonenumber) VALUES ((select pkid from enduser where userid = "'+enduser+'"),(SELECT pkid FROM personaladdressbook WHERE nickname = "'+nickname+'" AND fkenduser = (select pkid from enduser where userid = "'+enduser+'")),"'+tkpersonalphonenumber+'","'+phonenumber+'")'
row="<sql update='"+query+"'/>\n"
axlsql.write(row)
def speeddial(device, speeddialindex, speeddialnumber, label, labelascii):
label =saninitse(label)
labelascii=saninitse(labelascii)
query='INSERT INTO speeddial (fkdevice, speeddialindex, speeddialnumber, label, labelascii) VALUES ((SELECT pkid FROM device WHERE name = "'+device+'"),"'+speeddialindex+'","'+speeddialnumber+'","'+label+'","'+labelascii+'")'
row="<sql update='"+query+"'/>\n"
axlsql.write(row)
axlsql.write("""<?xml version="1.0" encoding="UTF-8"?>
<!--DTD generated by XMLSPY v5 rel. 4 U (http://www.xmlspy.com)-->
<!DOCTYPE data [
<!ELEMENT data (sql+)>
<!ELEMENT sql EMPTY>
<!ATTLIST sql
query CDATA #IMPLIED
update CDATA #IMPLIED
>
]>
<data>""") #####Cisco Black Magic Header
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('CREATE TABLE contact (firstname TEXT, lastname TEXT, nickname TEXT, phonenumber TEXT, tkpersonalphonenumber TEXT, email TEXT, userid TEXT)')
conn.commit()
contacts = glob.glob("./output/contacts/in/*.csv")
for file in contacts: #Import speeddials into database
userid=file.split("/")[-1].split(".csv")[0] #get userid, strip path and csv from filename
contact=open(file,"r")
dialect = csv.Sniffer().sniff(contact.read(1024))
contact.seek(0)
reader= csv.reader(contact, dialect)
for i in reader:
i.append(userid)
c.execute('INSERT INTO contact values (?,?,?,?,?,?,?)',i)
conn.commit()
c.execute('SELECT userid FROM contact GROUP BY userid')
users = c.fetchall()
for i in users:
c.execute('SELECT firstname, lastname, nickname, email FROM contact WHERE userid = (?) GROUP BY nickname',(i[0],))
oneuserscontacts=c.fetchall()
for entry in oneuserscontacts:
personaladdressbook(i[0], entry[0], entry[1], entry[2], entry[3])
c.execute('SELECT tkpersonalphonenumber, phonenumber from contact WHERE userid = (?) AND nickname = (?)',(i[0],entry[1]))
onecontactsnumbers = c.fetchall()
for number in onecontactsnumbers:
personalphonebook(i[0], entry[2], number[0], number[1])
speeddials = glob.glob("./output/speeddial/in/*.csv")
for file in speeddials: #Import speeddials into database
devicename=file.split("/")[-1].split(".csv")[0] #get devicename, strip path and csv from filename
device=open(file,"r")
dialect = csv.Sniffer().sniff(device.read(1024))
device.seek(0)
reader= csv.reader(device, dialect)
for i in reader:
speeddial(devicename, i[0], i[1], i[2], i[3])
axlsql.write("</data>") ####Cisco Black Magic Footer