Exporting_Zotero_SQLite_to_JSONL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "67253751",
   "metadata": {},
   "source": [
    "\n",
    "# Exporting Items from a Specific Zotero Group Library to a JSONL file\n",
    "\n",
    "This Jupyter Notebook demonstrates the process of extracting data from a Zotero SQLite database and converting it into a JSONL format for further analysis and sharing. The code provided is tailored for extracting specific fields but can be modified to suit different data extraction needs.\n",
    "\n",
    "## How to Use This Notebook\n",
    "1. **Database Connection**: Modify the database path to point to your Zotero SQLite database.\n",
    "2. **Queries**: The SQL queries are set up to extract specific data. Modify these queries based on your data extraction requirements.\n",
    "3. **Execution**: Run the cells sequentially to extract data, transform it, and save it in the desired format.\n",
    "4. **Customization**: Feel free to add or modify code blocks to suit your specific data processing and analysis needs.\n",
    "\n",
    "**Note**: Before running, ensure that you have the necessary libraries installed (`sqlite3`, `pandas`, etc.) and that your Zotero database file is accessible from this notebook.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be8bd900",
   "metadata": {},
   "source": [
    "### Database Connection\n",
    "This cell establishes a connection to the Zotero SQLite database. Make sure to update the `zotero_db_path` with the correct path to your database file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6ac29ff",
   "metadata": {},
   "source": [
    "### Data Extraction\n",
    "In this cell, we execute SQL queries to extract data from the database. The extracted data includes authors, attachment paths, and other relevant fields."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f907712",
   "metadata": {},
   "source": [
    "### Data Transformation\n",
    "Here we transform the extracted data, pivoting some of it to make it more suitable for analysis and merging different dataframes into a single dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cedfecd",
   "metadata": {},
   "source": [
    "### Saving Data\n",
    "This final cell saves the transformed data into a JSONL format. You can modify the file path and name as per your requirement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Current working directory: /mnt/e/Google Drive/RuBase (1)/Temporality\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "# Define the path to the working directory in WSL format\n",
    "working_dir = '/mnt/e/Google Drive/RuBase (1)/Temporality'\n",
    "\n",
    "# Set the working directory\n",
    "os.chdir(working_dir)\n",
    "\n",
    "# Verify that the working directory is set correctly\n",
    "print(f\"Current working directory: {os.getcwd()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>itemID</th>\n",
       "      <th>libraryID</th>\n",
       "      <th>author_last_name</th>\n",
       "      <th>author_first_name</th>\n",
       "      <th>DOI</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>ISSN</th>\n",
       "      <th>abstractNote</th>\n",
       "      <th>accessDate</th>\n",
       "      <th>bookTitle</th>\n",
       "      <th>...</th>\n",
       "      <th>proceedingsTitle</th>\n",
       "      <th>publicationTitle</th>\n",
       "      <th>publisher</th>\n",
       "      <th>shortTitle</th>\n",
       "      <th>thesisType</th>\n",
       "      <th>title</th>\n",
       "      <th>university</th>\n",
       "      <th>url</th>\n",
       "      <th>volume</th>\n",
       "      <th>path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1021983</td>\n",
       "      <td>200</td>\n",
       "      <td>Tomes</td>\n",
       "      <td>Robert R.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0095-327X</td>\n",
       "      <td>Conflicting time and space perceptions, seated...</td>\n",
       "      <td>2024-01-12 00:37:51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Armed Forces &amp; Society</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Space-Time Orientations and Contemporary Polit...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.jstor.org/stable/48616598</td>\n",
       "      <td>31</td>\n",
       "      <td>storage:Cunningham and Tomes - 2004 - Space-Ti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1021983</td>\n",
       "      <td>200</td>\n",
       "      <td>Cunningham</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0095-327X</td>\n",
       "      <td>Conflicting time and space perceptions, seated...</td>\n",
       "      <td>2024-01-12 00:37:51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Armed Forces &amp; Society</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Space-Time Orientations and Contemporary Polit...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.jstor.org/stable/48616598</td>\n",
       "      <td>31</td>\n",
       "      <td>storage:Cunningham and Tomes - 2004 - Space-Ti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1021885</td>\n",
       "      <td>200</td>\n",
       "      <td>Solomon</td>\n",
       "      <td>Ty</td>\n",
       "      <td>10.1111/isqu.12091</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0020-8833</td>\n",
       "      <td>Critically inclined International Relations (I...</td>\n",
       "      <td>2024-01-12 00:09:20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>International Studies Quarterly</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Time and Subjectivity in World Politics</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://doi.org/10.1111/isqu.12091</td>\n",
       "      <td>58</td>\n",
       "      <td>storage:Solomon - 2014 - Time and Subjectivity...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1021908</td>\n",
       "      <td>200</td>\n",
       "      <td>Amoureux</td>\n",
       "      <td>Jack L.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-01-12 00:13:04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>International Theory</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Is Faster Better?</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Is Faster Better? Political and Ethical Framin...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.cambridge.org/core/journals/intern...</td>\n",
       "      <td>12</td>\n",
       "      <td>storage:Amoureux - 2020 - Is faster better Pol...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1021971</td>\n",
       "      <td>200</td>\n",
       "      <td>Neumann</td>\n",
       "      <td>Iver B.</td>\n",
       "      <td>10.1177/0305829818771339</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0305-8298, 1477-9021</td>\n",
       "      <td>Drawing on identity and prototype theory, the ...</td>\n",
       "      <td>2024-01-12 00:31:09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Millennium: Journal of International Studies</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Halting Time</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Halting Time: Monuments to Alterity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://journals.sagepub.com/doi/10.1177/030582...</td>\n",
       "      <td>46</td>\n",
       "      <td>storage:Neumann - 2018 - Halting Time Monument...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    itemID  libraryID author_last_name author_first_name  \\\n",
       "0  1021983        200            Tomes         Robert R.   \n",
       "1  1021983        200       Cunningham             Kevin   \n",
       "2  1021885        200          Solomon                Ty   \n",
       "3  1021908        200         Amoureux           Jack L.   \n",
       "4  1021971        200          Neumann           Iver B.   \n",
       "\n",
       "                        DOI ISBN                  ISSN  \\\n",
       "0                       NaN  NaN             0095-327X   \n",
       "1                       NaN  NaN             0095-327X   \n",
       "2        10.1111/isqu.12091  NaN             0020-8833   \n",
       "3                       NaN  NaN                   NaN   \n",
       "4  10.1177/0305829818771339  NaN  0305-8298, 1477-9021   \n",
       "\n",
       "                                        abstractNote           accessDate  \\\n",
       "0  Conflicting time and space perceptions, seated...  2024-01-12 00:37:51   \n",
       "1  Conflicting time and space perceptions, seated...  2024-01-12 00:37:51   \n",
       "2  Critically inclined International Relations (I...  2024-01-12 00:09:20   \n",
       "3                                                NaN  2024-01-12 00:13:04   \n",
       "4  Drawing on identity and prototype theory, the ...  2024-01-12 00:31:09   \n",
       "\n",
       "  bookTitle  ... proceedingsTitle  \\\n",
       "0       NaN  ...              NaN   \n",
       "1       NaN  ...              NaN   \n",
       "2       NaN  ...              NaN   \n",
       "3       NaN  ...              NaN   \n",
       "4       NaN  ...              NaN   \n",
       "\n",
       "                               publicationTitle publisher         shortTitle  \\\n",
       "0                        Armed Forces & Society       NaN                NaN   \n",
       "1                        Armed Forces & Society       NaN                NaN   \n",
       "2               International Studies Quarterly       NaN                NaN   \n",
       "3                          International Theory       NaN  Is Faster Better?   \n",
       "4  Millennium: Journal of International Studies       NaN       Halting Time   \n",
       "\n",
       "  thesisType                                              title university  \\\n",
       "0        NaN  Space-Time Orientations and Contemporary Polit...        NaN   \n",
       "1        NaN  Space-Time Orientations and Contemporary Polit...        NaN   \n",
       "2        NaN            Time and Subjectivity in World Politics        NaN   \n",
       "3        NaN  Is Faster Better? Political and Ethical Framin...        NaN   \n",
       "4        NaN                Halting Time: Monuments to Alterity        NaN   \n",
       "\n",
       "                                                 url volume  \\\n",
       "0              https://www.jstor.org/stable/48616598     31   \n",
       "1              https://www.jstor.org/stable/48616598     31   \n",
       "2                 https://doi.org/10.1111/isqu.12091     58   \n",
       "3  https://www.cambridge.org/core/journals/intern...     12   \n",
       "4  http://journals.sagepub.com/doi/10.1177/030582...     46   \n",
       "\n",
       "                                                path  \n",
       "0  storage:Cunningham and Tomes - 2004 - Space-Ti...  \n",
       "1  storage:Cunningham and Tomes - 2004 - Space-Ti...  \n",
       "2  storage:Solomon - 2014 - Time and Subjectivity...  \n",
       "3  storage:Amoureux - 2020 - Is faster better Pol...  \n",
       "4  storage:Neumann - 2018 - Halting Time Monument...  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "\n",
    "# Path to the Zotero SQLite database\n",
    "zotero_db_path = '/mnt/e/Zotero/zotero.sqlite'\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect(zotero_db_path)\n",
    "\n",
    "# LibraryID to search for\n",
    "library_id = 200\n",
    "\n",
    "# Query 1: Fetch authors for items with libraryID 200\n",
    "query_authors = '''\n",
    "    SELECT ic.itemID, i.libraryID, c.lastName as author_last_name, c.firstName as author_first_name\n",
    "    FROM itemCreators ic\n",
    "    JOIN creators c ON ic.creatorID = c.creatorID\n",
    "    JOIN items i ON ic.itemID = i.itemID\n",
    "    WHERE i.libraryID = ?;\n",
    "'''\n",
    "df_authors = pd.read_sql_query(query_authors, conn, params=(library_id,))\n",
    "\n",
    "# Query 2: Fetch attachment paths for these items\n",
    "if not df_authors.empty:\n",
    "    item_ids_str = ', '.join(df_authors['itemID'].astype(str).tolist())\n",
    "    query_paths = f'''\n",
    "        SELECT parentItemID, path \n",
    "        FROM itemAttachments \n",
    "        WHERE parentItemID IN ({item_ids_str})\n",
    "    '''\n",
    "    df_paths = pd.read_sql_query(query_paths, conn)\n",
    "else:\n",
    "    df_paths = pd.DataFrame(columns=['parentItemID', 'path'])\n",
    "\n",
    "# Query 3: Fetch other fields for items with libraryID 200\n",
    "query_item_data = '''\n",
    "    SELECT id.itemID, fc.fieldName, idv.value\n",
    "    FROM itemData id\n",
    "    JOIN fieldsCombined fc ON id.fieldID = fc.fieldID\n",
    "    JOIN itemDataValues idv ON id.valueID = idv.valueID\n",
    "    JOIN items i ON id.itemID = i.itemID\n",
    "    WHERE i.libraryID = ?;\n",
    "'''\n",
    "df_item_data = pd.read_sql_query(query_item_data, conn, params=(library_id,))\n",
    "\n",
    "# Pivot the item data DataFrame\n",
    "df_item_data_pivot = df_item_data.pivot(index='itemID', columns='fieldName', values='value').reset_index()\n",
    "\n",
    "# Close the connection to the database\n",
    "conn.close()\n",
    "\n",
    "# Merge the DataFrames\n",
    "df_zotero = pd.merge(df_authors, df_item_data_pivot, on='itemID', how='left')\n",
    "df_zotero = pd.merge(df_zotero, df_paths, left_on='itemID', right_on='parentItemID', how='left').drop('parentItemID', axis=1)\n",
    "\n",
    "# Display the first few rows of the final DataFrame\n",
    "df_zotero.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File \"Temporality_zotero_library_240113.jsonl\" has been created.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from datetime import datetime\n",
    "\n",
    "# Get the current date in YYMMDD format\n",
    "date_suffix = datetime.now().strftime('%y%m%d')\n",
    "\n",
    "# Define the file path with the date suffix\n",
    "output_file_path = f'Temporality_zotero_library_{date_suffix}.jsonl'\n",
    "\n",
    "# Write the DataFrame to a JSONL file without ASCII output commenting\n",
    "df_zotero.to_json(output_file_path, orient='records', lines=True, force_ascii=False, date_format='iso')\n",
    "\n",
    "# Verify that the file has been created\n",
    "print(f'File \"{output_file_path}\" has been created.')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "llmware",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}