WORKING_CODE-itearative.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2737838a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pyodbc \n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "203100be",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Apertura della connessione al database 'Group_21_DB' e creazione del cursore \n",
    "server = \"tcp:131.114.72.230\"\n",
    "database = \"Group_21_DB\"\n",
    "username = \"Group_21\"\n",
    "password = \"FQL479WW\"\n",
    "connectionString = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password\n",
    "cnxn = pyodbc.connect(connectionString)\n",
    "cursor = cnxn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1c58347a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def loading(dictionary_input ):\n",
    "    is_header = True\n",
    "    for name in dictionary_input:\n",
    "        lines=dictionary_input[name]\n",
    "        print(lines)\n",
    "        sql = \"\"\n",
    "        print(\"----- POPULATING TABLE \" + name + \"-----\")\n",
    "        for row in lines:\n",
    "            if is_header:\n",
    "                count_param = 0\n",
    "                # creazione della query leggendo l'header\n",
    "                parametric_vals = \"\"\n",
    "                data_st = \"\"\n",
    "                for elem in row:\n",
    "                    data_st += elem + \",\"\n",
    "                    parametric_vals += \"?,\"\n",
    "                    count_param+=1\n",
    "                sql = f\"INSERT INTO {username}.{name}({data_st[:-1]}) VALUES({parametric_vals[:-1]})\" #query per inserire il dato \n",
    "                print(sql)\n",
    "                is_header = False\n",
    "            else: #assegnazione del datatype in base alla tabella che stiamo caricando\n",
    "                if name == \"geography\":\n",
    "                    print(row)\n",
    "                    rows=cursor.execute(sql,(int(row[0]),str(row[1]),str(row[2]),str(row[3])))\n",
    "                elif name == \"date\":\n",
    "                    rows=cursor.execute(sql,str(row[0]), str(row[1]),int(row[2]),int(row[3]),int(row[4]),int(row[5]))\n",
    "                elif name == \"subject\":\n",
    "                     rows=cursor.execute(sql,int(row[0]),str(row[1]))\n",
    "                elif name == \"organization\":\n",
    "                     rows=cursor.execute(sql,int(row[0]),int(row[1]),int(row[2]),int(row[3]))\n",
    "                elif name == \"user1\":\n",
    "                     rows=cursor.execute(sql,int(row[0]),str(row[1]),int(row[2]),int(row[3]))\n",
    "                elif name == \"answer\":\n",
    "                     rows=cursor.execute(sql,int(row[0]),int(row[1]),int(row[2]),int(row[3]), str(row[4]),\n",
    "                                         int(row[5]), int(row[6]),int(row[7]),int(row[8]),int(row[9]))\n",
    "                else:\n",
    "                    raise ValueError(\" got a strange name.\")\n",
    "        \n",
    "        cnxn.commit()\n",
    "        print(f\"Done - Table{name}\")\n",
    "        print(\"------------------------------------------\")\n",
    "        is_header = True # ricominciamo il ciclo\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d524e88f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'answer': <_csv.reader at 0x2a0febec220>}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dt_data={}\n",
    "\n",
    "\n",
    "#File da aprire: creando una lista vuota riavviandio il kernel di volta in volta, usiamo la funzione loading per caricare \n",
    "#il file all'interno del database\n",
    "\n",
    "#geography = open(Path(\"geography_table.csv\"),\"r\") \n",
    "#geography_lines= csv.reader(geography, delimiter = \",\")\n",
    "#dt_data[\"geography\"] = geography_lines\n",
    "\n",
    "#date = open(Path(\"date_table.csv\"),\"r\") \n",
    "#date_lines= csv.reader(date, delimiter = \",\")\n",
    "#dt_data[\"date\"] = date_lines\n",
    "\n",
    "#subject = open(Path(\"subject_table.csv\"),\"r\") \n",
    "#subject_lines= csv.reader(subject, delimiter = \",\")\n",
    "#dt_data[\"subject\"] = subject_lines\n",
    "\n",
    "#user1= open(Path(\"user_table.csv\"),\"r\") \n",
    "#user1_lines= csv.reader(user1, delimiter = \",\")\n",
    "#dt_data[\"user1\"] = user1_lines\n",
    "\n",
    "#organization = open(Path(\"organization_table.csv\"),\"r\") \n",
    "#organization_lines= csv.reader(organization, delimiter = \",\")\n",
    "#dt_data[\"organization\"] = organization_lines\n",
    "\n",
    "answer = open(Path(\"answer_table.csv\"),\"r\") \n",
    "answer_lines= csv.reader(answer, delimiter = \",\")\n",
    "dt_data[\"answer\"] = answer_lines\n",
    "\n",
    "dt_data\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "79f9a919",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_csv.reader object at 0x000002A0FEBEC220>\n",
      "----- POPULATING TABLE answer-----\n",
      "INSERT INTO Group_21.answer(AnswerId,QuestionId,UserId,OrganizationId,DateId,SubjectId,AnswerValue,CorrectAnswer,IsCorrect,Confidence) VALUES(?,?,?,?,?,?,?,?,?,?)\n",
      "Done - Tableanswer\n",
      "------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "loading(dt_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49428049",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f75faabb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24250269",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}