# 파이썬 데이터베이스 프로그래밍
# 데이터의 영속성을 부여하는 방법 중 하나
# 작은 양의 데이터는 파일입출력을 통해 처리 가능
# 대량의 데이터를 체계적으로 저장해서 원하는 목적에 따라
# 데이터를 처리(검색,수정,삭제)할 수 있도록 해 줌
# 파이썬에서는 일반적인 관계형 데이터베이스를 이용해서
# 데이터를 저장,검색,수정,삭제할 수 있음
# 또한, 독립적인 데이터베이스 서버없이 파일기반
# 데이터베이스를 이용해서 간편하게 데이터를 조작할 수도 있음
# 내장형(임베디드) 파일기반 데이터베이스 : sqlite
# sqlite
# 내장형 파일기반 데이터베이스
# 서버가 필요없고 복잡한 설정도 필요없으면서
# 트랜잭션이 지원되는 데이터베이스
# 하나의 파일에 테이블,뷰,색인,트리거등이 저장
# 용량이 작아 메모리에 올리기도 쉽고 속도도 빠른편
# 안드로이드, ios에는 기본적으로 포함되어 있음
# 단, 복잡하고 용량이 큰 데이터를 저장하기에는 다소 적절치 않음
# sqlite.org
# sqlite 설치법
# sqlite-tools-win32-x86-3370200.zip (2022.2.14기준)
# 압축해제 후 sqlit3.exe를 c:/Java 에 저장
# 명령프롬프트에서 sqlite3.exe를 실행 함
# => cd \Java
# .import ~~.csv ~~
# 테이블 생성
create table member(
userid varchar(18) primary key,
passwd varchar(18) not null,
name varchar(18) not null,
email varchar(18) not null
);
# 테이블 목록확인
# .table
# 테이블 생성 구문 확인
# .schema
# .open bigdata.db
# 테이블 구조확인
# pragma table_info('member')
# 데이터 삽입
insert into member
values ('abc123', '987xyz', 'abc123', 'abc@xyz.com');
# 데이터조회
# select * from member;
# 조회시 컬럼헤더 설정
# .header on
# .mode column
# 데이터베이스 종료
# .quit
# csv 파일 import하기
# .mode csv
# .import csv파일명 테이블명
# 직책별 사원수 조회
# 국가별 금메달 수상수 조회
#select country, count(medal) cnt from summ
#where medal = 'Gold'
#group by country
#order by cnt desc
#limit 0,10
# 파이썬으로 데이터 다루기1 - select
import sqlite3
# 데이터베이스 연결
conn = sqlite3.connect('c:/java/bigdata.db')
# sql 실행을 위해 커서 생성
cur = conn.cursor()
# 실행할 sql문을 생성하고
# 실행후 결과를 커서에 저장
sql = 'select * from member'
cur.execute(sql)
# 반복문을 이용해서 커서에 저장된 결과집합으로부터
# 한 행씩 읽어와서 처리
while True:
# 커서로부터 한행씩 읽은 후
row = cur.fetchone()
#읽은 행에 내용이 없으면 반복 중단
if row == None: break
# 읽어온 행에서 각 컬럼의 값을 변수에 저장 후 출력
result = f'{row[0]}{row[1]}{row[2]}{row[3]}'
print(result)
# 반복실행이 끝나면 커서 사용 종료
cur.close()
# 데이터베이스 사용종료 - 연결 해제
conn.close()
# 파이썬으로 데이터 다루기 2
# 직책별 사원수 조회
import sqlite3
conn = sqlite3.connect('c:/java/bigdata.db')
cur = conn.cursor()
sql = 'select job_id, count(job_id) cnt from emp group by job_id order by cnt desc'
cur.execute(sql)
while True:
row = cur.fetchone()
if row == None: break
result += f'{row[0]} {row[1]}'
print(result)
cur.close()
conn.close()
# 파이썬으로 데이터 다루기 3
import sqlite3
conn = sqlite3.connect('c:/java/bigdata.db')
cur = conn.cursor()
sql = "select country, count(medal) cnt from summ where medal = 'Gold' group by country order by cnt desc limit 10"
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
result += f'{row[0]} {row[1]}'
print(result)
cur.close()
conn.close()
# 파이썬으로 데이터 다루기 4 - insert
# 입력한 회원정보를 member테이블에 저장
import sqlite3
userid = input('아이디는?')
passwd = input('비밀번호는?')
name = input('이름은?')
email = input('이메일은?')
conn = sqlite3.connect('c:/java/bigdata.db')
cur = conn.cursor()
# 아래방식은 비추 = sql 인젝션 공격의 위험 존재
# sql = f'insert into member' \
# f'values ({userid}, {passwd}, {name}, {email} ) '
# 매개변수 placeholder(?) 이용한 매개변수 지정
sql = 'insert into member values (?, ?, ?, ?)'
params = (userid, passwd, name, email)
cur.execute(sql, params)
conn.commit()
cur.close()
conn.close()
# 파이썬으로 데이터 다루기 5 - delete
# 삭제할 회원의 아이디를 입력받아 회원정보 삭제
import sqlite3
conn = sqlite3.connect('c:/java/bigdata.db')
cur = conn.cursor()
# 매개변수 placeholder(?) 이용한 매개변수 지정
sql = 'delete from member where userid = ? '
params = (userid, )
cur.execute(sql, params)
print(f'{cur.rowcount}행이 제거됨!!')
conn.commit()
cur.close()
conn.close()
# 파이썬으로 데이터 다루기 6 - update
# abc123 회원의 이름을 xyz987로
# 이메일은 xyz@abc.co.kr로 수정함
import sqlite3
userid = input('아이디는?')
name = input('이름은?')
email = input('이메일은?')
conn = sqlite3.connect('c:/java/bigdata.db')
cur = conn.cursor()
# 매개변수 placeholder(?) 이용한 실제값 지정
sql = 'update member set name =:name, email =: email where userid =: userid'
params = (name, email,userid)
cur.execute(sql, params)
print(f'{cur.rowcount}행이 제거됨!!')
conn.commit()
cur.close()
conn.close()