I have been trying to get this program to work but every time I fix one area it throws 2 more codes, please show me what I am doing wrong, it is supposed to create a Database, with options to view all, add to, take away, update row, and view individual rows with a loop to repeat. _________ import sqlite3 #Define functions def CreateMemberDB(): conn=sqlite3.connect('Members.db') curs=conn.cursor() #create a table cmd_str=''' CREATE TABLE Member_Info( Name TEXT, Number INT, Phone NUMERIC )''' #send string to DB curs.execute(cmd_str) starting_data = ( ('Anna', 2435, 2345678910), ('Bryan', 2436, 3456789101), ('Claudia', 2437, 4567891012), ('Tom', 2438, 5678911234), ('Dusty', 2439, 6789112345) ) for row in starting_data: cmd_str= ''' INSERT INTO Member_Info(Name, Number, Phone) VALUES(?,?,?) ''' curs.execute(cmd_str, row) conn.commit() conn.close() def GetallMemberInfo(): conn=sqlite3.connect('Members.db') curs=conn.cursor() sql_cmd = 'SELECT ROWID, Name from Member_Info' curs.execute(sql_cmd) data = curs.fetchall() conn.commit() conn.close() return data #Show available members def Displaymembers(data): for line in data: format = "%-2s %-10s" print(format%line) data = GetallMemberInfo Displaymembers(data) # Add to Database def AddNew(): conn = sqlite3.connect('Members.db') NewName = input('Please enter new Member name: ') NewmemberID = float(input('Please enter new Member number: ')) Newphone = int(input('Please enter new Member name: ')) return (NewName, NewmemberID, Newphone) def InsertNewMember(data): conn=sqlite3.connect('Members.db') curs = conn.cursor() sql_cmd= '''INSERT INTO Member_Info(Name, Number, Phone) VALUES (?,?,?)''' curs.execute(sql_cmd, data) conn.commit() conn.close() return # Remove from database def removemember(row_id): conn=sqlite3.connect('Members.db') curs = conn.cursor() sql_cmd= ''' DELETE FROM Member_Info WHERE ROWID = ?''' curs.execute(sql_cmd, row_id) conn.commit() conn.close() return # Find and show name, member number, and phone number def Showmemberinfo(index): conn = sqlite3.connect("Members.db") curs = conn.cursor() sql_cmd='SELECT ROWID, * FROM Member_Info WHERE ROWID = ?' curs.execute(sql_cmd, index) data = curs.fetchall() conn.commit conn.close() return data def Displaydata(data): format = "%-7s %-10s ID- %-10s Ph#- %-10s" colunm_headers = ('Index', 'Name', 'Member #', 'Phone') print(format%colunm_headers) for line in data: print (format%line) data = GetallMemberInfo() Displaydata(data)
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
I have been trying to get this program to work but every time I fix one area it throws 2 more codes, please show me what I am doing wrong, it is supposed to create a
_________
import sqlite3
#Define functions
def CreateMemberDB():
conn=sqlite3.connect('Members.db')
curs=conn.cursor()
#create a table
cmd_str='''
CREATE TABLE Member_Info(
Name TEXT,
Number INT,
Phone NUMERIC
)'''
#send string to DB
curs.execute(cmd_str)
starting_data = (
('Anna', 2435, 2345678910),
('Bryan', 2436, 3456789101),
('Claudia', 2437, 4567891012),
('Tom', 2438, 5678911234),
('Dusty', 2439, 6789112345)
)
for row in starting_data:
cmd_str= '''
INSERT INTO Member_Info(Name, Number, Phone)
VALUES(?,?,?)
'''
curs.execute(cmd_str, row)
conn.commit()
conn.close()
def GetallMemberInfo():
conn=sqlite3.connect('Members.db')
curs=conn.cursor()
sql_cmd = 'SELECT ROWID, Name from Member_Info'
curs.execute(sql_cmd)
data = curs.fetchall()
conn.commit()
conn.close()
return data
#Show available members
def Displaymembers(data):
for line in data:
format = "%-2s %-10s"
print(format%line)
data = GetallMemberInfo
Displaymembers(data)
# Add to Database
def AddNew():
conn = sqlite3.connect('Members.db')
NewName = input('Please enter new Member name: ')
NewmemberID = float(input('Please enter new Member number: '))
Newphone = int(input('Please enter new Member name: '))
return (NewName, NewmemberID, Newphone)
def InsertNewMember(data):
conn=sqlite3.connect('Members.db')
curs = conn.cursor()
sql_cmd= '''INSERT INTO Member_Info(Name, Number, Phone)
VALUES (?,?,?)'''
curs.execute(sql_cmd, data)
conn.commit()
conn.close()
return
# Remove from database
def removemember(row_id):
conn=sqlite3.connect('Members.db')
curs = conn.cursor()
sql_cmd= '''
DELETE FROM Member_Info WHERE ROWID = ?'''
curs.execute(sql_cmd, row_id)
conn.commit()
conn.close()
return
# Find and show name, member number, and phone number
def Showmemberinfo(index):
conn = sqlite3.connect("Members.db")
curs = conn.cursor()
sql_cmd='SELECT ROWID, * FROM Member_Info WHERE ROWID = ?'
curs.execute(sql_cmd, index)
data = curs.fetchall()
conn.commit
conn.close()
return data
def Displaydata(data):
format = "%-7s %-10s ID- %-10s Ph#- %-10s"
colunm_headers = ('Index', 'Name', 'Member #', 'Phone')
print(format%colunm_headers)
for line in data:
print (format%line)
data = GetallMemberInfo()
Displaydata(data)
selected_index = input('Please enter Row number for member to view: ')
member_data = Showmemberinfo(selected_index)
print(member_data)
# Update member information
def UpdateMemberInfo(data, row_id):
conn=sqlite3.connect('Members.db')
curs = conn.cursor()
sql_cmd= '''
UPDATE Member_Info
SET Name = ?, Number = ?, Phone = ?
WHERE ROWID = ?
'''
curs.execute(sql_cmd, data+(row_id))
conn.commit()
conn.close()
return
def Get_Row_ID():
return input('Please Enter Row ID to Update: ')
#Greet User
def Greeting():
print('Welcome to The Little Black Book Of Names!\n')
# get user input for function
def User_Choice():
userchoice= input("What would you like to do?\nL = See list of names \nM = Show Member Information \nA = Add a Member\n U = Update Member Iformation \nR = Remove A Member")
selection = userchoice.strip()[0].lower()
if selection == 'a':
data = AddNew()
InsertNewMember(data)
GetallMemberInfo()
Displaymembers()
elif selection == 'r':
row_id = Get_Row_ID()
is_sure = input('Are you sure (Y/N)? ')
issure = is_sure.strip()[0].lower()
if issure == 'y':
removemember(row_id)
GetallMemberInfo
Displaymembers()
elif issure == 'n':
User_Choice()
else:
print('Invalid Answer, Please Try again!')
User_Choice()
elif selection == 'm':
Showmemberinfo()
Displaydata()
elif selection == 'l':
GetallMemberInfo()
Displaymembers()
elif selection == 'u':
row_id = Get_Row_ID()
data = GetallMemberInfo()
UpdateMemberInfo(data, row_id)
GetallMemberInfo()
Displaydata()
else:
print('\n\nInvalid selection, Please try again!\n\n')
User_Choice()
# Ask if user would like to find another
def doanother():
doanother= input("Would you like to find another name? ")
another= doanother.strip()[0].lower()=='y'
print()
return another
# Say goodbye
def Goodbye():
print('Thank you for using to The Little Digital Black Book Of Names!\nPlease come back again soon!')
#begin program
Greeting()
CreateMemberDB()
while True:
User_Choice()
if not doanother(): break
Goodbye()
Step by step
Solved in 3 steps with 2 images
