sql-test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_104524/125094152.py:2: DeprecationWarning: \n",
      "Pyarrow will become a required dependency of pandas in the next major release of pandas (pandas 3.0),\n",
      "(to allow more performant data types, such as the Arrow string type, and better interoperability with other libraries)\n",
      "but was not found to be installed on your system.\n",
      "If this would cause problems for you,\n",
      "please provide us feedback at https://github.com/pandas-dev/pandas/issues/54466\n",
      "        \n",
      "  import pandas\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import pandas\n",
    "from sqlite3 import Error\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.kaggle.com/datasets/shiivvvaam/bitcoin-historical-data\n",
    "df = pandas.read_csv('Bitcoin History.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Date</th>\n",
       "      <th>Price</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Vol.</th>\n",
       "      <th>Change %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Feb 09, 2024</td>\n",
       "      <td>47,545.4</td>\n",
       "      <td>45,293.3</td>\n",
       "      <td>47,710.2</td>\n",
       "      <td>45,254.2</td>\n",
       "      <td>86.85K</td>\n",
       "      <td>4.97%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feb 08, 2024</td>\n",
       "      <td>45,293.3</td>\n",
       "      <td>44,346.2</td>\n",
       "      <td>45,579.2</td>\n",
       "      <td>44,336.4</td>\n",
       "      <td>66.38K</td>\n",
       "      <td>2.15%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Feb 07, 2024</td>\n",
       "      <td>44,339.8</td>\n",
       "      <td>43,088.4</td>\n",
       "      <td>44,367.9</td>\n",
       "      <td>42,783.5</td>\n",
       "      <td>48.57K</td>\n",
       "      <td>2.91%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Feb 06, 2024</td>\n",
       "      <td>43,087.7</td>\n",
       "      <td>42,697.6</td>\n",
       "      <td>43,375.5</td>\n",
       "      <td>42,566.8</td>\n",
       "      <td>33.32K</td>\n",
       "      <td>0.91%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Feb 05, 2024</td>\n",
       "      <td>42,697.2</td>\n",
       "      <td>42,581.4</td>\n",
       "      <td>43,532.2</td>\n",
       "      <td>42,272.5</td>\n",
       "      <td>39.26K</td>\n",
       "      <td>0.27%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4950</th>\n",
       "      <td>Jul 22, 2010</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.16K</td>\n",
       "      <td>0.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4951</th>\n",
       "      <td>Jul 21, 2010</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.58K</td>\n",
       "      <td>0.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4952</th>\n",
       "      <td>Jul 20, 2010</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.26K</td>\n",
       "      <td>0.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4953</th>\n",
       "      <td>Jul 19, 2010</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.57K</td>\n",
       "      <td>0.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4954</th>\n",
       "      <td>Jul 18, 2010</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.08K</td>\n",
       "      <td>0.00%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4955 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Date     Price      Open      High       Low    Vol. Change %\n",
       "0     Feb 09, 2024  47,545.4  45,293.3  47,710.2  45,254.2  86.85K    4.97%\n",
       "1     Feb 08, 2024  45,293.3  44,346.2  45,579.2  44,336.4  66.38K    2.15%\n",
       "2     Feb 07, 2024  44,339.8  43,088.4  44,367.9  42,783.5  48.57K    2.91%\n",
       "3     Feb 06, 2024  43,087.7  42,697.6  43,375.5  42,566.8  33.32K    0.91%\n",
       "4     Feb 05, 2024  42,697.2  42,581.4  43,532.2  42,272.5  39.26K    0.27%\n",
       "...            ...       ...       ...       ...       ...     ...      ...\n",
       "4950  Jul 22, 2010       0.1       0.1       0.1       0.1   2.16K    0.00%\n",
       "4951  Jul 21, 2010       0.1       0.1       0.1       0.1   0.58K    0.00%\n",
       "4952  Jul 20, 2010       0.1       0.1       0.1       0.1   0.26K    0.00%\n",
       "4953  Jul 19, 2010       0.1       0.1       0.1       0.1   0.57K    0.00%\n",
       "4954  Jul 18, 2010       0.1       0.0       0.1       0.1   0.08K    0.00%\n",
       "\n",
       "[4955 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop('Vol.', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = pandas.to_datetime(df['Date'])\n",
    "df['Price'] = pandas.to_numeric(df['Price'].str.replace(',',''))\n",
    "df['Open'] = pandas.to_numeric(df['Open'].str.replace(',',''))\n",
    "df['High'] = pandas.to_numeric(df['High'].str.replace(',',''))\n",
    "df['Low'] = pandas.to_numeric(df['Low'].str.replace(',',''))\n",
    "\n",
    "df['Change %'] = pandas.to_numeric(df['Change %'].str.replace('%',''))/100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.6.0\n"
     ]
    },
    {
     "ename": "ValueError",
     "evalue": "Table 'Bitcoin_History' already exists.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[4], line 18\u001b[0m\n\u001b[1;32m     16\u001b[0m conn \u001b[38;5;241m=\u001b[39m create_connection(\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m./sql.db\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m     17\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m conn:\n\u001b[0;32m---> 18\u001b[0m     \u001b[43mdf\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mBitcoin_History\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     19\u001b[0m     conn\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/util/_decorators.py:333\u001b[0m, in \u001b[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    327\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(args) \u001b[38;5;241m>\u001b[39m num_allow_args:\n\u001b[1;32m    328\u001b[0m     warnings\u001b[38;5;241m.\u001b[39mwarn(\n\u001b[1;32m    329\u001b[0m         msg\u001b[38;5;241m.\u001b[39mformat(arguments\u001b[38;5;241m=\u001b[39m_format_argument_list(allow_args)),\n\u001b[1;32m    330\u001b[0m         \u001b[38;5;167;01mFutureWarning\u001b[39;00m,\n\u001b[1;32m    331\u001b[0m         stacklevel\u001b[38;5;241m=\u001b[39mfind_stack_level(),\n\u001b[1;32m    332\u001b[0m     )\n\u001b[0;32m--> 333\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfunc\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/core/generic.py:3081\u001b[0m, in \u001b[0;36mNDFrame.to_sql\u001b[0;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   2886\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   2887\u001b[0m \u001b[38;5;124;03mWrite records stored in a DataFrame to a SQL database.\u001b[39;00m\n\u001b[1;32m   2888\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   3077\u001b[0m \u001b[38;5;124;03m[(1,), (None,), (2,)]\u001b[39;00m\n\u001b[1;32m   3078\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m  \u001b[38;5;66;03m# noqa: E501\u001b[39;00m\n\u001b[1;32m   3079\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mio\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m sql\n\u001b[0;32m-> 3081\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43msql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   3082\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3083\u001b[0m \u001b[43m    \u001b[49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3084\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcon\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3085\u001b[0m \u001b[43m    \u001b[49m\u001b[43mschema\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mschema\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3086\u001b[0m \u001b[43m    \u001b[49m\u001b[43mif_exists\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mif_exists\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3087\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3088\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex_label\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_label\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3089\u001b[0m \u001b[43m    \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3090\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3091\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   3092\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:842\u001b[0m, in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[1;32m    837\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mNotImplementedError\u001b[39;00m(\n\u001b[1;32m    838\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mframe\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m argument should be either a Series or a DataFrame\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    839\u001b[0m     )\n\u001b[1;32m    841\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m pandasSQL_builder(con, schema\u001b[38;5;241m=\u001b[39mschema, need_transaction\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m) \u001b[38;5;28;01mas\u001b[39;00m pandas_sql:\n\u001b[0;32m--> 842\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    843\u001b[0m \u001b[43m        \u001b[49m\u001b[43mframe\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    844\u001b[0m \u001b[43m        \u001b[49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    845\u001b[0m \u001b[43m        \u001b[49m\u001b[43mif_exists\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mif_exists\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    846\u001b[0m \u001b[43m        \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    847\u001b[0m \u001b[43m        \u001b[49m\u001b[43mindex_label\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_label\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    848\u001b[0m \u001b[43m        \u001b[49m\u001b[43mschema\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mschema\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    849\u001b[0m \u001b[43m        \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    850\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    851\u001b[0m \u001b[43m        \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    852\u001b[0m \u001b[43m        \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    853\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    854\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:2851\u001b[0m, in \u001b[0;36mSQLiteDatabase.to_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[1;32m   2840\u001b[0m             \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mcol\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m (\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mmy_type\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m) not a string\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m   2842\u001b[0m table \u001b[38;5;241m=\u001b[39m SQLiteTable(\n\u001b[1;32m   2843\u001b[0m     name,\n\u001b[1;32m   2844\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   2849\u001b[0m     dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[1;32m   2850\u001b[0m )\n\u001b[0;32m-> 2851\u001b[0m \u001b[43mtable\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcreate\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2852\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m table\u001b[38;5;241m.\u001b[39minsert(chunksize, method)\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:986\u001b[0m, in \u001b[0;36mSQLTable.create\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    984\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mexists():\n\u001b[1;32m    985\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mif_exists \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfail\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m--> 986\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mTable \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mname\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m already exists.\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m    987\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mif_exists \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mreplace\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[1;32m    988\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mpd_sql\u001b[38;5;241m.\u001b[39mdrop_table(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mname, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mschema)\n",
      "\u001b[0;31mValueError\u001b[0m: Table 'Bitcoin_History' already exists."
     ]
    }
   ],
   "source": [
    "def create_connection(db_file):\n",
    "    \"\"\" create a database connection to a SQLite database \"\"\"\n",
    "    conn = None\n",
    "    try:\n",
    "        conn = sqlite3.connect(db_file)\n",
    "        print(sqlite3.version)\n",
    "        return conn\n",
    "    except Error as e:\n",
    "        print(e)\n",
    "    # finally:\n",
    "    #     if conn:\n",
    "    #         conn.close()\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    conn = create_connection(r\"./sql.db\")\n",
    "    if conn:\n",
    "        df.to_sql('Bitcoin_History', conn)\n",
    "        conn.close()\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Date</th>\n",
       "      <th>Price</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Change %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-02-09</td>\n",
       "      <td>47545.4</td>\n",
       "      <td>45293.3</td>\n",
       "      <td>47710.2</td>\n",
       "      <td>45254.2</td>\n",
       "      <td>0.0497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-02-08</td>\n",
       "      <td>45293.3</td>\n",
       "      <td>44346.2</td>\n",
       "      <td>45579.2</td>\n",
       "      <td>44336.4</td>\n",
       "      <td>0.0215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-02-07</td>\n",
       "      <td>44339.8</td>\n",
       "      <td>43088.4</td>\n",
       "      <td>44367.9</td>\n",
       "      <td>42783.5</td>\n",
       "      <td>0.0291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-02-06</td>\n",
       "      <td>43087.7</td>\n",
       "      <td>42697.6</td>\n",
       "      <td>43375.5</td>\n",
       "      <td>42566.8</td>\n",
       "      <td>0.0091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-02-05</td>\n",
       "      <td>42697.2</td>\n",
       "      <td>42581.4</td>\n",
       "      <td>43532.2</td>\n",
       "      <td>42272.5</td>\n",
       "      <td>0.0027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4950</th>\n",
       "      <td>2010-07-22</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4951</th>\n",
       "      <td>2010-07-21</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4952</th>\n",
       "      <td>2010-07-20</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4953</th>\n",
       "      <td>2010-07-19</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4954</th>\n",
       "      <td>2010-07-18</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4955 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date    Price     Open     High      Low  Change %\n",
       "0    2024-02-09  47545.4  45293.3  47710.2  45254.2    0.0497\n",
       "1    2024-02-08  45293.3  44346.2  45579.2  44336.4    0.0215\n",
       "2    2024-02-07  44339.8  43088.4  44367.9  42783.5    0.0291\n",
       "3    2024-02-06  43087.7  42697.6  43375.5  42566.8    0.0091\n",
       "4    2024-02-05  42697.2  42581.4  43532.2  42272.5    0.0027\n",
       "...         ...      ...      ...      ...      ...       ...\n",
       "4950 2010-07-22      0.1      0.1      0.1      0.1    0.0000\n",
       "4951 2010-07-21      0.1      0.1      0.1      0.1    0.0000\n",
       "4952 2010-07-20      0.1      0.1      0.1      0.1    0.0000\n",
       "4953 2010-07-19      0.1      0.1      0.1      0.1    0.0000\n",
       "4954 2010-07-18      0.1      0.0      0.1      0.1    0.0000\n",
       "\n",
       "[4955 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 'index', 'INTEGER', 0, None, 0)\n",
      "(1, 'Date', 'TIMESTAMP', 0, None, 0)\n",
      "(2, 'Price', 'REAL', 0, None, 0)\n",
      "(3, 'Open', 'REAL', 0, None, 0)\n",
      "(4, 'High', 'REAL', 0, None, 0)\n",
      "(5, 'Low', 'REAL', 0, None, 0)\n",
      "(6, 'Change', 'REAL', 0, None, 0)\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('sql.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"PRAGMA table_info(Bitcoin_History)\")\n",
    "\n",
    "rows = cur.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)\n",
    "# print(conn.execute('SELECT * FROM Bitcoin_History'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\tindex INTEGER, -- Unique ID for each product\n",
      "\tDate TIMESTAMP, -- Date of the recorded data\n",
      "\tPrice REAL, -- Closing price of Bitcoin on the given date\n",
      "\tOpen REAL, -- Opening price of Bitcoin on the given date\n",
      "\tHigh REAL, -- Highest price of Bitcoin on the given date\n",
      "\tLow REAL, -- Lowest price of Bitcoin on the given date\n",
      "\tChange REAL, -- Percentage change in Bitcoin's price from the previous day. stored as decimal. not percent.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "def get_table_metadata(table_name, db_name, metadata_name):\n",
    "    conn = sqlite3.connect(db_name)\n",
    "    cur = conn.cursor()\n",
    "    table_name = table_name\n",
    "    cur.execute(f\"PRAGMA table_info({table_name})\")\n",
    "\n",
    "    rows = cur.fetchall()\n",
    "\n",
    "    schema = \"\"\n",
    "    with open(metadata_name) as f:\n",
    "        comment_dict = json.load(f)\n",
    "\n",
    "        for row in rows:\n",
    "            schema += f'\\t{row[1]} {row[2]}, -- {comment_dict[row[1]]}\\n'\n",
    "\n",
    "    return schema\n",
    "\n",
    "print(get_table_metadata(\"Bitcoin_History\", \"sql.db\", \"sqldb.json\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "#ensure no edit commands are passed in\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "conn.close()"
   ]
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}