728x90
반응형
SMALL
■ sqlite
#1. 별도의 DB SERVER 가 필요 없이 DB 파일 기초하여 데이터베이스를 처리하는 엔진
import sqlite3
sqlite3.version
sqlite3.__file__
# 임시 저장 영역 구축(메모리)
# 메모리생성
conn = sqlite3.connect(':memory:') # transaction 실행
conn
c = conn.cursor() # sql문 실행메모리 영역
# sql하기 위한 실행, 문자형식으로 작성
c.execute('create table emp(id integer, name char, sal integer)')
# 문자 insert 실행시, "" or ''. 단, insert문 을 ''으로 했다면, 문자입력할 때에는 ""으로 작성하는 것이 가장 best!
c.execute('insert into emp(id, name, sal) values(1, "홍길동", 1000)')
c.execute('select * from emp')
c.fetchone() # 암시적 커서
c.execute('insert into emp(id, name, sal) values(2, "박찬호", 2000)')
c.execute('select * from emp')
c.fetchone() # 한 행만 fetch
c.fetchone()
c.fetchone()
c.execute('select * from emp')
c.fetchall() # 여러행 fetch , 명시적 커서
transaction
#1. 논리적으로 DML(insert, update, delete, merge) 문장으로 한꺼번에 처리하는 작업단위
#2. COMMIT, BOLLBACK
#3. sqlite3 에서는 connect 를 기반으로 commit, rollback 해야 한다.
# transaction 취소
c.execute('insert into emp(id, name, sal) values(1, "홍길동", 1000)')
c.execute('insert into emp(id, name, sal) values(2, "박찬호", 2000)')
c.execute('select * from emp')
c.fetchall()
c.execute('select * from emp')
c.fetchall()
conn.rollback()
c.execute('select * from emp')
c.fetchall()
# commit 작업
c.execute('insert into emp(id, name, sal) values(1, "홍길동", 1000)')
c.execute('insert into emp(id, name, sal) values(2, "박찬호", 2000)')
c.execute('select * from emp')
c.fetchall()
conn.commit() # transaction 영구히 저장
c.execute('select * from emp')
c.fetchall()
# sqlite3 종료
c.close() # cursor 종료
conn.close() # connect 종료
# sqlite3 종료하는 순간 임시성이기 때문에 emp 테이블은 확인불가.
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('select * from emp')
c.fetchall()
# 데이터베이스에 영구히 보존하려고 할때 사용하는 방법
# cursor, connect 를 종료해도 emp 테이블을 존재한다
conn = sqlite3.connect('c:/data/insa.db') # 데이터파일 생성
c = conn.cursor()
c.execute('create table emp(id integer, name char, sal integer)')
c.execute('insert into emp(id, name, sal) values(1, "홍길동", 1000)')
c.execute('insert into emp(id, name, sal) values(2, "박찬호", 2000)')
conn.commit()
c.execute('select * from emp')
c.fetchall()
c.close() # cursor 종료
conn.close() # connect 종료
conn = sqlite3.connect('c:/data/insa.db')
c = conn.cursor()
c.execute('select * from emp')
c.fetchall()
# insa.db 파일안에 생성된 테이블 정보 확인
conn = sqlite3.connect('c:/data/insa.db')
c = conn.cursor()
c.execute('select * from sqlite_master')
c.fetchall()
# 테이블 컬럼의 구조 확인
c.execute('pragma table_info(emp)')
c.fetchall()
# 테이블 삭제
c.execute('drop table emp')
c.execute('select * from sqlite_master')
c.fetchall()
# pandas dataframe 을 sqlite3 테이블로 이관작업(data migration)
#1. db open -> connect 생성
data = pd.read_csv('c:/data/emp.csv')
data.info()
data
#2. to_sql : pandas 메소드, 이관작업
# emp 테이블 복제, index 복제X
data.to_sql('emp', conn, index=False)
# table명(type 자동변형), 접속환경, index 유무
c.execute('select * from sqlite_master')
c.fetchall()
c.execute('select * from emp')
c.fetchall() # 튜플 형식으로 확인
# type 확인
c.execute('pragma table_info(emp)')
c.fetchall()
data.info()
c.close()
conn.close()
# 재접속, 영구히 저장
conn = sqlite3.connect('c:/data/insa.db')
c = conn.cursor()
c.execute('select * from sqlite_master')
c.fetchall()
c.execute('pragma table_info(emp)')
c.fetchall()
c.execute('select * from emp')
c.fetchall()
(# 위와 반대 작업)
# sqlite3 에 있는 테이블을 pandas dataframe 이관 작업
emp_new = pd.read_sql_query('select * from emp where department_id = 20', conn)
emp_new
emp_new.info()
c.execute('select * from emp')
c.fetchall()
c.execute("select * from emp where last_name= 'king'")
c.fetchall()
c.execute("select * from emp where lower(last_name) = 'king'")
c.fetchall()
c.execute("select * from emp where upper(last_name) = 'KING'")
c.fetchall()
# substr() 가능
c.execute('''select last_name, substr(last_name, 1, 1), substr(last_name, 2) from emp''')
c.fetchall()
# 연결연산자 ||
c.execute("""select last_name, substr(last_name, 1, 1)||substr(last_name, 2) from emp""")
c.fetchall()
# 중첩
c.execute("""select last_name, lower(substr(last_name, 1, 1))||upper(substr(last_name, 2)) from emp""")
c.fetchall()
c.execute("""select last_name, substr(last_name, -2, 2) from emp""")
c.fetchall()
# length() : 문자의 길이
c.execute("""select last_name, length(last_name) from emp""")
c.fetchall()
# replace()
# SQL : from dual -> sqlite3 : from dual 생략
c.execute('select replace("오라클 분석가", "분석가", "엔지니어")')
c.fetchall()
# trim()
c.execute('select trim(" 오라클 엔지니어 ")')
c.fetchall()
c.execute('select ltrim(" 오라클 엔지니어 ")')
c.fetchall()
c.execute('select rtrim(" 오라클 엔지니어 ")')
c.fetchall()
# trim() : 접미, 접두에 특정 문자열을 제거
c.execute('select trim("오라클오라클 오라클 엔지니어 오라클", "오라클")')
c.fetchall()
c.execute('select ltrim("오라클오라클 오라클 엔지니어", "오라클")')
c.fetchall()
c.execute('select rtrim("오라클 엔지니어 오라클", "오라클")')
c.fetchall()
# 위치 표시 : 1번부터 시작
c.execute("select instr('aaa@itwill.com', '@')")
c.fetchall()
# 사칙연산 : 정수값
# 정수/ = 몫
c.execute("select 1+2, 2-1, 2*3, 10/3, 10%3")
c.fetchall()
# 실수/ = 몫.나머지
c.execute("select 1+2, 2-1, 2*3, 10./3, 10%3")
c.fetchall()
# round()
c.execute("select round(45.926, 2)")
c.fetchall()
c.execute("select round(45.926, 0), round(45.926, 2)")
c.fetchall()
c.execute("select round(45.926), round(45.926, 0), round(45.926, 2)")
c.fetchall()
# round(45.926, -2) = round(45.926, 0)와 동일
c.execute("select round(45.926), round(45.926, 0), round(45.926, 2), round(45.926, -1), round(45.926, -2)")
c.fetchall()
# 결측값 = None
c.execute("select last_name, salary, commission_pct from emp")
c.fetchall()
# 결측값 계산
c.execute("select last_name, salary*12+commission_pct from emp")
c.fetchall()
# ifnull()
c.execute("select last_name, salary*12+ifnull(commission_pct,0) from emp")
c.fetchall()
# coalesce() = ifnull()와 동일
c.execute("select last_name, salary*12+coalesce(commission_pct,0) from emp")
c.fetchall()
# nullif() : ture or false
c.execute("""select last_name, length(last_name), nullif(length(last_name),5) from emp""")
c.fetchall()
# unique X
c.execute("""select unique department_id from emp""")
c.fetchall()
# distinct
c.execute("""select distinct department_id from emp""")
c.fetchall()
c.execute("""select distinct department_id, job_id from emp""")
c.fetchall()
# like 연산자
c.execute("""select * from emp where last_name like 'K%'""")
c.fetchall()
# _문자%
c.execute("""select * from emp where last_name like '_i%'""")
c.fetchall()
# in 연산자
c.execute("""select * from emp where department_id = 10 or department_id = 20""")
c.fetchall()
c.execute("""select * from emp where department_id in (10, 20)""")
c.fetchall()
# not in 연산자
c.execute("""select * from emp where department_id != 10 and department_id != 20""")
c.fetchall()
c.execute("""select * from emp where department_id not in (10, 20)""")
c.fetchall()
# between-and 연산자
c.execute("""select * from emp where salary >= 10000 and salary <= 15000""")
c.fetchall()
c.execute("""select * from emp where salary between 10000 and 15000""")
c.fetchall()
c.execute("""select manager_id from emp""")
c.fetchall()
# exists, 서브쿼리문
c.execute("""select *
from emp
where employee_id in (select manager_id from emp) """)
c.fetchall()
c.execute("""select *
from emp o
where exists (select 'x' from emp where manager_id = o.employee_id) """)
c.fetchall()
# not exists
c.execute("""select *
from emp
where employee_id not in (select manager_id from emp where manager_id is not null) """)
c.fetchall()
c.execute("""select *
from emp o
where not exists (select 'x' from emp where manager_id = o.employee_id) """)
c.fetchall()
# is null / is not null
c.execute("""select *
from emp
where commission_pct is null """)
c.fetchall()
c.execute("""select *
from emp
where commission_pct is not null """)
c.fetchall()
# 날짜
c.execute('select date("now")')
c.fetchall()
# 시간형태
c.execute('select datetime("now")')
c.fetchall()
# 현 시간
c.execute('select datetime("now", "localtime")')
c.fetchall()
# 10일 후
c.execute('select date("now", "10 day")')
c.fetchall()
# 10일 전
c.execute('select date("now", "-10 day")')
c.fetchall()
# 3달 후
c.execute('select date("now", "3 month")')
c.fetchall()
# 3달 전
c.execute('select date("now", "-3 month")')
c.fetchall()
# 1년 후
c.execute('select date("now", "1 year")')
c.fetchall()
# 1년 3개월 후
c.execute('select date("now", "1 year", "3 month")')
c.fetchall()
# 일 시분초 더하기
c.execute('''select datetime("now", "localtime", "1 day", "5 hour", "60 minute", "30 second")''')
c.fetchall()
# 0:일 - 6:토
c.execute('select date("now", "weekday 5")')
c.fetchall()
# 테이블 확인
c.execute('pragma table_info(emp)')
c.fetchall()
# hire_date 문자형태, 날짜형태
c.execute("select hire_date, date(hire_date) from emp")
c.fetchall()
# 날짜 - 날짜 = 년수
c.execute("select date('now') - date(hire_date) from emp")
c.fetchall()
c.execute("""select datetime('now', 'localtime') - date(hire_date) from emp""")
c.fetchall()
# 날짜를 문자형으로 변경
c.execute("""select strftime('%Y %m %d %H %M %S %s', datetime('now', 'localtime'))""")
c.fetchall()
c.execute('select strftime("%s", "now")')
c.fetchall()
# 날짜 - 날짜 = 년수
# 초 - 초 = 초
# 초/86400 = 일수
c.execute('select (strftime("%s", "now") - strftime("%s", "2023-10-05"))/86400')
c.fetchall()
# 사원들의 근무일수
c.execute('''select (strftime("%s", "now") - strftime("%s", datetime(hire_date)))/86400 from emp''')
c.fetchall()
# 형변환 : cast() = to_number
c.execute('''select *
from emp
where date(hire_date) between date('2006-01-01') and date('2006-12-31')
and cast(strftime("%m", date(hire_date)) as integer) %2 != 0''')
c.fetchall()
# 컬럼 형태 = 문자타입
c.execute("""select strftime("%m", date(hire_date)) from emp """)
c.fetchall()
# 문자타입 사칙연산
c.execute("""select strftime("%m", date(hire_date)) %2 from emp """)
c.fetchall()
# 암시적으로 형변환이 되었다.
c.execute("""select cast(strftime("%m", date(hire_date)) as integer) %2 from emp """)
c.fetchall()
# real 타입 = 실수형
c.execute("""select cast(strftime("%m", date(hire_date)) as real) %2 from emp """)
c.fetchall()
# char 타입
c.execute("""select cast(strftime("%m", date(hire_date)) as char) %2 from emp """)
c.fetchall()
# order by 절
c.execute("""select employee_id, last_name, department_id from emp order by department_id asc """)
c.fetchall()
c.execute("""select employee_id, last_name, department_id from emp order by department_id desc """)
c.fetchall()
# order by 절: 위치표기법
c.execute("""select employee_id, last_name, department_id from emp order by 3 asc """)
c.fetchall()
c.execute("""select employee_id, last_name, department_id from emp order by 3 desc """)
c.fetchall()
# order by 절: 별칭
c.execute("""select employee_id, last_name, department_id as dept_id from emp order by dept_id asc """)
c.fetchall()
c.execute("""select employee_id, last_name, department_id as dept_id from emp order by dept_id desc """)
c.fetchall()
c.execute("""select employee_id, last_name, department_id, salary from emp order by 3 asc, 4 desc """)
c.fetchall()
# case when-then else end
c.execute("""select last_name, salary, commission_pct,
case
when commission_pct is null then salary*12
else (salary*12) + (salary*12*commission_pct)
end
from emp""")
c.fetchall()
# count()
c.execute("select count(*), count(commission_pct) from emp")
c.fetchall()
# 그룹함수
c.execute("select sum(salary), total(salary), avg(salary), max(salary), min(salary) from emp")
c.fetchall()
# group by절
c.execute("select department_id, sum(salary) from emp group by department_id")
c.fetchall()
c.execute("select department_id, job_id, sum(salary) from emp group by department_id, job_id")
c.fetchall()
# having
c.execute("""select department_id, job_id, sum(salary) from emp
group by department_id, job_id
having sum(salary) >= 20000 """)
c.fetchall()
# 숫자요일 표현
c.execute("""select strftime('%w', date(hire_date)), count(*)
from emp
group by strftime('%w', date(hire_date))""")
c.fetchall()
# 문자요일 표현, inline view
c.execute("""select
case week
when '0' then '일'
when '1' then '월'
when '2' then '화'
when '3' then '수'
when '4' then '목'
when '5' then '금'
when '6' then '토'
end||'요일', cnt
from ( select strftime('%w', date(hire_date)) week, count(*) cnt
from emp
group by strftime('%w', date(hire_date)) )""")
c.fetchall()
728x90
반응형
LIST