Linux/Bash shell
[Linux-vim] vim에디터 사용하기 7 - local DB sqlite3, sqlite3 를 활용한 CRUD model 실습
걍작
2022. 3. 17. 00:08
# 정리
** DB는 원격DB / 로컬 DB 로 크게 나눌 수 있다.
- 네트워크로 접속, 여러 사용자가 사용
- 로컬 기기로 접속, 단일 사용자가 사용
# sqlite3 - 리눅스 로컬 데이터베이스
<파일 생성 명령 후, 실행을 하나 이상 해줘야 파일이 저장되어 남는다.>
//리눅스에서 사용하는 로컬 데이터베이스로, CRUD 모두 가능하며, 표준 쿼리문이 제공된다.
- 따로 계정정보(id,pw)가 없고, 파일 이름 자체가 계정이 된다.(로컬 영역이라 로그인이 필요 없다.)
- 트랜젝션(커밋 , 롤백)이 없다.
// $ sqlite3 (파일명) 으로 계정(db 파일)을 생성한다.
* 파일명이 없으면 임시DB (transient in-memory DB)로 생성되며, 작업이 끝나면 파일이 자동으로 삭제된다.
//추가 패키지 설치 후 사용 가능하다.
- $ sudo apt install -y sqlite3
//sqlite3는 아이폰이나 안드로이드폰에도 적용할 수 있다.
//대표적인 명령어 (명령어 사용 및 실행 결과 실습 예제 참조)
- .exit - 종료
- .databases - 데이터 베이스 정보 확인(DB파일 경로 출력됨)
- .tables - 보유 테이블 안내(테이블이 없으면 아무 결과도 안나옴)
- .schema -DB구조 보기(테이블, 시퀀스 생성문 출력됨)
//대표적인 쿼리문 약속어 (명령어 사용 및 실행 결과 실습 예제 참조)
- 타입 integer / text / date
- primary key : 중복 제한
- autoincrement : 자동 번호 부여
- current_timestamp : 현재 시각 표현
- create if not exists (테이블명) : 테이블 중복 생성 제한
- strftime : 날짜 / 시간을 스트링으로 변환(string format time)
* ex. select num, name, age, strftime('%Y-%m-%d-%H-%M-%S',wdate)as wdate from test;
//명령 프롬프트에서 sqlite3 실행 (명령 의미 해설은 더보기 코드 참조)
- pi@raspberry:~/practice $ sqlite3 sh28test.db
- sqlite> create table if not exists test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
- sqlite> insert into test(name, age, wdate) values('kim1',33,current_timestamp);
- sqlite> select * from test; - sqlite> drop table test; - sqlite> create table test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
//명령 프롬프트에서 직접 실행
- sqlite3 (파일명) "명령문" 형태로 sql명령 실행 가능
- 동일 이름의 파일이 없는 경우 파일 생성이 동시에 이루어짐
- ** vim 편집창에서도 동일하게 적용 가능 **
** DB는 원격DB / 로컬 DB 로 크게 나눌 수 있다.
- 네트워크로 접속, 여러 사용자가 사용
- 로컬 기기로 접속, 단일 사용자가 사용
# sqlite3 - 리눅스 로컬 데이터베이스
<파일 생성 명령 후, 실행을 하나 이상 해줘야 파일이 저장되어 남는다.>
//리눅스에서 사용하는 로컬 데이터베이스로, CRUD 모두 가능하며, 표준 쿼리문이 제공된다.
- 따로 계정정보(id,pw)가 없고, 파일 이름 자체가 계정이 된다.(로컬 영역이라 로그인이 필요 없다.)
- 트랜젝션(커밋 , 롤백)이 없다.
// $ sqlite3 (파일명) 으로 계정(db 파일)을 생성한다.
* 파일명이 없으면 임시DB (transient in-memory DB)로 생성되며, 작업이 끝나면 파일이 자동으로 삭제된다.
//추가 패키지 설치 후 사용 가능하다.
- $ sudo apt install -y sqlite3
//sqlite3는 아이폰이나 안드로이드폰에도 적용할 수 있다.
//대표적인 명령어 (명령어 사용 및 실행 결과 실습 예제 참조)
- .exit - 종료
- .databases - 데이터 베이스 정보 확인(DB파일 경로 출력됨)
- .tables - 보유 테이블 안내(테이블이 없으면 아무 결과도 안나옴)
- .schema -DB구조 보기(테이블, 시퀀스 생성문 출력됨)
//대표적인 쿼리문 약속어 (명령어 사용 및 실행 결과 실습 예제 참조)
- 타입 integer / text / date
- primary key : 중복 제한
- autoincrement : 자동 번호 부여
- current_timestamp : 현재 시각 표현
- create if not exists (테이블명) : 테이블 중복 생성 제한
- strftime : 날짜 / 시간을 스트링으로 변환(string format time)
* ex. select num, name, age, strftime('%Y-%m-%d-%H-%M-%S',wdate)as wdate from test;
//명령 프롬프트에서 sqlite3 실행 (명령 의미 해설은 더보기 코드 참조)
- pi@raspberry:~/practice $ sqlite3 sh28test.db
- sqlite> .databases
- sqlite> .tables
- sqlite> .schema
- sqlite> create table if not exists test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
- sqlite> insert into test(name, age, wdate) values('kim1',33,current_timestamp);
- sqlite> select * from test;
- sqlite> drop table test;
- sqlite> create table test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
-- 코드 보기 --
더보기
#sqlite3 로 sh28test.db 파일 생성
pi@raspberry:~/practice $ sqlite3 sh28test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
#db 경로 확인
sqlite> .databases
main: /home/pi/practice/sh28test.db
#보유 테이블 "test"확인 >> 테이블 없으면 출력 없음
sqlite> .tables
test
#테이블 생성
sqlite> create table test(num integer primary key autoincrement, name text, age integer, wdate date);
Error: table test already exists
#데이터 구조 출력 (테이블 및 시퀀스 생성문 출력)
sqlite> .schema
CREATE TABLE test(num integer primary key autoincrement, name text, age integer, wdate date);
CREATE TABLE sqlite_sequence(name,seq);
# 데이터 삽입
sqlite> insert into test(name, age, wdate) values('kim1',33,current_timestamp);
sqlite> insert into test(name, age, wdate) values('kim1',33,current_timestamp);
sqlite> insert into test(name, age, wdate) values('kim1',33,current_timestamp);
# 데이터 출력
sqlite> select * from test;
1|kim1|33|2022-03-16 14:12:38
2|kim1|33|2022-03-16 14:12:39
3|kim1|33|2022-03-16 14:12:40
# 테이블 삭제
sqlite> drop table test;
sqlite> .tables
sqlite> create table test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
sqlite> insert into (name,age) values ('lee',55);
Error: near "(": syntax error
sqlite> insert into test(name,age) values ('lee',55);
sqlite> insert into test(name,age) values ('lee',55);
sqlite> insert into test(name,age) values ('lee',55);
sqlite> select * from test;
1|lee|55|2022-03-16 14:15:18
2|lee|55|2022-03-16 14:15:19
3|lee|55|2022-03-16 14:15:20
#(날짜 표시 형태를 지정된 범위와 식별자로 변경하여 별칭 wdate로 다시 column 생성하여) 데이터 출력
sqlite> select num, name, age, strftime('%Y-%m-%d %H:%M:%S',wdate)as wdate from test;
1|lee|55|2022-03-16 14:15:18
2|lee|55|2022-03-16 14:15:19
3|lee|55|2022-03-16 14:15:20
sqlite> select num, name, age, strftime('%Y-%m-%d-%H-%M-%S',wdate)as wdate from test;
1|lee|55|2022-03-16-14-15-18
2|lee|55|2022-03-16-14-15-19
3|lee|55|2022-03-16-14-15-20
sqlite> create table test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
Error: table test already exists
# 동일 이름의 테이블 중복 생성 제한
sqlite> create table if not exists test(num integer primary key autoincrement, name text, age integer, wdate date default current_timestamp);
sqlite>
//명령 프롬프트에서 직접 실행
- sqlite3 (파일명) "명령문" 형태로 sql명령 실행 가능
- 동일 이름의 파일이 없는 경우 파일 생성이 동시에 이루어짐
- ** vim 편집창에서도 동일하게 적용 가능 **
------------------------------
// vim 편집기에서 sqlite3 CRUD 예제 실습
- 자동 더미데이터 생성 + selectAll , insert, update, delete 실습
-- 코드 보기 --
더보기
#!/bin/bash
clear
pwd
echo $0
echo "----------------------------"
#sudo apt install -y sqlite3
sqlite3 sh28test.db "create table if not exists test(num integer primary key autoincrement,name text,age integer,wdate date default current_timestamp)"
echo "create table successed.."
sqlite3 sh28test.db ".databases"
sqlite3 sh28test.db ".tables"
echo "----------------------------"
row_count=$(sqlite3 sh28test.db "select count (*) from test")
echo $row_count
if ((row_count==0));then
echo "insert 10...start"
for i in $(seq 0 9)
do
sqlite3 sh28test.db "insert into test(name,age) values('kim$i',$((i+20)))"
done
fi
echo "----------------------------"
########## insert ##################
sqlite3 sh28test.db "insert into test(name,age) values('lee',44)"
########## update ##################
name="lee"
age=66
num=3
sqlite3 sh28test.db "update test set name='$name',age=$age where num=$num"
########## delete ##################
num=9
sqlite3 sh28test.db "delete from test where num=$num"
##########selectAll##################
sqlite3 sh28test.db "select num,name,age,strftime('%Y-%m-%d-%H-%M-%S',wdate) as wdate from test order by num desc"
exit 0
#mission 1 : yad와 sqlite3를 이용해 성적 처리 모델을 만들어라.
-- 예제코드 --
더보기
#!/bin/bash
clear
pwd
echo $0
echo "----------------------------"
################insert########################
insert(){
insert_form=$(yad --title="insertScore" --form --field="name" --field="kor" --field="eng" --field="math" "$1" "$2" "$3" "$4") insert_result=$?
echo insert_form: $insert_form
echo result: $insert_result
if ((insert_result==0)); then
echo "insert OK"
name=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $1}' )
kor=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $2}' )
eng=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $3}' )
math=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $4}' )
sqlite3 sh29score.db "insert into score(name,kor,eng,math) values('$name',$kor,$eng,$math)"
else
echo "insert cancel"
fi
selectAll
}
################update#########################
update(){
update_form=$(yad --title="updateScore" --form --field="num" --field="name" --field="kor" --field="eng" --field="math" "$1" "$2" "$3" "$4" "$5") update_result=$?
echo update_form: $update_form
echo result: $update_result
if ((update_result==0)); then
echo "update OK"
num=$(echo $update_form |awk 'BEGIN {FS="|"} {print $1}' )
name=$(echo $update_form |awk 'BEGIN {FS="|"} {print $2}' )
kor=$(echo $update_form |awk 'BEGIN {FS="|"} {print $3}' )
eng=$(echo $update_form |awk 'BEGIN {FS="|"} {print $4}' )
math=$(echo $update_form |awk 'BEGIN {FS="|"} {print $5}' )
sqlite3 sh29score.db "update score set name='$name',kor=$kor,eng=$eng,math=$math where num=$num"
else
echo "update cancel"
fi
selectAll
}
###############delete##########################
delete(){
delete_form=$(yad --title="deleteScore" --text="$1 $2 data delete OK?") delete_result=$?
echo "position para: " $1 $2
echo delete_result: $delete_result
if ((delete_result==0)); then
echo "delete OK"
num=$(echo $delete_form |awk 'BEGIN {FS="|"} {print $1}' )
name=$(echo $delete_form |awk 'BEGIN {FS="|"} {print $2}' )
sqlite3 sh29score.db "delete from score where num=$1"
else
echo "delete cancel"
fi
selectAll
}
########### create table & dummy datas #############
echo "------------ create table -------------"
#arr_score="1|kim1|99|88|77 "
#arr_score+="2|kim2|99|88|77 "
#arr_score+="3|kim3|99|88|77 "
sqlite3 sh29score.db "create table if not exists score(num integer primary key autoincrement, name text, kor integer, eng integer, math integer)"
echo "create table successed.."
sqlite3 sh29score.db ".databases"
sqlite3 sh29score.db ".tables"
row_count=$(sqlite3 sh29score.db "select count (*) from score")
echo $row_count
if ((row_count==0));then
echo "insert 10 dummy datas..."
for i in $(seq 0 9)
do
sqlite3 sh29score.db "insert into score(name,kor,eng,math) values('kim$i',99,88,77)"
done
fi
################## selectAll #########################
selectAll(){
arr_score=$(echo $(sqlite3 sh29score.db "select * from score order by num desc"))
echo "arr_score.length: ${#arr_score[@]}"
IFS="| " read -ra rows <<< $arr_score
echo ${rows[@]}
echo "rows.length:"${#rows[@]}
scoreList=$(yad --width 350 --height 500 --title="score list" --list --button="update" --button="insert" --button="delete" --column="num" --column="name" --column="kor" --column="eng" --column="math" "${rows[@]}") result_scoreList=$?
echo "scoreList:" $scoreList
echo "result_scoreList:" $result_scoreList
printf "%s %d %d %d %d %.2f %s\n" $name $kor $eng $math $total $avg $grade
echo "--------------- select button -------------"
num=$(echo $scoreList |awk 'BEGIN {FS="|"} {print $1}' )
name=$(echo $scoreList |awk 'BEGIN {FS="|"} {print $2}' )
kor=$(echo $scoreList |awk 'BEGIN {FS="|"} {print $3}' )
eng=$(echo $scoreList |awk 'BEGIN {FS="|"} {print $4}' )
math=$(echo $scoreList |awk 'BEGIN {FS="|"} {print $5}' )
if ((result_scoreList==0));then
echo "update button..."
update $num $name $kor $eng $math
elif ((result_scoreList==1)); then
echo "insert button.."
insert
elif ((result_scoreList==2)); then
echo "delete button.."
delete $num $name
else
echo "cancel"
exit 0
fi
}
#end selectAll
selectAll
exit 0
#mission 2 : yad와 sqlite3를 이용해 회원 정보 처리 모델을 만들어라.
-- 예제 코드 --
더보기
#!/bin/bash
clear
pwd
echo $0
echo "----------------------------"
################ insert ################
insert(){
echo "insert..."
insert_form=$(yad --title="insertMember" --form --field="id" --field="pw" --field="name" --field="tel" "insert" "hi123" "lee" "911") insert_result=$?
echo insert_form: $insert_form
echo result: $insert_result
if ((insert_result==0)); then
echo "insert OK"
id=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $1}' )
pw=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $2}' )
name=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $3}' )
tel=$(echo $insert_form |awk 'BEGIN {FS="|"} {print $4}' )
sqlite3 sh30member.db "insert into member(id,pw,name,tel) values('$id','$pw','$name',$tel)"
else
echo "insert cancel"
fi
selectAll
}
################ update ################
update(){
echo "update..." $1 $2 $3 $4 $5
update_form=$(yad --title="updateMember" --form --field="num" --field="id" --field="pw" --field="name" --field="tel" "$1" "$2" "$3" "$4" "$5") update_result=$?
echo update_form: $update_form
echo result: $update_result
if ((update_result==0)); then
echo "update OK"
num=$(echo $update_form |awk 'BEGIN {FS="|"} {print $1}' )
id=$(echo $update_form |awk 'BEGIN {FS="|"} {print $2}' )
pw=$(echo $update_form |awk 'BEGIN {FS="|"} {print $3}' )
name=$(echo $update_form |awk 'BEGIN {FS="|"} {print $4}' )
tel=$(echo $update_form |awk 'BEGIN {FS="|"} {print $5}' )
sqlite3 sh30member.db "update member set id='$id',pw='$pw',name='$name',tel=$tel where num='$num'"
else
echo "update cancel"
fi
selectAll
}
################ delete ################
delete(){
echo "delete..."
delete_form=$(yad --title="deleteMember" --text="$1 $2 data delete OK?") delete_result=$?
echo "position para: " $1 $2
echo delete_result: $delete_result
if ((delete_result==0)); then
echo "delete OK"
sqlite3 sh30member.db "delete from member where num=$1"
else
echo "delete cancel"
fi
selectAll
}
########### create table & dummy datas #############
echo "------------ create table -------------"
#vos="1|admin1|hi1231|kim1|011 "
#vos+="2|admin2|hi1232|kim2|012 "
#vos+="3|admin3|hi1233|kim3|013 "
#vos+="4|admin4|hi1234|kim4|014 "
sqlite3 sh30member.db "create table if not exists member(num integer primary key autoincrement, id text, pw text, name text, tel integer)"
echo "create table successed.."
sqlite3 sh30member.db ".databases"
sqlite3 sh30member.db ".tables"
data_count=$(sqlite3 sh30member.db "select count (*) from member")
echo $data_count
if ((data_count==0));then
echo "insert 10 dummy datas..."
for i in $(seq 0 9);
do
sqlite3 sh30member.db "insert into member(id,pw,name,tel) values('admin$i','h123$i','kim$i',11$i)"
done
else
echo "datas already exist"
fi
################### selectAll ####################
selectAll(){
data_all=$(sqlite3 sh30member.db "select * from member order by num desc")
vos=$(echo $data_all)
IFS="| " read -ra rows <<< $vos
echo ${rows[@]}
echo "rows.length:"${#rows[@]}
form_selectAll=$(yad --width 400 --height 500 --title "memberList" --button=update --button=insert --button=delete --list --column=NUM --column=ID --column=PW --column=NAME --column=TEL "${rows[@]}") result_selectAll=$?
echo $form_selectAll
echo $result_selectAll
echo "--------------- select button -------------"
num=$(echo $form_selectAll |awk 'BEGIN {FS="|"} {print $1}' )
id=$(echo $form_selectAll |awk 'BEGIN {FS="|"} {print $2}' )
pw=$(echo $form_selectAll |awk 'BEGIN {FS="|"} {print $3}' )
name=$(echo $form_selectAll |awk 'BEGIN {FS="|"} {print $4}' )
tel=$(echo $form_selectAll |awk 'BEGIN {FS="|"} {print $5}' )
if ((result_selectAll==0));then
echo "update button..."
update $num $id $pw $name $tel
elif ((result_selectAll==1)); then
echo "insert button.."
insert
elif ((result_selectAll==2)); then
echo "delete button.."
delete $num $id
else
echo "cancel"
exit 0
fi
}
#end selectAll
selectAll
exit 0