etl_all_columns.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sqlite3\n",
    "import csv\n",
    "\n",
    "# Define the directory and database name\n",
    "data_directory = \"data\"\n",
    "db_name = \"birds_db\"\n",
    "csv_file = \"birdmetadata.csv\"\n",
    "\n",
    "# Construct path for the database:\n",
    "db_path = os.path.join(data_directory, db_name)\n",
    "\n",
    "# Construct path for the csv file:\n",
    "csv_path = os.path.join(data_directory, csv_file)\n",
    "\n",
    "# Connect to the database\n",
    "conn = sqlite3.connect(db_path)\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Drop the table if it exists\n",
    "cursor.execute(\"DROP TABLE IF EXISTS birdmetadata\")\n",
    "\n",
    "# Create the table\n",
    "cursor.execute('''\n",
    "    CREATE TABLE IF NOT EXISTS birdmetadata (\n",
    "        COMMON_NAME TEXT PRIMARY KEY,\n",
    "        SCIENTIFIC_NAME TEXT,\n",
    "        STATUS_IN_ILLINOIS TEXT,\n",
    "        LENGTH TEXT,\n",
    "        WEIGHT TEXT,\n",
    "        WINGSPAN TEXT,\n",
    "        HABITAT TEXT,\n",
    "        FOOD TEXT,\n",
    "        CLUTCH_SIZE TEXT,\n",
    "        BEHAVIOR TEXT\n",
    "    )\n",
    "''')\n",
    "\n",
    "# Construct SQL command\n",
    "placeholders = ', '.join(['?' for _ in range(10)])\n",
    "sql_command = f\"INSERT INTO birdmetadata VALUES ({placeholders})\"\n",
    "\n",
    "with open(csv_path, 'r') as file:\n",
    "    reader = csv.reader(file)\n",
    "    # next(reader)  # Skip the header row\n",
    "    for row in reader:\n",
    "        rows = [row for row in reader]\n",
    "cursor.executemany(sql_command, rows)\n",
    "conn.commit()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}