python Create a SQLite3 Database THE ASSIGNMENT Your assignment is to create two

Question

python

Create a SQLite3 Database

THE ASSIGNMENT Your assignment is to create two programs . Program one creates the database and table . Program two-uses a menu to allow the user to o print all the records in the table o add a new record o delete a record o exit the progranm Here is the data for the table. Feel free to add more, but at the minimum these records should be there You determine the name of the database and of the table empID SL21 SG37 SG14 SA09 SL52 SA59 fName John Ann David Maria Ro Fred IName White Beech Ford Fernand Assistant Johnson Lee osition Manager Supervisor F Assistant hourlyRate sala 20.19 17.79 14.42 13.82 16.94 11.54 sex 42000 37000 30000 28750 35240 24000 Production M Production M

Explanation / Answer

For many programming languages database connection is achieved using APIs.

API is Application Programmable interface. (Nothing but a piece of code/software acts as interface betwee program and database)

For Python SQLite, we use sqlite3 module API. (You do not need to install API it comes with Python)

Note -(Take care of Indentation)

Program One:  

File - one.py

# imports sqlite3 API from python

import sqlite3

# The following line tries to connect to the DB specified

# if it can't find the Database it creates a new Database (You can change path and DB name here)

dbConnection = sqlite3.connect('d:\employee.db')

# You can use the above dbConnection for other operations on DB

print "Database created or Connected successfully"

dbConnection.execute(' ' ' CREATE TABLE Employees (

empID CHAR(10) NOT NULL PRIMARY KEY,

fName CHAR(100),

lName CHAR(100),

position CHAR(100),

sex CHAR(1),

hourlyRate REAL,

salary REAL );' ' ')

print "Table Employees Created successfully";

# Closing DB Connection

dbConnection.close();

Program Two: (CURD operations- Create Update Read Delete)

File - two.py:

# DB connection same as above program one

import sqlite3

option = 0

print "Choose Your option for the output"

while(option!=4):

# MENU for User

print "Enter 1 to print records"

print "Enter 2 to add a new record"

print "Enter 3 to delete a record"

print "Enter 4 to exit the program"

# Reading user input and cast to int

option = int(input("Your option :"))

if option == 1:

cursor=dbConnection.execute("SELECT empID, fName, lName, position, sex, hourlyRate,salary from Employees")

for r in cursor:

print "empID: ", r[0]

  print "fName: ", r[1]

  print "lName: ", r[2]

print "position: ", r[3]

print "sex: ", r[4]

print "hourlyRate: ", r[5]

print "salary: ", r[6]

if option == 2:

   # Here you can give the dynamic data

# You can change the data and excecute or you can ask user for dynamic data

dbConnection.excecute("INSERT INTO Employees (empID, fName,lName,position,sex,hourlyRate,salary)")

dbConnection.commit();

if option == 3:

print "Enter record empID to be deleted"

empID= input("Enter ID")

dbConnection.excecute("DELETE from Employees where ID= "+empID)

dbConnection.commit()

print "Deleted successfully"

if option == 4:

  print "Exting...."

dbConnection.close()

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.