파이썬에 기본적으로 내장된 sqllite 데이타베이스를 사용하는 방법.

1. 준비

DB경로가 명시적인 것이 좋다.

그래서 보통 프로그램 실행위치 아래 DB 라는 폴더를 만들고 그안에 데이타를 저장한다.

* DB파일의 경로 설정(현재 실행중인 폴더 아래에 DB폴더).

BASE_DIR = os.path.abspath('.')

TARGET_DIR = os.path.join(BASE_DIR, "DB")

TARGET_FILE = 'test.db'

TARGET_FILE_FULL_PATH = os.path.join(TARGET_DIR, TARGET_FILE)

* DB폴더 생성

만약에 해당 폴더가 없다면 DB 를 새로 만들거나 할때 오류가 난다.

폴더가 있나 체크해서 없으면 새로 만듬.

import os, sqlite3
BASE_DIR = os.path.abspath('.')
TARGET_DIR = os.path.join(BASE_DIR, "DB")
TARGET_FILE = 'test.db'
TARGET_FILE_FULL_PATH = os.path.join(TARGET_DIR, TARGET_FILE)

def makeDir( ):
	if not os.path.isdir( TARGET_DIR ):
		os.makedirs( TARGET_DIR )

2. DB/연결객체/커서 만들기

* 먼저 DB를 사용하려면 연결객체(connection)와 커서(cursor)를 만들어야 한다.

   .연결책체: 마치 파일을 오픈하듯 DB를 지정해서 여는 역할. 파일이 없으면 생성함.

        파일명 ' :memory:' 키워드를 사용하면 메모리에 DB파일 생성, 종료하면 없어짐

                      con = sqlite3.connect(':memory:') #메모리니 빠름. 자주 쓰는 임시 DB용.

   . 커서: 프로그램과 데이타 베이스를 연결 하는 역할.

     데이타베이스에 대한 처리는 커서를 연결객체나 커서 통해서 한다.

     처리결과가 필요할 때는 커서로만 가능 함.

makeDir(TARGET_FILE_FULL_PATH) # 폴더가 없을때 에러가 방지
con = sqlite3.connect( TARGET_FILE_FULL_PATH )
cur = con.cursor()

3. DB 테이블 생성

 - 테이블이 필드 형식 지정해서 생성.(필드형식 생략도 가능)

 - sqlite 는 필드 형식 : NULL, INTEGER, REAL, TEXT, BLOB

 -  필드의 데이터 타입 선언이 엄격하지 않고, 다양한 데이터 타입을 허용.

     INTEGER 로 형식을 지정해 놓고 텍스트 값을 입력할 수 있음

    즉, 필드타입이 우선이고 값에 따라 필드타입과 다른 데이타도 자동으로 

          최적의 타입으로변환해서 저장

  INTEGER : 1, 2, 4, 6, 또는 8바이트 크기의 값을 저장,(값에 따라 크기가 결정됨).

            INT, TINYINT,  BIGINT  는 INTEGER 로 인식

  REAL : 실수형 데이터를 저장합. 8바이트의 IEEE 부동소수점 숫자.

           FLOAT, DOUBLE, DECIMAL → REAL

  TEXT: 문자열 데이터를 저장. UTF-8, UTF-16BE, 또는 UTF-16LE 인코딩으로 저장.

           CHAR, VARCHAR(N), STRING → TEXT

   BLOB : 바이너리 데이터. 입력한 데이터 그대로 저장하며, 특별한 변환 없이 처리

              내경우 이미지파일이나 엑셀파일등을 db에 직접 넣을때 사용

   테이블의 생성(기본)

sql = ''' Create Table if not exists PhoneBook (

Name TEXT,PhoneNum TEXT,Address TEXT

) '''
cur.execute(sql)

  다른 테이블의 검색(쿼리) 결과를 테이블로 만들기

* Create Table 문뒤에 as를 쓰고 그뒤에 필드이름과 형식 대신에 select 구문을 쭉 써주면 됨.

* select 내의 필드명 뒤에 as 를 줘서 새테이블의 필드명 지정.

* 쿼리문은 대소문자 구분 없이 사용.

* 테이블이 이미 있다면 생성시 에러나므로 먼저삭제 하거나 if not Exists 붙여줌

   테이블 삭제예문 :   cur.execute( 'Drop Table If Exists new_table')

* 다른테이블을 이용해서 테이블 생성하는 구문

sql = ''' Create Table If Not Exists new_table AS
 SELECT expressions
 FROM existing_tables
 WHERE conditions; '''

예) 테이블내에 시도와 법정동이름이 같은것을 하나로 묶어 테이블 생성.

      지번 데이타(jibun ) 800 만건에서 동이름 같은것을 묶어서 시도명 ,시군구명,동이름 을가진 디비 생성

sql = '''Create Table sigudong as select 
 min(t1.SiDoName) as SiDoName, min(t1.SiGunGuName) as SiGunGuName ,
 min(t1.BJUMDName) as BJUMDName, min(t1.BJReName) as BJReName
 from jibun t1 group by t1.SiGunGuName,t1.BJUMDName , t1.BJReName
;'''

4. DB 테이블 삭제

* 테이블이 있다면 삭제.

cur.execute( 'Drop Table If Exists PhoneBook')

5. 데이타 입력.

- 기본( 필드명 없이 데이타 순서대로 ,Primary Key는 자동이므로 넣으면 안됨.)

sql = "INSERT INTO table_name VALUES( 'Kim ManJae', '010-1234-5678', '서울 용산');"
cur.execute(sql)

- 필드명 지정(순서는 상관없이 필드명과 값이 매치되면됨.)

sql ="Insert Into PhoneBook (Name, PhoneNum, Address ) VALUES('Kim ManJae', '010-1234-5678', '서울 용산');"
cur.execute(sql)

- 변수를 이용1 (튜플,리스트)

  플레이스홀더(?) 를 데이타자리에 준뒤 sql을 실행할때 튜플이나 리스트로 값을 전딜

  데이타내의 따옴표나 데이타형식을 자동변환 해주므로 가장안전한 방법 ( 권장 )

sql = "INSERT INTO PhoneBook (Name, PhoneNum, Address ) VALUES(?, ?, ?);"
data = ('Kang Minkyung', '010-8521-7896', '강원도 인제')
cur.execute( sql , data  )

   ​

- 변수를 이용2 (딕셔너리)

변수의 데이타순서는 앞의 값과 매칭만되면됨)

name, phone, addr = 'Kweon Mijin', '011-7852-2587', '서울 마포'
sql = "INSERT INTO PhoneBook (Name, PhoneNum, Address ) VALUES(:iName, :iPhone, :iAddr);"
cur.execute(sql, {'iPhone':phone, 'iName':name, 'iAddr':addr})

- 데이타리스트를 이용한 다량의 데이타 등록

   sql문을 한번만 컴파일 하므로 다량의 데이타 입력시 속도 빠름

   여러개의 데티라를 입력할때 저장할 데이타리스트를 튜플이나 리스트로 만들고 한방에 입력

   다량의 데이타를 입력할때 주로 사용

datalist =(('Tom', '010-1543-5820','제주 제주시'), ('John', '010-5152-5462','충남 부여'))
sql = "INSERT INTO PhoneBook (Name, PhoneNum, Address ) VALUES(?, ?, ?);"
cur.executemany( sql, datalist)

6. 데이타 조회

쿼리 결과는 튜플(tuple) 형식

- 모든 데이타 가져오기.

cur.execute("SELECT * FROM PhoneBook;")

- 데이타 3개만 가져오기

cur.execute("SELECT * FROM PhoneBook limit 3 ;")

- 조건에 맞는 데이타중 3번째부터 2개 가져오기.

cur.execute("SELECT * FROM PhoneBook limit 3,2 ;")

- 가져온 레코드를 하나씩 읽어서 처리

cur.execute("SELECT * FROM PhoneBook;")
for row in cur:
	print(row)

. 불러온 데이타를 한개만 읽어오기.

cur.execute("SELECT * FROM PhoneBook;")
data1 = cur.fetchone() # 쿼리 결과를 첫번째 것 자동으로 다음레코드로 포인터 이동,
data2 =cur.fetchone() # 쿼리 결과를 두번째 것 자동으로 다음레코드로 포인터 이동,

.  쿼리결과 원하는 갯수 가져오기( fetchmany )

cur.execute("SELECT * FROM PhoneBook;")
datas = cur.fetchmany(3) # 쿼리 결과를 3개 가져오기. 포인터는 자동으로 4번째로.

. 모든 쿼리 결과를 가져오기( fetchall )

   리스트 안에 튜플형태로 가져옴.

cur.execute("SELECT * FROM PhoneBook;")
datas = cur.fetchall()

결과: ( ( 'kim', '011-7852-2587, '마포'), ( 'lee', 010-1234-2345','강원도'), ... )

* 튜플을 없애고 필드를 연결해서 한개의 문자열 리스트로

strdatas = [ data[0] +' '+ data[1]+' '+ data[2] for data in datas ]

결과 : [ 'kim 011-7852-2587 마포', 'lee 010-1234-2345 강원도', ... ]

참조1 : cursor 객체는 순차 읽기만 가능. 결과는 튜플로 반환되기 때문에 수정불가

           따라서 데이타를를 수정/삭제하려면 아래와 같이 쿼리 결과를 list 형식으로 를 만들어 사용.

cur.execute("SELECT * FROM PhoneBook;")
datas = cur.fetchall()
datas = [ list(data) for data in datas ]

참조2: 쿼리 결과가 몇건인지 나타내는 cur.rowcount 는 Python 에서는 작동안함.

           쿼리문으로 쿼리 결과 갯수를 알아 내야함.

cur.execute("SELECT count() FROM PhoneBook;")
rowcnt = cur.fetchone()

7. 데이타 닫기.

데이타를 추가 했거나 업데이트 했으면 받드시 commit 해야함.

DB사용이 끝났으면 반드시 닫아야 한다.

con.commit()

cur.close()

con.close()