SQLite
SQL은 구조화된 질의 언어의 약자. 대형 데이터베이스 제품들이 사용되는 주된 언어.
SQLite는 SQL 데이터베이스로 사용할 수 있는 무료 소프트웨어
download : www.sqlite.org
관계형 데이터베이스 이해
1) primary key : 각 테이블에 있는 필드, 레코드에 대한 고유 식별자를 담고 있음.
2) 사용할 수 있는 데이터타입 : integer(정수값), real(부동의 소수점값), text(텍스트 문자열), blob(이미지 데이터 등 숫자와 문자로 표현할 수 없는 바이너리)
3) 라이브러리 임포트 : import sqlite3
4) 데이터베이스 연결 : with sqlite3.connect("company.db") as db: cursor=db.cursor()
5) """ : 세개의 큰 따옴표를 사용하면 코드를 여러줄로 분할 할 수 있게 하므로, 한줄로 표시하는 것보다 읽기 쉽게 할 수 있다.
#139
다음의 데이터로 구성된 names라는 이름의 데이터를 가지고 있는 phoneBook이라는 sql을 생성하자.
import sqlite3
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS Names(
id integer PRIMARY KEY,
firstname text NOT NULL,
lastname text NOT NULL,
Phonenum integer);""")
cursor.execute("""INSERT INTO Names(id, firstname, lastname, phonenum)
VALUES("1","Simon","Howels","01223349752")""") #num 띄어쓰기 안해서 확인시 오류 발생
db.commit()
cursor.execute("""INSERT INTO Names(id, firstname, lastname, phonenum)
VALUES("2","Karen","Phillips","01954 295773")""")
db.commit()
cursor.execute("""INSERT INTO Names(id, firstname, lastname, phonenum)
VALUES("3","Darren","Smith","01583 749012")""")
db.commit()
cursor.execute("""INSERT INTO Names(id, firstname, lastname, phonenum)
VALUES("4","Anne","Jones","01323 567322")""")
db.commit()
cursor.execute("""INSERT INTO Names(id, firstname, lastname, phonenum)
VALUES("5","Mark","Smith","01223 855534")""")
db.commit()
db.close
#140
139번 프로그램에서 만든 phoneBook데이터 베이스를 사용하여 다음과 같은 메뉴가 표시되는 프로그램을 작성하자.
Main menu
1) view phone book
2) add to phone book
3) search for surname
4) delete person from phone book
5) quit
enter your selection:
사용자가 1번을 선택하면 전체 레코드를 조회할 수 있다. 2번을 선택하면 새로운 사람 추가, 3번을 선택하면 사용자에게 성을 입력하라고 나오고,
4번을 입력하면 id입력하라고 나오고, 입력된 id의 레코드를 테이블에서 삭제한다.
5번을 선택하면 프로그램 종료. 마지막 잘못된 메뉴를 선택하면 적절한 메세지 표시. 사용자가 5번을 선택할 때까지 작업이 끝나면 다시 메뉴로 돌아옴
import sqlite3
# 데이터베이스 연결 및 테이블 생성 함수
def create_table():
# "PhoneBook.db" 데이터베이스에 연결
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
# Names 테이블 생성 (이미 존재하면 무시)
cursor.execute("""CREATE TABLE IF NOT EXISTS Names(
id integer PRIMARY KEY,
firstname text NOT NULL,
lastname text NOT NULL,
Phonenum text);""")
db.commit()
# 전체 전화번호부 조회 함수
def view_phone_book():
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
# Names 테이블에서 모든 레코드 선택
cursor.execute("SELECT * FROM Names")
# 선택된 모든 레코드를 가져와서 반복하며 출력
rows = cursor.fetchall()
for row in rows:
print(f"ID: {row[0]}, First Name: {row[1]}, Last Name: {row[2]}, Phone Number: {row[3]}")
# 새로운 연락처 추가 함수
def add_to_phone_book():
firstname = input("이름 입력: ")
lastname = input("성 입력: ")
phonenum = input("번호 추가: ")
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
# Names 테이블에 새로운 레코드 삽입
cursor.execute("INSERT INTO Names(firstname, lastname, phonenum) VALUES (?, ?, ?)",
(firstname, lastname, phonenum))
db.commit()
print("추가 성공")
# 성을 기준으로 검색 함수
def search_for_surname():
lastname = input("성으로 검색: ")
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
# Names 테이블에서 입력된 성에 대한 레코드 검색
cursor.execute("SELECT * FROM Names WHERE lastname=?", (lastname,))
# 검색된 레코드 출력 (여러 개일 수 있음)
rows = cursor.fetchall()
if len(rows) > 0:
for row in rows:
print(f"ID: {row[0]}, First Name: {row[1]}, Last Name: {row[2]}, Phone Number: {row[3]}")
else:
print("찾을 수 없음")
# 연락처 삭제 함수
def delete_person_from_phone_book():
id_to_delete = input("삭제할 아이디 입력: ")
with sqlite3.connect("PhoneBook.db") as db:
cursor = db.cursor()
# 입력된 아이디에 해당하는 레코드 삭제
cursor.execute("DELETE FROM Names WHERE id=?", (id_to_delete,))
db.commit()
# 삭제 결과 출력
if cursor.rowcount > 0:
print("삭제 성공")
else:
print("찾을 수 없음")
# 메인 함수
def main():
create_table()
while True:
print("\n메인 메뉴")
print("1) 전화번호부 조회")
print("2) 전화번호부 추가")
print("3) 성으로 검색")
print("4) 전화번호부에서 삭제")
print("5) 종료")
choice = input("메뉴 번호를 고르시오: ")
if choice == "1":
view_phone_book()
elif choice == "2":
add_to_phone_book()
elif choice == "3":
search_for_surname()
elif choice == "4":
delete_person_from_phone_book()
elif choice == "5":
print("프로그램 종료")
break
else:
print("잘못된 선택. 다시 입력")
main()
#141
저자의 목록과 그들이 쓴 저서를 저장할 떄 bookinfo 라는 새로운 sql 데이터베이스를 생성한다.
여기에는 두개의 테이블이 있게 된다. 첫번째는 author라는 테이블이며, 다음과 같은 데이터를 가진다.
두번째는 books 라는 테이블이며, 다음의 데이터를 가진다.
import sqlite3
with sqlite3.connect("Bookinfo.db") as db:
cursor = db.cursor()
# author 테이블 생성
cursor.execute("""CREATE TABLE IF NOT EXISTS author(
Name text PRIMARY KEY,
Birthplace text NOT NULL);""")
# books 테이블 생성
cursor.execute("""CREATE TABLE IF NOT EXISTS books(
id integer PRIMARY KEY,
title TEXT NOT NULL,
author_id integer,
publication_year integer,
FOREIGN KEY (author_id) REFERENCES author(Name));""")
# author 데이터 삽입
author_data = [
("Agatha Christie", "Torquay"),
("Cecelia Ahern", "Dublin"),
("J.K Rowling", "bristol"),
("Oscar Wilde", "Dublin")
]
cursor.executemany("INSERT INTO author (name, birthplace) VALUES (?, ?);", author_data) #cursor.executemany()는 SQLite 데이터베이스에 여러 개 쿼리를 한 번에 실행
# books 데이터 삽입
books_data = [
("1", "De Profundis", "Oscar Wilde", "1905"),
("2", "Harry Potter and the chamber of secrets", "J.K Rowling", "1998"),
("3", "Harry Potter and the prisoner of Azkaban", "J.K Rowling", "1999"),
("4", "Lyrebird", "Cecelia Ahern", "2017"),
("5", "Murder on the Orient Express", "Agatha Christie", "1934"),
("6", "Perfect", "Cecelia Ahern", "2017"),
("7", "The marble collector", "Cecelia Ahern", "2016"),
("8", "The murder on the links", "Agatha Christie", "1923"),
("9", "The picture of Dorian Gray", "Oscar Wilde", "1890"),
("10", "The secret adversary", "Agatha Christie", "1921"),
("11", "The seven dials mystery", "Agatha Christie", "1929"),
("12", "The year i met you", "Cecelia Ahern", "2014")
]
cursor.executemany("INSERT INTO books (id, title, author_id, publication_year) VALUES (?,?, ?, ?);", books_data)
db.close
#142
141번 프로그램의 bookinfo 데이터 베이스를 사용하여 저자와 그의 출생지 목록을 출력한다.
출생지를 입력하라고 요청하고, 입력된 값에서 태어난 저자의 모든 책에 대한 title, publication_year, author_id 를 모두 표시하도록 하자.
import sqlite3
with sqlite3.connect("bookinfo.db") as db:
cursor = db.cursor()
# 저자와 출생지 목록 출력
cursor.execute("SELECT Name, Birthplace FROM author")
author_data = cursor.fetchall()
for author in author_data:
print(f"저자: {author[0]}, 출생지: {author[1]}")
# 출생지 입력 받기
birthplace_input = input("출생지 입력: ")
# 출생지에서 태어난 저자의 책 목록 출력
cursor.execute("""SELECT id, title, publication_year, author_id
FROM books WHERE author_id
IN (SELECT Name FROM author WHERE Birthplace=?)""",
(birthplace_input,)) #in 안에 7개 값 전달. 튜플로 래핑
books_data = cursor.fetchall()
if len(books_data) > 0:
print(f"{birthplace_input}에서 태어난 저자의 책:")
for book in books_data:
print(f"ID: {book[0]}, 제목: {book[1]}, 출판 연도: {book[2]}, 저자 ID: {book[3]}")
else:
print(f"'{birthplace_input}'에서 태어난 저자의 책 없음")#143
bookinfo 데이터베이스를 사용한다. 사용자에게 연도를 입력하라고 요청하고,
입력된 연도 이후의 모든 책을 출간된 연도순으로 정렬하여 출력하라.
import sqlite3
with sqlite3.connect("bookinfo.db") as db:
cursor = db.cursor()
# 연도 입력 받기
publication_year_input = int(input("연도 입력: "))
# 연도 이후 책 목록 출력 / 연도순으로 (orderby)
cursor.execute("""SELECT id, title, publication_year, author_id
FROM books WHERE publication_year > ?
ORDER BY publication_year""",
(publication_year_input,))
books_data = cursor.fetchall()
if len(books_data) > 0:
print(f"{publication_year_input}년 이후 출간된 책 목록:")
for book in books_data:
print(f"ID: {book[0]}, 제목: {book[1]}, 출판 연도: {book[2]}, 저자 ID: {book[3]}")
else:
print(f"{publication_year_input}년 이후 출간된 책 없음")
#144
bookinfo 데이터베이스를 사용한다. 사용자에게 저자의 이름을 입력하라고 요청하고,
입력된 저자의 모든 책 정보를 텍스트 파일에 저장하라. 각 필드는 "-"" 대시로 구분하라
생성된 파일을 열어 제대로 되었는지 확인하자.
import sqlite3
with sqlite3.connect("bookinfo.db") as db:
cursor = db.cursor()
# 저자 입력 받기
author_id_input = input("저자 입력: ")
# 입력된 저자의 모든 책 정보 가져오기
cursor.execute("""SELECT id, title, publication_year
FROM books WHERE author_id =
(SELECT Name FROM author WHERE Name=?)""",
(author_id_input,))
books_data = cursor.fetchall()
if len(books_data) > 0:
# 텍스트 파일에 책 정보 저장
file_name = f"{author_id_input}_books.txt"
with open(file_name, "w") as file:
for book in books_data:
id = book[0]
title = book[1]
publication_year = book[2]
file.write(f"{id} - {title} - {author_id_input} - {publication_year}\n")
print(f"{author_id_input}의 책 정보를 {file_name} 파일에 저장했습니다.")
else:
print(f"{author_id_input}의 책 정보가 없습니다.")
try:
with open(file_name, "r") as file: #파일 읽고, file_name에 할당
content = file.read() # content 변수에 저장
print(f"{file_name} 파일 내용:\n{content}")
except FileNotFoundError:
print(f"{file_name} 파일 찾을 수 없음")
#145
다음과 같은 화면을 표시하는 프로그램을 만든다.
add 버튼 클릭하면 testScore라는 이름의 sql 데이터베이스에 입력한 데이터를 저장하고, clear 버튼은 입력된 것을 지워야한다.
import sqlite3
from tkinter import *
def addtolist():
newname = student_name.get()
newgrade = student_grade.get()
cursor.execute("""INSERT INTO Score (name, score) VALUES (?, ?)""", (newname, newgrade))
db.commit()
student_name.delete(0, END)
student_grade.delete(0, END)
student_name.focus()
def clearlist():
student_name.delete(0, END)
student_grade.delete(0, END)
student_name.focus()
with sqlite3.connect("TestScore.db") as db:
cursor = db.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS Scores
(id integer PRIMARY KEY,
name text NOT NULL,
score integer);""")
window = Tk()
window.title("TestScores")
window.geometry("450x200")
label1 = Label(text = "Enter student's name: ")
label1.place(x=30, y=35)
student_name = Entry(text = "")
student_name.place(x=170, y=35, width=200, height=25)
student_name.focus()
label2 = Label(text = "Enter student's grade: ")
label2.place(x=30, y=80)
student_grade = Entry(text ="")
student_name.place(x=170, y=80, width=200, height=25)
student_name.focus()
addbtn = Button(text = "Add", command=addtolist)
addbtn.place(x=170, y=120, width=75, height=25)
clearbtn = Button(text = "Clear", command=clearlist)
clearbtn.place(x=270, y=120, width=75, height=25)
window.mainloop()
db.close()'Python challenge' 카테고리의 다른 글
| [147] Mastermind (0) | 2023.10.19 |
|---|---|
| [146] 시프트코드 (1) | 2023.10.05 |
| [124~132] Tkinter GUI (1) | 2023.10.01 |
| Do you think that is possible? (0) | 2023.09.24 |
| [118~123] 함수 (0) | 2023.09.24 |