[Django] SQLite3 데이터를 조작하는 방법 - connect & cursor

SQLite3 에 쿼리 명령어를 날려서 데이터를 조작할 수 있습니다.
장고에서 view 함수를 만들어 커넥션을 구축합니다.
이후 해당 라이브러리의 메서드를 사용할 수 있습니다.

1. Connection objects 생성

import sqlite3
con = sqlite3.connect('example.db')

2. Cursor object 생성

create a Cursor object and call its execute() method to perform SQL command.

cur = con.cursor()

3. execute() 쿼리문 실행

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

4. commit() 반영

# Save (commit) the changes
con.commit()

5. close() 저장

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

The saved data is persistent: it can be reloaded in a subsequent session even after restarting the Python interpreter:

6. 출력(print)

>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

7. 출력 fetchall()

To retrieve data after executing a SELECT statement, either treat the cursor as an iterator, call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.

• 메서드 이어붙이는 경우

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""
    select * from pragma_compile_options
    where compile_options like 'THREADSAFE=%'
""").fetchall()

• 메서드 이어붙이지 않는 경우

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table lang (name, first_appeared)")

# This is the qmark style:
cur.execute("insert into lang values (?, ?)", ("C", 1972))

# The qmark style used with executemany():
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

# And this is the named style:
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())

con.close()

참고자료
https://docs.python.org/3/library/sqlite3.html