DB_setup (old).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d2e36860",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np\n",
    "import psycopg2\n",
    "import pgvector\n",
    "from pgvector.psycopg2 import register_vector\n",
    "from langchain_huggingface import HuggingFaceEmbeddings\n",
    "from psycopg2.extras import execute_values\n",
    "from pgvector.psycopg2 import register_vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "071a9275",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_embeddings(text):\n",
    "    embeddings = HuggingFaceEmbeddings()\n",
    "    query_result = embeddings.embed_query(text)\n",
    "    return query_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8f5ec283",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('Listings_Details.csv')\n",
    "df.fillna(\"NA\", inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7a8db311",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "Hire our Handwashing Hubs. Location is NA. Hire our contactless handwashing facilities to help make your event safer. Give your patrons a dedicated space to wash their hands with water and soap, whilst supporting WaterAid's work to bring clean water to people around the world. Frequent handwashing is one of the most effective ways to stop the spread of COVID-19. With this is mind we have developed a freestanding handwashing station with running water and soap. Our contactless handwashing hubs features a foot pump and touchless soap dispensers to minimise contact - following the same design used in the countries we work.WaterAid staff will manage the space and ensure the Hubs are cleaned regularly, to the highest standard.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\chuae\\AppData\\Local\\Programs\\Python\\Python39\\lib\\site-packages\\sentence_transformers\\cross_encoder\\CrossEncoder.py:11: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from tqdm.autonotebook import tqdm, trange\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "768\n",
      "1\n",
      "Swim Serpentine. Location is Hyde Park, London. Enjoy a late summer swim in the beautiful surrounds of Hyde Park's Serpentine. Take in the views with a leisurely half mile or push yourself to complete the Super Six: whichever you choose, by joining Team WaterAid, you'll be helping to bring the joy of clean water to people around the world. \n",
      "768\n",
      "2\n",
      "World Water Day activities. Location is NA. Use World Water Day on 22 March to introduce your pupils to the importance of clean water, and explore why so many people are still living without. 703 million people in the world – that's almost one in ten of us – don't have clean water close to home.Without this basic human right, whole communities are held back: children miss out on an education, people struggle to earn a decent living, and families are often ill.Use World Water Day as an opportunity to introduce your pupils to the importance of water, learn about some of the barriers that prevent people accessing this vital resource, and explore how WaterAid works with communities to reach everyone, everywhere with clean water.\n",
      "768\n",
      "3\n",
      "Educational resources from your local water company. Location is NA. Discover the education opportunities your local water company is offering, including regional online resources, school talks and visits to their sites. In 1981, WaterAid was set up by members of the UK water industry. Their aim was simple – to unlock the potential of communities across the world by getting clean water, decent toilets and good hygiene to everyone.Today, our water industry partners remain at the heart of what we do, and make an invaluable contribution to helping change lives for good. We work closely with their education teams to help teach young people about the value of taps and toilets.\n",
      "768\n",
      "4\n",
      "Muslim Faith Giving. Location is NA. Around the world, many Muslim communities do not have clean water. It impacts their health, opportunities and religious practices. With your help, we can change this. Clean water and good hygiene means families can stay healthy, children can spend more time in school, women and men can earn a living. By giving Zakat or Sadaqah, you can make clean water a reality for more people around the world. Your donations fund vital projects that empower communities to change their lives for the better. \n",
      "768\n",
      "5\n",
      "KS2 resources. Location is NA. Take your KS2 pupils on a journey to meet children like Josy (above) and discover the importance of clean water, decent toilets and good hygiene through our curriculum-linked lesson plans and engaging activities. NA\n",
      "768\n",
      "6\n",
      "KS3: Climate change impacts. Location is NA. This lesson introduces the impacts of climate change, what they mean for our planet, and how they might change over time. By the end of this lesson, pupils will be able to:appraise and organise the different impacts of climate change,\n",
      "construct a chain of impacts of climate change.\n",
      "768\n",
      "7\n",
      "Give in celebration. Location is NA. Instead of receiving presents, why not pass on the gifts of clean water, decent toilets and good hygiene to those who need them most? From birthdays and weddings to religious festivals and anniversaries – mark a special date in your own life, and use your celebration to help others transform theirs for good. \n",
      "768\n",
      "8\n",
      "Henley Swim Festival. Location is Henley-on-Thames. Join Team WaterAid at this family-friendly festival, celebrating the joys of being next to, on, or in the river! Take your pick from a range of distances, from half a mile to 4km – perfect if you're new to outdoor swimming! Plus, with plenty of other activities on offer too – both in the water and firmly on solid ground – there's something for everyone to enjoy. Register for Henley Swim Festival Not the right challenge for you? You can also join us at one of Henley Swim's other iconic events, Club to Pub or the Thames Marathon. \n",
      "768\n",
      "9\n",
      "Bake for WaterAid. Location is NA. Sweet or savoury, everyone loves a bake sale – and few people would turn down a piece of cake for a good cause! If you don't want to be the only one slaving over a hot stove, get your colleagues or friends involved by adding a competitive element – nominate a few lucky judges and ask them to vote on the tastiest culinary contribution. Remind everyone it's all for a great cause with some WaterAid resources to draw attention to your sale. We can send you a tablecloth, balloons, bunting, leaflets and a collection tin – drop us a line at [email protected] and we'll be happy to help. \n",
      "768\n",
      "10\n",
      "KS1 resources. Location is NA. Introduce your KS1 pupils to children like Bienvenu (above) and discover the importance of clean water, decent toilets and good hygiene with our engaging lesson plans. NA\n",
      "768\n",
      "11\n",
      "Great North Run. Location is Newcastle. Take to the streets of Newcastle, Gateshead and South Shields as you join over 57,000 runners on the world's biggest half marathon. To help support all our runners in the lead up to race day we’ve teamed up with the company RunningWithUs to provide you with a special training package. As a member of our team you’ll receive:We also provide a fundraising pack to help you meet your targets, fundraising and event resources like balloons, collecting tins and banners (upon request) and regular newsletters with support and advice from our dedicated WaterAid events team.If you have a question or would like to chat about joining Team WaterAid please get in touch.It feels amazing knowing that a little bit of effort running an event can change someone’s life. And WaterAid were always there to support me all the way.Craig Holliday, previous runner A bespoke training plan – suitable for your level of experience\n",
      "Email helpline – ask a coach or physio your training, race, nutrition or injury questions\n",
      "Twitter and Facebook ask-the-coach services and webinar sessions\n",
      "A WaterAid running vest or technical t-shirt\n",
      "768\n",
      "12\n",
      "Play the WaterAid Minecraft map. Location is NA. Help bring water to a village in this brand new and exclusive WaterAid world for Minecraft. Collect all the resources you need to build pipes and taps, and learn about how important it is to have clean water close to home. Please note: in order to play this map you must have already purchased Minecraft. \n",
      "768\n",
      "13\n",
      "Listen to The Sound of Water voice app. Location is NA. Immerse yourself in the soothing sounds of rivers, lakes and waterfalls around the world with our new voice application. Perfect for zoning out, meditating or even helping you focus while working and being creative. NA\n",
      "768\n",
      "14\n",
      "KS2: Climate change. Location is NA. This lesson helps explain the global impacts of climate change, and introduces the idea of shared responsibility. By the end of this lesson, pupils will be able to:understand the effects of climate change on the world,\n",
      "understand that we have a shared responsibility to help.\n",
      "768\n",
      "15\n",
      "Henley Swim Festival: Club to Pub. Location is Henley Rowing Club. Enjoy a summer evening's swim from Henley Rowing Club – straight to the pub! Setting off in the early evening from Henley Rowing Club, you'll swim 400m upstream towards Marsh lock, before turning and hugging the Mill Meadow bank downstream, all the way to the Angel on the Bridge pub. Register for Club to Pub Not the right challenge for you? You can also join us at one of Henley Swim's other iconic events, Henley Swim Festival or the Thames Marathon. \n",
      "768\n",
      "16\n",
      "Swim Marathon. Location is Pool, lido or open water. Take on a challenge like no other as you swim a marathon or half marathon distance over 12 weeks. Whether you’re already a swimming fanatic or you're looking to dip your toe in for the first time, take your health, wellbeing and fitness to the next level – and raise funds for the almost 1 in 10 people who don’t have clean water close to home. How you do it is completely up to you: break your swim challenge down to weekly laps, or reach your distance over a few swims. What's important is you have fun, enjoy the water and raise some money to bring the joy of clean water to others around the world! \n",
      "768\n",
      "17\n",
      "WaterAid Scotland Munro Challenge. Location is Any of Scotland's 282 Munros!. Challenge yourself to tackle one of Scotland's highest peaks while supporting WaterAid's vital work in Rwanda, the land of a thousand hills. Scotland's 282 Munros (so named after Sir Hugh Munro, who first documented them all) are over 3,000 feet high and scattered across twelve regions.\n",
      "768\n",
      "18\n",
      "RideLondon 100. Location is London and Essex. Join thousands of riders on the capital's biggest closed-road cycling event, taking you from the heart of London to the rolling lanes of Essex – and back again! The RideLondon-Essex 100 is a unique opportunity to ride 100 miles on traffic-free roads, through the heart of London and out into neighbouring Essex. Starting on Victoria Embankment and finishing at Tower Bridge, the ride will take you through Epping Forest and past some of Essex's most picturesque villages – including a section of the 2014 Tour de France route. Join us – and together we can make clean water, decent toilets and good hygiene a normal part of daily life, for everyone, everywhere. For a taster of what it's like to take part in the RideLondon-Surrey 100 for WaterAid, check out our thank you film from 2019: \n",
      "768\n",
      "19\n",
      "KS3 resources. Location is NA. Teach your KS3 pupils about the water and sanitation crisis, discover the effect it has on girls like Julianna, and explore simple solutions that are helping solve the crisis. NA\n",
      "768\n",
      "20\n",
      "Make your donation go further with Gift Aid. Location is NA. Your donation can have an even bigger impact. By using Gift Aid, for every pound you give to WaterAid, we get an extra 25p from HMRC at no extra cost to you. You can help even more people like Lucie from Madagascar to transform their lives with clean water, decent toilets and good hygiene. Using Gift Aid means that for every pound you give to WaterAid, we get an extra 25p from HMRC at no extra cost to you, helping your donation do so much more. Please fill out the form to make your donation go further with Gift Aid. These details are required so we can claim Gift Aid on your behalf. \n",
      "768\n",
      "21\n",
      "Wordsearch. Location is NA. Use this wordsearch to encourage word recognition and recap vocabulary relevant to WaterAid. With thanks to Dave Padfield, long-term volunteer and former teacher, for creating this resource. Download wordsearch sheet (PDF) Download wordsearch sheet with answers (PDF) \n",
      "768\n",
      "22\n",
      "Dragon Boat Race. Location is South Dock, Canary Wharf. Get a team together and join us as we take over Canary Wharf’s South Dock for another fun filled, fast paced day on the water! Based on a Chinese tradition dating back over 2,000 years, dragon boat racing is one of the fastest-growing and most popular corporate sports in the UK today.Suitable for all ages and skill levels, you don’t need any previous experience to join us: our expert partners, Dragon Boat Events, provide full training and all equipment – so all you need to bring is your team and a boatload of enthusiasm!Register your interest for 2025\n",
      "768\n",
      "23\n",
      "KS3: Managing water and climate change. Location is NA. This lesson explores the impacts of climate change on people’s access to water, and introduces different ways of managing these impacts. By the end of this lesson, pupils will be able to:investigate the best methods for solving water access issues,\n",
      "select and defend the best solution to solve a water access problem.\n",
      "768\n",
      "24\n",
      "KS2: Climate change impacts. Location is NA. This lesson introduces the impacts of climate change, what they mean for our planet, and how they might change over time. By the end of this lesson, pupils will be able to:compare the impacts of climate change,\n",
      "explain how climate change might affect our lives.\n",
      "768\n",
      "25\n",
      "WaterAid's Good Loos. Location is NA. Are you a festival or event organiser, looking for a block of environmentally friendly, self-sufficient, composting toilets? A block of toilets that will make a difference not only to your event’s attendees, but also to people around the world? The Good Loos are an environmentally friendly, self-sufficient block of composting toilets with toilet paper and sanitary bins.They have capacity for between 10-20 individual loos, plus one accessible loo, in either one large or two smaller blocks.We work with J Healy Productions who support the Good Loos through the toilets' set up, maintenance and pack down. Check out our hire options belowFancy volunteering at our Good Loos?Check out our festival volunteering opportunities\n",
      "768\n",
      "26\n",
      "Stream for Water. Location is NA. Unite with gamers and streamers to help bring clean water, decent toilets and good hygiene to everyone, everywhere. Whether you stream on Twitch, YouTube or any other live platform, join us to help raise money for our life-changing water, sanitation and hygiene work around the world. \n",
      "768\n",
      "27\n",
      "Great North Swim. Location is Windermere, Lake District. Whether you’re already a swimming fanatic or you're looking for a brand-new challenge, join team WaterAid and help bring the joy of clean water to people around the world. The Great Swim Series has provided thousands of swimmers with exhilarating safe open water swimming events for over ten years. The Great North Swim offers a range of distances – from the family-friendly to the Olympic-level – so there really is an event for everyone! \n",
      "768\n",
      "28\n",
      "Hackney Half Marathon. Location is Hackney Marshes, London. Cross the finish line into a full festival of fitness on Hackney Marshes – just like the borough, this route brings the spirit! Registrations to join Team WaterAid for the Hackney Half Marathon 2025 are now open!Register for the Hackney Half 2025If you've got your own place in next year's race, we'd love for you to join us! Let us know by emailing us on [email protected], or by setting up a fundraising page on JustGiving – they'll tell us you're raising money for WaterAid, and then we'll get in touch with you.\n",
      "768\n",
      "29\n",
      "Philanthropy and trusts. Location is NA. [Hidden] Invest in WaterAid and you’ll be joining an established movement of philanthropists, trusts and foundations who share our vision of a world where everyone, everywhere has clean water, decent toilets and good hygiene.With all three, we can unlock progress for people, communities and economies worldwide. And with major investment, we can accelerate that progress and demand change on a global scale.\n",
      "768\n",
      "30\n",
      "KS4: Human causes of climate change. Location is NA. This lesson introduces the anthropogenic causes of climate change and explores how human activity is exacerbating the greenhouse effect. By the end of this lesson, pupils will be able to:explain the human causes of climate change,\n",
      "judge the evidence of anthropogenic climate change.\n",
      "768\n",
      "31\n",
      "KS3: Managing climate change. Location is NA. This lesson asks how we can manage the impacts of climate change, and the explores the differences between adaptation and mitigation techniques. By the end of this lesson, pupils will be able to:interpret the methods of managing climate change,\n",
      "critique the best techniques for managing climate change.\n",
      "768\n",
      "32\n",
      "KS4: Managing climate change. Location is NA. This lesson asks how we can manage the impacts of climate change, and the explores the differences between adaptation and mitigation techniques. By the end of this lesson, pupils will be able to:explain different methods of managing climate change,\n",
      "evaluate the most appropriate management techniques.\n",
      "768\n",
      "33\n",
      "KS2: Managing climate change. Location is NA. This lesson asks how we can manage the impacts of climate change, and the explores the differences between adaptation and mitigation techniques. By the end of this lesson, pupils will be able to:compare different ways of managing climate change,\n",
      "select the most appropriate management techniques.\n",
      "768\n",
      "34\n",
      "Book a free Speaker. Location is NA. Our 100 trained Speakers are experts in water, sanitation and hygiene. We've delivered over 12,000 talks and workshops, covering topics including climate change, COVID-19, periods and more! Our Speaker-led talks are for any audience interested in learning about WaterAid and our work.\n",
      "768\n",
      "35\n",
      "Lent Appeal: Jars of Change. Location is NA. Together, we can help those hit hardest by climate change to prepare for a more certain future – whatever the weather. If you were able to support our Jars of Change appeal this Lent, thank you! \n",
      "768\n",
      "36\n",
      "Add the gift of a donation in your will. Location is NA. Including the gift of clean water in your will is a wonderful way for you to help future generations transform their lives. We won’t stop until clean water, decent toilets and good hygiene are normal for everyone, everywhere. You can help create this future with a gift in your will.To add WaterAid to your will you need our name, address and registered charity numbers: WaterAid, 6th Floor, 20 Canada Square, London, E14 5NNCharity registration numbers: 288701 (England and Wales) and SC039479 (Scotland)You can order your free information pack, which contains all the practical information you need to include a gift to WaterAid in your will by clicking or tapping the button below.Order your free gifts in wills guideOr, you can simply download the information pack (PDF) now here.Adding water to your will is not just practical, giving people clean water that’s safe to drink, somewhere decent to go to the toilet and the means to wash their hands through good hygiene. It’s more personal than that. It’s your life meaning the most to someone else’s by giving them the opportunity to unlock their potential.Almost 1 in 10 people around the world don't have clean water close to home, and almost 1 in 4 don't have a decent toilet of their own. By adding water to your will, you can fund the taps and toilets that communities need to help them change their own lives forever.At WaterAid, we are grateful for every gift we receive from wonderful supporters like you. This income means that year on year, we can deliver more of the taps and toilets needed to ensure that everyone, everywhere has the clean water, decent toilets and good hygiene they need to transform their own lives.We spend every pound carefully, making sure that we use these gifts in the best possible way by ensuring that each community gets the clean water systems, decent toilets and hygiene awareness that are right for them, and that will last well into the future.With the help of amazing people like you, we've already reached millions of people with clean water, decent toilets and good hygiene, enabling entire communities to unlock their potential, break free from poverty and change their lives for good.In the last year alone, we supported:\n",
      "768\n",
      "37\n",
      "Menstrual Hygiene Day activities. Location is NA. 28 May is Menstrual Hygiene Day: the perfect opportunity to explore gender inequality, menstrual hygiene management, and issues around access to water and toilets. On any given day, more than 300 million people are on their period – but a quarter of them have to manage without essential clean water, decent toilets and good hygiene knowledge. \n",
      "768\n",
      "38\n",
      "Sign up to the schools newsletter. Location is NA. Sign up to our e-newsletter for everything you need to bring WaterAid into your classroom. Our regular emails include new learning resources and lesson plans for all key stages,  exciting news and fundraising inspiration. NA\n",
      "768\n",
      "39\n",
      "Volunteer at an event. Location is NA. Join us on the sideline at some of our events and give our amazing fundraisers the support they deserve. Every event is different, but one thing's for certain: there's always an incredible atmosphere, passionate people, and terrific displays of dedication! We always need extra support to make sure everything runs smoothly: you could be cheering on our fundraisers, face-painting, presenting medals, giving out water, course marshalling, or registering participants. Once you complete our application form, we’ll be able to let you know what roles we have available and how you can help. Apply to volunteer with WaterAid \n",
      "768\n",
      "40\n",
      "TCS London Marathon. Location is London. With an electric atmosphere, an iconic route, and great support along the way, this is a bucket list race for a reason! One of the six World Marathon Majors and a firm fixture in the UK's cultural calendar, the TCS London Marathon truly is a race like no other. You'll run past some of the capital's greatest attractions – supported by masses of cheering crowds along the whole route – before celebrating an iconic finish on the Mall. \n",
      "768\n",
      "41\n",
      "Royal Parks Half Marathon. Location is Hyde Park, London. Wind through four of London's eight Royal Parks on this stunningly scenic half marathon. Starting and finishing in Hyde Park, you'll run along closed roads and through three more of London's eight Royal Parks – The Green Park, St James's Park, and Kensington Gardens – as you pass by some of the capital's most recognised landmarks. When you cross the finish line, you won't just be achieving your own goal: you'll be helping others reach theirs too, thanks to the life-changing power of clean water, decent toilets and good hygiene. \n",
      "768\n",
      "42\n",
      "Just Water. Location is NA. Drink nothing but water as you take part in the ultimate detox – for a week, a fortnight, or even the whole month – and fundraise to give others the gift of clean water. After the over-indulgence of the festive season, January is the perfect time to give your body a fresh start! Plus, getting sponsored for this challenge means the benefits will extend far beyond just you – you'll help us reach the almost 1 in 10 people around the world who don't have access to clean water. Are you in? Registrations for January 2025 will open later this year. \n",
      "768\n",
      "43\n",
      "Climate change teaching resources. Location is NA. Use our curriculum-based lesson plans, games, activities and films to explore climate change with your pupils: from its causes and impacts on people and the planet, to the different approaches for managing our response to it. NA\n",
      "768\n",
      "44\n",
      "KS4 resources. Location is NA. Learn about the water and sanitation crisis with your KS4 pupils, explore the idea of human rights and inspire your pupils to help reach children like Cheick (above left) with clean water, decent toilets and good sanitation. NA\n",
      "768\n",
      "45\n",
      "KS4: Evidence of climate change. Location is NA. This lesson introduces scientific methods for measuring climate change, and asks students to analyse different sources of information.. By the end of this lesson, pupils will be able to:compare different ways of managing climate change,\n",
      "select the most appropriate management techniques.\n",
      "768\n",
      "46\n",
      "KS3: Causes of climate change. Location is NA. This lesson introduces the differences between weather and climate, describes the atmosphere and the greenhouse effect, and explores sources of greenhouse gases in daily activities. By the end of this lesson, pupils will be able to:explain the greenhouse effect,\n",
      "judge the evidence of natural climate change.\n",
      "768\n",
      "47\n",
      "Fundraising ideas for schools. Location is NA. There are loads of great ways to raise money for WaterAid at your school, with activities that your pupils will love. NA\n",
      "768\n",
      "48\n",
      "KS4: Managing water and climate change. Location is NA. This lesson explores the impacts of climate change on people’s access to water, and introduces different ways of managing these impacts. By the end of this lesson, pupils will be able to:investigate the best methods for solving water access issues,select and defend the best solution to solve a water access problem.investigate the best methods for solving water access issues,\n",
      "\n",
      "\n",
      "select and defend the best solution to solve a water access problem.\n",
      "768\n",
      "49\n",
      "The WaterAid Raffle. Location is NA. Playing our raffle helps to bring clean water, decent toilets and good hygiene to communities around the world, ensuring girls like Malala can go to school, gain an education, and achieve their goals. NA\n",
      "768\n",
      "50\n",
      "Ride Across Britain: Land's End to John O'Groats. Location is Land's End to John O'Groats. Land’s End to John O’Groats by bike: it’s a bucket-list challenge for a reason! Take on the ultimate UK cycle challenge over nine days, fully supported and with all the logistics taken care of – so all you need to do is focus on enjoying this iconic ride.From clifftops to moorlands, along hidden backroads and over soaring peaks: you’ll experience the most spectacular cycling the UK has to offer as you pedal through 23 counties across England, Wales and Scotland.Find out more on the Ride Across Britain website\n",
      "768\n",
      "51\n",
      "No water, no lessons, no choice: Educational resources. Location is NA. Use these classroom resources, created in partnership with Anglian Water and Welsh Water, to engage your pupils with issues surrounding WASH (water, sanitation and hygiene) and girls’ access to education. A film, presentations, teacher notes, games and worksheet activities are linked to a variety of subjects and aligned to the English and Welsh curriculum. With a focus on Nepal, activities introduce pupils to the themes of access to WASH, and its impacts on girls' education. Students will develop their key skills and understanding, within the context of international development. Resources can be used as part of a sequence of learning, a one-off lesson, or as standalone activities. With thanks to Anglian Water and Welsh Water for creating the lesson plans and worksheets in partnership with us. \n",
      "768\n",
      "52\n",
      "Yorkshire Three Peaks weekend. Location is Chapel-le-Dale, North Yorkshire. Summit Ingleborough, Whernside and Pen y Ghent on this tough – but achievable! – weekend. Join a small group of like-minded trekkers on this fully-supported challenge, led by the experts at Discover Adventure.How to register\n",
      "768\n",
      "53\n",
      "Global Handwashing Day: Germ craft activity. Location is NA. Learn what germs do, how they spread, and how we can get rid of them with good hand hygiene. Use this craft-based activity to help young people understand that we can't see germs, and need to wash our hands even when they look or feel clean. Students can complete the activity in pairs or individually, depending on access to materials and time available. Download the activity pack (PDF) \n",
      "768\n",
      "54\n",
      "London to Brighton Bike Ride. Location is Clapham Common, London to Brighton beachfront. Join the ride of your life on this iconic city to coast challenge – and help us reach the 1 in 10 people worldwide who still don't have access to clean water. Perfect if you're new to cycling or a regular weekend rider, London to Brighton is an iconic route, taking you out of the city, through the countryside, to the south coast. There's support along the way and a beachside event village at the finish line, ready for you to put your feet up for a well-earned rest! Register directly with the London to Brighton Bike Ride organisers, and fundraise as much as you can for WaterAid.  \n",
      "768\n",
      "55\n",
      "Films and songs. Location is NA. How do you make water and sanitation issues engaging for students? Teach them using songs and videos! NA\n",
      "768\n",
      "56\n",
      "Ways to donate to WaterAid in memory of a loved one. Location is NA. Thank you for choosing to donate in memory of your loved one. Your gift not only honours them, but will provide clean water, decent toilets and improved hygiene to people like Madeleine Ravaonarivo, known as Mamavao, and her grandson Rindra. Make a donation in memory of your loved one Alternatively, you can set up a Lasting Love tribute space to share with others and contribute to collectively. \n",
      "768\n",
      "57\n",
      "Fundraising ideas for schools. Location is NA. There are loads of great ways to raise money for WaterAid at your school, with activities that your pupils will love. NA\n",
      "768\n",
      "58\n",
      "Soap experiment. Location is NA. Learn how soap works to keep us safe, with this simple interactive experiment! Washing our hands is vital to stop the spread of diseases like flu and COVID-19. But did you know that almost 2 billion people in the world don't have the soap and/or water they need to wash their hands at home? This experiment is a great way to get young people talking about handwashing, as well as introducing them to the science behind it through fun, practical learning. Download the experiment activity sheet (PDF) Download the supporting activity guide (PDF) \n",
      "768\n",
      "59\n",
      "Colouring sheets. Location is NA. Use our free colouring sheets to support geography lessons, or as a calming mindfulness activity. Choose from our six beautiful drawings, created by illustrator Patrick Miller, and get creative with some colouring! \n",
      "768\n",
      "60\n",
      "Edinburgh Marathon Festival. Location is Edinburgh, Scotland. Take in stunning views and iconic landmarks as you represent WaterAid at Scotland's biggest running event. Fast, flat routes combined with striking surrounds make Edinburgh perfect for runners of all abilities – and come with genuine PB potential! \n",
      "768\n",
      "61\n",
      "University and RAG fundraising. Location is NA. Are you passionate about everyone having access to clean water? Do you want to make a difference during your time at university? Get involved with WaterAid as a student and turn your passion into action. Here in the UK, we probably take clean water, decent toilets and good hygiene for granted. But millions of people still don't have these vital services. Help us make them normal for everyone, everywhere within a generation - there are so many ways to get involved... \n",
      "768\n",
      "62\n",
      "KS4: Climate change impacts. Location is NA. This lesson introduces the impacts of climate change, and asks what they will mean for society and the environment. By the end of this lesson, pupils will be able to:appraise and organise the different impacts of climate change,\n",
      "construct a chain of impacts of climate change.\n",
      "768\n",
      "63\n",
      "Volunteer with the Community and Events team. Location is NA. Develop your skills while helping the Community and Events team provide excellent stewardship to WaterAid supporters. Please note: this is a voluntary role, with travel expenses to/from the WaterAid office in Canary Wharf and in-person events covered. Responsibilities include: This role also has the possibility to extend to assist with other events and wider team activities. Download the full role description (PDF) \n",
      "768\n",
      "64\n",
      "KS2: Causes of climate change. Location is NA. This lesson introduces the differences between weather and climate, describes the atmosphere and the greenhouse effect, and explores sources of greenhouse gases in daily activities. By the end of this lesson, pupils will be able to:explain the causes of climate change,\n",
      "identify activities which create climate change.\n",
      "768\n",
      "65\n",
      "London Landmarks Half Marathon. Location is Pall Mall, London. Celebrate the very best of the capital – past and present – on this unique half marathon, the only one to cross both the City of London and the City of Westminster. Expect a wonderfully celebratory atmosphere, plenty of quirky discoveries and surprises, and – of course! – the unique opportunity to run past London's most iconic landmarks on closed roads. Run for WaterAid and when you cross the finish line, you won't just be achieving your own goal: you'll be helping others reach theirs too, thanks to the life-changing power of clean water, decent toilets and good hygiene. Register to join our LLHM 2025 team \n",
      "768\n",
      "66\n",
      "Berlin Marathon. Location is Berlin, Germany. Run for WaterAid in the BMW Berlin Marathon – one of the six renowned World Marathon Majors. A fast, flat route around this vibrant, fascinating city makes Berlin a great choice for first-timers and seasoned pros alike!Attracting over 40,000 runners from around the globe – plus thousands more supporters lining the streets – this popular race enjoys a truly celebratory carnival atmosphere, along with a great chance of setting a new personal best.What a great day running the Berlin Marathon for WaterAid! Loved running in this great city.Debbie Shearing, previous runner\n",
      "768\n",
      "67\n",
      "Hire our event services. Location is NA. If you’re an event organiser looking to hire water, sanitation and hygiene services, such as toilets, handwashing stations or waterpoint management, look no further than WaterAid’s special events team! Our mission is to bring clean water, decent toilets and good hygiene to everyone, everywhere, which means we know a thing or two about creating high-quality sanitation services. What's more, we've been working with major festivals and events for decades, taking our dedicated volunteers to deliver efficient, friendly and great value services. \n",
      "768\n",
      "68\n",
      "World Toilet Day activities. Location is NA. Use World Toilet Day on 19 November to learn more about the importance of decent toilets. It can be easy to take a simple toilet for granted. But 1.5 billion people – that's almost one in five of us – don’t have a decent one of their own.Not having access to a decent toilet isn't just undignified – it's dangerous.With no way of managing human waste properly, diseases spread quickly. Going outside is unsafe, especially at night, and for women and children.And, when there's no loo at school or work, many people stay at home instead – missing out on important opportunities to get an education, earn a decent living, and improve their lives.\n",
      "768\n",
      "69\n",
      "Paying in your money. Location is NA. Thank you for raising money for WaterAid! There are a number of ways you can pay it in and the sooner you do, the sooner we can use your fundraising money to bring clean water, decent toilets and good hygiene to everyone, everywhere. You can send in your donation in a number of different ways.You can use our online paying in form, which allows you to pay via credit or debit card or PayPal.Pay in onlineIf you’re making a bank transfer please use the following details:WaterAid Current Account\n",
      "Barclays Bank PLC\n",
      "Sort code: 20-65-82\n",
      "Account number: 20422746Make sure you also fill in and send us the relevant donation form (see below). Without the form we won't know where the money has come from, or be able to thank you for your generosity!If you’re sending in a cheque, please make it payable to WaterAid and send it to:FREEPOST RUGA-JGET-UXRZ\n",
      "WaterAid\n",
      "126 Fairlie Road\n",
      "Slough\n",
      "SL1 4PYPlease also fill in the relevant donation form (see below) and send it in to us along with your cheque.If you want to pay your money in over the phone, or you have any queries about the paying in process, then give our Supporter Care team a call on 020 7793 4594 – they'll be happy to help!\n",
      "768\n",
      "70\n",
      "The Severn Trent Mountain Challenge. Location is NA. Challenge yourself to trek 10, 20 or 30 miles through the challenging terrain and glorious views of the Peak District. Setting off from Tittesworth Reservoir Visitor Centre, you'll self-navigate across the edge of the Peak District, flexing your map-reading and orienteering skills along the way.Take on some exhilarating inclines – and enjoy spectacular views from the top! – before descending back to the reservoir for refreshments and hot food.All funds raised will go towards our work to bring clean water, decent toilets and good hygiene to communities in Bangladesh, supported by the Severn Trent WaterAid committee.\n",
      "768\n",
      "71\n",
      "Henley Swim Festival: Thames Marathon. Location is Marlow. Push yourself with a half marathon challenge (5.5km), or tackle the full 13km from Henley to Marlow! Covering a beautiful stretch of the river, the Thames Marathon has become one of open water swimming's most prestigious endurance swims. Register for the Thames Marathon Not the right challenge for you? You can also join us at one of Henley Swim's other iconic events, Henley Swim Festival or Pub to Club. \n",
      "768\n",
      "72\n",
      "KS4: Natural causes of climate change. Location is NA. This lesson introduces the natural causes of climate change and explores the greenhouse effect. By the end of this lesson, pupils will be able to:explain the greenhouse effect,\n",
      "judge the evidence of natural climate change.\n",
      "768\n",
      "73\n",
      "Recycle and donate your unwanted items to WaterAid. Location is NA. Clearing out your cupboards? Don’t throw your unloved items away! Your old mobile phone, unwanted jewellery and even your old car can change lives, if you recycle them with WaterAid. Love your new phone, but not sure what to do with your old one? Recycle it for WaterAid and we’ll use the money to transform lives with clean water, decent toilets and good hygiene. You could even arrange a collection at your workplace or school. And with every phone raising an average of £5, you’ll soon be making a big difference.Ready to get started? Follow these four easy steps on Fonebank >\n",
      "768\n",
      "74\n",
      "Trek Kilimanjaro. Location is NA. Tackle Kilimanjaro: Africa's highest peak, and one of the most impressive and recognisable mountains in the world. Join a small group of like-minded adventurers, led by an expert team from Discover Adventure, to take on this once-in-a-lifetime challenge.\n",
      "768\n",
      "75\n",
      "Join the Pupil Pipeline. Location is NA. Our educational challenge brings to life topics from Science, Geography, Art, and Design and Technology, whilst helping your pupils learn about water issues around the world. Pupils need to work together to transport 10 litres of water over 50 metres, within 20 minutes, with as little waste as possible. The catch? Nobody can leave their spot in the line!You can choose when to take on the challenge, and your pupils could compete against other classes, or even against local schools! The winning team is the one to transport the most water with the least waste, within the time allowed.Register for your free resources to take part\n",
      "768\n",
      "76\n",
      "                                         Listing URL  \\\n",
      "0  https://www.wateraid.org//uk/get-involved/givi...   \n",
      "1  https://www.wateraid.org//uk/get-involved/even...   \n",
      "2  https://www.wateraid.org//uk/get-involved/teac...   \n",
      "3  https://www.wateraid.org//uk/get-involved/teac...   \n",
      "4  https://www.wateraid.org//uk/get-involved/givi...   \n",
      "\n",
      "                                    Name of Activity               Date  \\\n",
      "0                          Hire our Handwashing Hubs                 NA   \n",
      "1                                    Swim Serpentine  14 September 2024   \n",
      "2                         World Water Day activities                 NA   \n",
      "3  Educational resources from your local water co...                 NA   \n",
      "4                                Muslim Faith Giving                 NA   \n",
      "\n",
      "            Location                                     Event Synopsis  \\\n",
      "0                 NA  Hire our contactless handwashing facilities to...   \n",
      "1  Hyde Park, London  Enjoy a late summer swim in the beautiful surr...   \n",
      "2                 NA  Use World Water Day on 22 March to introduce y...   \n",
      "3                 NA  Discover the education opportunities your loca...   \n",
      "4                 NA  Around the world, many Muslim communities do n...   \n",
      "\n",
      "                                   Event Description  \\\n",
      "0  Frequent handwashing is one of the most effect...   \n",
      "1  Take in the views with a leisurely half mile o...   \n",
      "2  703 million people in the world – that's almos...   \n",
      "3  In 1981, WaterAid was set up by members of the...   \n",
      "4  Clean water and good hygiene means families ca...   \n",
      "\n",
      "                                   Registration Link   Activity Category  \\\n",
      "0                                                 NA              giving   \n",
      "1  https://forms.office.com/Pages/ResponsePage.as...              events   \n",
      "2                                                 NA  teaching resources   \n",
      "3                                                 NA            teaching   \n",
      "4                                                 NA              giving   \n",
      "\n",
      "                                          embeddings  \n",
      "0  [-0.01802974008023739, -0.020945101976394653, ...  \n",
      "1  [-0.0447535440325737, -0.014815650880336761, -...  \n",
      "2  [-0.012358235195279121, 0.0913315936923027, -0...  \n",
      "3  [0.005786288529634476, 0.0727236419916153, -0....  \n",
      "4  [0.005968248005956411, 0.03302057459950447, -0...  \n"
     ]
    }
   ],
   "source": [
    "all_embeddings = []\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    print(index)\n",
    "    text_to_embed = row[1] + \". Location is \" + row[3] + \". \" + row[4] + \" \" + row[5]\n",
    "    print(text_to_embed)\n",
    "    final_embeddings = get_embeddings(text_to_embed)\n",
    "    print(len(final_embeddings))\n",
    "    all_embeddings.append(final_embeddings)\n",
    "\n",
    "df['embeddings'] = all_embeddings\n",
    "\n",
    "print(len(df))\n",
    "print(df.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e5381ce4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The following code cells involve setting up the database with a table that provides a simple view of the primary data. \n",
    "# These code were implemented with reference to the approach used in this URL: https://www.timescale.com/blog/postgresql-as-a-vector-database-create-store-and-query-openai-embeddings-with-pgvector/.\n",
    "\n",
    "#connect to postgresql database \n",
    "    # This connection string uses local database  \n",
    "connection_string = \"postgresql://postgres:123456@localhost:5433/wateraid\"\n",
    "    # This connection string uses database hosted on Neon but is commented out because:\n",
    "    # ultimately, decided to use local database since embeddings generated on hosted database were different despite using the same embedding model\n",
    "    # and this difference in embeddings caused chatbot to generate worse results and responses when using the hosted database, which is not ideal\n",
    "    # Although the final chatbot application uses local database, instruction for deploying database on Neon is still provided in the system manual\n",
    "# connection_string = \"postgresql://wateraid_owner:TLolWd8Gzpy0@ep-falling-lab-a2olaxf3.eu-central-1.aws.neon.tech/wateraid?sslmode=require\"\n",
    "\n",
    "\n",
    "conn = psycopg2.connect(connection_string)\n",
    "cur = conn.cursor() #creates a cursor object cur which allows you to execute SQL commands and queries\n",
    "\n",
    "#install pgvector\n",
    "cur.execute(\"CREATE EXTENSION IF NOT EXISTS vector;\") #executes a SQL command to create the pgvector extension if it is not already installed\n",
    "conn.commit() #commit current transaction to database\n",
    "\n",
    "# Register the vector type with psycopg2\n",
    "register_vector(conn)\n",
    "\n",
    "table_create_command = \"\"\"\n",
    "CREATE TABLE listings (\n",
    "            id bigserial primary key, \n",
    "            listing_url text,\n",
    "            name_of_activity text,\n",
    "            date text,\n",
    "            location text,\n",
    "            event_synopsis text,\n",
    "            event_description text,\n",
    "            registration_url text,\n",
    "            activity_category text,\n",
    "            embedding vector(768)\n",
    "            );\n",
    "            \"\"\"\n",
    "\n",
    "cur.execute(table_create_command)\n",
    "cur.close()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "67be65e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Batch insert embeddings and data from dataframe into PostgreSQL database\n",
    "register_vector(conn)\n",
    "cur = conn.cursor()\n",
    "# Prepare the list of tuples to insert\n",
    "data_list = [(row['Listing URL'], row['Name of Activity'], row['Date'], row['Location'], row['Event Synopsis'], row['Event Description'], row['Registration Link'], row['Activity Category'], np.array(row['embeddings'])) for index, row in df.iterrows()]\n",
    "# Use execute_values to perform batch insertion\n",
    "execute_values(cur, \"INSERT INTO listings (listing_url, name_of_activity, date, location, event_synopsis, event_description, registration_url, activity_category, embedding) VALUES %s\", data_list)\n",
    "cur.close()\n",
    "# Commit after we insert all embeddings\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "489dc9c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of vector records in table:  76 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# run simple queries against newly inserted data to check\n",
    "\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT COUNT(*) as cnt FROM listings;\")\n",
    "num_records = cur.fetchone()[0]\n",
    "print(\"Number of vector records in table: \", num_records,\"\\n\")\n",
    "# Correct output should be 76"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ff731c02",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First record in table:  [(1, 'https://www.wateraid.org//uk/get-involved/giving/hire-our-handwashing-hubs', 'Hire our Handwashing Hubs', 'NA', 'NA', \"Hire our contactless handwashing facilities to help make your event safer. Give your patrons a dedicated space to wash their hands with water and soap, whilst supporting WaterAid's work to bring clean water to people around the world.\", 'Frequent handwashing is one of the most effective ways to stop the spread of COVID-19. With this is mind we have developed a freestanding handwashing station with running water and soap. Our contactless\\xa0handwashing hubs features a foot pump and touchless soap dispensers to minimise contact - following the same design used in the countries we work.WaterAid staff will manage the space and ensure the Hubs are cleaned regularly, to the highest standard.', 'NA', 'giving', array([-1.80297401e-02, -2.09451020e-02, -3.08032613e-02,  1.13275256e-02,\n",
      "       -3.62302661e-02, -2.22108234e-02,  5.13730720e-02, -3.51665132e-02,\n",
      "        1.25545308e-01,  2.00858675e-02, -1.04976054e-02,  1.60633586e-02,\n",
      "        2.91887932e-02,  2.43032612e-02,  1.59117728e-02, -2.37444369e-03,\n",
      "       -1.65123586e-02,  3.88047360e-02, -4.11748290e-02, -6.24920847e-03,\n",
      "        1.03937108e-02, -2.22281995e-03, -2.16029659e-02,  1.77823380e-02,\n",
      "        3.15929167e-02, -4.25451144e-04,  1.97461694e-02, -1.48222344e-02,\n",
      "        1.63058750e-02, -5.36338240e-02,  8.28310922e-02,  4.43899408e-02,\n",
      "        8.96938087e-04,  2.40820311e-02,  2.20484185e-06, -2.03239154e-02,\n",
      "        6.28079101e-02, -1.34355458e-03, -2.47127060e-02,  8.69716890e-03,\n",
      "       -5.60209528e-02,  1.24385916e-02,  3.46883349e-02,  5.64242154e-02,\n",
      "        2.55883187e-02, -2.81881783e-02,  4.51706760e-02,  2.75404584e-02,\n",
      "       -4.72110277e-03, -1.37351863e-02, -2.40586568e-02,  6.01666570e-02,\n",
      "       -8.83290917e-02,  1.07323984e-02, -4.50181821e-03,  2.94692907e-03,\n",
      "       -3.19293374e-03,  5.43938056e-02,  4.76840101e-02, -3.71868312e-02,\n",
      "       -2.60407627e-02,  8.47894549e-02, -3.26626822e-02, -2.21140459e-02,\n",
      "        1.61687359e-02,  4.31728922e-02, -4.41963114e-02, -4.45342474e-02,\n",
      "        5.32704964e-03,  1.48796588e-02, -1.61335841e-02, -1.63993500e-02,\n",
      "        2.41240561e-02, -9.39091481e-03, -3.44528332e-02, -7.15280604e-03,\n",
      "       -2.74730171e-03, -5.23651354e-02, -2.06724275e-02,  5.84823545e-03,\n",
      "       -8.35300535e-02,  3.15325893e-02,  1.37820551e-02, -1.08693354e-03,\n",
      "        3.33431587e-02,  7.46238902e-02, -1.23351729e-02,  3.39690894e-02,\n",
      "       -3.44745591e-02,  3.52095850e-02, -8.37561488e-03, -5.02448082e-02,\n",
      "        2.68202312e-02, -1.65752263e-03,  5.39344922e-03, -4.86003384e-02,\n",
      "        5.66583313e-02,  2.23140754e-02,  5.98120019e-02, -1.78063139e-02,\n",
      "        3.83366868e-02, -6.67583477e-03, -1.09737329e-02,  1.32347178e-02,\n",
      "       -2.23692786e-02, -7.48596117e-02, -2.20025480e-02, -4.40007485e-02,\n",
      "       -9.36886854e-03,  3.05588134e-02, -1.71729829e-02, -1.92354005e-02,\n",
      "       -5.24906348e-03,  8.96842685e-03, -4.18228619e-02,  2.57574935e-02,\n",
      "        8.74563027e-03,  1.03630219e-03,  2.40014996e-02, -1.60207599e-02,\n",
      "        6.17543608e-03,  3.29901017e-02,  7.62268975e-02,  3.26725538e-03,\n",
      "        2.03827880e-02,  1.10677339e-01, -4.65938561e-02,  2.69202329e-02,\n",
      "       -4.79831286e-02,  4.05835640e-03, -1.88274048e-02,  1.76618330e-03,\n",
      "        1.32856667e-02,  3.10776755e-03, -3.67533155e-02, -1.39727807e-02,\n",
      "        1.49614410e-03, -1.10977339e-02, -8.38486850e-03,  1.90069042e-02,\n",
      "        3.80057730e-02, -2.01037005e-02, -5.32322638e-02,  1.96046736e-02,\n",
      "        5.79527020e-02, -7.79914297e-03,  1.03751402e-02,  1.45970620e-02,\n",
      "        1.10352580e-02, -3.33155729e-02, -2.76402365e-02, -3.02182976e-02,\n",
      "       -4.21815348e-05,  4.15823087e-02,  6.20429665e-02,  1.27821146e-02,\n",
      "       -2.19261106e-02, -3.42981182e-02, -2.06455626e-02,  2.44774353e-02,\n",
      "       -1.38193415e-02, -3.06065064e-02, -6.28354400e-02, -2.05587577e-02,\n",
      "       -7.65932910e-03,  2.18823347e-02,  6.12028055e-02, -8.86486564e-03,\n",
      "        4.60183807e-02,  1.22693218e-02, -1.83275323e-02, -4.04509827e-02,\n",
      "       -2.30239611e-03,  6.56836480e-02,  2.23436113e-03,  6.87773107e-03,\n",
      "       -4.98298407e-02, -1.07181497e-01,  2.26843567e-03, -2.33733300e-02,\n",
      "        5.68610895e-03, -6.46008179e-02, -2.47479752e-02, -6.25406057e-02,\n",
      "        6.04852289e-02,  1.59917958e-02,  1.96741279e-02,  2.18293760e-02,\n",
      "       -6.15568385e-02,  1.70913059e-02, -1.33471824e-02, -1.73339166e-03,\n",
      "       -3.40915769e-02, -1.55380089e-02,  1.27440570e-02, -5.02527431e-02,\n",
      "        2.03890987e-02,  1.70755647e-02,  1.32724950e-02, -3.10533810e-02,\n",
      "        2.85431426e-02, -5.86093739e-02,  7.00903460e-02,  1.08365454e-01,\n",
      "       -4.53797951e-02,  1.15693090e-02, -1.64782275e-02,  1.69577338e-02,\n",
      "       -4.18938510e-03, -1.37714827e-02, -4.75836433e-02,  2.06993707e-02,\n",
      "        1.72635615e-02,  9.86555498e-03, -4.23564538e-02,  1.88051853e-02,\n",
      "        2.74164360e-02,  6.33732975e-02, -3.26425508e-02,  7.47774541e-02,\n",
      "       -2.85599008e-02,  4.14571911e-02, -3.04147340e-02, -6.69333572e-03,\n",
      "        1.57661382e-02,  6.76257312e-02,  3.73269767e-02,  2.13498175e-02,\n",
      "       -4.29116935e-02, -2.45907111e-03,  5.04465587e-02,  3.42664048e-02,\n",
      "       -1.34208845e-03, -2.40057372e-02,  2.00458076e-02,  1.24291265e-02,\n",
      "        4.93932255e-02,  4.73190770e-02, -1.75960455e-02, -1.24676647e-02,\n",
      "       -8.22509546e-03,  2.53785402e-03,  6.14261217e-02,  2.09492110e-02,\n",
      "        8.08555633e-03,  6.52028248e-02,  1.62059981e-02,  3.90746398e-03,\n",
      "        1.62500236e-02, -9.54703335e-03,  1.54013224e-02,  2.26013437e-02,\n",
      "        4.24997844e-02, -1.32687800e-02,  1.68896671e-02, -1.91857219e-02,\n",
      "        6.11923821e-02,  2.20062807e-02, -1.60993505e-02,  1.18890414e-02,\n",
      "       -6.09935001e-02,  8.69738963e-03, -9.54705104e-03, -2.05439292e-02,\n",
      "        3.58202346e-02, -2.46942136e-03,  2.39794869e-02, -9.51810703e-02,\n",
      "        1.27057349e-02, -2.72853649e-03,  1.29502844e-02,  1.95040721e-02,\n",
      "        4.18705158e-02, -2.88133007e-02,  9.44098632e-04, -1.26811536e-02,\n",
      "        2.25639269e-02,  7.84573182e-02, -2.64606513e-02,  2.94160079e-02,\n",
      "       -3.57362744e-03, -6.77265227e-02, -3.08900233e-02,  2.84279436e-02,\n",
      "        1.18372925e-02,  5.65599948e-02,  4.78906520e-02,  7.53197260e-03,\n",
      "       -4.55842055e-02, -5.05135581e-02,  8.93809181e-03, -1.01846792e-02,\n",
      "        4.02588956e-03, -2.97570339e-04, -3.32247764e-02, -3.59286368e-03,\n",
      "       -7.59571791e-02,  8.37919302e-03, -3.34839672e-02, -2.60916799e-02,\n",
      "       -9.43079498e-03,  2.64915321e-02, -2.77078561e-02,  1.28211696e-02,\n",
      "       -2.46026926e-02,  3.19542189e-04,  3.03487368e-02, -2.16711201e-02,\n",
      "       -1.37254726e-02,  2.48643011e-02, -1.67002750e-03,  5.00254296e-02,\n",
      "        1.62686072e-02, -2.90490314e-02,  6.06716275e-02, -1.56655852e-02,\n",
      "       -1.99249014e-02,  2.23554205e-02, -9.82397050e-03,  4.88704443e-02,\n",
      "        2.99942140e-02,  2.17883452e-03, -3.90535444e-02, -8.97495542e-03,\n",
      "        8.59528314e-03,  3.53151113e-02, -2.57222764e-02, -4.11162935e-02,\n",
      "       -7.68410563e-02, -9.08406749e-02,  3.99413370e-02, -9.75917384e-04,\n",
      "       -7.89952055e-02,  7.13894665e-02, -5.98505773e-02, -7.61342123e-02,\n",
      "        1.55044971e-02, -7.78077319e-02, -1.61821041e-02, -1.30295297e-02,\n",
      "       -1.07775826e-03, -3.98274809e-02, -1.03460699e-02, -2.34929076e-03,\n",
      "       -3.88379884e-03,  1.26505494e-02,  8.15450493e-03,  1.50813702e-02,\n",
      "        2.57453648e-03,  2.72792038e-02,  1.96688101e-02,  5.09711206e-02,\n",
      "       -1.55278295e-02,  5.21431267e-02, -2.12051086e-02,  9.97213200e-02,\n",
      "       -7.44446665e-02, -4.10220176e-02, -2.30858643e-02,  4.08311598e-02,\n",
      "       -3.88393775e-02,  1.06630540e-02,  4.52518202e-02,  1.96470646e-03,\n",
      "        3.20284441e-02,  5.91672808e-02, -2.53296345e-02,  3.69691327e-02,\n",
      "       -4.59599309e-02, -4.15629819e-02, -6.29397063e-03,  2.45048087e-02,\n",
      "       -2.18180567e-02, -8.21213145e-03,  3.24496138e-03,  3.60461734e-02,\n",
      "        2.21455321e-02,  1.45921074e-02, -5.75481798e-04, -1.21489363e-02,\n",
      "       -2.41376762e-03,  2.63027335e-03,  8.50486197e-03,  1.92907057e-03,\n",
      "       -2.92133000e-02, -1.14933699e-02, -6.45282939e-02, -6.70995265e-02,\n",
      "        9.27267298e-02,  2.22031176e-02,  1.85365696e-02, -6.11286648e-02,\n",
      "       -4.71969210e-02, -2.32673883e-02,  1.78139308e-03,  2.81314589e-02,\n",
      "        5.85697629e-02,  2.19468959e-02,  1.10785887e-02,  2.47813612e-02,\n",
      "       -1.16813264e-03,  4.87683527e-02, -5.60943112e-02,  2.28663906e-02,\n",
      "       -3.67116071e-02, -1.90387887e-03,  3.23875472e-02, -1.69115700e-02,\n",
      "        2.75332499e-02,  3.90085654e-04, -4.08919808e-03, -6.79041892e-02,\n",
      "        3.91286127e-02, -4.22090255e-02, -4.91399653e-02, -4.33150083e-02,\n",
      "       -4.49403236e-03,  5.73094338e-02,  5.23378104e-02, -1.09800018e-01,\n",
      "        1.27373422e-02,  6.89891726e-02,  4.56858762e-02,  2.12160293e-02,\n",
      "        5.19476123e-02,  1.77087672e-02,  1.21865077e-02,  3.87970619e-02,\n",
      "       -5.52980369e-03,  5.10977022e-03, -4.55406010e-02,  2.18553040e-02,\n",
      "        1.84597895e-02,  1.29385991e-02,  5.89504689e-02, -1.89483743e-02,\n",
      "       -4.52998802e-02, -4.09660488e-02, -6.47238865e-02, -1.43842949e-02,\n",
      "       -2.61785593e-02,  4.49019708e-02,  3.36340517e-02,  1.62247289e-02,\n",
      "        7.22863013e-03, -3.46459285e-03,  4.28033527e-03,  5.67882173e-02,\n",
      "       -1.75679568e-02, -6.26226813e-02,  3.35952342e-02, -4.42857929e-02,\n",
      "        2.28304863e-02,  2.27298774e-02,  1.66039784e-02, -1.55545846e-02,\n",
      "        2.97770910e-02, -1.27335442e-02, -5.02967015e-02,  5.86662218e-02,\n",
      "        8.19706917e-03,  3.15427743e-02, -2.07417216e-02,  2.09968481e-02,\n",
      "        1.37865156e-01, -3.03508155e-02,  3.61555582e-03, -3.67697440e-02,\n",
      "       -2.32449211e-02, -1.81447417e-02,  1.44835692e-02, -1.01129254e-02,\n",
      "       -1.14787566e-02, -1.24686519e-02, -1.16712879e-02,  1.40774110e-02,\n",
      "        1.65902786e-02, -7.55457804e-02, -7.79256085e-03,  1.27575612e-02,\n",
      "        1.58331711e-02,  6.12448081e-02,  1.75302066e-02,  1.45854997e-02,\n",
      "       -4.38886173e-02,  5.27603403e-02,  6.07782565e-02, -3.42662111e-02,\n",
      "       -1.04701621e-02,  3.85160558e-02, -5.24243601e-02, -3.15310471e-02,\n",
      "       -2.03098673e-02,  6.41193390e-02, -4.16802540e-02,  2.99136471e-02,\n",
      "        7.28125572e-02, -3.93772572e-02, -1.21388328e-03,  6.52252696e-03,\n",
      "        1.05591603e-02,  2.42127609e-02,  2.19466146e-02, -2.58465707e-02,\n",
      "        1.40437884e-02, -2.08488032e-02, -2.58426405e-02, -5.01160743e-03,\n",
      "        9.07467213e-03, -4.05074358e-02, -2.31876057e-02,  2.57121697e-02,\n",
      "       -1.15423203e-02, -3.43538485e-02, -1.61304211e-04,  5.63197471e-02,\n",
      "       -3.79796959e-02, -3.68353049e-03, -2.38334574e-02,  1.31353736e-03,\n",
      "        1.21498732e-02, -1.55671891e-02,  8.11699312e-03, -2.73060761e-02,\n",
      "       -4.03922498e-02, -1.45103289e-02,  1.16718439e-02,  1.50260869e-02,\n",
      "       -2.48408057e-02,  5.14590042e-03, -3.18589732e-02, -5.73482690e-03,\n",
      "        2.89295875e-02, -1.01589179e-03, -1.37638366e-02, -1.44504998e-02,\n",
      "        1.02793969e-01, -2.75236042e-03, -5.64458687e-03, -5.85091487e-03,\n",
      "       -4.18115631e-02,  1.96139943e-02, -7.06277713e-02, -2.91947722e-02,\n",
      "       -4.07448187e-02,  1.37170460e-02, -5.03705889e-02, -1.78357251e-02,\n",
      "        4.63125017e-03, -2.34513842e-02,  7.33699882e-03,  5.34650609e-02,\n",
      "       -2.91345920e-02,  1.24206126e-03, -4.40041646e-02, -6.48963321e-33,\n",
      "        2.00388376e-02, -2.53759231e-02, -2.49806531e-02, -1.10401414e-01,\n",
      "       -2.79138368e-02,  6.32758066e-02, -3.74397933e-02,  1.81997626e-03,\n",
      "       -4.04122174e-02, -7.91458227e-03,  2.66497582e-03, -1.14803929e-02,\n",
      "        3.72237749e-02,  9.88807809e-03,  3.80349085e-02,  1.53315924e-02,\n",
      "        4.39350605e-02,  1.74587555e-02, -1.64659489e-02,  4.44517098e-02,\n",
      "        2.08941363e-02, -8.51618964e-03, -3.40139158e-02,  6.09091036e-02,\n",
      "       -2.95880809e-03, -7.94625580e-02, -7.52201723e-03,  4.84690778e-02,\n",
      "        1.13724461e-02, -6.05352446e-02, -2.93701254e-02, -1.78943295e-02,\n",
      "        5.54700755e-03, -3.06595745e-03, -6.19877968e-03,  9.13035050e-02,\n",
      "       -4.44139764e-02, -2.04215292e-02, -3.08873635e-02, -1.00753186e-02,\n",
      "       -5.66792823e-02, -1.95996575e-02, -9.07674432e-03, -8.00064113e-03,\n",
      "       -1.20466948e-02,  2.29195524e-02, -2.51932926e-02,  5.27396146e-03,\n",
      "        3.98293100e-02,  4.99147363e-02,  7.21141994e-02, -4.29165736e-02,\n",
      "       -1.59930978e-02, -1.22727593e-02, -5.51486090e-02, -3.32872719e-02,\n",
      "        9.74325836e-03,  1.53859509e-02,  9.24475025e-03,  1.76087245e-02,\n",
      "        4.46819589e-02, -7.38830166e-03,  8.45334772e-03, -4.09958251e-02,\n",
      "        4.02140548e-04, -2.82527395e-02, -4.95344773e-03, -5.30853532e-02,\n",
      "        1.14504145e-02,  1.17068188e-02, -1.98900253e-02,  1.85932405e-02,\n",
      "       -3.73112895e-02, -3.55428979e-02,  1.54654151e-02, -6.23989441e-02,\n",
      "       -1.79223660e-02,  2.43032537e-02, -2.56093368e-02,  8.12138543e-02,\n",
      "       -9.70293116e-03, -4.22875136e-02,  3.74554098e-02, -1.82351358e-02,\n",
      "       -4.01404947e-02, -1.29056379e-01, -1.60012543e-02, -1.42345056e-02,\n",
      "        1.06412508e-02,  3.16104926e-02, -1.91336742e-03, -4.00093086e-02,\n",
      "       -2.85065398e-02,  5.73443957e-02,  1.01057477e-02,  2.98062339e-02,\n",
      "        8.67011994e-02,  1.93271823e-02,  1.46691073e-02, -8.38224497e-03,\n",
      "       -3.59425619e-02,  2.57058945e-02, -4.77231443e-02,  2.13379264e-02,\n",
      "        3.99779342e-03,  3.37774530e-02, -4.52851616e-02, -3.00179645e-02,\n",
      "       -2.80179530e-02,  5.12811914e-02, -7.44061917e-03,  2.93199830e-02,\n",
      "       -2.16610227e-02, -2.93569230e-02, -2.99020018e-02, -5.01611382e-02,\n",
      "        1.89258978e-02,  3.91061120e-02,  2.26152223e-03, -3.47220302e-02,\n",
      "        5.73277473e-02,  3.87505963e-02, -1.00883387e-03,  8.97896513e-02,\n",
      "        2.03321278e-02, -3.05418228e-03, -3.66209783e-02,  6.75279647e-02,\n",
      "        5.64544536e-02, -2.26238789e-03,  1.24353892e-03,  1.39093231e-02,\n",
      "        2.75082954e-07, -2.27112994e-02, -8.97034258e-03,  1.95848905e-02,\n",
      "       -1.50629198e-02,  3.00870817e-02,  4.35075685e-02, -2.59859636e-02,\n",
      "        1.04181422e-02, -4.86410148e-02, -4.44103330e-02, -1.07093789e-02,\n",
      "       -4.93668839e-02, -5.46494834e-02,  2.03294996e-02, -8.08590278e-02,\n",
      "        2.14806348e-02,  3.37361097e-02, -3.08460295e-02, -2.36743800e-02,\n",
      "       -3.93416323e-02, -4.55616154e-02,  8.41933209e-03, -1.17791584e-02,\n",
      "       -3.09179351e-02,  2.73624379e-02, -2.55487692e-02, -2.41296459e-03,\n",
      "       -1.71983801e-02,  8.10949504e-02,  1.91010348e-02, -4.40661088e-02,\n",
      "       -6.47187978e-02,  3.92177813e-02, -3.65804210e-02,  6.64794166e-03,\n",
      "       -2.75148619e-02,  8.43426399e-03,  4.68592495e-02, -2.26109494e-02,\n",
      "        1.19076138e-02, -3.04292459e-02, -3.50016393e-02, -1.75242517e-02,\n",
      "       -6.46358803e-02,  2.44469568e-02,  2.06208858e-03,  1.46674011e-02,\n",
      "        2.69443579e-02, -4.17986363e-02,  2.13977192e-02, -1.37967719e-02,\n",
      "        2.94886623e-02, -4.86100949e-02,  8.99858191e-04, -1.32522369e-02,\n",
      "       -8.41879565e-03,  9.93160903e-03,  5.57827875e-02,  3.15314084e-02,\n",
      "       -5.55700138e-02, -2.98782960e-02, -3.96989025e-02,  3.22632603e-02,\n",
      "       -1.17680766e-02, -1.16475355e-02, -2.66397335e-02, -7.11716292e-03,\n",
      "        2.10070552e-34, -2.06459761e-02, -2.91084182e-02, -3.11743207e-02,\n",
      "        3.58689800e-02,  2.38090637e-03, -1.70089994e-02, -2.36851163e-02,\n",
      "        1.79844629e-02, -1.22810074e-03,  2.11697654e-03, -2.80066300e-03],\n",
      "      dtype=float32))]\n"
     ]
    }
   ],
   "source": [
    "# print the first record in the table, for sanity-checking\n",
    "cur.execute(\"SELECT * FROM listings LIMIT 1;\")\n",
    "records = cur.fetchall()\n",
    "print(\"First record in table: \", records)\n",
    "\n",
    "cur.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7e7e5234",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an index on the data for faster retrieval\n",
    "# this isn't really needed for 76 vectors, but it shows the usage for larger datasets\n",
    "# Note: always create this type of index after you have data already inserted into the DB\n",
    "import math\n",
    "#calculate the index parameters according to best practices\n",
    "num_lists = num_records / 1000\n",
    "if num_lists < 10:\n",
    "    num_lists = 10\n",
    "if num_records > 1000000:\n",
    "    num_lists = math.sqrt(num_records)\n",
    "\n",
    "#use the cosine distance measure, which is what we'll later use for querying\n",
    "cur = conn.cursor()\n",
    "cur.execute(f'CREATE INDEX ON listings USING ivfflat (embedding vector_cosine_ops) WITH (lists = {num_lists});')\n",
    "cur.close()\n",
    "conn.commit() \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "26ad02aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Yorkshire Three Peaks weekend', 'https://www.wateraid.org//uk/get-involved/events/yorkshire-three-peaks-weekend', 'Summit Ingleborough, Whernside and Pen y Ghent on this tough – but achievable! – weekend.'), ('Bake for WaterAid', 'https://www.wateraid.org//uk/get-involved/fundraising/bake-for-wateraid', 'Sweet or savoury, everyone loves a bake sale – and few people would turn down a piece of cake for a good cause!'), ('London to Brighton Bike Ride', 'https://www.wateraid.org//uk/get-involved/events/london-to-brighton-bike-ride', \"Join the ride of your life on this iconic city to coast challenge – and help us reach the 1 in 10 people worldwide who still don't have access to clean water.\")]\n"
     ]
    }
   ],
   "source": [
    "# Helper function: Get top 3 most similar documents from the database\n",
    "def get_top3_similar_docs(query_embedding, conn):\n",
    "    embedding_array = np.array(query_embedding)\n",
    "    # Register pgvector extension\n",
    "    register_vector(conn)\n",
    "    cur = conn.cursor()\n",
    "    # Get the top 3 most similar documents using the KNN <=> operator\n",
    "    cur.execute(\"SELECT name_of_activity, listing_url, event_synopsis FROM listings ORDER BY embedding <=> %s LIMIT 3\", (embedding_array,))\n",
    "    top3_docs = cur.fetchall()\n",
    "    cur.close()\n",
    "    return top3_docs\n",
    "\n",
    "user_input = \"I am based in Newcastle. I am a accountant. I like to watch variety shows in my free time.\"\n",
    "# cur = conn.cursor()\n",
    "related_docs = get_top3_similar_docs(get_embeddings(user_input), conn)\n",
    "# cur.close()\n",
    "print(related_docs)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13c3cae6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}