資料庫py檔

[weiyellow2]

import sqlite3

def calculate_accuracy():

conn = sqlite3.connect('tweets.db')
cursor = conn.cursor()

cursor.execute("""
    SELECT COUNT(*) AS total_tweets,
           SUM(CASE WHEN emotion = real_emotion THEN 1 ELSE 0 END) AS correct_predictions,
           (CAST(SUM(CASE WHEN emotion = real_emotion THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*)) * 100 AS accuracy_percentage
    FROM Tweets;
""")
result = cursor.fetchone()
total_tweets, correct_predictions, accuracy_percentage = result

print(f"Total tweets analyzed: {total_tweets}")
print(f"Correct predictions: {correct_predictions}")
print(f"Accuracy percentage: {accuracy_percentage:.2f}%")

conn.close()

calculate_accuracy()

[weiyellow2]

import sqlite3

def clean_and_check_data():

conn = sqlite3.connect('tweets.db')
cursor = conn.cursor()

cursor.execute("UPDATE Tweets SET emotion = TRIM(LOWER(emotion)), real_emotion = TRIM(LOWER(real_emotion))")
conn.commit()

cursor.execute("""
    SELECT COUNT(*) AS total_tweets,
           SUM(CASE WHEN emotion = real_emotion THEN 1 ELSE 0 END) AS correct_predictions,
           (CAST(SUM(CASE WHEN emotion = real_emotion THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*)) * 100 AS accuracy_percentage
    FROM Tweets;
""")
result = cursor.fetchone()
print(f"清洗後 - 總分析推文數: {result[0]}")
print(f"正確預測數: {result[1]}")
print(f"準確率百分比: {result[2]:.2f}%")

conn.close()

clean_and_check_data()

[weiyellow2]

import sqlite3

連接到SQLite資料庫（如果資料庫不存在則會自動創建）

conn = sqlite3.connect('tweets.db') cursor = conn.cursor()

創建關鍵詞表（如果尚未創建）

cursor.execute('''CREATE TABLE IF NOT EXISTS Terms ( term_id INTEGER PRIMARY KEY, term_text TEXT NOT NULL )''')

從文件中讀取數據

file_path = 'term.txt.txt' with open(file_path, 'r', encoding='utf-8') as file: lines = file.readlines()

解析並插入數據

for line in lines: term_id, term_text = line.strip().split(', ') cursor.execute('INSERT INTO Terms (term_id, term_text) VALUES (?, ?)', (term_id, term_text))

提交事務並關閉連接

conn.commit() conn.close()

print("關鍵詞數據已成功插入SQLite資料庫。")

[weiyellow2]

import sqlite3 import csv

連接到SQLite資料庫（如果資料庫不存在則會自動創建）

conn = sqlite3.connect('tweets.db') cursor = conn.cursor()

創建關鍵詞表（如果尚未創建）

cursor.execute(''' CREATE TABLE IF NOT EXISTS Tweets ( tweet_id INTEGER PRIMARY KEY, text TEXT NOT NULL, emotion TEXT, real_emotion TEXT, cleaned_text TEXT, user_id INTEGER, term_id INTEGER, FOREIGN KEY(user_id) REFERENCES Users(user_id), FOREIGN KEY(term_id) REFERENCES Terms(term_id) ) ''')

從文件中讀取數據

file_path = 'tweets1.txt.csv' with open(file_path, 'r', encoding='big5') as file: csv_reader = csv.reader(file) next(csv_reader) # Skip the header row if there is one for row in csv_reader:

Insert data into Tweets table, adjust the number of ? to match number of columns

    cursor.execute('INSERT INTO Tweets (tweet_id, text, emotion, real_emotion, cleaned_text, user_id, term_id) VALUES (?, ?, ?, ?, ?, ?, ?)', row)

提交事務並關閉連接

conn.commit() conn.close()

print("推文數據已成功插入SQLite資料庫。")

[weiyellow2]

import sqlite3

連接到SQLite資料庫（如果資料庫不存在則會自動創建）

conn = sqlite3.connect('tweets.db') cursor = conn.cursor()

創建用戶表（如果尚未創建）

cursor.execute('''CREATE TABLE IF NOT EXISTS Users ( user_id INTEGER PRIMARY KEY, username TEXT NOT NULL, location TEXT )''')

從文件中讀取數據

file_path = 'user.txt.txt' with open(file_path, 'r', encoding='utf-8') as file: lines = file.readlines()

解析並插入數據

for line in lines: user_id, username, location = line.strip().split(', ') cursor.execute('INSERT INTO Users (user_id, username, location) VALUES (?, ?, ?)', (user_id, username, location))

提交事務並關閉連接

conn.commit() conn.close()

print("用戶數據已成功插入SQLite資料庫。")