sqlite3 - DB API 2.0 interface for SQLite databases

SQLite는 별도의 서버 없이 디스크 기반으로 SQL 쿼리를 지원하는 가벼운 C 라이브러리 데이터베이스 이다.

import sqlite3
 
dbpath = "fruit.db"
conn = sqlite3.connect(dbpath)
# Opens a connection to the SQLite database file database.
# By default returns a Connection object, unless a custom factory
# is given. You can use ":memory:" to open a database connection to 
# a database that resides in RAM instead of on disk.
cur = conn.cursor()
# The cursor method accepts a single optional parameter factory.
# If supplied, this must be a callable returning an instance of
# Cursor or its subclasses.
 
cur.executescript("""
drop table if exists items;

create table items(
    item_id integer primary key,
    name text unique,
    price integer
);

insert into items(name, price) values("Apple", 800);
insert into items(name, price) values("Orange", 700);
insert into items(name, price) values("Banana", 430);
""")
# 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.
 
conn.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.
 
cur.execute("select * from items")
# This is a nonstandard shortcut that creates a cursor object by
# calling the cursor() method, calls the cursor’s execute() method
# with the parameters given, and returns the cursor.
item_list = cur.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.
for it in item_list:
    print(it)
 
print()
 
cur.execute("insert into items(name, price) values('Grape', 500)")
conn.commit()
 
cur.execute("select item_id, name, price from items")
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
print()
 
cur.execute("insert into items(name, price) values(?, ?)", ("Strawberry", 800))
#conn.commit() 67라인에서 commit()을 호출 하므로 굳이 여기서 할 필요는 없다.
 
data = [("Mango", 250), ("Kiwi", 740), ("Peach", 650)]
cur.executemany("insert into items(name, price) values(?, ?)", data)
# Executes an SQL command against all parameter sequences or mappings found in the
# sequence seq_of_parameters.
conn.commit()
 
cur.execute("select item_id, name, price from items")
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
print()
 
price_range = (600, 700)
cur.execute("select * from items where name = 'Kiwi' or (price >= ? and price <= ?)",
            price_range)
item_list = cur.fetchall()
for it in item_list:
    print("Name: ", it[1], ", Price: ", it[2])
 
for it in item_list:
    print("ID: %s, Name: %s, Price: %s" %it) # it 자체(튜플)를 전달해도 된다.
 
print("\nStrawberry를 Watermelon으로 변경, Orange 삭제")
 
cur.execute("update items set name = 'Watermelon', price = 1500 where name = 'Strawberry'")
cur.execute("delete from items where name = 'Orange'")
cur.execute("select * from items")
conn.commit()
 
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
cur.close()
conn.close()

fruit.db 파일이 생성 된다.