Consider the following table Supplier and item .Write a python script for (i) to (ii)

Question

Write a Python script to create a table called ITEM with following specification?

SUPPLIER
Suppno	Name	City	Icode	SuppQty
S001	Prasad	Delhi	1008	100
S002	Anu	Bangalore	1010	200
S003	Shahid	Bangalore	1008	175
S004	Akila	Hydrabad	1005	195
S005	Girish	Hydrabad	1003	25
S006	Shylaja	Chennai	1008	180
S007	Lavanya	Chennai	1005	325

1. Display Name, City and Itemname of suppliers who do not reside in Delhi.

2. Increment the SuppQty of Akila by 40

Answer 1

1. import sqlite3

connection = sqlite3.connect(“ABC.db”)

cursor = connection,cursor( ) 

cursor.execute(“SELECT Supplier.Name, Supplier.city,

Item.ItemName FROM Supplier, Item

where Supplier.Icode = Item.Icode AND Supplier.city NOT IN ‘Delhi'”)

co=[i[0] for i in cursor.description]

print(co)

result = cursor. fetchall( )

for r in result:

print(r)

Output:

[‘Name’ , ‘City’ , ‘ItemName’]

[‘Anu’ , ‘Bangalore’ , ‘Mouse’]

[‘Shahid’ , ‘Bangalore’ , ‘Monitor’]

[‘Akila’ , ‘Hydrabad’ , ‘Printer’]

[‘Girish’ , ‘Hydrabad’ , ‘Scanner’]

[‘Shylaja’ , ‘Chennai’ , ‘Monitor’]

[‘Lavanya’ , ‘Mumbai’ , ‘Printer’]

2. Increment the suppQty of Akila by 40 import sqlite3 connection = sqlite3.connect(“ABC.db”) 

cursor = connection.cursor( ) 

cursor.execute(“UPDATE Supplier SET SuppQty = 

SuppQty + 40 where Name = ‘Akila'”) 

cursor.commit( )

result = cursor. fetchall( )

print(result)

connection.close( )

Output:

(S004, ‘Akila’ , ‘Hydrabad’ , 1005, 235)