파이썬 + SQLite 사용 후 몇 가지 기록

당연한 이야기 몇 가지 기억을 위해 남깁니다. 

 

0. commit

데이터를 조작하는 INSERT, REPLACE, DELETE 등의 명령은 커밋이 필요합니다.
오토 커밋도 가능 하지만...

from sqlite3 import connect

DB = 'test.sqlite3'


def query_and_commit(sql):
    """
    INSERT, REPLACE, DELETE ...
    """
    with connect(DB) as conn:
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()  # commit


def query(sql):
    """
    SELECT ...
    """
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql)


# 생성
query_and_commit('CREATE TABLE IF NOT EXISTS table1 (name TEXT, age INTEGER, sex TEXT)')

# 입력
query_and_commit('DELETE FROM table1')
query_and_commit('INSERT INTO table1 VALUES ("Kim", 32, "M")')
query_and_commit('INSERT INTO table1 VALUES ("Lee", 48, "F")')
query_and_commit('INSERT INTO table1 VALUES ("Choi", 19, "M")')
query_and_commit('INSERT INTO table1 VALUES ("Park", 28, "F")')

# 확인
print(tuple(query('SELECT * FROM table1')))
#(('Kim', 32, 'M'), ('Lee', 48, 'F'), ('Choi', 19, 'M'), ('Park', 28, 'F'))

 

1. with as

파이썬의 with as(컨텍스트 관리자)는 사랑입니다. 

import sqlite3

conn = sqlite3.connect('test.sqlite3')
cur = conn.cursor()
cur.execute('SELECT * FROM table1')
rows = cur.fetchall()
for row in rows:
    print(row)
conn.close()

아래쪽의 가독성이 더 좋고 편합니다. 

import sqlite3

with sqlite3.connect('test.sqlite3') as conn:
    cur = conn.cursor()
    cur.execute('SELECT * FROM table1')
    rows = cur.fetchall()
    for row in rows:
        print(row)

 

2. yield from

yield from은 제너레이터를 만들어 주는 구문입니다.
익숙해지면 편합니다.

from sqlite3 import connect

DB = 'test.sqlite3'


def query(sql):
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql)


for row in query('SELECT * FROM table1'):
    print(row)

 

3. Placeholder

우리말로 자리잡개 정도 되려나요?
컬럼'값' 대신 '?'으로 자리를 잡아두면 됩니다.
(컬럼명, 테이블명은 안 되는 것 같습니다.)
SQL 인젝션 예방에 도움이 된다고 합니다. 

placeholder를 1개만 사용했다고 해서
뒤에 실제 컬럼값을 1개만 홀로 두면 안됩니다.
예제에도 있듯 tuple, list 등으로 묶어야 합니다.   

from sqlite3 import connect

DB = 'test.sqlite3'


def query_with_placeholder(sql, placeholder):
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql, placeholder)


# 작동 됨
for row in query_with_placeholder('SELECT name FROM table1 WHERE sex=?', ('M',)):
    print(row)
# ('Kim',)
# ('Choi',)

# 비 정상적으로 작동함
for row in query_with_placeholder('SELECT ? FROM table1 WHERE sex=?', ('name', 'M')):
    print(row)
# ('name',)
# ('name',)

# 에러
for row in query_with_placeholder('SELECT name FROM ? WHERE sex=?', ('table1', 'M')):
    print(row)
# sqlite3.OperationalError: near "?": syntax error

 

4. Named Placeholder

플레이스 홀더에 이름을 붙일 수도 있습니다. 
SQL 문 안에 ':이름'으로 자리를 잡아주고,
뒤에 딕셔너리로 {'이름': '컬럼값'} 자리에 넣어줄 컬럼값을 설정하면 됩니다.  

from sqlite3 import connect

DB = 'test.sqlite3'


def query_with_placeholder(sql, placeholder):
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql, placeholder)


# 작동 됨
for row in query_with_placeholder('SELECT name FROM table1 WHERE sex=:sex', {'sex': 'M'}):
    print(row)
# ('Kim',)
# ('Choi',)

# 비 정상적으로 작동함
for row in query_with_placeholder('SELECT :column FROM table1 WHERE sex=:sex', {'column': 'name', 'sex': 'M'}):
    print(row)
# ('name',)
# ('name',)

# 에러
for row in query_with_placeholder('SELECT name FROM :table WHERE sex=:sex', {'table': 'table1', 'sex': 'M'}):
    print(row)
# sqlite3.OperationalError: near ":table": syntax error

 

5. executemany

INSERT 등을 여러 번 해야 할 때,
모아서 한 번에 하면 빠르고 편리합니다.

placeholder가 필요합니다.
그리고, placeholder에 들어갈 값들의 묶음을
한번 더 묶어서 넘겨주면 됩니다. 

from sqlite3 import connect

DB = 'test.sqlite3'


def query_and_commit(sql):
    with connect(DB) as conn:
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()  # commit


def query(sql):
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql)


def query_many(sql, data):
    with connect(DB) as conn:
        cur = conn.cursor()
        cur.executemany(sql, data)  # execute MANY~!
        conn.commit()  # commit


# name 컬럼에는 UNIQUE 속성을 사용합시다. 
query_and_commit('CREATE TABLE IF NOT EXISTS table2 (name TEXT UNIQUE, age INTEGER, sex TEXT)')
query_and_commit('DELETE FROM table2')
query_many('INSERT INTO table2 VALUES (?,?,?)',
           (("Kim", 32, "M"),
            ("Lee", 48, "F"),
            ("Choi", 19, "M"),
            ("Jang", 38, "M")))

print(tuple(query('SELECT * FROM table2')))
# (('Kim', 32, 'M'), ('Lee', 48, 'F'), ('Choi', 19, 'M'), ('Jang', 38, 'M'))

 

6. 서로 다른 테이블의 데이터를 비교해 차이를 찾을 때

차집합을 이용해 보았습니다.
더 좋은 방법이 있는지는 모르겠습니다. (SQL 만년 초보)

print(tuple(query('SELECT * FROM table1 EXCEPT SELECT * FROM table2')))
# (('Park', 28, 'F'),)

print(tuple(query('SELECT * FROM table2 EXCEPT SELECT * FROM table1')))
# (('Jang', 38, 'M'),)

 

7. REPLACE INTO

테이블에 데이터가 없으면 INSERT INTO,
데이터가 있으면 UPDATE 해줍니다. 
고유키(UNIQUE)가 있는 경우 편리하게 쓸 수 있습니다. 

# 에러, name 컬럼(UNIQUE)에 Jang 이 있는데 또 INSERT 했다.  
query_and_commit('INSERT INTO table2 VALUES("Jang", 36, "M")')
# sqlite3.IntegrityError: UNIQUE constraint failed: table2.name

# 38 -> 36으로 바뀌는 지 확인
query_and_commit('REPLACE INTO table2 VALUES("Jang", 36, "M")')
print(tuple(query('SELECT * FROM table2')))
# (('Kim', 32, 'M'), ('Lee', 48, 'F'), ('Choi', 19, 'M'), ('Jang', 36, 'M'))

 

8. IF NOT EXISTS, IF EXISTS

테이블을 만들 때, 테이블 있으면 에러가 발생합니다. 
CREATE TABLE IF NOT EXISTS table로 생성하는 게 편할 때가 많습니다. 
DROP TABLE IF EXISTS table 도 마찬가지입니다.   

9. VIEW

SQL계의 매크로입니다. 긴 SQL을 줄여줍니다. 중첩된 SQL을 분리할 때 사용해도 좋습니다. 
가상의 TABLE이기 때문에 VIEW에 INSERT, DELETE, UPDATE 등은 불가능합니다.
실행할 때마다 가상의 TABLE을 다시 SELECT 하기 때문에 속도 향상 효과는 없습니다. 
DB 파일에 기록됩니다.  

print(tuple(query('SELECT name FROM (SELECT * FROM table1 EXCEPT SELECT * FROM table2)')))
# (('Park',),)

query_and_commit('CREATE VIEW IF NOT EXISTS view1 AS SELECT * FROM table1 EXCEPT SELECT * FROM table2')
print(tuple(query('SELECT name FROM view1')))
# (('Park',),)

 

10. DEFAULT

디폴트 값으로 date나 time 또는 datetime을 주면 자동으로 시간을 기록해줍니다. 
파이썬의 시간을 Sqlite로 옮기는 것 보다 간편합니다.  
인서트 시 DEFAULT가 설정된 열의 값은 없어야 합니다.  

sql = 'CREATE TABLE IF NOT EXISTS visitor ' \
      '(visited_at TEXT DEFAULT(datetime(\'now\',\'localtime\')), address TEXT, tel TEXT)'
query_and_commit(sql)

query_and_commit('INSERT INTO visitor (address, tel) VALUES ("KangNam", "000-000-1234")')
query_and_commit('INSERT INTO visitor (address, tel) VALUES ("YongSan", "000-000-0000")')
print(tuple(query('SELECT * FROM visitor')))
# (('2021-11-04 16:31:34', 'KangNam', '000-000-1234'), ('2021-11-04 16:31:34', 'YongSan', '000-000-0000'))

* \'now\'의 작은 따옴표에 주의합시다. 

 

11. TEMP TABLE

메모리에 존재하는 가상 테이블입니다.  
파이썬에서는 close 하면 사라져 버려 쓰기가 조금 불편합니다.
SQL 콘솔 환경에서는 유용하게 쓸 수 있을 것 같습니다. 

 

물론 제가 익숙하지 않아 그렇겠지만...
괄호나 따옴표 때문에 에러가 생기는 일이 가끔 있었습니다. 

 

전체 코드

from sqlite3 import connect

DB = 'test.sqlite3'


def query_and_commit(sql):
    """
    INSERT, UPDATE, DELETE ...
    """
    with connect(DB) as conn:
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()  # commit


def query(sql):
    """
    SELECT ...
    """
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql)


# 생성
query_and_commit('CREATE TABLE IF NOT EXISTS table1 (name TEXT, age INTEGER, sex TEXT)')

# 입력
query_and_commit('DELETE FROM table1')
query_and_commit('INSERT INTO table1 VALUES ("Kim", 32, "M")')
query_and_commit('INSERT INTO table1 VALUES ("Lee", 48, "F")')
query_and_commit('INSERT INTO table1 VALUES ("Choi", 19, "M")')
query_and_commit('INSERT INTO table1 VALUES ("Park", 28, "F")')

# 확인
print(tuple(query('SELECT * FROM table1')))


def query_with_placeholder(sql, placeholder):
    with connect(DB) as conn:
        cur = conn.cursor()
        yield from cur.execute(sql, placeholder)


# 작동 됨
for row in query_with_placeholder('SELECT name FROM table1 WHERE sex=?', ('M',)):
    print(row)
# ('Kim',)
# ('Choi',)

# # 비 정상적으로 작동함
# for row in query_with_placeholder('SELECT ? FROM table1 WHERE sex=?', ('name', 'M')):
#     print(row)
# # ('name',)
# # ('name',)
#
# # 에러
# for row in query_with_placeholder('SELECT name FROM ? WHERE sex=?', ('table1', 'M')):
#     print(row)
# # sqlite3.OperationalError: near "?": syntax error

# 작동 됨
for row in query_with_placeholder('SELECT name FROM table1 WHERE sex=:sex', {'sex': 'M'}):
    print(row)
# ('Kim',)
# ('Choi',)

# # 비 정상적으로 작동함
# for row in query_with_placeholder('SELECT :column FROM table1 WHERE sex=:sex', {'column': 'name', 'sex': 'M'}):
#     print(row)
# # ('name',)
# # ('name',)
#
# # 에러
# for row in query_with_placeholder('SELECT name FROM :table WHERE sex=:sex', {'table': 'table1', 'sex': 'M'}):
#     print(row)
# # sqlite3.OperationalError: near ":table": syntax error


def query_many(sql, data):
    with connect(DB) as conn:
        cur = conn.cursor()
        cur.executemany(sql, data)  # execute MANY~!
        conn.commit()  # commit


# name 컬럼에는 UNIQUE 속성을 사용합시다.
query_and_commit('CREATE TABLE IF NOT EXISTS table2 (name TEXT UNIQUE, age INTEGER, sex TEXT)')
query_and_commit('DELETE FROM table2')
query_many('INSERT INTO table2 VALUES (?,?,?)',
           (("Kim", 32, "M"),
            ("Lee", 48, "F"),
            ("Choi", 19, "M"),
            ("Jang", 38, "M")))

# print(tuple(query('SELECT * FROM table2')))
# (('Kim', 32, 'M'), ('Lee', 48, 'F'), ('Choi', 19, 'M'), ('Jang', 38, 'M'))

print(tuple(query('SELECT * FROM table1 EXCEPT SELECT * FROM table2')))
# (('Park', 28, 'F'),)

print(tuple(query('SELECT * FROM table2 EXCEPT SELECT * FROM table1')))
# (('Jang', 38, 'M'),)

# # 에러, name 컬럼(UNIQUE)에 Jang 이 있는데 또 INSERT 했다.
# query_and_commit('INSERT INTO table2 VALUES("Jang", 36, "M")')
# # sqlite3.IntegrityError: UNIQUE constraint failed: table2.name

# 38 -> 36으로 바뀌는 지 확인
query_and_commit('REPLACE INTO table2 VALUES("Jang", 36, "M")')
print(tuple(query('SELECT * FROM table2')))
# (('Kim', 32, 'M'), ('Lee', 48, 'F'), ('Choi', 19, 'M'), ('Jang', 36, 'M'))

print(tuple(query('SELECT name FROM (SELECT * FROM table1 EXCEPT SELECT * FROM table2)')))
# (('Park',),)

query_and_commit('CREATE VIEW IF NOT EXISTS view1 AS SELECT * FROM table1 EXCEPT SELECT * FROM table2')
print(tuple(query('SELECT name FROM view1')))
# (('Park',),)


query_and_commit('DROP TABLE IF EXISTS visitor')

sql = 'CREATE TABLE IF NOT EXISTS visitor ' \
      '(visited_at TEXT DEFAULT(datetime(\'now\', \'localtime\')), address TEXT, tel TEXT)'
query_and_commit(sql)

query_and_commit('INSERT INTO visitor (address, tel) VALUES ("KangNam", "000-000-1234")')
query_and_commit('INSERT INTO visitor (address, tel) VALUES ("YongSan", "000-000-0000")')
print(tuple(query('SELECT * FROM visitor')))
# (('2021-11-04 16:38:22', 'KangNam', '000-000-1234'), ('2021-11-04 16:38:22', 'YongSan', '000-000-0000'))