14. 데이터베이스 사용

데이터베이스 사용

1. SQLite3

- 디스크 기반 데이터베이스 라이브러리

- 서버가 필요없는 데이터베이스 솔루션

- 적은 자원 사용

- 트랙잭션 지원으로 데이터 무결성 보장

* 참고: Mac에 기본적으로 SQLite3가 설치 되어 있음(sqlite3 명령어를 통해 명령프롬프트에서 사용 가능)

2. Gerhard Haring

- C기반의 SQLite3 이용

- DB-API 2.0 스펙을 따르는 인터페이스를 제공하는 pysqlite 모듈을 작성 함

* 참고:  DB-API는 관계형 데이터베이스를 사용하기 위한 공통 인터페이스 규약

(자세한 내용은 https://wiki.python.org/moin/DatabaseProgramming 참고)

- 파이썬 배포판에 기본적으로 포함됨, 별도의 모듈을 설치하지 않고도 바로 사용이 가능

(eclipse에서 사용할 수 있는 SQLite3 플러그인을 검색해봤지만 적당한 것을 찾을 수 없었음...)

모듈 함수

1. 전역 함수

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

1) sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

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

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

Opens a connection to the SQLite database file database. You can use":memory:" to open a database connection to a database that resides in RAM instead of on disk.

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

For the isolation_level parameter, please see the Connection.isolation_levelproperty of Connection objects.

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself. Thedetect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.

detect_types defaults to 0 (i. e. off, no type detection), you can set it to any combination of PARSE_DECLTYPES and PARSE_COLNAMES to turn type detection on.

By default, the sqlite3 module uses its Connection class for the connect call. You can, however, subclass the Connection class and make connect() use your class instead by providing your class for the factory parameter.

Consult the section SQLite and Python types of this manual for details.

The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 100 statements.

If uri is true, database is interpreted as a URI. This allows you to specify options. For example, to open a database in read-only mode you can use:

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

More information about this feature, including a list of recognized options, can be found in the SQLite URI documentation.

Changed in version 3.4: Added the uri parameter.

2) sqlite3.complete_statement(sql)

- 세미콜론으로 끝나는 SQL 문장에 대해서 True를 반환, SQL문장의 구문이 올바른지 확인 안함

sqlite3.complete_statement(sql)

Returns True if the string sql contains one or more complete SQL statements terminated by semicolons. It does not verify that the SQL is syntactically correct, only that there are no unclosed string literals and the statement is terminated by a semicolon.

This can be used to build a shell for SQLite, as in the following example:

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")

while True:
    line = input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print(cur.fetchall())
        except sqlite3.Error as e:
            print("An error occurred:", e.args[0])
        buffer = ""

con.close()

3) sqlite3.register_adapter(type, callable)

- 사용자지정 파이썬 자료형을 SQLite3에서 사용하도록 등록

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

sqlite3.register_adapter(type, callable)

Registers a callable to convert the custom Python type type into one of SQLite’s supported types. The callable callable accepts as single parameter the Python value, and must return a value of the following types: int, float, str or bytes.

4) sqlite3.register_converter(typename, callable)

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

- typename: SQLite3에서 내부적으로 사용될 자료형의 이름

- callable: 변환을 위해 사용되는 함수

sqlite3.register_converter(typename, callable)

Registers a callable to convert a bytestring from the database into a custom Python type. The callable will be invoked for all database values that are of the type typename. Confer the parameter detect_types of the connect() function for how the type detection works. Note that the case of typename and the name of the type in your query must match!

Connection 클래스

- 연결된 데이터베이스를 동작시키는 역할

1. Connection.cursor(): Cursor 객체를 생성함

cursor([cursorClass])

The cursor method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor.

2. Connection.commit()

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

- 메서드를 명시적으로 호출하지 않으면 작업한 내용이 DB에 반영되지 않음

commit()

This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

3. Connection.isolation_level

- 트랜잭션의 격리 수준(isolation_level)을 확인/설정

- 입력가능한 값은 None, DEFERRED, IMMEDIATE, EXCLUSIVE

isolation_level

Get or set the current isolation level. None for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See section Controlling Transactions for a more detailed explanation.

4. Connection.rollback()

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

rollback()

This method rolls back any changes to the database since the last call to commit().

5. Connection.close(): 

- DB연결을 종료

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

close()

This closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!

6. Connection.execute(), Connection.executemany(sql[, parameters]), Connection.executescript(sql_script)

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

- Cursor 클래스의 해당 메서드와 동일함

7. Connection.create_aggregate(name, num_params, aggregate_class): 사용자정의 집계(aggregate)함수를 생성

create_aggregate(name, num_params, aggregate_class)

Creates a user-defined aggregate function.

The aggregate class must implement a step method, which accepts the number of parameters num_params, and a finalize method which will return the final result of the aggregate.

The finalize method can return any of the types supported by SQLite: bytes, str, int, float and None.

Example:

import sqlite3

class MySum:
    def __init__(self):
        self.count = 0

    def step(self, value):
        self.count += value

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print(cur.fetchone()[0])

8. Connection.create_collation(name, callable)

- 문자열 정렬시 SQL 구문에서 사용될 이름과 정렬함수를 지정

- 정렬함수는 인자로 문자열 2개를 받음

- 첫 문자열이 두번째 문자열 보다 순서가 낮은 경우 -1, 같은 경우 0, 높은 경우 1을 반환

create_collation(name, callable)

Creates a collation with the specified name and callable. The callable will be passed two string arguments. It should return -1 if the first is ordered lower than the second, 0 if they are ordered equal and 1 if the first is ordered higher than the second. Note that this controls sorting (ORDER BY in SQL) so your comparisons don’t affect other SQL operations.

Note that the callable will get its parameters as Python bytestrings, which will normally be encoded in UTF-8.

The following example shows a custom collation that sorts “the wrong way”:

import sqlite3

def collate_reverse(string1, string2):
    if string1 == string2:
        return 0
    elif string1 < string2:
        return 1
    else:
        return -1

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print(row)
con.close()

To remove a collation, call create_collation with None as callable:

con.create_collation("reverse", None)

9. Connection.iterdump(): 연결된 DB의 내용을 SQL 질의로 형태로 출력할 수 있는 이터레이터를 반환

iterdump

Returns an iterator to dump the database in an SQL text format. Useful when saving an in-memory database for later restoration. This function provides the same capabilities as the .dump command in the sqlite3 shell.

Example:

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3, os

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

Cursor 클래스

- 실제적으로 데이터베이스에 SQL 문장을 수행, 조회된 결과를 가지고 오는 역할

1. Cursor.execute(sql[, parameters]): SQL 문장을 실행, 실행할 문장은 인자를 가질 수 있음

execute(sql[, parameters])

Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).

Here’s an example of both styles:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript() if you want to execute multiple SQL statements with one call.

2. Cursor.executemany(sql, seq_of_parameters)

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

- 파라미터 변경은 파라미터 시퀀스, 이터레이터를 이용할 수 있음

executemany(sql, seq_of_parameters)

Executes an SQL command against all parameter sequences or mappings found in the sequence sql. The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.

import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print(cur.fetchall())

Here’s a shorter example using a generator:

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")
print(cur.fetchall())

3. Cursor.executescript(sql_script): 세미콜론으로 구분된 연속된 SQL 문장을 수행

executescript(sql_script)

This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.

sql_script can be an instance of str or bytes.

Example:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)

4. Cursor.fetchone(): 조화된 결과로부터 데이터 1개를 반환, 더이상 데이터가 없는 경우 None을 반환

fetchone()

Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.

5. Cursor.fetchmany([size=cursor, array size])

- 조회된 결과로부터 size만큼의 데이터를 리스트 형태로 반환

- 데이터가 없는 경우 빈 리스트를 반환

fetchmany(size=cursor.arraysize)

Fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available.

The number of rows to fetch per call is specified by the sizeparameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one fetchmany()call to the next.

6. Cursor.fetchall(): 조화된 결과 모두를 리스트 형태로 반환, 데이터가 없는 경우 빈 리스트를 반환

fetchall()

Fetches all (remaining) rows of a query result, returning a list. Note that the cursor’s arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available.

Row 클래스

- 조합된 결과 집합(Result set)에서 Row객체는 관게형 데이터베이스 모델에서 튜플을 나타냄

class sqlite3.Row

A Row instance serves as a highly optimized row_factory for Connectionobjects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and len().

If two Row objects have exactly the same columns and their members are equal, they compare equal.

keys()

This method returns a list of column names. Immediately after a query, it is the first member of each tuple in Cursor.description.

Let’s assume we initialize a table as in the example given above:

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()

Now we plug Row in:

>>>

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

데이터베이스 연결

1. Connection객체

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

- 해당하는 물리적인 DB파일이 없는 경우 해당 경로에 파일을 생성, 파일이 이미 존재하는 경우 그 DB을 그대로 사용

- DB파일에 이미 테이블이 생성되거나 레코드가 입력된 경우, Connection 객체를 통해 조회, 입력 등의 연산이 가능

import sqlite3

con = sqlite3.connect("../db/test.db")

print("type(con):", type(con))

type(con): <class 'sqlite3.Connection'>

2. ":memory:" 키워드

- 메모리상에 DB파일을 생성

- 연결이 종료되면 현재까지 작업한 모든 내용이 사라지게 됨

- 물리적인 DB파일에 기록하는 것 보다는 연산 속도가 빠름

import sqlite3

conmem = sqlite3.connect(":memory:")

print("type(conmem):", type(conmem))

type(conmem): <class 'sqlite3.Connection'>

SQL문 수행

1. Cursor.execute() 메서드

- SQL 문을 입력 받아 수행

1) 전화번호부(PhoneBook) 테이블 생성, 레코드 하나를 입력하는 예제

import sqlite3

conmem = sqlite3.connect(":memory:")

print("type(conmem):", type(conmem))

cur = conmem.cursor()       # 커서 객체 생성

cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);")

cur.execute("INSERT INTO PhoneBook VALUES('Derick', '010-1234-5678');")

2) 사용하는 SQL문은 동일하지만 입력되는 인자만 바뀌는 경우 예제

- pysqlite에서 인자 전달 방식을 지원함

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

a. INSERT 구문을 인자 전달방식으로 작성한 예제

name = "Eunguru"

phoneNumber = "010-1020-0805"

cur.execute("INSERT INTO PhoneBook VALUES(?, ?);", (name, phoneNumber))

b. SQL구문의 각 인자에 이름을 부여하고 인자를 전달할때 사전을 전달

- '?'를 이용한 인자 전달방식을 사용하는 경우, 개발자는 SQL 구문의 인자 전달 순서를 모두 기억하여 그에 맞도록 시퀀스 객체를 전달해야 하는 불편함을 해결

cur.execute("INSERT INTO PhoneBook VALUES(:inputName, :inputNum);", 

            {"inputNum":phoneNumber, "inputName":name})

2. Cursor.executemany()메서드

1) 인자 전달 방식과 동일하게 SQL 구문을 작성, 두번째 인자에 시퀀스와 사전을 전달하면 됨

datalist = (('Tom', '010-234-5678'), ('Jane', '010-345-6789'))

cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", datalist)

2) 시퀀스 객체 대신에 이터레이터나 제너레이터를 이용할 수도 있음

def dataGenerator():

    datalist = {('Kevin', '010-456-7891'), ('Judy', '010-567-8910')}

    for item in datalist:

        yield item

cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", dataGenerator())

3. Cursor.executescript()메서드

- SQL구문을 파일에 저장하고 이 모두를 한번에 수행

- 스크립트 수행하기 이전에 우선 커밋을 수행하여 이전에 작업한 내용을 모두 DB에 반영, 그 다음 입력된 스크립트를 수행

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

1) script.txt 내용

CREATE TABLE PhoneBook(Name test, PhoneNum text);

INSERT INTO PhoneBook VALUES('Drick', '010-1234-5619');

2) 스크립트 수행

- 스크립트 수행 이전에 자동 커밋이나 수동 커밋을 해야함

import sqlite3

con = sqlite3.connect("../db/script.db")

con.isolation_level = None

with open('script.txt') as f:

    SQLScript = f.read()

   

cur = con.cursor()

cur.executescript(SQLScript)

(* 참고: 책에는 메모리 DB를 사용하게 되있지만 왜 인지 결과가 제대로 나오지 않아서 실 DB로 변환하여 실습..)

3) 레코드 조회

import sqlite3

con = sqlite3.connect("../db/script.db")

cur = con.cursor()

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

print(cur.fetchall())

[('Drick', '010-1234-5619')]

레코드 조회

- execute() 메서드를 이용하여 "SELECT"문을 수행 

1. Cursor 객체를 조화된 Row 객체에 대한 이터레이터처럼 사용

import sqlite3

con = sqlite3.connect("../db/test.db")

cur = con.cursor()

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

for row in cur:

    print(row)

('Drick', '010-1234-5678')

('Eunguru', '010-1020-0805')

('Eunguru', '010-1020-0805')

('Tom', '010-234-5678')

('Jane', '010-345-6789')

('Kevin', '010-456-7891')

('Judy', '010-567-8910')

2. Cursor.fetchone(): 조화된 결과 집합으로 부터 Row 객체를 가지고 옴

import sqlite3

con = sqlite3.connect("../db/test.db")

cur = con.cursor()

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

print(cur.fetchone())

('Drick', '010-1234-5678')

3. Cursor.fetchmany(n)

- 조회된 결과에서 인자로 입력된 n개 만큼 Row를 리스트 형태로 반환

- fetchone() 메서드로 1개의 레코드 조회한 후 fetchmany()를 호출하면 다름 레코드부터 반환

- 만약, 조회된 전체 결과 집합보다 큰 값이 인자로 입력되면, 조회된 결과를 모두 반환

import sqlite3

con = sqlite3.connect("../db/test.db")

cur = con.cursor()

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

print(cur.fetchmany(3))

[('Drick', '010-1234-5678'), ('Eunguru', '010-1020-0805'), ('Eunguru', '010-1020-0805')]

4. Cursor.fetchall(): 조화된 결과의 다음 Row부터 모든 레코드 리스트를 리스트 형태로 반환

import sqlite3

con = sqlite3.connect("../db/test.db")

cur = con.cursor()

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

print(cur.fetchall())

[('Drick', '010-1234-5678'), ('Eunguru', '010-1020-0805'), ('Eunguru', '010-1020-0805'), ('Tom', '010-234-5678'), ('Jane', '010-345-6789'), ('Kevin', '010-456-7891'), ('Judy', '010-567-8910')]

트랜잭션 처리

1. 작업한 내용이 커밋되지 않는 예제

- fetchcall(): 조화된 결과 모두를 리스트 형태로 반환, 데이터가 없는 경우 빈 리스트를 반환

1) 테이블 생성, 레코드 입력, 레코드 조회

import sqlite3

con = sqlite3.connect("../db/test.db")

print("type(con):", type(con))

cur = con.cursor()

cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);")

cur.execute("INSERT INTO PhoneBook VALUES('Drick', '010-1234-5678');")

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

result = cur.fetchall();

print(result)

print("type(result):", type(result))

type(con): <class 'sqlite3.Connection'>

[('Drick', '010-1234-5678')]

type(result): <class 'list'>

2) 레코드 조회

- 레코드가 출력되지 않고 빈 리스트가 나타남 (결과는 pysqlite 트랜잭션 처리와 연관되어 있음)

import sqlite3

con = sqlite3.connect("../db/test.db")

cur = con.cursor()

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

print(cur.fetchall())

[]

2. 트랜잭션

- 데이터베이스에서 논리적 작업의 단위

[예제]

1. 트랜잭션 시작
2. A통장에서 100만원 출금 (만약, 정전과 같은 상황이 발생하더라도 A통장에는 커밋되기 전까지 인출되지 않음)
3. B통장으로 출금한 100만원 입금
4. 트랜잭션 커밋(commit)/롤백(rallback)

- 트랜잭션 내에서 변경된 사항들은 데이터베이스에 반영(commit)되어 영구히 저장되거나 롤백하여 트랜잭션 수행 이전 상태로 복원됨

1) Connection.commit()

- 수행한 SQL구문을 데이터베이스에 반영할 수 있음

import sqlite3

con = sqlite3.connect("../db/commit.db")

cur = con.cursor()

cur.execute("CREATE TABLE PhoneBook(Name test, PhoneNum text);")

cur.execute("INSERT INTO PhoneBook VALUES('Drick', '010-1234-5619');")

con.commit()

commit()

This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

- DB에 커밋된 내용 확인

import sqlite3

con = sqlite3.connect("../db/commit.db")

cur = con.cursor()

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

print(cur.fetchall())

[('Drick', '010-1234-5619')]

2) 자동 커밋모드 설정

- SQL 구문을 수행할 때마다 명시적인 commit() 호출없이 데이터베이스에 반영하기 위해 Connection 객체의 isolation level 속성에 None을 입력하여 모드 변환 함

import sqlite3

con = sqlite3.connect("../db/autocommit.db")

con.isolation_level = None

cur = con.cursor()

cur.execute("CREATE TABLE PhoneBook(Name test, PhoneNum text);")

cur.execute("INSERT INTO PhoneBook VALUES('Drick', '010-1234-5619');")

- 레코드 조회

import sqlite3

con = sqlite3.connect("../db/autocommit.db")

cur = con.cursor()

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

print(cur.fetchall())

[('Drick', '010-1234-5619')]

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

- 특별한 정렬 조건에 따라서 결과 집합을 생성해야 하는 경우가 있음

1. 정렬하기 전 출력

- 아무 조치를 하지 않으면 입력된 순서대로 정렬되어 조회됨

1) 테이블 생성, 레코드 입력

import sqlite3

con = sqlite3.connect("../db/order.db")

con.isolation_level = None

cur = con.cursor()

cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);")

def dataGenerator():

    datalist = {('Kevin', '010-456-7891'), ('Judy', '010-567-8910'), 

                ('Drick', '010-1234-5678'), ('Tom', '010-234-5678'), 

                ('Jane', '010-345-6789'), ('eunguru', '010-1020-0805')}

    for item in datalist:

        yield item

cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", dataGenerator())

cur.execute("SELECT * FROM PhoneBook")

2) 레코드 조회

import sqlite3

con = sqlite3.connect("../db/order.db")

cur = con.cursor()

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

print(cur.fetchall())

[('Drick', '010-1234-5678'), ('Judy', '010-567-8910'), ('Jane', '010-345-6789'), ('Tom', '010-234-5678'), ('eunguru', '010-1020-0805'), ('Kevin', '010-456-7891')]

2. 'ORDER BY'구문을 이용하여 정렬

1) Name필드를 알파벳 순으로 정렬

- 대소문자 구분, 대문자 이후 소문자가 정렬됨

import sqlite3

con = sqlite3.connect("../db/order.db")

cur = con.cursor()

cur.execute("SELECT * FROM PhoneBook ORDER BY Name;")     # 알파벳 순으로 정렬 

for row in cur:

    print(row)

('Drick', '010-1234-5678')

('Jane', '010-345-6789')

('Judy', '010-567-8910')

('Kevin', '010-456-7891')

('Tom', '010-234-5678')

('eunguru', '010-1020-0805')

2) Name필드를 알파벳 역순으로 정렬

import sqlite3

con = sqlite3.connect("../db/order.db")

cur = con.cursor()

cur.execute("SELECT * FROM PhoneBook ORDER BY Name DESC;")     # 알파벳 역순으로 정렬 

for row in cur:

    print(row)

('eunguru', '010-1020-0805')

('Tom', '010-234-5678')

('Kevin', '010-456-7891')

('Judy', '010-567-8910')

('Jane', '010-345-6789')

('Drick', '010-1234-5678')

3) 사용자 임의로 정렬 방식을 변경

- 사용자 정렬함수를 미리 정의하고 Connection.create_collation()메서드를 이용하여 DB에 등록

- SELECT 문에서 이 정렬 방식을 명시적으로 지정하여 처리

import sqlite3

con = sqlite3.connect("../db/order.db")

cur = con.cursor()

# 대소문자 구별 없이 정렬하는 함수 

def OrderFunc(str1, str2):

    s1 = str1.upper()

    s2 = str2.upper()

    return (s1 > s2) - (s1 < s2)

con.create_collation('myordering', OrderFunc)   # SQL 구문에서 호출할 이름과 함수를 등록 

cur.execute("SELECT Name FROM PhoneBook ORDER BY Name COLLATE myordering;") # 사용자 정렬 함수 

for row in cur:

    print(row[0])

Drick

eunguru

Jane

Judy

Kevin

Tom

SQLite3 내장 집계 함수

- SQLite3에 정의되어있는 내장 집계(aggregate) 함수를 이용하여 개발자의 용도에 맞게 결과값을 가공할 수 있음

1. 내장함수 목록

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

2. 내장 집계함수 사용 예제

1) 테이블 생성, 레코드 입력

import sqlite3

con = sqlite3.connect("../db/aggregate.db")

con.isolation_level = None

cur = con.cursor()

cur.execute("CREATE TABLE PhoneBook(Name text, Age integer);")

list = (('Tom', 24), ('Derick', 30), ('Peter', 53), ('Jane', 29))

cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", list)

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

print(cur.fetchall())

[('Tom', 24), ('Derick', 30), ('Peter', 53), ('Jane', 29)]

2) 내장 집계 함수 사용 레코드 조회

import sqlite3

con = sqlite3.connect("../db/aggregate.db")

cur = con.cursor()

# 레코드 조회 

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

print(cur.fetchall())

# 문자열 길이, 대문자, 소문자 

cur.execute("SELECT length(Name), upper(Name), lower(Name) FROM PhoneBook;")

print("== length(), upper(), lower() ==")

print(cur.fetchall())

# 최대값, 최소값, 총합 

cur.execute("SELECT max(Age), min(Age), sum(Age) FROM PhoneBook;")

print("== max(), min(), sum() ==")

print(cur.fetchall())

# 레코드 개수, 임의의 값 

cur.execute("SELECT count(*), random(*) FROM PhoneBook;")

print("== count(*), random(*) ==")

print(cur.fetchall())

[('Tom', 24), ('Derick', 30), ('Peter', 53), ('Jane', 29)]

== length(), upper(), lower() ==

[(3, 'TOM', 'tom'), (6, 'DERICK', 'derick'), (5, 'PETER', 'peter'), (4, 'JANE', 'jane')]

== max(), min(), sum() ==

[(53, 24, 136)]

== count(*), random(*) ==

[(4, 3726220532186825365)]

사용자정의 집계 함수

- 사용자가 직접 집계를 위한 클래스를 작성하여 Connection.create_aggregate()메서드를 이용하여 등록할 수 있음

- 집계를 위한 클래스에는 step(), finalize() 메서드가 정의되어 있어야 함

- step(): create_aggregate()메서드에 등록시 지정된 인자의 개수만큼 인자를 전달 받음

- finalize(): 집계된 결과를 반환

- 정의된 클래스는 Connection.create_aggregate() 메서드를 호출하여 DB에 등록

1. 사용자 정의 집계 함수 사용 예제

1) 집계 클래스, 테이블 생성, 레코드 입력, 사용자 집계 함수 등록

class Average:

    '''

    classdocs

    '''

    def __init__(self):

        '''

        Constructor

        '''

        self.sum = 0

        self.cnt = 0

    

    def step(self, value):

        self.sum += value

        self.cnt += 1

    

    def finalize(self):

        return self.sum / self.cnt

import sqlite3

con = sqlite3.connect("../db/average.db")

con.isolation_level = None

cur = con.cursor()

cur.execute("CREATE TABLE User(Name text, Age int);")

list = (('Tom', '16'), ('DSP', '33'), ('Derick', '25'))

cur.executemany("INSERT INTO User VALUES(?, ?);", list)

# Average 클래스를 사용자 정의 집계함수로 등록 

con.create_aggregate("avg", 1, Average)

2) 레코드 조회

import sqlite3

con = sqlite3.connect("../db/average.db")

con.isolation_level = None

cur = con.cursor()

cur.execute("SELECT avg(Age) FROM User;")

print(cur.fetchone()[0])

24.666666666666668

자료형

- SQLite3의 기본 자료형: NULL, INTEGER, REAL, TEXT, BLOB(바이너리 파일을 데이터베이스에 직접 저장)

1. SQLite3의 자료형과 대응하는 파이썬 자료형

- 해당되는 SQLite3와 파이썬의 자료형에 대해서는 특별한 변환없이 상호사용이 가능

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

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

import sqlite3

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

cur = con.cursor()

# SQLite3 자료형으로 tbl_1 테이블 생성 

cur.execute("CREATE TABLE tbl_1(Name TEXT, Age INTEGER, Money REAL);")

# 파이썬 자료형으로 tbl_2 테이블 생성

cur.execute("CREATE TABLE tbl_2(Name str, Age int, Money float);")

# 동일한 방법으로 두 테이블에 레코드 입력 가능 

cur.execute("INSERT INTO tbl_1 VALUES('Tiger', 25, 123.45);")

cur.execute("INSERT INTO tbl_2 VALUES('Lion', 25, 123.45);")

# 레코드 조회 

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

print(cur.fetchall())

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

print(cur.fetchall())

[('Tiger', 25, 123.45)]

[('Lion', 25, 123.45)]

사용자 정의 자료형

- 클래스와 같은 사용자정의 자료형을 SQLite3에 등록하여 직접 클래스 객체를 DB에 입력할 수 있음

1. 사용 예제

1) 변환 함수

- PointAdapter(): 클래스 객체를 SQLite 기본 자료형 형태로 변환하는 함수, 콜론(:)을 구분자로 하여 x, y 좌표를 TEXT 형태로 반환

- PointConverter(): 조회 결과는 기본 자료형으로 변환된 형태이기 때문에 클래스 객체에 맞도록 복원해주는 함수, 콜론으로 두 값을 구분하여 Point 객체를 생성하여 반환

- 변환 함수들은 DB에 등록되어야 명시적인 변환과정 없이 내부적으로 자동 변환되어 사용할 수 있음

sqlite3.register_adater(<파이썬 자료형>, <변환함수>)

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

2) 예제 코드

class Point(object):
    '''
    classdocs
    '''
    def __init__(self, x, y):
        '''
        Constructor
        '''
        self.x, self.y = x, y
    
    # Point 객체의 내용 출력     
    def __repr__(self):
        return "Point(%f, %f)" % (self.x, self.y)

import sqlite3

# 클래스 객체에서 SQLite3 입력 가능한 자료형으로 변환 
def PointAdapter(point):
    return "%f:%f" % (point.x, point.y)

# SQLite3에서 조회한 결과를 클래스 객체로 변환 
def PointConverter(s):
    x, y = list(map(float, s.decode().split(":")))
    return Point(x, y)

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

# 입력할 데이터(파이썬 클래스 객체)
p = Point(4, -3.2)
p2 = Point(-1.4, 6.2)

# 암묵적으로 선언된 자료형으로 조회하도록 설정 
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()

cur.execute("CREATE TABLE test(p point);")
cur.execute("INSERT INTO test VALUES (?);", (p, ))
cur.execute("INSERT INTO test(p) VALUES (?);", (p2, ))

cur.execute("SELECT p FROM test;")
print([r[0] for r in cur])

cur.close()
cur.close()

3) 생성된 테이블, 입력된 레코드 확인

- 이해를 쉽게하기 위해서 실 디비에 저장후 테이블, 사용자 정의 자료형, 입력 레코드를 확인함

데이터베이스 덤프 만들기

- 데이터베이스의 이동이나 백업등의 이유로 현재 상태를 SQL 구문으로 추출해야 하는 경우가 있음

- Connection.iterdump(): SQL 구문을 이터레이터 형태로 반환

1) 예제 소스 코드

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-1234-5678');")

list = (('Tom', '010-2345-6789'), ('DSP', '010-3456-7890'))

cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", list)

# 덤프한 내용 출력 

for l in con.iterdump():

    print(l)

    

# 덤프한 내용 저장

with open("dump.sql", "w") as f:

    for l in con.iterdump():

        f.write('{0}\n'.format(l))

2) 덤프한 내용 출력 내용

BEGIN TRANSACTION;

CREATE TABLE PhoneBook(Name text, PhoneNum text);

INSERT INTO "PhoneBook" VALUES('Derick','010-1234-5678');

INSERT INTO "PhoneBook" VALUES('Tom','010-2345-6789');

INSERT INTO "PhoneBook" VALUES('DSP','010-3456-7890');

COMMIT;

3) 덤프한 내용 저장 파일 확인

명령어 프롬프트에서 SQLite3 관리하기

- 리눅스나 Mac의 경우 기본적으로 SQLite3가 설치되어 있고 sqlite3 명령어를 이용하여 기능 사용이 가능

1.  명령어 프롬프트 SQLite 관리 유틸리티 생성 예제

1) 기능 명세

- 표준 SQL 구문은 모두 수행 가능

- SQL구문의 종료는 ';'를 명시적으로 사용, 한번에 1개의 구문만 입력 가능하도록 함

- 프로그램 수행시 명시적으로 데이터베이스 파일을 지정하면 그 파일에 수행 결과를 저장

- 데이터베이스 파일을 지정하지 않으면 메모리 내에서만 수행되고, 결과는 파일에 저장하지 않음

- SQL 구문이 아닌 특수 명령어로 데이터베이스 덤프 기능 지원, 파일과 화면으로 덤프된 내용이 출력 가능하도록 함

- 엔터키를 누르는 경우 프로그램 종료됨

- .help; 명령어를 입력하면 사용 가능한 특수 명령어를 출력함

2) 유틸리티 소스 코드

import sqlite3
import sys
import re

# 데이터베이스 경로 설정 
if len(sys.argv) == 2:
    path = sys.argv[1]
else:
    path = ":memory:"
    
con = sqlite3.connect(path)

# 트랜잭션없이 자동 커밋되도록 설정 
con.isolation_level = None
cur = con.cursor()

buffer = ""     # 쿼리 버퍼 

# 프로그램 소개 메시지 출력 함수 
def PrintIntro():
    print("--------------------------------------------------------")
    print("pysqlite의 command 프로그램입니다.")
    print("사용법: python pysqlite_command.py [데이터베이스이름]")
    print("특수 명령어를 알고 싶으시면 '.help;'를 입력하세요.")
    print("SQL 구문은 ';'으로 끝나야 합니다.")
    print("Enter키 입력시 프로그램이 종료됩니다.")
    print("--------------------------------------------------------")

# 도움말 출력 메시지 출력 함수, '.help;'명령어 입력시 실행됨 
def PrintHelp():
    print("--------------------------------------------------------")
    print("프로그램을 도움말을 출력합니다.")
    print("\t.dump\t\t데이터베이스의 내용을 덤프합니다.")
    print("\t.help\t\t유틸리티 도움말을 출력합니다.")
    print("--------------------------------------------------------")

# 데이터베이스 내용 덤프 수행 함수, '.dump;'명령어 입력시 실행됨 
def SQLDump(con, file=None):
    
    if file != None:
        f = open(file, "w")
        
    else:
        f = sys.stdout
    
    for l in con.iterdump():
        f.write("{0}\n".format(l))
        
    if f != sys.stdout:
        f.close()

PrintIntro()        # 소개 메시지 출력 

while True:
    line = input("pysqlite>> ")     # 명령어 입력 
    
    if buffer == "" and line == "":
        break;
    
    buffer += line
    
    # ';'으로 SQL 구문이 끝나는지 검사,';'으로 끝나는 경우  
    if sqlite3.complete_statement(buffer):
        buffer = buffer.strip()
        
        # 특수 명령어 인 경우 
        if buffer[0] == ".":
            cmd = re.sub('[ ;]', ' ', buffer).split()
            
            if cmd[0] == '.help':
                PrintHelp()
            
            elif cmd[0] == '.dump':
                if len(cmd) == 2:
                    SQLDump(con, cmd[1])
                else:
                    SQLDump(con)
        
        # 일반 SQL 구문인 경우 
        else:
            try:
                buffer = buffer.strip()
                cur.execute(buffer)
                
                # SELECT 질의인 경우 
                if buffer.lstrip().upper().startswith("SELECT"):
                    print("--------------------------------------------------------")
                    print(cur.fetchall())
                    print("--------------------------------------------------------")
                
            except sqlite3.Error as e:
                print("[FAIL] Error: ", e.args[0])
            
            else:
                print("[SUCCESS] 구문이 성공적으로 수행되었습니다.")
        
        buffer = ""     # 입력 버퍼 초기화 
    
    # SQL 구문이 끝나는지 검사,';'으로 끝나지 않는 경우 
    else:
        print("[FAIL] SQL 구문';'으로 끝나지 않습니다. 다시 입력해주세요!")
        buffer = ""     # 입력 버퍼 초기화 

con.close()
print("--------------------------------------------------------")
print("프로그램을 종료합니다.")
print("--------------------------------------------------------")

3) 유틸리티 실행

-  dbdump.sql 파일 생성 및 dbdump.sql 파일 내용