# book_manager_simple.py (OCR/バーコード機能なし版)
import tkinter as tk
from tkinter import ttk, messagebox, scrolledtext, filedialog
import sqlite3
import requests
from bs4 import BeautifulSoup
from PIL import Image, ImageTk
import os
from datetime import datetime
class BookDatabase:
"""SQLiteデータベース管理クラス"""
def __init__(self, db_path="book_manager.db"):
self.db_path = db_path
self.init_database()
def get_connection(self):
conn = sqlite3.connect(self.db_path)
conn.row_factory = sqlite3.Row
return conn
def init_database(self):
conn = self.get_connection()
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
isbn TEXT UNIQUE,
title TEXT,
image_path TEXT,
amazon_rating REAL,
amazon_review_count INTEGER,
amazon_url TEXT,
status TEXT DEFAULT 'owned',
purchase_date TEXT,
sell_date TEXT,
sell_price INTEGER,
notes TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS search_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
isbn TEXT,
title TEXT,
searched_at TEXT DEFAULT CURRENT_TIMESTAMP,
found_in_db INTEGER
)
""")
cur.execute("CREATE INDEX IF NOT EXISTS idx_isbn ON books(isbn)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_status ON books(status)")
conn.commit()
conn.close()
def add_book(self, book_data):
conn = self.get_connection()
cur = conn.cursor()
try:
cur.execute("""
INSERT INTO books (
isbn, title, image_path, amazon_rating,
amazon_review_count, amazon_url, status, purchase_date
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""", (
book_data.get('isbn'),
book_data.get('title'),
book_data.get('image_path'),
book_data.get('amazon_rating'),
book_data.get('amazon_review_count'),
book_data.get('amazon_url'),
'owned',
datetime.now().strftime('%Y-%m-%d')
))
book_id = cur.lastrowid
conn.commit()
return book_id
except sqlite3.IntegrityError:
return None
finally:
conn.close()
def get_books(self, status=None):
conn = self.get_connection()
cur = conn.cursor()
if status:
cur.execute("SELECT * FROM books WHERE status = ? ORDER BY created_at DESC", (status,))
else:
cur.execute("SELECT * FROM books ORDER BY created_at DESC")
books = [dict(row) for row in cur.fetchall()]
conn.close()
return books
def update_book(self, book_id, updates):
conn = self.get_connection()
cur = conn.cursor()
set_clause = ", ".join([f"{k} = ?" for k in updates.keys()])
values = list(updates.values()) + [book_id]
cur.execute(f"UPDATE books SET {set_clause} WHERE id = ?", values)
conn.commit()
conn.close()
def check_duplicate(self, isbn=None, title=None):
conn = self.get_connection()
cur = conn.cursor()
results = []
if isbn:
cur.execute("SELECT * FROM books WHERE isbn = ?", (isbn,))
results.extend([dict(row) for row in cur.fetchall()])
if title and not results:
cur.execute("SELECT * FROM books WHERE title LIKE ?", (f"%{title}%",))
results.extend([dict(row) for row in cur.fetchall()])
cur.execute("""
INSERT INTO search_history (isbn, title, found_in_db)
VALUES (?, ?, ?)
""", (isbn, title, len(results) > 0))
conn.commit()
conn.close()
return results
def get_low_rated_books(self, threshold=3.5):
conn = self.get_connection()
cur = conn.cursor()
cur.execute("""
SELECT * FROM books
WHERE amazon_rating IS NOT NULL
AND amazon_rating < ?
AND status = 'owned'
ORDER BY amazon_rating ASC
""", (threshold,))
books = [dict(row) for row in cur.fetchall()]
conn.close()
return books
def get_stats(self):
conn = self.get_connection()
cur = conn.cursor()
cur.execute("""
SELECT
status,
COUNT(*) as count,
AVG(amazon_rating) as avg_rating,
SUM(CASE WHEN sell_price IS NOT NULL THEN sell_price ELSE 0 END) as total_sales
FROM books
GROUP BY status
""")
stats = [dict(row) for row in cur.fetchall()]
conn.close()
return stats
class AmazonScraper:
@staticmethod
def isbn13_to_isbn10(isbn13):
if len(isbn13) != 13 or not isbn13.startswith('978'):
return isbn13
isbn10_base = isbn13[3:-1]
check = sum((i + 1) * int(x) for i, x in enumerate(isbn10_base)) % 11
check_digit = 'X' if check == 10 else str(check)
return isbn10_base + check_digit
@staticmethod
def search_amazon(isbn):
if not isbn:
return {'rating': None, 'review_count': 0, 'url': None}
try:
isbn10 = AmazonScraper.isbn13_to_isbn10(isbn)
url = f"https://www.amazon.co.jp/dp/{isbn10}"
headers = {
'User-Agent': 'Mozilla/5.0 (Linux; Android 10) AppleWebKit/537.36'
}
response = requests.get(url, headers=headers, timeout=15)
if response.status_code != 200:
return {'rating': None, 'review_count': 0, 'url': url}
soup = BeautifulSoup(response.content, 'html.parser')
rating = None
review_count = 0
rating_elem = soup.select_one('span.a-icon-alt')
if rating_elem:
rating_text = rating_elem.get_text()
try:
rating = float(rating_text.split()[0].replace('5つ星のうち', ''))
except:
pass
review_elem = soup.select_one('#acrCustomerReviewText')
if review_elem:
review_text = review_elem.get_text()
try:
review_count = int(''.join(filter(str.isdigit, review_text)))
except:
pass
return {'rating': rating, 'review_count': review_count, 'url': url}
except Exception as e:
print(f"Amazon検索エラー: {e}")
return {'rating': None, 'review_count': 0, 'url': None}
class BookManagerApp:
def __init__(self, root):
self.root = root
self.root.title("📚 蔵書管理 (簡易版)")
self.root.geometry("400x700")
self.db = BookDatabase()
self.image_dir = "book_images"
os.makedirs(self.image_dir, exist_ok=True)
self.current_image_path = None
self.current_books = []
self.low_rated_books = []
self.create_widgets()
self.show_welcome()
def show_welcome(self):
stats = self.db.get_stats()
total = sum(s['count'] for s in stats)
messagebox.showinfo("起動", f"📚 蔵書管理システム\n登録済み: {total}冊")
def create_widgets(self):
self.notebook = ttk.Notebook(self.root)
self.notebook.pack(fill='both', expand=True, padx=5, pady=5)
self.create_register_tab()
self.create_list_tab()
self.create_sell_tab()
self.create_check_tab()
self.create_stats_tab()
def create_register_tab(self):
tab = ttk.Frame(self.notebook, padding=10)
self.notebook.add(tab, text='📝 登録')
ttk.Label(tab, text="ISBN (13桁):").pack(anchor='w', pady=(5,0))
self.isbn_entry = ttk.Entry(tab)
self.isbn_entry.pack(fill='x', pady=2)
ttk.Label(tab, text="タイトル:").pack(anchor='w', pady=(10,0))
self.title_entry = ttk.Entry(tab)
self.title_entry.pack(fill='x', pady=2)
ttk.Label(tab, text="メモ:").pack(anchor='w', pady=(10,0))
self.notes_text = scrolledtext.ScrolledText(tab, height=6)
self.notes_text.pack(fill='both', expand=True, pady=2)
ttk.Button(tab, text="✅ 登録してAmazon評価取得",
command=self.register_book).pack(pady=10, fill='x')
ttk.Button(tab, text="🗑️ クリア",
command=self.clear_form).pack(fill='x')
# 使い方
help_text = """【使い方】
1. 本の裏表紙からISBNコード(13桁)を入力
2. タイトルを入力
3. 登録ボタンでAmazon評価を自動取得
4. 一覧タブで確認"""
ttk.Label(tab, text=help_text, foreground='gray',
justify='left').pack(pady=10, anchor='w')
def create_list_tab(self):
tab = ttk.Frame(self.notebook, padding=10)
self.notebook.add(tab, text='📋 一覧')
filter_frame = ttk.Frame(tab)
filter_frame.pack(fill='x', pady=5)
self.filter_var = tk.StringVar(value='all')
ttk.Radiobutton(filter_frame, text="全て", variable=self.filter_var,
value='all', command=self.load_books).pack(side='left')
ttk.Radiobutton(filter_frame, text="所有", variable=self.filter_var,
value='owned', command=self.load_books).pack(side='left')
ttk.Radiobutton(filter_frame, text="売却済", variable=self.filter_var,
value='sold', command=self.load_books).pack(side='left')
list_frame = ttk.Frame(tab)
list_frame.pack(fill='both', expand=True, pady=5)
scrollbar = ttk.Scrollbar(list_frame)
scrollbar.pack(side='right', fill='y')
self.book_listbox = tk.Listbox(list_frame, yscrollcommand=scrollbar.set)
self.book_listbox.pack(side='left', fill='both', expand=True)
scrollbar.config(command=self.book_listbox.yview)
self.book_listbox.bind('<>', self.on_book_select)
ttk.Label(tab, text="詳細:").pack(anchor='w')
self.detail_text = scrolledtext.ScrolledText(tab, height=10)
self.detail_text.pack(fill='both', expand=True, pady=5)
ttk.Button(tab, text="🔄 更新", command=self.load_books).pack(fill='x')
def create_sell_tab(self):
tab = ttk.Frame(self.notebook, padding=10)
self.notebook.add(tab, text='💰 売却')
ttk.Label(tab, text="低評価本（売却候補）",
font=('', 11, 'bold')).pack(pady=5)
criteria_frame = ttk.Frame(tab)
criteria_frame.pack(fill='x', pady=5)
ttk.Label(criteria_frame, text="評価:").pack(side='left')
self.rating_threshold = tk.DoubleVar(value=3.5)
ttk.Spinbox(criteria_frame, from_=1.0, to=5.0, increment=0.5,
textvariable=self.rating_threshold, width=5).pack(side='left', padx=2)
ttk.Label(criteria_frame, text="以下").pack(side='left')
ttk.Button(criteria_frame, text="🔍 検索",
command=self.find_low_rated).pack(side='left', padx=5, fill='x', expand=True)
list_frame = ttk.Frame(tab)
list_frame.pack(fill='both', expand=True, pady=5)
scrollbar = ttk.Scrollbar(list_frame)
scrollbar.pack(side='right', fill='y')
self.sell_listbox = tk.Listbox(list_frame, yscrollcommand=scrollbar.set)
self.sell_listbox.pack(side='left', fill='both', expand=True)
scrollbar.config(command=self.sell_listbox.yview)
price_frame = ttk.Frame(tab)
price_frame.pack(fill='x', pady=5)
ttk.Label(price_frame, text="売却価格:").pack(side='left')
self.sell_price_entry = ttk.Entry(price_frame, width=10)
self.sell_price_entry.pack(side='left', padx=2)
self.sell_price_entry.insert(0, "0")
ttk.Label(price_frame, text="円").pack(side='left')
ttk.Button(tab, text="✅ 売却済みにする",
command=self.mark_as_sold).pack(fill='x')
def create_check_tab(self):
tab = ttk.Frame(self.notebook, padding=10)
self.notebook.add(tab, text='🔍 重複確認')
ttk.Label(tab, text="購入前チェック",
font=('', 11, 'bold')).pack(pady=5)
ttk.Label(tab, text="ISBN:").pack(anchor='w')
self.check_isbn_entry = ttk.Entry(tab)
self.check_isbn_entry.pack(fill='x', pady=2)
ttk.Label(tab, text="タイトル:").pack(anchor='w')
self.check_title_entry = ttk.Entry(tab)
self.check_title_entry.pack(fill='x', pady=2)
ttk.Button(tab, text="🔍 重複チェック",
command=self.check_duplicate).pack(pady=10, fill='x')
self.check_result_text = scrolledtext.ScrolledText(tab)
self.check_result_text.pack(fill='both', expand=True, pady=5)
def create_stats_tab(self):
tab = ttk.Frame(self.notebook, padding=10)
self.notebook.add(tab, text='📊 統計')
self.stats_text = scrolledtext.ScrolledText(tab)
self.stats_text.pack(fill='both', expand=True, pady=5)
ttk.Button(tab, text="🔄 更新", command=self.load_stats).pack(fill='x')
self.load_stats()
def register_book(self):
isbn = self.isbn_entry.get().strip()
title = self.title_entry.get().strip()
notes = self.notes_text.get('1.0', 'end').strip()
if not isbn and not title:
messagebox.showwarning("警告", "ISBNまたはタイトルを入力してください")
return
if isbn:
duplicates = self.db.check_duplicate(isbn=isbn)
if duplicates:
if not messagebox.askyesno("重複", "既に登録されています。続行しますか？"):
return
messagebox.showinfo("処理中", "Amazon評価取得中...(15秒程度)")
self.root.update()
amazon_data = AmazonScraper.search_amazon(isbn)
book_data = {
'isbn': isbn,
'title': title,
'amazon_rating': amazon_data['rating'],
'amazon_review_count': amazon_data['review_count'],
'amazon_url': amazon_data['url'],
'image_path': None
}
book_id = self.db.add_book(book_data)
if book_id:
rating = f"★{amazon_data['rating']}" if amazon_data['rating'] else "未評価"
messagebox.showinfo("成功",
f"✅ 登録完了！\n\n{title}\nAmazon: {rating}")
self.clear_form()
self.load_books()
else:
messagebox.showerror("エラー", "登録失敗")
def load_books(self):
status = None if self.filter_var.get() == 'all' else self.filter_var.get()
books = self.db.get_books(status=status)
self.book_listbox.delete(0, 'end')
self.current_books = books
for book in books:
rating = f"★{book['amazon_rating']}" if book['amazon_rating'] else "未評価"
emoji = "📗" if book['status'] == 'owned' else "📕"
self.book_listbox.insert('end', f"{emoji} {book['title'][:30]} {rating}")
def on_book_select(self, event):
sel = self.book_listbox.curselection()
if not sel:
return
book = self.current_books[sel[0]]
detail = f"""【{book['title']}】
ISBN: {book['isbn'] or '未登録'}
評価: {book['amazon_rating'] or '未取得'}
レビュー: {book['amazon_review_count'] or 0}件
ステータス: {book['status']}
購入日: {book['purchase_date'] or '不明'}
登録日: {book['created_at'][:10] if book['created_at'] else '不明'}
URL: {book['amazon_url'] or 'なし'}
"""
self.detail_text.delete('1.0', 'end')
self.detail_text.insert('1.0', detail)
def find_low_rated(self):
threshold = self.rating_threshold.get()
books = self.db.get_low_rated_books(threshold)
self.sell_listbox.delete(0, 'end')
self.low_rated_books = books
for book in books:
self.sell_listbox.insert('end', f"{book['title'][:30]} ★{book['amazon_rating']}")
messagebox.showinfo("結果", f"{len(books)}冊見つかりました")
def mark_as_sold(self):
sel = self.sell_listbox.curselection()
if not sel:
messagebox.showwarning("警告", "本を選択してください")
return
book = self.low_rated_books[sel[0]]
try:
price = int(self.sell_price_entry.get())
except:
price = 0
if messagebox.askyesno("確認", f"売却済みにしますか？\n{book['title']}\n{price}円"):
updates = {
'status': 'sold',
'sell_date': datetime.now().strftime('%Y-%m-%d'),
'sell_price': price
}
self.db.update_book(book['id'], updates)
messagebox.showinfo("完了", "売却済みにしました")
self.find_low_rated()
self.load_stats()
def check_duplicate(self):
isbn = self.check_isbn_entry.get().strip()
title = self.check_title_entry.get().strip()
if not isbn and not title:
messagebox.showwarning("警告", "ISBNまたはタイトルを入力")
return
results = self.db.check_duplicate(isbn, title)
self.check_result_text.delete('1.0', 'end')
if results:
text = f"⚠️ {len(results)}冊が既に登録されています\n\n"
for book in results:
rating = f"★{book['amazon_rating']}" if book['amazon_rating'] else "未評価"
text += f"・{book['title']}\n {rating} ({book['status']})\n\n"
messagebox.showwarning("重複", "既に所有しています")
else:
text = "✅ 重複なし\n購入できます！"
messagebox.showinfo("OK", "重複なし")
self.check_result_text.insert('1.0', text)
def load_stats(self):
stats = self.db.get_stats()
books = self.db.get_books()
text = "="*35 + "\n📊 蔵書統計\n" + "="*35 + "\n\n"
total = sum(s['count'] for s in stats)
text += f"総数: {total}冊\n\n"
for stat in stats:
name = {"owned": "所有中", "sold": "売却済"}.get(stat['status'], stat['status'])
avg = f"{stat['avg_rating']:.2f}" if stat['avg_rating'] else "N/A"
text += f"{name}: {stat['count']}冊 (平均★{avg})\n"
self.stats_text.delete('1.0', 'end')
self.stats_text.insert('1.0', text)
def clear_form(self):
self.isbn_entry.delete(0, 'end')
self.title_entry.delete(0, 'end')
self.notes_text.delete('1.0', 'end')
if __name__ == '__main__':
root = tk.Tk()
app = BookManagerApp(root)
root.mainloop()
コメント
コメントを投稿