File size: 6,579 Bytes
84d3b50 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | #!/usr/bin/env python3
"""
Setup library database schema in Supabase
Creates member, book, and borrow tables with sample data
"""
from supabase import create_client, Client
import pandas as pd
def setup_library_database():
"""Create the library database schema in Supabase"""
url = "https://bnjblzcqaumctpehgoid.supabase.co"
key = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM"
print("🚀 Setting up Library Database in Supabase")
print("=" * 50)
try:
# Create Supabase client
supabase: Client = create_client(url, key)
print("✅ Connected to Supabase!")
# Note: Direct SQL execution requires the service role key
# With anon key, we'll insert data into tables (assuming they exist)
print("\n📝 Creating sample data using Supabase API...")
# Insert members
print("👥 Adding members...")
try:
member_data = [
{"name": "Aisha Khan", "email": "aisha@example.org"},
{"name": "Omar Ali", "email": "omar@example.org"}
]
# Try to insert members
result = supabase.table("member").insert(member_data).execute()
print(f"✅ Added {len(result.data)} members")
except Exception as e:
print(f"⚠️ Members insertion note: {str(e)}")
print(" (This is expected if tables don't exist yet)")
# Insert books
print("📚 Adding books...")
try:
book_data = [
{"title": "Clean Data", "author": "J. Doe", "year": 2019},
{"title": "Relational Basics", "author": "S. Smith", "year": 2021}
]
result = supabase.table("book").insert(book_data).execute()
print(f"✅ Added {len(result.data)} books")
except Exception as e:
print(f"⚠️ Books insertion note: {str(e)}")
# Insert borrow records
print("📖 Adding borrow records...")
try:
borrow_data = [
{"member_id": 1, "book_id": 2, "borrow_date": "2025-09-01"},
{"member_id": 2, "book_id": 1, "borrow_date": "2025-09-02"}
]
result = supabase.table("borrow").insert(borrow_data).execute()
print(f"✅ Added {len(result.data)} borrow records")
except Exception as e:
print(f"⚠️ Borrow records insertion note: {str(e)}")
print("\n🔍 Checking what tables are accessible...")
# Check which tables we can access
tables_to_check = ["member", "book", "borrow"]
accessible_tables = []
for table_name in tables_to_check:
try:
result = supabase.table(table_name).select("*").limit(1).execute()
accessible_tables.append(table_name)
print(f"✅ Table '{table_name}' is accessible")
# Show sample data
if result.data:
print(f" Sample row: {result.data[0]}")
except Exception as e:
if "PGRST205" in str(e):
print(f"❌ Table '{table_name}' not found - needs to be created in Supabase SQL editor")
else:
print(f"⚠️ Table '{table_name}' access issue: {str(e)}")
if accessible_tables:
print(f"\n🎉 Found {len(accessible_tables)} accessible tables!")
# Show some sample queries
print("\n📊 Sample queries:")
if "member" in accessible_tables:
try:
members = supabase.table("member").select("*").execute()
print(f"Total members: {len(members.data)}")
if members.data:
df = pd.DataFrame(members.data)
print("Members:")
print(df.to_string(index=False))
except Exception as e:
print(f"Error querying members: {e}")
if "book" in accessible_tables:
try:
books = supabase.table("book").select("*").execute()
print(f"\nTotal books: {len(books.data)}")
if books.data:
df = pd.DataFrame(books.data)
print("Books:")
print(df.to_string(index=False))
except Exception as e:
print(f"Error querying books: {e}")
else:
print("\n📋 To create the tables, run this SQL in your Supabase SQL editor:")
print("""
-- Create tables
CREATE TABLE member (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(120) UNIQUE NOT NULL
);
CREATE TABLE book (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author VARCHAR(120) NOT NULL,
year INT CHECK (year >= 0)
);
CREATE TABLE borrow (
id BIGSERIAL PRIMARY KEY,
member_id BIGINT NOT NULL REFERENCES member(id) ON DELETE CASCADE,
book_id BIGINT NOT NULL REFERENCES book(id) ON DELETE CASCADE,
borrow_date DATE NOT NULL DEFAULT CURRENT_DATE,
return_date DATE,
CONSTRAINT borrow_unique_open UNIQUE (member_id, book_id, borrow_date)
);
-- Indexes
CREATE INDEX idx_borrow_member ON borrow(member_id);
CREATE INDEX idx_borrow_book ON borrow(book_id);
CREATE INDEX idx_book_title ON book(title);
-- Insert sample data
INSERT INTO member (name, email) VALUES
('Aisha Khan','aisha@example.org'),
('Omar Ali','omar@example.org');
INSERT INTO book (title, author, year) VALUES
('Clean Data','J. Doe',2019),
('Relational Basics','S. Smith',2021);
INSERT INTO borrow (member_id, book_id, borrow_date) VALUES
(1, 2, '2025-09-01'),
(2, 1, '2025-09-02');
""")
return accessible_tables
except Exception as e:
print(f"❌ Error setting up database: {str(e)}")
return []
if __name__ == "__main__":
tables = setup_library_database()
print(f"\n🚀 Ready to launch Gradio interface!")
print("Run: source venv/bin/activate && python app_supabase.py")
|