· 8 years ago · Feb 25, 2018, 11:42 PM
1import sqlite3
2import sys
3import re
4
5insert = """
6INSERT INTO tools (tool, pocket, X, Y, Z, A, B, C, U, V, W, D, frontangle, backangle, orientation, description)
7VALUES (:Tool, :Pocket, :X, :Y, :Z, :A, :B, :C, :U, :V, :W, :Diameter, :Frontangle, :Backangle, :Orientation, ":Comment");
8"""
9
10schema = """
11BEGIN TRANSACTION;
12DROP TABLE IF EXISTS tools;
13CREATE TABLE tools (
14 tool INTEGER PRIMARY KEY UNIQUE NOT NULL,
15 pocket INTEGER NOT NULL,
16 X REAL DEFAULT 0.0,
17 Y REAL DEFAULT 0.0,
18 Z REAL DEFAULT 0.0,
19 A REAL DEFAULT 0.0,
20 B REAL DEFAULT 0.0,
21 C REAL DEFAULT 0.0,
22 U REAL DEFAULT 0.0,
23 V REAL DEFAULT 0.0,
24 W REAL DEFAULT 0.0,
25 D REAL DEFAULT 0.0,
26 frontangle REAL DEFAULT NULL,
27 backangle REAL DEFAULT NULL,
28 orientation INTEGER DEFAULT NULL,
29 description TEXT DEFAULT ""
30);
31COMMIT;
32"""
33
34def dict_into_query(q, d):
35 "replaces each occurence of :key in the input file with the textual representation of the value of d[key]"
36
37 f = re.search(':([\w]+)', q)
38 while f:
39 if f.group(1) in d:
40 if d[f.group(1)]:
41 q = q[:f.start()] + str(d[f.group(1)]) + q[f.end():]
42 else:
43 q = q[:f.start()] + "NULL" + q[f.end():]
44 else: #Tags that are in the query, but not the dict
45 q = q[:f.start()] + "NULL" + q[f.end():]
46 f = re.search(':([\w]+)', q)
47 return q
48
49if len(sys.argv) < 3:
50 print("usage: tool.tbl new.sqlite")
51 sys.exit(0)
52
53toolfile = open(sys.argv[1])
54toolinfo = toolfile.readlines()
55toolfile.close()
56
57db = sqlite3.connect(sys.argv[2])
58db.executescript(schema)
59
60for t in toolinfo:
61 tool = {'Tool':re.search('[Tt]([0-9+-.]+) ', t),
62 'Pocket':re.search('[Pp]([0-9+-.]+)', t),
63 'X':re.search('[Xx]([0-9+-.]+)', t),
64 'Y':re.search('[Yy]([0-9+-.]+)', t),
65 'Z':re.search('[Zz]([0-9+-.]+)', t),
66 'A':re.search('[Aa]([0-9+-.]+)', t),
67 'B':re.search('[Bb]([0-9+-.]+)', t),
68 'C':re.search('[Cc]([0-9+-.]+)', t),
69 'U':re.search('[Uu]([0-9+-.]+)', t),
70 'V':re.search('[Vv]([0-9+-.]+)', t),
71 'W':re.search('[Ww]([0-9+-.]+)', t),
72 'Diameter':re.search('[Dd]([0-9+-.]+)', t),
73 'Frontangle':re.search('[Ii]([0-9+-.]+)', t),
74 'Backangle':re.search('[Jj]([0-9+-.]+)', t),
75 'Orientation':re.search('[Qq]([0-9+-.]+)', t),
76 'Comment':re.search(';(.*)', t)}
77 for m in tool:
78 if tool[m]:
79 tool[m] = tool[m].group(1)
80 db.executescript(dict_into_query(insert, tool))