sqlite3 技術手冊
import pickle
import os.path
import sqlite3
db_name = 'dvd_lib.sqlite3'
def connect(name):
create = not os.path.exists(name)
conn = sqlite3.connect(name)
if create:
cursor = conn.cursor()
cursor.execute("CREATE TABLE dvds ("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
"title TEXT NOT NULL, "
"year INTEGER NOT NULL, "
"duration INTEGER NOT NULL, "
"director_id TEXT NOT NULL)")
conn.commit()
return conn
class DVD:
def __init__(self, title, year=None, duration=None, director_id=None):
self.title = title
self.year = year
self.duration = duration
self.director_id = director_id
def save(self):
with connect(db_name) as conn:
conn.cursor().execute("INSERT INTO dvds (title, year, duration, director_id) VALUES (?, ?, ?, ?)",
(self.title, self.year, self.duration, self.director_id))
@staticmethod
def load(title):
with connect(db_name) as conn:
c = conn.cursor()
fields = c.execute('SELECT title, year, duration, director_id FROM dvds WHERE title =?', (title,)).fetchone()
return DVD(*fields)
def __str__(self):
return repr(self)
def __repr__(self):
return "DVD('{0}', {1}, {2}, '{3}')".format(
self.title, self.year, self.duration, self.director_id)
dvd1 = DVD('Birds', 2016, 1, 'Justin Lin')
dvd1.save()
dvd2 = DVD.load('Birds')
print(dvd2)