SQL.py

# Necessary sqlite3 import
import sqlite3

# Create a connection object to represent
# a database and a print statement confirming 
# it was opened successfully
conn = sqlite3.connect("Assignment6.db")
#conn = sqlite3.connect("test127.db")
print "Opened database successfully\n";

# Create a cursor object to perform
# SQL commands
c = conn.cursor()
 
# Table creations with accompanying print statements
c.execute("create table T1(var1 text, var2 int primary key, var3 int, var4 int)")
print "Table 1 created successfully";

c.execute("create table T2(var1 text primary key, foreignVar integer references T1(var2), var3 int, var4 int)")
print "Table 2 created successfully";

c.execute("create table T3(foreignVar text references T1(var1), var2 int primary key, var3 int, var4 int)")
print "Table 3 created successfully";

c.execute("create table T4(var1 text, foreignVar integer references T1(var2), var3 int, var4 int)")
print "Table 4 created successfully";

c.execute("create table T5(var1 text, var2 int primary key, var3 int, var4 int)")
print "Table 5 created successfully";

word = 'random'

# Nested for loop to add 1000000 rows to each table
for i in range(0,1000):
    for j in range(0,100):
        
        # Creation of rows to be implimented
        hundredKrows1 = [(word, int(j), int(i)), (word, int(j), int(i))]
        hundredKrows2 = [(int(j), int(i)), (int(j), int(i))]
        hundredKrows3 = [(int(j), int(i)), (int(j), int(i))]
        hundredKrows4 = [(word, int(j), int(i)), (word, int(j), int(i))]
        hundredKrows5 = [(word, int(j), int(i)), (word, int(j), int(i))]

        # Using the 'insert' query to update each table
        c.executemany("insert into T1 (var1, var3, var4) values (?, ?, ?)", hundredKrows1)
        c.executemany("insert into T2 (var3, var4) values (?, ?)", hundredKrows2)
        c.executemany("insert into T3 (var3, var4) values (?, ?)", hundredKrows3)
        c.executemany("insert into T4 (var1, var3, var4) values (?, ?, ?)", hundredKrows4)
        c.executemany("insert into T5 (var1, var3, var4) values (?, ?, ?)", hundredKrows5)

        # Updating incrementation values
        i += 1
        j += 1

# Attempt at retrieving and
# printing the number of rows 
# in each table. The printed values
# always showed up as 
# '<sqlite3.Cursor object at 0x7fb41d655dc0>'
#r1 = c.execute("select count(*) from T1");
#r2 = c.execute("select count(*) from T2");
#r3 = c.execute("select count(*) from T3");
#r4 = c.execute("select count(*) from T4");
#r5 = c.execute("select count(*) from T5");

#print"\n"
#print "Number of rows in table 1: ", r1
#print "Number of rows in table 2: ", r2
#print "Number of rows in table 3: ", r3
#print "Number of rows in table 4: ", r4
#print "Number of rows in table 5: ", r5
#print"\n"

# Variable to keep count of the
# number of SQL queries successfully
# executed
sq = 0

# Creation of random variables
randomWord = 'word'
int1 = 8
int2 = 12

# SQL queries
# -----------

# Insert query
c.execute("insert into T1 (var1, var3, var4) values(?, ?, ?)", (randomWord, int1, int2))
print "Successful query";
sq += 1

# Four select queries
c.execute("select * from T1")
print "Successful query";
sq += 1

"select count * from T1"
print "Successful query";
sq += 1

"select * from T2 where foreignVar = 8 and var3 > 5"
print "Successful query";
sq += 1

"select var1 from T4 where foreignVar < 20"
print "Successful query";
sq += 1

# Five select/join queries
"select var1 from T1 cross join T4"
print "Successful query";
sq += 1

"select var3 from T2 inner join T3 on T2.var1 = T3.foreignVar"
print "Successful query";
sq += 1

"select var3 from T2 left outer join T3 on T2.var1 = T3.foreignVar"
print "Successful query";
sq += 1

"select var3 from T2 right outer join T3 on T2.var1 = T3.foreignVar"
print "Successful query";
sq += 1

"select var3 from T2 full outer join T3 on T2.var1 = T3.foreignVar"
print "Successful query";
sq += 1


# Deletion and update queries
"delete from T1 where var2 = '8'"
print "Successful query";
sq += 1

"update T2 set var1 = 5 where foreignVar = 'hello'"
print "Successful query";
sq += 1


# Schema query for datbase synopsis
".schema T5"
print "Successful query";
sq += 1


# Explain query to check indicies
"explain query plan select * from T1"
print "Successful query";
sq += 1


print "Number of successful queries: ", sq;

conn.close()