20210111_ Python 입문9, 데이터베이스 연동(SQLite)

Python 데이터베이스 연동(SQLite)

---

• Python의 경우 기본적으로 sqlite 패키지가 있음 굳이 설치 안해도 됨

• DB의 경우 깃허브를 사용하면 잘 알것이라고 생각한다. 저장소 느낌이라고 보면 된다.

• 장기적으로 보존해야하는 정보를 안전하게 다룰수 있고 복원도 쉬움

• 데이터 표준화, 독립성 보장, 실시간 처리 가능

1. 파이썬 데이터베이스 테이블 생성, 데이터 삽입

1) datetime 패키지를 활용한 삽입 날짜 생성

import datetime
now = datetime.datetime.now() # 나중에 현재시간을 기록할 함수를 써먹기 위해서 알아가는 거임
print('now : ' , now) # now : 연월일 시분초 밀리세컨 까지 출력

'날짜 시간 포맷 변경'
nowDatetime = now.strftime('%Y-%m-%d %H:%M:%S')
print('nowDatetime : ', nowDatetime)

• %Y : YYYY 년도
• %y : YY년도
• %M : MM 분 (Minute)
• %m : mm 월 (moon)
• %H : HH 시 두자리 시 24시제 표기
• %h : hhh 월 영문 월 약어 표기
• %D : Date 날짜 dd/mm/yy
• %d : date 일자 dd
• %S : second 초 SS
• %s (X) : 에러남 없음

 

2) sqlite3 패키지 버전 확인

print('sqlite3.version : ', sqlite3.version) # 버전확인
print('sqlite3.sqlite_version : ', sqlite3.sqlite_version) # 엔진버전 확인
# 최신버전은 pip 나 install로 가능

3) SQLiteDatabaseBrowerPortable 설치

• 해당 프로그램은 DB 구축 확인 브라우저임

4) sqlite3 연결

하여 DB파일 생성, cursor 연결

import sqlite3 # 패키지 import
conn = sqlite3.connect('C:/database.db', isolation_level=None)
# 1. conn 객체에 sqlite3패키지의 connect 함수로 연결('경로', isolation)
# isolation_level=None은 commit 안해도 자동으로 반영 되게 해놓은 거임(auto commit)
# 2. database.db 파일 생성 되었는지 확인
c = conn.cursor() # 3.  c 객체에 conn객체 커서 연결
print(type(c)) # 4.  객체 c의 타입과 dir 확인
print(dir(c)) 
conn.close() # 5. 접속해제 꼭 해야함

 

5) 쿼리 작성을 통한 테이블 생성

• '4)' 이 되어 있다는 전제 하에
• execute(): python에서 쿼리 명령어 실행하는 함수
• 기본적인 테이블 쿼리 명령문 : CRUD = C(CREATE 생성), R(RETREIVE 조회), U(UPDATE 수정), D(DELETE 삭제)

 

• 테이블 생성 (CREATE TABLE 사용)

c.execute('CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, username text, email text, phone text, website text, regedate text)')
# c.execute('CREATE(생성해라) 테이블 
# 만약 users이름의 테이블이 존재하지 않으면
#  users 테이블은
# (id필드는 integer 정수형이고 primary key 기본키 이다.,
# username 필드는 text 형이다, email 필드는 text형이다, 
# phone필드는 text형이다. website필드는 text 이다, 
# regedate등록일 필드는 text형이다.')
'브라우저에 users테이블 생성과 데이터 보기에서 필드 생성 확인'

 

• 데이터 삽입 (ISERT INTO 테이블명 VALUES() 사용)

• 데이터 삽입 방법 1

  쿼리문에 값을 넣으라고 코딩하는 법
  단, ragedate 값은 함수를 이용해야 하는데 쿼리문 내에서는 외부 함수사용 및 변수사용이 불가능함 그래서 외부 데이터를 넣어주는 포맷으로 ?를 사용함

c.execute("INSERT INTO users VALUES(1, 'KIM', 'kim@naver.com', '010-0000-0000', 'kim.com', ?)", (nowDatetime))
#  VALUES 값을 INSERT 해라 INTO users 테이블 
# VALUES 는 괄호 순서대로 넣어라 , ? 표시는 밖의 값을 ?에 넣겠다. 밖에 표시할 값은 튜플형태로 nowDatetime 을 표시함
'브라우저에서 users 테이블 데이터 값 확인'
# Auto commit 이 아니면 conn.commit() 해야 브라우저에 반영됨

 

• 데이터 삽입 방법 2

  values()에 필드 명 만큼 ? 를 채워 밖의 값을 넣는 방식
  단, values ? 개수와 외부 값의 개수가 맞아야 함

c.execute("INSERT INTO users VALUES(?, ?, ?, ?, ?, ?)" , (2, 'Kang', 'Kang@naver.com', '010-1111-1111', 'kim.com', nowDatetime))
# Auto commit 이 아니면 conn.commit() 해야 브라우저에 반영됨

 

• 데이터 삽입 방법 3 (executemany를 활용한 대용량 데이터 삽입)

  executemnay를 활용해서 list, tuple형식을 대용량 데이터를 한번에 삽입
  보통 웹상에서 입력받은 내용을 가져올때 사용

userList = (
    (3, 'woo', 'woo@naver.com', '010-2222-2222', 'woo.com', nowDatetime),
    (4, 'min', 'min@daum.com', '010-3333-3333', 'min.com', nowDatetime),
    (5, 'yoo', 'yoo@google.com', '010-4444-4444', 'yoo.net', nowDatetime)
)

c.executemany("INSERT INTO users VALUES(?, ?, ?, ?, ?, ?)", userList)

 

• 데이터 삭제 (DELETE FROM 테이블명 사용)

conn.execute("DELETE FROM users")
print("users db deleted : ", conn.execute("DELETE FROM users").rowcount) 
# rowcount 속성으로 접근하면 지워진 데이터가 몇개인지 반환

Q. 그러면 rowcount는 어디서 온 걸까?

---

2. 파이썬 데이터베이스 테이블 조회, 데이터 조회

1) 조회를 위한 fetch함수 사용

• 실제로 들고오는게 아니고 조회로만 들고오는 것임

• fetchone, fetchmany, fetchall

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
c.execute("SELECT * FROM users") # 모두 선택한다. users 테이블로 부터

print('id 1 print -> ' , c.fetchone()) 
# cursor가 SELECT한 것인 users에서 fetchone을 통해 한 줄 들고 옴 커서는 뒤로 감
print('id 2,3,4 print -> ', c.fetchmany(size=3)) # fetchmany를 통해 여러 줄의 데이터 들고옴 옵션 size가 들고올 개수를 지정하는 것
print('id 5 print -> ', c.fetchall()) # fetchall 남은 데이터 모두 들고옴

 

• fetchall 함수 , for 문 활용

• 모든 내용을 들고와사 한줄씩 순회하여 조회1

  (SELECT 할당 rows에 fetchall할당)

c.execute("SELECT * FROM users")
rows = c.fetchall() # rows 변수에 users 테이블 조회 값 할당
for row in rows:
    print('retrieve1(조회 1)', row)
    # 순회하면서 값을 조회 

 

• 모든 내용을 들고와사 한줄씩 순회하여 조회2 (권장)

  (SELECT 할당하고 fetchall할당없이 직접)

c.execute("SELECT * FROM users")
for row in c.fetchall():
    print('retrieve2(조회 2)', row)
    # 순회하면서 값을 조회 

 

• 모든 내용을 들고와사 한줄씩 순회하여 조회3

  (SELECT 할당과 fetchall할당도 없이 바로 for문에 직접 적용)
  ORDER BU id desc 는 id 필드를 내림차순으로 정렬하여라는 뜻

for row in c.excute('SELECT * FROM users ORDER BY id desc'):
    print('retrieve3(조회 3)', row)
    # 순회하면서 값을 조회 

 

2) ** 특정 값 조회를 위한 쿼리문 WHERE 사용

• WHERE + tuple 형태 바인딩 (? 사용)

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
idlist1 = (3, ) # 튜플 지정
c.execute('SELECT * FROM users WHERE id=?', idlist1)
print('idlist1 -> ' , c.fetchone()) # id 3에 해당하는 값 출력
print('idlist1 -> ' , c.fetchone()) # 더이상 데이터 가 없어 출력X

 

• WHERE + integer 형태 바인딩 ("%" 사용)

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
idlist2 = 4 # 인티저 형태로 지정
c.execute('SELECT * FROM users WHERE id="%s"' % idlist2)
print('idlist2 -> ' , c.fetchone()) # id 4에 해당하는 값 출력

 

• WHERE + dict 형태 바인딩 (:key 사용)

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
c.execute('SELECT * FROM users WHERE id=:Id', {'Id': 5, })
print('idlist3 -> ' , c.fetchone()) # id 5에 해당하는 값 출력

 

• WHERE + tuple 형태 다중 값 바인딩 (? 사용)

  쿼리문에서 IN을 사용 , fetchall 사용

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
idlist4 = (3, 5) # 튜플 지정
c.execute('SELECT * FROM users WHERE id IN(?, ?)', idlist4)
print('idlist4 -> ' , c.fetchall()) # id 3, 5에 해당하는 값 출력

 

• WHERE + tuple 형태 다중 값 바인딩 (% 사용)

  쿼리문에서 IN을 사용, fetchall 사용

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
c.execute('SELECT * FROM users WHERE id IN("%d","%d")' % (3, 4))
print('idlist5 -> ' , c.fetchall()) # id 3, 4에 해당하는 값 출력

 

• WHERE + dict 형태 다중 값 바인딩

  쿼리문에서 or 를 사용, fetchall 사용

import sqlite3
conn = sqlite3.connect('C:/datavase.db') # 연결 바인딩
c = conn.cursor() # 커서 바인딩
c.execute('SELECT * FROM users WHERE id=:id1 or id=:id2', {'id1': 2 ,'id2': 5 } 
print('idlist6 -> ' , c.fetchall()) # id 2, 5에 해당하는 값 출력

 

3. 파이썬 데이터베이스 Dump 출력

  보통 백업하고 다른 컴퓨터에서 DB잭구성시 사용, 복원을 위한 것으로 덤프 뜬다고 함
  덤프 파일 가지고 브라우저 sql문 실행하면 데이터 베이스 다시 구축 됨

with conn: # 연결 with 문
    with open('C:/dump.sql', 'w') as f: 
    # 복원 파일 제작 dump파일로서 sql확장자임
        for line in conn.iterdump(): 
        # conn(연결되어 있는 파일)iterdump 덤프 반복기 호출
            f.write('%s\n' % line) # line 을 f에 쓴다
        print('Dump Print Complete') # 덤프가 모두 쓰여지면 출력

---

3. 파이썬 데이터베이스 테이블, 데이터 수정 및 삭제, 확인

• 수정이므로 UPDATE 테이블 이름 SET 쿼리문 사용

1) 테이블, 데이터 수정

• 수정 방법 1 (? 사용)

import sqlite3
conn = sqlite3.connect('C:/database.db', isolation_level=None)
c = conn.cursor()

c.execute("UPDATE users SET username = ? WHERE id = ?", ('niceman', 2))
# users 테이블을 수정한다. username을 ?로 설정한다.(SET) (WHERE)id의 ?를  

 

• 수정 방법 2 (dict형 key사용)

c.execute("UPDATE users SET username =:name WHERE id =:id", {'name': 'goodboy', 'id': 5})
# # users 테이블을 수정한다. username을 name key값으로 설정한다.(SET) (WHERE)id의 id key값을 가진 것을 (name key의 값은 'goodboy', id key의 값은 5번) 즉, 5번 키의 값을 가진 것의 username을 'goodboy' 로 변경한다.

 

• 수정 방법 3 (tuple형 % 사용)

c.execute("UPDATE users SET username = '%s' WHERE id = '%s'" % ('goodboy',  5))

 

2) 테이블, 데이터 삭제

• 데이터 삭제 1 (기본 ? 활용)

c.execute("DELETE FROM users WHERE id = ?", (2,))

 

• 데이터 삭제 2 (dict)

c.execute("DELETE FROM users WHERE id =:id", {'id': 5 })

 

• 데이터 삭제 3 (% 활용)

c.execute("DELETE FROM users WHERE id = '%s'" % 4)

 

• 테이블 전체 삭제

print("user DB deleted : " , conn.execute("DELETE FROM users").rowcount,'줄(row)')

 

3) 중간 테이블, 데이터 확인

• 중간 데이터 확인 (기본)

for user in c.execute("SELECT * FROM users"): # users 테이블 모두 선택
    print(user)