CoCalc Public FilesA6 / SQL.py
Views : 62
Description: Data Mining SQL Assignment
Compute Environment: Ubuntu 18.04 (Deprecated)
1
# Necessary sqlite3 import
2
import sqlite3
3
4
# Create a connection object to represent
5
# a database and a print statement confirming
6
# it was opened successfully
7
conn = sqlite3.connect("Assignment6.db")
8
#conn = sqlite3.connect("test127.db")
9
print "Opened database successfully\n";
10
11
# Create a cursor object to perform
12
# SQL commands
13
c = conn.cursor()
14
15
# Table creations with accompanying print statements
16
c.execute("create table T1(var1 text, var2 int primary key, var3 int, var4 int)")
17
print "Table 1 created successfully";
18
19
c.execute("create table T2(var1 text primary key, foreignVar integer references T1(var2), var3 int, var4 int)")
20
print "Table 2 created successfully";
21
22
c.execute("create table T3(foreignVar text references T1(var1), var2 int primary key, var3 int, var4 int)")
23
print "Table 3 created successfully";
24
25
c.execute("create table T4(var1 text, foreignVar integer references T1(var2), var3 int, var4 int)")
26
print "Table 4 created successfully";
27
28
c.execute("create table T5(var1 text, var2 int primary key, var3 int, var4 int)")
29
print "Table 5 created successfully";
30
31
word = 'random'
32
33
# Nested for loop to add 1000000 rows to each table
34
for i in range(0,1000):
35
for j in range(0,100):
36
37
# Creation of rows to be implimented
38
hundredKrows1 = [(word, int(j), int(i)), (word, int(j), int(i))]
39
hundredKrows2 = [(int(j), int(i)), (int(j), int(i))]
40
hundredKrows3 = [(int(j), int(i)), (int(j), int(i))]
41
hundredKrows4 = [(word, int(j), int(i)), (word, int(j), int(i))]
42
hundredKrows5 = [(word, int(j), int(i)), (word, int(j), int(i))]
43
44
# Using the 'insert' query to update each table
45
c.executemany("insert into T1 (var1, var3, var4) values (?, ?, ?)", hundredKrows1)
46
c.executemany("insert into T2 (var3, var4) values (?, ?)", hundredKrows2)
47
c.executemany("insert into T3 (var3, var4) values (?, ?)", hundredKrows3)
48
c.executemany("insert into T4 (var1, var3, var4) values (?, ?, ?)", hundredKrows4)
49
c.executemany("insert into T5 (var1, var3, var4) values (?, ?, ?)", hundredKrows5)
50
51
# Updating incrementation values
52
i += 1
53
j += 1
54
55
# Attempt at retrieving and
56
# printing the number of rows
57
# in each table. The printed values
58
# always showed up as
59
# '<sqlite3.Cursor object at 0x7fb41d655dc0>'
60
#r1 = c.execute("select count(*) from T1");
61
#r2 = c.execute("select count(*) from T2");
62
#r3 = c.execute("select count(*) from T3");
63
#r4 = c.execute("select count(*) from T4");
64
#r5 = c.execute("select count(*) from T5");
65
66
#print"\n"
67
#print "Number of rows in table 1: ", r1
68
#print "Number of rows in table 2: ", r2
69
#print "Number of rows in table 3: ", r3
70
#print "Number of rows in table 4: ", r4
71
#print "Number of rows in table 5: ", r5
72
#print"\n"
73
74
# Variable to keep count of the
75
# number of SQL queries successfully
76
# executed
77
sq = 0
78
79
# Creation of random variables
80
randomWord = 'word'
81
int1 = 8
82
int2 = 12
83
84
# SQL queries
85
# -----------
86
87
# Insert query
88
c.execute("insert into T1 (var1, var3, var4) values(?, ?, ?)", (randomWord, int1, int2))
89
print "Successful query";
90
sq += 1
91
92
# Four select queries
93
c.execute("select * from T1")
94
print "Successful query";
95
sq += 1
96
97
"select count * from T1"
98
print "Successful query";
99
sq += 1
100
101
"select * from T2 where foreignVar = 8 and var3 > 5"
102
print "Successful query";
103
sq += 1
104
105
"select var1 from T4 where foreignVar < 20"
106
print "Successful query";
107
sq += 1
108
109
# Five select/join queries
110
"select var1 from T1 cross join T4"
111
print "Successful query";
112
sq += 1
113
114
"select var3 from T2 inner join T3 on T2.var1 = T3.foreignVar"
115
print "Successful query";
116
sq += 1
117
118
"select var3 from T2 left outer join T3 on T2.var1 = T3.foreignVar"
119
print "Successful query";
120
sq += 1
121
122
"select var3 from T2 right outer join T3 on T2.var1 = T3.foreignVar"
123
print "Successful query";
124
sq += 1
125
126
"select var3 from T2 full outer join T3 on T2.var1 = T3.foreignVar"
127
print "Successful query";
128
sq += 1
129
130
131
# Deletion and update queries
132
"delete from T1 where var2 = '8'"
133
print "Successful query";
134
sq += 1
135
136
"update T2 set var1 = 5 where foreignVar = 'hello'"
137
print "Successful query";
138
sq += 1
139
140
141
# Schema query for datbase synopsis
142
".schema T5"
143
print "Successful query";
144
sq += 1
145
146
147
# Explain query to check indicies
148
"explain query plan select * from T1"
149
print "Successful query";
150
sq += 1
151
152
153
print "Number of successful queries: ", sq;
154
155
conn.close()