[go: up one dir, main page]
Scribd
Upload
4K views5 pages

Database Connection 1

The document provides code to create a database table called ITEMS using SQLite. It inserts sample data, performs updates and deletes on records, and runs queries to return data from the table. The code is split into multiple sections that each build on the previous section by adding additional data manipulation functionality to the ITEMS table.

Uploaded by

Senthil Lakshmi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4K views5 pages

Database Connection 1

The document provides code to create a database table called ITEMS using SQLite. It inserts sample data, performs updates and deletes on records, and runs queries to return data from the table. The code is split into multiple sections that each build on the previous section by adding additional data manipulation functionality to the ITEMS table.

Uploaded by

Senthil Lakshmi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 5

===================================================================================

===================================================================================
===================================================================================
============================================================1======================
========

#!/bin/python3

import sys
import os
import sqlite3

# Complete the following function:

def main():
conn = sqlite3.connect('SAMPLE.db')
#create connection cursor
cursor = conn.cursor()
sql2 = '''
CREATE TABLE ITEMS (
item_id INT(6) NOT NULL,
item_name CHAR(20) NOT NULL,
item_description CHAR(20),
item_category CHAR(5),
quantity_in_stock INT(10)
)
'''
cursor.execute(sql2)
conn.close()
#create table ITEMS using the cursor

#commit connection

#close connection

'''To test the code, no input is required'''


if __name__ == "__main__":

=====================================================2=============================
==============================

#!/bin/python3

import sys
import os
import sqlite3

# Complete the function below.

def main():
conn = sqlite3.connect('SAMPLE.db')
cursor = conn.cursor()
cursor.execute("drop table if exists ITEMS")

sql_statement = '''CREATE TABLE ITEMS


(item_id integer not null, item_name varchar(300),
item_description text, item_category text,
quantity_in_stock integer)'''

cursor.execute(sql_statement)

items = [(101, 'Nik D300', 'Nik D300', 'DSLR Camera', 3),


(102, 'Can 1300', 'Can 1300', 'DSLR Camera', 5),
(103, 'gPhone 13S', 'gPhone 13S', 'Mobile', 10),
(104, 'Mic canvas', 'Mic canvas', 'Tab', 5),
(105, 'SnDisk 10T', 'SnDisk 10T', 'Hard Drive', 1)
]

try:
cursor.executemany('Insert into ITEMS values (?,?,?,?,?)', items)
print('hello')
conn.commit()
#Add code to select items here
except:
return 'Unable to perform the transaction.'
cursor.execute('select * from ITEMS')
rowout=[]
for row in cursor.fetchall():
rowout.append(row)
print(row)
return rowout
conn.close()

if __name__ == "__main__":

==============================================================3====================
======================================

#!/bin/python3

import sys
import os
import sqlite3

# Complete the function below.

def main():
conn = sqlite3.connect('SAMPLE.db')
cursor = conn.cursor()

cursor.execute("drop table if exists ITEMS")

sql_statement = '''CREATE TABLE ITEMS


(item_id integer not null, item_name varchar(300),
item_description text, item_category text,
quantity_in_stock integer)'''

cursor.execute(sql_statement)
items = [(101, 'Nik D300', 'Nik D300', 'DSLR Camera', 3),
(102, 'Can 1300', 'Can 1300', 'DSLR Camera', 5),
(103, 'gPhone 13S', 'gPhone 13S', 'Mobile', 10),
(104, 'Mic canvas', 'Mic canvas', 'Tab', 5),
(105, 'SnDisk 10T', 'SnDisk 10T', 'Hard Drive', 1)
]

try:
cursor.executemany('Insert into ITEMS values (?,?,?,?,?)', items)
print('hello')
conn.commit()
#Add code to select items here
except:
return 'Unable to perform the transaction.'
cursor.execute('select * from ITEMS where item_id < 103')
rowout=[]
for row in cursor.fetchall():
rowout.append(row)
print(row)
return rowout
conn.close()

'''For testing the code, no input is required'''


if __name__ == "__main__":

=====================================================4=============================
========================

#!/bin/python3

import sys
import os
import sqlite3

# Complete the function below.

def main():
conn = sqlite3.connect('SAMPLE.db')
cursor = conn.cursor()
cursor.execute("drop table if exists ITEMS")

sql_statement = '''CREATE TABLE ITEMS


(item_id integer not null, item_name varchar(300),
item_description text, item_category text,
quantity_in_stock integer)'''

cursor.execute(sql_statement)

items = [(101, 'Nik D300', 'Nik D300', 'DSLR Camera', 3),


(102, 'Can 1300', 'Can 1300', 'DSLR Camera', 5),
(103, 'gPhone 13S', 'gPhone 13S', 'Mobile', 10),
(104, 'Mic canvas', 'Mic canvas', 'Tab', 5),
(105, 'SnDisk 10T', 'SnDisk 10T', 'Hard Drive', 1)
]
try:
cursor.executemany("Insert into ITEMS values (?,?,?,?,?)", items)

#Add code for updating quantity_in_stock


cursor.execute("UPDATE ITEMS SET quantity_in_stock = 4 where item_id = 103
")
cursor.execute("UPDATE ITEMS SET quantity_in_stock = 2 where item_id = 101
")
cursor.execute("UPDATE ITEMS SET quantity_in_stock = 0 where item_id = 105
")
cursor.execute("select item_id,quantity_in_stock from ITEMS")

except:
'Unable to perform the transaction.'
rowout=[]
for row in cursor.fetchall():
rowout.append(row)
return rowout
conn.close()

'''For testing the code, no input is required'''


if __name__ == "__main__":

===========================================================5=======================
===========================================

#!/bin/python3

import sys
import os
import sqlite3

# Complete the function below.

def main():
conn = sqlite3.connect('SAMPLE.db')
cursor = conn.cursor()

cursor.execute("drop table if exists ITEMS")

sql_statement = '''CREATE TABLE ITEMS


(item_id integer not null, item_name varchar(300),
item_description text, item_category text,
quantity_in_stock integer)'''

cursor.execute(sql_statement)

items = [(101, 'Nik D300', 'Nik D300', 'DSLR Camera', 3),


(102, 'Can 1300', 'Can 1300', 'DSLR Camera', 5),
(103, 'gPhone 13S', 'gPhone 13S', 'Mobile', 10),
(104, 'Mic canvas', 'Mic canvas', 'Tab', 5),
(105, 'SnDisk 10T', 'SnDisk 10T', 'Hard Drive', 1)
]

try:
cursor.executemany("Insert into ITEMS values (?,?,?,?,?)", items)
cursor.executemany("update ITEMS set quantity_in_stock = ? where item_id
= ?",
[(4, 103),
(2, 101),
(0, 105)])
#Add code below to delete items
cursor.execute("DELETE FROM ITEMS WHERE item_id = 105")

cursor.execute("select item_id from ITEMS")


except:
return 'Unable to perform the transaction.'
rowout=[]
for row in cursor.fetchall():
rowout.append(row)
return rowout
conn.close()

'''For testing the code, no input is required'''


if __name__ == "__main__":

===================================================================================
===================================================================================
===================================================================================
===================================================================================
====================================================

You might also like