CoCalc Shared FilesA6 / SQL.py
Views : 9
Description: Data Mining SQL Assignment
1# Necessary sqlite3 import
2import sqlite3
3
4# Create a connection object to represent
5# a database and a print statement confirming
6# it was opened successfully
7conn = sqlite3.connect("Assignment6.db")
8#conn = sqlite3.connect("test127.db")
9print "Opened database successfully\n";
10
11# Create a cursor object to perform
12# SQL commands
13c = conn.cursor()
14
15# Table creations with accompanying print statements
16c.execute("create table T1(var1 text, var2 int primary key, var3 int, var4 int)")
17print "Table 1 created successfully";
18
19c.execute("create table T2(var1 text primary key, foreignVar integer references T1(var2), var3 int, var4 int)")
20print "Table 2 created successfully";
21
22c.execute("create table T3(foreignVar text references T1(var1), var2 int primary key, var3 int, var4 int)")
23print "Table 3 created successfully";
24
25c.execute("create table T4(var1 text, foreignVar integer references T1(var2), var3 int, var4 int)")
26print "Table 4 created successfully";
27
28c.execute("create table T5(var1 text, var2 int primary key, var3 int, var4 int)")
29print "Table 5 created successfully";
30
31word = 'random'
32
33# Nested for loop to add 1000000 rows to each table
34for 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
77sq = 0
78
79# Creation of random variables
80randomWord = 'word'
81int1 = 8
82int2 = 12
83
84# SQL queries
85# -----------
86
87# Insert query
88c.execute("insert into T1 (var1, var3, var4) values(?, ?, ?)", (randomWord, int1, int2))
89print "Successful query";
90sq += 1
91
92# Four select queries
93c.execute("select * from T1")
94print "Successful query";
95sq += 1
96
97"select count * from T1"
98print "Successful query";
99sq += 1
100
101"select * from T2 where foreignVar = 8 and var3 > 5"
102print "Successful query";
103sq += 1
104
105"select var1 from T4 where foreignVar < 20"
106print "Successful query";
107sq += 1
108
109# Five select/join queries
110"select var1 from T1 cross join T4"
111print "Successful query";
112sq += 1
113
114"select var3 from T2 inner join T3 on T2.var1 = T3.foreignVar"
115print "Successful query";
116sq += 1
117
118"select var3 from T2 left outer join T3 on T2.var1 = T3.foreignVar"
119print "Successful query";
120sq += 1
121
122"select var3 from T2 right outer join T3 on T2.var1 = T3.foreignVar"
123print "Successful query";
124sq += 1
125
126"select var3 from T2 full outer join T3 on T2.var1 = T3.foreignVar"
127print "Successful query";
128sq += 1
129
130
131# Deletion and update queries
132"delete from T1 where var2 = '8'"
133print "Successful query";
134sq += 1
135
136"update T2 set var1 = 5 where foreignVar = 'hello'"
137print "Successful query";
138sq += 1
139
140
141# Schema query for datbase synopsis
142".schema T5"
143print "Successful query";
144sq += 1
145
146
147# Explain query to check indicies
148"explain query plan select * from T1"
149print "Successful query";
150sq += 1
151
152
153print "Number of successful queries: ", sq;
154
155conn.close()