get-started.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "42a40f80-89eb-42ab-8c74-fba422c9a91a",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Intro zur DB-Benutzung"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5255aa9b-93c0-4853-9692-aec9c74d9631",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import sqlite3 as sqlite"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba72d147-9317-48b2-8553-370ee324df94",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Connect to Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f809e0b2-a42d-4cce-9f96-38435867475d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "database_path = Path(\"DeTox-Dataset_public.sqlite3\") # path to your database file\n",
    "dbconnect = None\n",
    "cursor = None\n",
    "\n",
    "if not database_path.is_file():\n",
    "    print(f\"Database {database_path} does not exist. Creating a new database now ...\")\n",
    "try:\n",
    "        # open database connection\n",
    "        dbconnect = sqlite.connect(database_path, detect_types=sqlite.PARSE_DECLTYPES | sqlite.PARSE_COLNAMES)\n",
    "        cursor = dbconnect.cursor()\n",
    "        # Check Foreign-Key Constraints can be switched off if needed with the following line:\n",
    "        # cursor.execute(\"PRAGMA foreign_keys = OFF;\")\n",
    "except sqlite.Error as e:\n",
    "        # if errors occur\n",
    "        print(\"Error %s:\" % e.args[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a305c232-0146-44c3-9834-98635aff107d",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Example Request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a5de5297-8ab1-4ee4-9907-5981c25a70df",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                  c_id c_text  nb_annotators  dataset_id    duration  \\\n0  1383933685519437827                     4         6.0  366.750000   \n1  1383935384464236546                     4         6.0  149.000000   \n2  1383936217331363852                     3         8.0   48.333333   \n3  1383940124530991113                     3         1.0   44.000000   \n4  1383946030975045641                     3         7.0   20.333333   \n\n     incomp  sentiment  hate_speech  criminal_rel  p_86  ...  \\\n0  0.000000       -1.0     0.000000      0.000000   0.0  ...   \n1  0.000000       -1.0     0.000000      0.000000   0.0  ...   \n2  0.000000       -1.0     0.333333      0.333333   0.0  ...   \n3  0.000000       -1.0     0.666667      1.000000   0.0  ...   \n4  0.333333        0.0     0.000000      0.000000   0.0  ...   \n\n   discrim_engagement  discrim_sexIdent  discrim_characteristics  \\\n0                 0.0               0.0                      0.0   \n1                 0.0               0.0                      0.0   \n2                 0.0               0.0                      0.0   \n3                 0.0               0.0                      0.0   \n4                 0.0               0.0                      0.0   \n\n   discrim_nation  discrim_religion  discrim_socialStatus  discrim_worldview  \\\n0             0.0               0.0                   0.0                0.0   \n1             0.0               0.0                   0.0                0.0   \n2             0.0               0.0                   0.0                0.0   \n3             0.0               0.0                   0.0                0.0   \n4             0.0               0.0                   0.0                0.0   \n\n   discrim_Ethnicity  threat  extrem  \n0                0.0     0.0     0.0  \n1                0.0     0.0     0.0  \n2                0.0     0.0     0.0  \n3                0.0     0.0     0.0  \n4                0.0     0.0     0.0  \n\n[5 rows x 41 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>c_id</th>\n      <th>c_text</th>\n      <th>nb_annotators</th>\n      <th>dataset_id</th>\n      <th>duration</th>\n      <th>incomp</th>\n      <th>sentiment</th>\n      <th>hate_speech</th>\n      <th>criminal_rel</th>\n      <th>p_86</th>\n      <th>...</th>\n      <th>discrim_engagement</th>\n      <th>discrim_sexIdent</th>\n      <th>discrim_characteristics</th>\n      <th>discrim_nation</th>\n      <th>discrim_religion</th>\n      <th>discrim_socialStatus</th>\n      <th>discrim_worldview</th>\n      <th>discrim_Ethnicity</th>\n      <th>threat</th>\n      <th>extrem</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1383933685519437827</td>\n      <td></td>\n      <td>4</td>\n      <td>6.0</td>\n      <td>366.750000</td>\n      <td>0.000000</td>\n      <td>-1.0</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.0</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1383935384464236546</td>\n      <td></td>\n      <td>4</td>\n      <td>6.0</td>\n      <td>149.000000</td>\n      <td>0.000000</td>\n      <td>-1.0</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.0</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1383936217331363852</td>\n      <td></td>\n      <td>3</td>\n      <td>8.0</td>\n      <td>48.333333</td>\n      <td>0.000000</td>\n      <td>-1.0</td>\n      <td>0.333333</td>\n      <td>0.333333</td>\n      <td>0.0</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1383940124530991113</td>\n      <td></td>\n      <td>3</td>\n      <td>1.0</td>\n      <td>44.000000</td>\n      <td>0.000000</td>\n      <td>-1.0</td>\n      <td>0.666667</td>\n      <td>1.000000</td>\n      <td>0.0</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1383946030975045641</td>\n      <td></td>\n      <td>3</td>\n      <td>7.0</td>\n      <td>20.333333</td>\n      <td>0.333333</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.0</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 41 columns</p>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotations = pd.read_sql_query(\"SELECT * from Goldstandard;\", con=dbconnect)\n",
    "annotations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [],
   "source": [
    "# Important: close the database connection at the end\n",
    "dbconnect.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}