Assignment 1 Part 3-4.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"provenance":[]},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"code","execution_count":null,"metadata":{"id":"jWP_WSc11XG1"},"outputs":[],"source":["!pip install -U -q openai"]},{"cell_type":"code","source":["import openai\n","import base64\n","import textwrap\n","import json\n","from bs4 import BeautifulSoup\n","import requests\n","\n","key = 'c2stdmN2cHJyNGdrZFdBbGE0Z0g1SnFUM0JsYmtGSjJ5RTNkZnY5Q01zNVNGZndtenZq'\n","openai.api_key = base64.b64decode(key).decode('ascii')"],"metadata":{"id":"wUPOv992RjAr"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":[],"metadata":{"id":"VAbcKGwtCHSX"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!sudo pip3 install -U -q PyMySQL sqlalchemy sql_magic"],"metadata":{"id":"Hp7lfU8f_qcP"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["import openai\n","# Set up your OpenAI API key\n","openai.api_key = \"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\"\n","\n","# Define the get_completion function\n","def get_completion(sentiment_prompt, model=\"gpt-3.5-turbo\"):\n","    messages = [{\"role\": \"user\", \"content\": sentiment_prompt}]\n","    response = openai.ChatCompletion.create(\n","        model=model,\n","        messages=messages,\n","        temperature=0,  # this is the degree of randomness of the model's output\n","    )\n","    return response.choices[0].message[\"content\"]\n","\n"],"metadata":{"id":"KqHcfESg_rHD"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["create_table_query = \"\"\"CREATE TABLE IF NOT EXISTS cn2125_sentiment (\n","\n","                      url TEXT,\n","                      sentiment_score TEXT)\"\"\"\n","\n","# Set up the connection with the database\n","# The 'with' command ensures that the connection will be properly closed when done\n","with engine.connect() as connection:\n","    connection.execute(text(create_table_query))\n","            # Insert the sentiment score into a new table (e.g., 'sentiment_scores')\n"],"metadata":{"id":"IB6P8d2SCRSJ"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["create_table_query = \"\"\"CREATE TABLE IF NOT EXISTS cn2125_entities (\n","\n","                      url TEXT,\n","                      entities TEXT)\"\"\"\n","\n","# Set up the connection with the database\n","# The 'with' command ensures that the connection will be properly closed when done\n","with engine.connect() as connection:\n","    connection.execute(text(create_table_query))\n","            # Insert the sentiment score into a new table (e.g., 'sentiment_scores')\n"],"metadata":{"id":"6srC2ETpCRPj"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["create_table_query = \"\"\"CREATE TABLE IF NOT EXISTS cn2125_takeaways (\n","\n","                      url TEXT,\n","                      takeaways TEXT)\"\"\"\n","\n","# Set up the connection with the database\n","# The 'with' command ensures that the connection will be properly closed when done\n","with engine.connect() as connection:\n","    connection.execute(text(create_table_query))\n","            # Insert the sentiment score into a new table (e.g., 'sentiment_scores')\n"],"metadata":{"id":"4ck_ZAbXnOeL"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":[],"metadata":{"id":"MzZb8oX9jSfW"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["from sqlalchemy import create_engine, text\n","\n","# Define your connection string with the database name\n","conn_string = \"mysql+pymysql://{user}:{password}@{host}/{db_name}\".format(\n","    host=\"db.ipeirotis.org\", user=\"student\", password=\"dwdstudent2015\", db_name=\"public\"\n",")\n","\n","engine = create_engine(conn_string)\n","\n","select_query = \"SELECT * FROM cn2125_news\"  # Limiting to 10 rows for demonstration\n"],"metadata":{"id":"DgRe_T8GnOiC"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["insert_sentiment_query = f\"\"\"\n","                          INSERT IGNORE INTO\n","                          public.cn2125_sentiment\n","                          (url, sentiment_score)\n","                          VALUES (:url, :sentiment_score)\n","                          \"\"\"\n","\n","# Set up the connection with the database\n","with engine.connect() as connection:\n","    result = connection.execute(text(select_query))\n","\n","    # Iterate over the result set and print each row\n","    for row in result:\n","        full_text = row[7]  # Access the first (and only) column by its index (0)\n","        url = row[4]\n","        # Extract sentiment using ChatGPT\n","        sentiment_prompt = f\"The sentiment score of this article is (answers either its only negative, neutral, or positive. Only answer with one word)\\n\\nReview text: {full_text}\"\n","        sentiment_response = get_completion(sentiment_prompt)\n","        #print (entities_response)\n","\n","        sentiment_query_parameters={\n","            'url': url,\n","            'sentiment_score':sentiment_response\n","        }\n","\n","\n","        connection.execute(text(insert_sentiment_query), sentiment_query_parameters)\n","    connection.commit()\n"],"metadata":{"id":"ziHViRaJhNHp"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["insert_entities_query = f\"\"\"\n","                          INSERT IGNORE INTO\n","                          public.cn2125_entities\n","                          (url, entities)\n","                          VALUES (:url, :entities)\n","                          \"\"\"\n","# Set up the connection with the database\n","with engine.connect() as connection:\n","    result = connection.execute(text(select_query))\n","\n","    # Iterate over the result set and print each row\n","    for row in result:\n","        full_text = row[7]  # Access the first (and only) column by its index (0)\n","        url = row[4]\n","        # Extract sentiment using ChatGPT\n","        entities_prompt = f\"List the entities discussed in this article: (give answers with comma) \\n\\nReview text: {full_text}\"\n","        entities_response = get_completion(entities_prompt)\n","        #print (entities_response)\n","        entities_query_parameters={\n","            'url': url,\n","            'entities':entities_response\n","        }\n","\n","        connection.execute(text(insert_entities_query), entities_query_parameters)\n","    connection.commit()"],"metadata":{"id":"np-nlwU5K4WH"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["insert_takeaways_query = f\"\"\"\n","                          INSERT IGNORE INTO\n","                          public.cn2125_takeaways\n","                          (url, takeaways)\n","                          VALUES (:url, :takeaways)\n","                          \"\"\"\n","# Set up the connection with the database\n","with engine.connect() as connection:\n","    result = connection.execute(text(select_query))\n","\n","    # Iterate over the result set and print each row\n","    for row in result:\n","        full_text = row[7]  # Access the first (and only) column by its index (0)\n","        url = row[4]\n","        # Extract sentiment using ChatGPT\n","        takeaways_prompt = f\"Give a short takeaway of this article: \\n\\nReview text: {full_text}\"\n","        takeaways_response = get_completion(takeaways_prompt)\n","        #print (entities_response)\n","        takeaways_query_parameters={\n","            'url': url,\n","            'takeaways':takeaways_response\n","        }\n","\n","        connection.execute(text(insert_takeaways_query), takeaways_query_parameters)\n","    connection.commit()"],"metadata":{"id":"o4vJ-BSkK4cy"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":[],"metadata":{"id":"5fx64ARx4FAd"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":[],"metadata":{"id":"v9VF1fe94FDm"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":[],"metadata":{"id":"rsgTwwgX4FFV"},"execution_count":null,"outputs":[]}]}