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()
