-
Notifications
You must be signed in to change notification settings - Fork 0
/
prob.py
87 lines (70 loc) · 4.15 KB
/
prob.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
import sqlite3
import win32com.client
import os
import json
folder = 'D:\\folder\\main1'
con = sqlite3.connect('15.db')
cur=con.cursor()
cur.execute(
'CREATE TABLE IF NOT EXISTS countries(id INTEGER PRIMARY KEY AUTOINCREMENT,'
'CountryName TEXT)')
cur.execute(
'CREATE TABLE IF NOT EXISTS TypeOfDocs(Country_Ind INTEGER,'
'id INTEGER PRIMARY KEY AUTOINCREMENT,'
'DocumentName TEXT,'
'FOREIGN KEY (Country_Ind) REFERENCES countries(id))')
cur.execute(
'CREATE TABLE IF NOT EXISTS samples(id INTEGER PRIMARY KEY AUTOINCREMENT ,'
'Document_Ind INTEGER,'
'SampleName TEXT,'
'FOREIGN KEY (Document_Ind) REFERENCES TypeOfDocs(id))')
CountryName=''
DocumentName=''
SampleName=''
for root,dirs,files in os.walk(folder):
for fil in files:
if "RFID.txt" in fil:
if CountryName != root.split("\\")[3]:
cur.execute("SELECT CAST(CASE WHEN EXISTS ( SELECT * FROM countries WHERE countries.CountryName ='{}') THEN 1 ELSE 0 END AS BIT) AS Result".format(root.split("\\")[3]))
data = cur.fetchone()
if data[0] == 1:
cur.execute("SELECT id FROM countries WHERE countries.CountryName='{}'".format(root.split("\\")[3]))
CountryInfo = cur.fetchone()
Country_id =CountryInfo[0]
else :
cur.execute('INSERT INTO countries(id,CountryName) VALUES(?,?)',(None,root.split("\\")[3]))
cur.execute("SELECT id,CountryName FROM countries WHERE countries.CountryName='{}'".format(root.split("\\")[3]))
CountryInfo = cur.fetchall()
Country_id =CountryInfo[0][0]
CountryName = CountryInfo[0][1]
if DocumentName != root.split("\\")[4]:
cur.execute("SELECT CAST(CASE WHEN EXISTS ( SELECT * FROM TypeOfDocs WHERE TypeOfDocs.DocumentName ='{}' AND Country_Ind = '{}') THEN 1 ELSE 0 END AS BIT) AS Result".format(root.split("\\")[4],Country_id))
data = cur.fetchone()
if data[0] == 1:
cur.execute("SELECT id FROM TypeOfDocs WHERE TypeOfDocs.DocumentName='{}' AND Country_Ind = '{}'".format(root.split("\\")[4],Country_id))
DocumentInfo = cur.fetchone()
Document_id =DocumentInfo[0]
else:
cur.execute('INSERT INTO TypeOfDocs(Country_Ind,id,DocumentName) VALUES(?,?,?)',(Country_id,None,root.split("\\")[4]))
cur.execute("SELECT id,DocumentName FROM TypeOfDocs WHERE TypeOfDocs.DocumentName='{}' AND Country_Ind = '{}'".format(root.split("\\")[4],Country_id))
DocumentInfo = cur.fetchall()
Document_id =DocumentInfo[0][0]
DocumentName=DocumentInfo[0][1]
if SampleName != os.path.basename(root):
cur.execute("SELECT CAST(CASE WHEN EXISTS ( SELECT * FROM samples WHERE samples.SampleName ='{}' AND samples.Document_Ind = '{}') THEN 1 ELSE 0 END AS BIT) AS Result".format(os.path.basename(root),Document_id))
data=cur.fetchone()
if data[0] == 1:
cur.execute("SELECT id FROM samples WHERE samples.SampleName='{}' AND Document_Ind = '{}'".format(os.path.basename(root),Document_id))
SampleInfo = cur.fetchone()
Sample_id=SampleInfo[0]
else:
cur.execute('INSERT INTO samples(id,Document_Ind,SampleName) VALUES(?,?,?)',(None,Document_id,os.path.basename(root)))
cur.execute("SELECT id,SampleName FROM samples WHERE samples.SampleName ='{}' AND Document_Ind = '{}'".format(os.path.basename(root),Document_id))
SampleInfo = cur.fetchall()
Sample_id =SampleInfo[0][0]
SampleName=SampleInfo[0][1]
cur.close()
con.commit()
print("fff")
print("fff")
print("fff")