113 lines
4.2 KiB
Python
113 lines
4.2 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
Script to populate the jokes database with sample data.
|
||
"""
|
||
|
||
import sqlite3
|
||
import os
|
||
|
||
def populate_database():
|
||
# Connect to the database
|
||
db_path = 'jokes.db'
|
||
if not os.path.exists(db_path):
|
||
print(f"❌ Database {db_path} does not exist!")
|
||
print("Please run database.py first to create the database.")
|
||
return False
|
||
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
# Check if jokes table is empty before adding data
|
||
try:
|
||
cursor.execute('SELECT COUNT(*) FROM jokes')
|
||
count = cursor.fetchone()[0]
|
||
except sqlite3.OperationalError:
|
||
print("❌ The jokes table does not exist. Please run database.py first.")
|
||
conn.close()
|
||
return False
|
||
|
||
if count > 0:
|
||
overwrite = input(f"⚠️ Database already contains {count} jokes. Overwrite? (y/N): ")
|
||
if overwrite.lower() != 'y':
|
||
print("❌ Operation cancelled.")
|
||
conn.close()
|
||
return False
|
||
|
||
# Clear existing data first
|
||
# Only try to delete from user_sentiments if the table exists
|
||
try:
|
||
cursor.execute('DELETE FROM user_sentiments')
|
||
except sqlite3.OperationalError:
|
||
# user_sentiments table doesn't exist, which is OK
|
||
print("ℹ️ user_sentiments table does not exist yet.")
|
||
pass
|
||
cursor.execute('DELETE FROM jokes')
|
||
|
||
# Use the clean SQL file without SELECT statements
|
||
sql_file = 'clean_sample_data.sql'
|
||
if not os.path.exists(sql_file):
|
||
print(f"❌ SQL file {sql_file} does not exist!")
|
||
return False
|
||
|
||
try:
|
||
# Execute the clean SQL file directly
|
||
with open(sql_file, 'r', encoding='utf-8') as f:
|
||
sql_commands = f.read()
|
||
|
||
cursor.executescript(sql_commands)
|
||
|
||
conn.commit()
|
||
print(f"✅ Successfully populated the jokes table with sample jokes!")
|
||
|
||
# Count the total number of jokes
|
||
cursor.execute('SELECT COUNT(*) FROM jokes')
|
||
count = cursor.fetchone()[0]
|
||
print(f"📊 Total jokes in database: {count}")
|
||
|
||
# Add some sample user sentiments for the first few jokes
|
||
print("🎯 Adding sample user sentiments...")
|
||
jokes_with_sentiment = [(1, 'up'), (1, 'up'), (1, 'neutral'),
|
||
(2, 'down'), (2, 'up'),
|
||
(3, 'up'), (3, 'up'), (3, 'up')]
|
||
|
||
for joke_id, sentiment in jokes_with_sentiment:
|
||
try:
|
||
cursor.execute('''
|
||
INSERT INTO user_sentiments (joke_id, user_sentiment)
|
||
VALUES (?, ?)
|
||
''', (joke_id, sentiment))
|
||
except sqlite3.OperationalError:
|
||
# If user_sentiments table doesn't exist, skip adding sentiments
|
||
print("ℹ️ Skipping user sentiments as the table doesn't exist yet.")
|
||
break
|
||
|
||
conn.commit()
|
||
print(f"✅ Added sample user sentiments for {len(jokes_with_sentiment)} joke entries")
|
||
|
||
# Show user sentiment distribution if the table exists
|
||
try:
|
||
cursor.execute('SELECT user_sentiment, COUNT(*) FROM user_sentiments GROUP BY user_sentiment')
|
||
user_distribution = cursor.fetchall()
|
||
if user_distribution:
|
||
print(f'\n👥 User Sentiment distribution:')
|
||
for sentiment, cnt in user_distribution:
|
||
emoji = {'up': '👍', 'down': '👎', 'neutral': '😐'}[sentiment]
|
||
print(f' {emoji} {sentiment.capitalize()}: {cnt} ratings')
|
||
except sqlite3.OperationalError:
|
||
print("\nℹ️ User sentiment table not available yet.")
|
||
|
||
conn.close()
|
||
return True
|
||
except Exception as e:
|
||
print(f"❌ Error executing SQL commands: {e}")
|
||
conn.rollback()
|
||
conn.close()
|
||
return False
|
||
|
||
if __name__ == '__main__':
|
||
print("🔄 Populating jokes database with sample data...")
|
||
success = populate_database()
|
||
if success:
|
||
print("\n🎉 Database successfully populated! You can now run jokes.py to enjoy the jokes.")
|
||
else:
|
||
print("\n💥 Failed to populate the database.") |