import sqlite3
dbName = 'test27.db'
################# Create the Database File ###################
# Connecting to the database file
conn = sqlite3.connect(dbName)
c = conn.cursor()
#Create the identification table with names and origin
c.execute('''CREATE TABLE apples(appleID INT PRIMARY KEY, Name TEXT,
farmGrown TEXT)''')
#Create the table with key data
c.execute('''CREATE TABLE keyFeature(appleID INT PRIMARY KEY, taste INT,
texture INT, Colour TEXT)''')
#Populate apples table and id in keyFeature table
AppleName = ['Granny Smith', 'Golden Delicious', 'Pink Lady', 'Russet']
appleFarmGrown = ['Farm 1', 'Farm 2', 'Farm 3', 'Farm 4']
id = []
for i in range(len(AppleName)):
id.append(i+1)
c = conn.cursor()
c.execute('''INSERT INTO apples(appleID, Name, farmGrown)
VALUES(?,?,?)''', (id[i], AppleName[i], appleFarmGrown[i]))
c.execute('''INSERT INTO keyFeature(appleID)
VALUES(?)''', (id[i],))
#Current Apple to populate row in keyFeature
appleCurrent = (('Pink Lady','Farm 3'))
tasteCurrent = 4
textureCurrent = 5
colourCurrent = 'red'
#Find ID and write into the database
c.execute("SELECT appleID FROM apples")
appleIDDB = c.fetchall()
c.execute("SELECT name FROM apples")
nameDB = c.fetchall()
c.execute("SELECT farmGrown FROM apples")
farmGrownDB = c.fetchall()
conn.commit()
conn.close()
# I assume that if I close the connection the cursor whould be positioned at the
# first row again but this doesn't appear to be the case
conn = sqlite3.connect(dbName)
for i in range(len(appleIDDB)):
c = conn.cursor()
if ((nameDB[i][0] == appleCurrent[0]) and (farmGrownDB[i][0] == appleCurrent[1])):
idCurrent = appleIDDB[i][0]
print("This apple matches the apple stored with id number " +str(idCurrent))
# This writes into the fifth row of the table
c.execute('''INSERT INTO keyFeature(taste, texture, Colour)
VALUES(?,?,?)''', (tasteCurrent, textureCurrent, colourCurrent))
conn.commit()
conn.close()
