Python, 데이터베이스의 사용

Gerhard Haring은 C기반의 SQLite3를 이용하여 DB-API2.0스펙을 따르는 인터페이스를 제공하는 pysqlite모듈을 작성하였다. 이 모듈은 파이썬 배포판에 기본적으로 포함되어 있으므로 별도의 모듈을 설치하지 않고도 바로 사용이 가능하다. pysqlite모듈에 정의된 함수와 클래스는 다음과 같다.

 

-. 모듈 함수

pysqlite모듈에는 데이터베이스 연결(connect)과 가팅 전역적으로 사용되는 함수들이 정의되어 있다.

>sqlite3.connect(database[, timeout, isolation_level, detect_types, factory])

SQLite3 DB에 연결하고 연결된 Connection객체를 반환

 

>sqlite3.complete_statement(sql)

세미콜론으로 끝나는 SQL문장에 대해서 True를 반환한다.

이 함수는 단지 세미콜론으로 종결유무만 검사하고, SQL문장의 구문이 올바른지는 확인하지 않는다.

 

>sqlite3.register_adapter(type, callable)

사용자정의 파이썬 자료형을 SQLite3에서 사용하도록 등록한다.

callable은 이러한 변환을 수행하는 함수로 1개의 인자를 받아서 파이썬에서 처리가능한 자료형으로 변환한다.

 

>sqlite3.register_converter(typename, callable)

SQLite3에 저장된 자료를 사용자정의 자료형으로 변환하는 함수를 등록한다.

typename은 SQLite3에서 내부적으로 사용될 자료형의 이름이고, callable은 변환을 위해 사용되는 함수이다.

 

 

-. Connection클래스

연결된 데이터베이스를 동작시키는 역할을 한다.

>Connection.cursor()

Cursor객체를 생성한다.

 

>Connection.commit()

현재 트랜잭션의 변경내역을 DB에 반영(commit)한다.

이 메서드를 명시적으로 호출하지 않으면 작업한 내용이 DB에 반영되지 않으므로, 다른 연결(Connection)에 그 내용이 나타나지 않는다.

 

>Connection.rollback()

가장 최근의 commit()이후 지금까지 작업한 내용에 대해서 DB에 반영하지 않고, 트랜잭션 이전 상태로 되돌린다.

 

>Connection.close()

DB연결을 종료한다.

자동으로 commit()메서드를 호출하는것이 아니기에, close()메서드를 호출하기 이전에 commit()/rollback()중 하나를 명시적으로 호출해야 한다.

 

>Connection.isolation_level

트랜잭션의 격리수준(isolation level)을 확인/설정한다.

입력가능한 값은 None, DEFERRED, IMMEDIATE, EXCLUSIVE이다.

 

>Connection.execute(sql[, parameters])

>Connection.executemany(sql[, parameters])

>Connection.executescript(sql_script)

임시 Cursor객체를 생성하여 해당 execute계열메서드를 수행한다

(Cursor클래스의 해당 메서드와 동일하므로 Cursor클래스에서 설명하겠다.)

 

>Connection.create_aggregate(name, num_params, aggregate_class)

사용자정의 집계(aggregate)함수를 생성한다.

 

>Connection.create_collation(name, callable)

문자열 정렬시 SQL구문에서 사용될 이름(name)과 정렬 함수를 지정한다.

정렬함수는 인자로 문자열 2개를 받으며, 첫 문자열이 두번째 문자열보다 순서가 낮은경우 -1, 같은경우 0, 높은경우 1을 반환해야 한다.

 

>Connection.iterdump()

연결된 DB의 내용을 SQL질의 형태로 출력할수 있는 이터레이터를 반환

 

 

-. Cursor클래스

실질적으로 데이터베이스에 SQL문장을 수행하고, 주회된 결과를 가지고 오는 역할을 한다.

>Cursor.execute(sql[, parameters])

SQL문장을 실행한다. 실행할 문장은 인자를 가질수 있다.

 

>Cursor.executemany(sql, seq_of_parameters)

동일한 SQL문장을 파라미터만 변경하며 수행한다.

파라미터 변경은 파라미터 시퀸스, 이터레이터를 이용할수 있다.

 

>Curosr.executescript(sql_script)

세미콜론으로 구분된 연속된 SQL문장을 수행한다.

 

>Cursor.fetchone()

조회된 결과(Record Set)로부터 데이터 1개를 반환한다. 더 이상 데이터가 없는 경우 None을 반환한다.

 

>Cursor.fetchmany([size=cursor.arraysize])

조회된 결과로부터 입력받은 size만큼의 데이터를 리스트 형태로 반환한다.

데이터가 없는 경우 빈 리스트를 반환한다.

 

>Cursor.fetchall()

조회된 결과 모두를 리스트형태로 반환한다.

데이터가 없는 경우, 빈리스트를 반환한다.

 

-. Row 클래스

조회된 결과 집합(Result set)에서 Row객체는 관계형 데이터베이스 모델에서 튜플을 나타낸다.

예를 들어 JOIN연산을 이용하여 2개 이상의 테이블을 조회한 결과인 경우, Row객체는 결과 뷰의 한 행을 나타낸다.

 

>> 데이터베이스 연결 <<

데이터베이스를 사용하려면 실제 저장된 데이터베이스 파일을 반영하는 Connection객체를 생성하여야 한다.

아래는 "test.db"파일과 연결된 Connection객체를 생성하는 예제이다.

 

해당하는 물리적인 DB파일이 없는 경우 해당 경로에 파일을 생성하며, 파일이 이미 존재하는 경우 그 DB파일을 그대로 사용한다. 이때 DB파일에 이미 테이블이 생성되거나 레코드가 입력된 경우, 그러한 데이터들도 모두 Connection객체를 통해 조회, 입력등의 연산이 가능하다.

>>> import sqlite3 >>> con=sqlite3.connect("test.db")

또한 ":memory:"라는 키워드를 사용하여, 메모리상에 DB파일을 만들수 있다. 이러한 경우 연결이 종료되면 현재까지 작업한 모든 내용이 사라지게 되지만, 물리적인 DB파일에 기록하는 것보다는 연산속도가 빠르다.

>>> con=sqlite3.connect(":memory:")

 

>> SQL문 수행 <<

데이터베이스에서 SQL문을 수행하는 execute계열 메서드부터 알아보자

기본적으로 Cursor.execute()메서드는 SQL문을 입력받아 수행한다.

아래는 전화번호부 테이블을 생성하고, 레코드 하나를 입력하는 예제이다.

>>> import sqlite3 >>> con=sqlite3.connect(":memory:") >>> cur=con.cursor() >>> cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);") <sqlite3.Cursor object at 0x01028EE0> >>> cur.execute("INSERT INTO PhoneBook VALUES('Derick', '010-1234-5678');") <sqlite3.Cursor object at 0x01028EE0>

 

실제 데이터베이스 프로그래밍을 하는 경우, 사용하는 SQL구문이 매번 동일하지만 입력되는 인자만 바뀌는 경우가 대부분이다. pysqlite에서도 이러한 경우를 위해 인자 전달방식을 지원한다.

Cursor.execute()함수의 SQL구문에서 인자로 채워질 부분을 "?"로 표시하고, 해당하는 인자를 시퀸스 객체로 전달한다.

아래는 INSERT구문을 인자 전달방식으로 작성한 예이다.

>>> name = "Youngjae" >>> phoneNumber = "010-1234-5678" >>> cur.execute("INSERT INTO PhoneBook VALUES(?, ?);", (name, phoneNumber)) <sqlite3.Cursor object at 0x01028EE0>

"?"를 이용한 인자 전달방식을 사용하는 경우, 개발자는 SQL구문의 인자 전달순서를 모두 기억하여 그에 맞도록 시퀸스 객체를 전달해야 한다. SQL구문의 각 인자에 이름을 부여하고 인자를 전달할때 사전을 전달하면, 이러한 불편함을 해결할수 있다.

 

예를 들어 아래와 같이 작성하여, 이전과 동일한 동작을 수행할수 있다.

>>> cur.execute("INSERT INTO PhoneBook VALUES(:inputName, :inputNum);", {"inputNum":phoneNumber, "inputName":name}) <sqlite3.Cursor object at 0x01028EE0>

 

동일한 SQL문장을 매개변수만 바꾸며 연속적으로 수행하는 경우, Cursor.executemany()를 이용할수 있다. 인자 전달방식과 동일하게 SQL구문을 작성하고, 두번째 인자에 시퀸스와 사전을 전달하면 된다.

아래는 리스트객체를 이용하여 2개의 레코드를 연속적으로 입력하는 예이다.

>>> datalist = (('Tom', '010-111-1111'),('DSP', '010-222-2222')) >>> cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", datalist) <sqlite3.Cursor object at 0x01028EE0>

 

또한 시퀸스객체 대신에 이터레이터나 제네레이터를 이용할수도 있다.

아래는 제네레이터를 이용하여 위와 동일한 동작을 수행하는 예이다.

>>> def dataGenerator():              datalist = {('Tom','010-111-1111'),('DSP','010-222-2222')}              for item in datalist:                            yield item   >>> cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", dataGenerator()) <sqlite3.Cursor object at 0x01028EE0>

 

수행해야 할 SQL구문을 파일에 저장하고 이 모두를 한번에 수행해야 한다면, Cursor.executescript()메서드가 유용하다.

이 메서드는 스크립트수행하기 이전에 우선 cummit()을 수행하여 이전에 작업한 내용을 모두 DB에 반영하고, 그 다음에 입력된 스크립트를 수행한다.

 

아래의 예제는 script.txt파일에 저장된 SQL구문을 읽어서 일괄 수행하는 예제이다.

import sqlite3 con = sqlite3.connect(":memory:") with open('script.txt') as f:     SQLScript = f.read() cur = con.cursor() cur.executescript(SQLScript)

 

>> 레코드 조회 <<

입력된 데이터를 데이터베이스로부터 가지고 오는(fetch) 메서드에 대해서 알아보자

execute()메서드를 이용하여 "SELECT"문을 수행하면, Cursor객체를 조회된 Row객체에 대한 이터레이터처럼 사용할수 있다.

>>> cur.execute("SELECT * FROM PhoneBook;") <sqlite3.Cursor object at 0x01028EE0> >>> for row in cur:              print(row)   ('Derick', '010-1234-5678') ('Youngjae', '010-1234-5678') ('Youngjae', '010-1234-5678') ('Tom', '010-111-1111') ('DSP', '010-222-2222') ('Tom', '010-111-1111') ('DSP', '010-222-2222')

 

Cursor.fetchone()은 조회된 결과 집합으로부터 Row객체를 가지고 온다.

Cursor.fetchmany(n)는 주회된 결과에서 인자로 입력된 'n'개 만큼 Row를 리스트 형태로 반환한다.

아래와 같이 앞서 fetchone()메서드로 1개의 레코드를 조회한 이후, fetchmany()를 호출하면 이미 조회된 1개의 레코드를 제외한 다음 레코드부터 반환한다. 만약 조회된 전체 결과 집합보다 큰 값이 인자로 입력되면, 조회된 결과를 모두 반환한다.

>>> cur.execute("SELECT * FROM PhoneBook;") <sqlite3.Cursor object at 0x01028EE0> >>> cur.fetchone() ('Derick', '010-1234-5678') >>> cur.fetchmany(2) [('Youngjae', '010-1234-5678'), ('Youngjae', '010-1234-5678')]

 

Cursor.fetchall()은 fetchmany()와 유사하게 조회된 결과의 다음 Row부터 모든 레코드를 리스트형태로 반환한다.

>>> cur.execute("SELECT * FROM PhoneBook;") <sqlite3.Cursor object at 0x01028EE0> >>> cur.fetchone() ('Derick', '010-1234-5678') >>> cur.fetchall() [('Youngjae', '010-1234-5678'), ('Youngjae', '010-1234-5678'), ('Tom', '010-111-1111'), ('DSP', '010-222-2222'), ('Tom', '010-111-1111'), ('DSP', '010-222-2222')]

 

>> 트랜잭션 처리 <<

트랜잭션(Transaction)이란?

트랜잭션은 데이터베이스에서 논리적 작업의 단위이다. 예를 들어 A통장에서 B통장으로 계좌이체를 진행할때, 비정상적인 정전이나 운영체제의 문제로 인하여 1번의 동작은 진행하고 2번이 수행되지 않는다면 계좌의 돈은 아무도 모르게 사라지게 된다.   1) A통장에서 100만원 출금        <-- 수행완료후 정전 2) B통장으로 출금한 100만원 입금 <-- 이체는 되지 않고, 100만원만 사라짐   이러한 문제를 막고자 연산을 트랜잭션으로 묶어서 두 개별작업이 하나의 연산처럼 성공과 실패가 분명하도록 만들고, 이러한 논리적 작업단위를 트랜잭션이라고 한다.   1) 트랜잭션 시작 2) A통장에서 100만원 출금         <-- 정전이 되어도 A통장에는 commit되기 전까지 인출되지 않음 3) B통장으로 출금한 100만원 입금 4) 트랜잭션 commit/rollback   트랜잭션 내에서 변경된 사항들은 데이터베이스에 반영(commit)되어 영구히 저장하거나, 롤백하여 트랜잭션수행이전상태로 복원할수 있다.

 

트랜잭션(transaction)처리를 보기에 앞서 우선 아래의 코드를 작성후 수행해보겠음

수행 이후 당연히 결과레코드가 출력될것이고, 'test.db'파일이 생성될것이다.

 

ex)작업한 내용이 cummit되지 않는 예제

import sqlite3 con = sqlite3.connect("./test.db") cur = con.cursor() cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);") cur.execute("INSERT INTO PhoneBook VALUES('Derick', '010-111-1111');") cur.execute("SELECT * FROM PhoneBook;") print(cur.fetchall())

실행결과

[('Derick', '010-111-1111')]

 

그럼 다시 아래의 코드를 수행해 보겠다. 쉽게 알수 있듯이 위으 코드에서 테이블생성과 레코드입력을 제외한 단지 조회만 하는 코드이다.

IDLE에서 수행을 한다면 쉘을 초기화(Crtl + F6)작업후 아래의 코드를 수행하면 된다.

 

ex)작업한 내용이 cummit되지 않는 결과 확인

import sqlite3 con = sqlite3.connect("./test.db") cur = con.cursor() cur.execute("SELECT * FROM PhoneBook;") print(cur.fetchall())

실행결과

[]

 

Connection.commit()을 호출하면 수행한 SQL구문을 데이터베이스에 반영할수 있다.

import sqlite3 con = sqlite3.connect("./commit.db") cur = con.cursor() cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);") cur.execute("INSERT INTO PhoneBook Values('Derick', '010-111-1111');") con.commit()

 

그 후에 DB에 수행한 내용을 정상적으로 반영되었는지 확인해보겠다.

위 예제를 실행후 아래는 새파일을 만들어 작성후 실행하였다.

import sqlite3 con = sqlite3.connect("./commit.db") cur = con.cursor() cur.execute("SELECT * FROM phoneBook;") print(cur.fetchall())

실행결과

[('Derick', '010-111-1111')]

 

위으 수행결과에서 정상적으로 데이터베이스에 반영되어 있는것을 확인할수 있다.

또한 SQL구문을 수행할때마다 명시적인 commit() 호출없이 데이터베이스에 반영하기 위하여, '자동 커밋모드(Autocommit mode)'로 설정할수 있다.

Connection객체의 'isolation_level'속성에 None을 입력하여 이러한 모드 변환을 설정할수 있다.

>>> con.isolation_level = None

 

>> 레코드정렬과 사용자정렬 함수 <<

지금까지 레코드조회결과는 입력된 순서대로 정렬이 되어있었다. 하지만 실제 데이터베이스 프로그래밍을 하는 경우 특별한 정렬조건에 따라서 결과 집합을 생성해야 하는 경우가 많다.

아래는 'ORDER BY'구문을 이용하여 'Name'필드를 알파벳순으로 정렬하는 예이다.

>>> cur.execute("SELECT * FROM PhoneBook ORDER BY Name") <sqlite3.Cursor object at 0x01028F60> >>> [r for r in cur] [('DSP', '010-222-2222'), ('Derick', '010-111-1111'), ('Tom', '010-333-3333'), ('YJ', '010-444-4444')]

 

다음은 'Name'필드를 알파벳 역순으로 정렬한 예이다.

>>> cur.execute("SELECT * FROM PhoneBook ORDER BY Name DESC") <sqlite3.Cursor object at 0x01028F60> >>> [r for r in cur] [('YJ', '010-444-4444'), ('Tom', '010-333-3333'), ('Derick', '010-111-1111'), ('DSP', '010-222-2222')]

 

아래와 같이 Name이 'apple'인 레코드를 하나 추가하고 Name순으로 정렬하면, 대문자가 먼저 정렬된 이후 소문자가 정렬되어 출력되는것을 알수 있다.

>>> cur.execute("INSERT INTO PhoneBook VALUES('apple', '010-555-5555');") <sqlite3.Cursor object at 0x01028F60> >>> cur.execute("SELECT Name FROM PhoneBook ORDER BY Name") <sqlite3.Cursor object at 0x01028F60> >>> [r[0] for r in cur] ['DSP', 'Derick', 'Tom', 'YJ', 'apple']

 

만약 이러한 정렬방식 대신에 사용자 임의로 정렬방식을 변경하고자 하는 경우, 어떻게 해야할까?

이러한 경우 사용자가 정렬함수를 미리 정의하고, Connection.create_collation()메서드를 이용하여 DB에 등록한뒤, SELECT문에서 이 정렬방식을 명시적으로 지정하여 처리할수 있다. 사용자 정렬함수는 두 문자열을 인자로 받아서 비교한 결과를 반환한다.

첫번째 인자가 두번째 인자보다 순서상 앞선 경우 음수를, 같은경우 '0'을, 뒤인 경우 양수를 반환하도록작성해야 한다.

 

아래는 대소문자 구별없이 Name필드를 기준으로 정렬하는 예이다.

>>> def OrderFunc(str1,str2):              s1 = str1.upper()              s2 = str2.upper()              return (s1 > s2) - (s1 < s2)   >>> con.create_collation('myordering', OrderFunc) >>> cur.execute("SELECT Name FROM PhoneBook ORDER BY Name COLLATE myordering") <sqlite3.Cursor object at 0x01028F60> >>> [r[0] for r in cur] ['apple', 'Derick', 'DSP', 'Tom', 'YJ']

 

>> SQLite3 내장 집계 함수 <<

SELECT문을 이요앟여 데이터베이스를 조회할 경우, 미리 SQLite3에 정의되어 있는 내장집계(aggregate)함수를 이용하여 개발자의 용도에 맞게 결과값을 가공할수 있다.

예를 들어 특정 필드의 최댓값, 최솟값, 평균값과 같은 수치 연산이나 문자열의 대/소문자 변환, 조건에 해당하는 튜플의 개수등이 있다.

아래의 표는 지원하는 내장함수의 목록이다.

함수	설명
abs(x)	인자의 절대값을 반환
length(x)	문자열의 길이를 반환
lower(x)	인자로 받은 문자열을 소문자로 반환 원본문자열은 변화없음
upper(x)	인자로 받은 문자열을 대문자로 반환 원본문자열은 변화없음
min(x, y, ...)	인자 중 가장 작은 값을 반환
max(x, y, ...)	인자 중 가장 큰 값을 반환
random(*)	임의의 정수를 반환
count(x)	조회 결과 중 필드 인자가 NULL이 아닌 튜플의 개수를 반환
count(*)	조회 결과의 튜플의 개수를 반환
sum(x)	조회 결과중 필드 인자희 합을 반환

 

ex)

import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor()   cur.execute("CREATE TABLE PhoneBook(Name text, Age integer);") lst = (('Tom', 24), ('Derick', 30), ('Peter',53), ('Jane', 29)) cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", lst)   cur.execute("SELECT length(Name), upper(Name), lower(Name) FROM PhoneBook") #문자열길이, 대문자, 소문자 print("== length(), upper(), lower() ==") print([r for r in cur])   cur.execute("SELECT max(Age), min(Age), sum(Age) FROM PhoneBook") #최댓값, 최솟값, 총합 print("== max(), min(), sum() ==") print([r for r in cur])   cur.execute("SELECT count(*), random(*) FROM PhoneBook") #레코드갯수, 임의의 값 print("== count(*), random(*) ==") print([r for r in cur])

실행결과

== length(), upper(), lower() == [(3, 'TOM', 'tom'), (6, 'DERICK', 'derick'), (5, 'PETER', 'peter'), (4, 'JANE', 'jane')] == max(), min(), sum() == [(53, 24, 136)] == count(*), random(*) == [(4, 4521323752561011741)]

 

>> 사용자정의 집계 함수 <<

Sqlite3에서 지원하는 내장 집계 함수만으로 부족한 경우가 있다. 이런 경우에는 사용자가 직접 집계를 위한 클래스를 작성하여 Connection.create_aggregate()메서드를 이용하여 등록할수 있다.

 

이 클래스에는 step()과 finalize()메서드가 정의되어야 한다.

step()은 create_aggregate()메서드에 등록시 지정된 인자의 개수만큼 인자를 전달받으며, finalize()에 이렇게 집계된 결과가 반환한다. 조회된 결과 집합으로부터 평균을 반환하는 간단한 예제를 통하여 좀더 자세히 알아보자

아래는 조회된 값의 평균을 산출하는 클래스 정의이다.

import sqlite3   class Average:     def __init__(self):         self.sum=0         self.cnt=0       def step(self, value):         self.sum += value         self.cnt += 1       def finalize(self):         return self.sum / self.cnt   con = sqlite3.connect(":memory:") cur = con.cursor()   cur.execute("CREATE TABLE User(Name text, Age int);") lst = (('Tom', '16'), ('Dsp', '33'), ('Derick', '25')) cur.executemany("INSERT INTO User VALUES(?, ?);", lst)   con.create_aggregate("avg", 1, Average)   cur.execute("SELECT avg(Age) FROM User") print(cur.fetchone()[0])

실행결과

24.666666666666668

위처럼 정의된 클래스는 Connection.create_aggregate()메서드를 호출하여 DB에 등록하여야 사용할수 있다.

create_aggregate()은 SQL구문에서 사용될 이름, step() 함수에 전달될 인자의 갯수, 클래스명을 순차적으로 인자로 입력받는다.

 

>> 자료형 <<

SQLite3는 NULL, INTEGER, REAL, TEXT, BLOB(바이너리 파일을 데이터베이스에 직접 저장)만을 기본 자료형으로 제공한다.

아래는 SQLite3에 자료형과 그에 해당하는 파이썬 자료형이다.

SQLite3 자료형	Python 자료형
NULL	None
INTEGER	int
REAL	float
TEXT	str, bytes
BLOB	buffer

 

기본으로 제공되는 자료형은 SQLite3와 Python은 특별한 변환없이 상호사용이 가능하다.

아래는 SQLite3자료형과 그에 해당하는 Python의 자료형을 사용하여 동일한 스키마를 갖는 테이블을 각각 생성하는 예제이다.

이렇게 생성된 두 테이블에 대해서 아래와 같이 동일한 INSERT문을 사용하여 레코드 입력이 가능하다.

import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("CREATE TABLE tbl_1(Name TEXT, Age INTEGER, Money REAL);") #SQLite3자료형으로 tbl_1테이블 생성   cur.execute("CREATE TABLE tbl_2(Name str, Age int, Money float);") #Python의 자료형으로 tbl_2테이블 생성   cur.execute("INSERT INTO tbl_1 VALUES('Tiger', 25, 123.45);") cur.execute("INSERT INTO tbl_2 VALUES('Tiger', 25, 123.45);") #동일한 방법으로 두 테이블에 레코드 입력 가능

 

>> 사용자정의 자료형 <<

프로젝트를 진행하다 보면 SQLite3에서 정의한 5개의 자료형만을 이용하여 구현하기에는 부족함을 많이 느낀다.

예를 들어 Python에서는 개인의 신상정보(이름, 전화번호, 주소, 나이)를 클래스로 작성하지만 SQLite3에서는 클래스를 직접 입력할수 없어서 TEXT, INTEGER 등의 자료형으로 풀어서 입력해야 하고, 조회환 결과도 클래스형태가 아닌 개별 자료형으로 받게 된다.

이러한 경우 클래스와 같은 사용자정의 자료형을 SQLite3에 등록하여, 직접 클래스 객체를 DB에 입력할수 있다.

그럼 2차원의 좌표를 나타내는 Point클래스를 SQLite3자료형으로 등록하고 직접 입력, 조회화는 예제를 통해 자세한 내용을 알아보자

아래에 나오는 예제는 하나의 문서에서 작성되었다.

 

ex) 사용자정의자료형(Point클래스)

1 class Point(object):  2     def __init__(self, x, y):  3         self.x = x  4         self.y = y  5          6     def __repr__(self): #Point객체의 내용 출력  7         return "point(%f, %f)" % (self.x, self.y)  8

 

이렇게 작성된 Point클래스를 SQLite3에서 입력/조회하기 위해서는 변환함수를 작성해야 한다.

SQLite3는 5개의 기본자료형만을 입력받을수 있기 때문에, 클래스객체를 SQLite3기본 자료형형태로 변환하는 함수가 필요하다. 반면에 조회결과는 기본자료형으로 변환된 형태이기 때문에, 해당하는 클래스 객체에 맞도록 복원해주는 함수가 필요하다.

 

아래의 예제에서 PointAdapter()함수는 사용자정의 자료형을 SQLite3에서 사용가능한 형태로 변환하는 함수이다. 콜론(:)을 구분자로 하여 x, y좌표를 TEXT형태로 반환한다.

또한 PointConverter()함수는 SQLite3에서 조회된 결과를 클래스 객체 형태로 변환하는 함수로, 콜론으로 두 값을 구분하여 Point객체를 생성하여 반환한다.

 

ex)사용자정의자료형(Point클래스 입출력용함수)

9 import sqlite3 10 11 def PointAdapter(point): #클래스객체에서 SQLite3입력가능한 자료형으로 변환 12     return "%f:%f" % (point.x, point.y) 13 14 def PointConverter(s): #SQLite3에서 조회환 결과를 클래스객체로 변환 15     x, y = list(map(float, s.decode().split(":"))) 16     return Point(x, y) 17

 

작성된 변환 함수들은 DB에 등록되어야, 명시적인 변환과정 없이 내부적으로 자동변환되어 사용할수 있다.

sqlite3.register_adapter()를 이용하여 Python 클래스를 SQLite3처리 가능한 자료형으로 변환하는 함수를 등록한다.

인자로는 <Python 자료형>과 <변환 함수>를 입력한다. 동일하게 sqlite3.register_converter()를 이용하여 SQLite3에서 사용된 자료형을 Python클래스로 변환하는 함수를 등록한다. 이때에는 인자로 SQL구문에서 사용될 <SQLite3자료형>과 <변환함수>를 입력한다.

- sqlite3.register_adapter(<Python자료형>,<변환함수>)

- sqlite3.register_converter(<SQLite3자료형>,<변환함수>)

예제에서는 아래와 같이 "point"라는 자료형을 데이터베이스 등록하였기 때문에, 이제부터 내장자료형처럼 사용이 가능하다.

 

ex)사용자정의자료형(클래스와 함수를 데이터베이스에 등록)

18 sqlite3.register_adapter(Point, PointAdapter) #클래스이름과 변환함수 등록 19 sqlite3.register_converter("point", PointConverter) #SQL구문에서 사용할 자료형이름과 변환함수 등록 20

 

이러한 등록 과정을 마치게 되면, SQLite3내에서 내장 자료형과 동일하게 레코드를 입력/조회할수 있다. 아래의 예제는 Point객체 2개를 DB에 입력하고 조회하는 예제이다.

 

ex)사용자정의자료형(Point객체 생성, DB입력/조회)

21 p = Point(4, -3.2) 22 p2 = Point(-1.4, 6.2) #입력할 데이터(Python 클래스객체) 23 24 con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES) #암묵적으로 선언된 자료형으로 조회하도록 설정 25 cur = con.cursor() 26 cur.execute("create table test(p point)") #point자료형을 이용하여 테이블생성 27 cur.execute("insert into test values (?)", (p, )) #point레코드 입력 28 cur.execute("insert into test(p) values (?)", (p2,)) 29 30 cur.execute("select p from test") #테이블 조회 31 print([r[0] for r in cur]) 32 cur.close() 33 con.close()

 

실행결과

[point(4.000000, -3.200000), point(-1.400000, 6.200000)]

 

>> 데이터베이스 덤프 만들기 <<

데이터베이스의 이동이나 백업등의 이유로 현재 상태를 SQL구문으로 추출해야 하는 경우가 있다. 이러한 경우 Connection.iterdump()를 이용하면 쉽게 이와 같은 작업을 수행할수 있다.

호출결과로 SQL구문을 이터레이터 형태로 반환한다.

import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor()   cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);") cur.execute("INSERT INTO PhoneBook VALUES('Derick', '010-111-1111');") lst = (('Tom', '010-222-2222'), ('DSP', '010-333-3333')) cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);",lst)   for l in con.iterdump():     print(l)

실행결과

BEGIN TRANSACTION; CREATE TABLE PhoneBook(Name text, PhoneNum text); INSERT INTO "PhoneBook" VALUES('Derick','010-111-1111'); INSERT INTO "PhoneBook" VALUES('Tom','010-222-2222'); INSERT INTO "PhoneBook" VALUES('DSP','010-333-3333'); COMMIT;

 

아래의 예제처럼 파일에 덤프한 내용을 저장할수도 있다.

with open('dump.sql','w') as f:     for l in con.iterdump():         f.write('{0}\n'.format(l))

 

 

참조 : 빠르게 활용하는 파이썬3 프로그래밍