1.06 Python and SQL databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python and SQL databases\n",
    "\n",
    "\n",
    "### Contents:\n",
    "0. Install packages\n",
    "1. Sqlite3 \n",
    "2. MySQL (issue)\n",
    "3. Mariadb (issue)\n",
    "4. Duckdb\n",
    "\n",
    "Please see also the notebook on InfluxDB in chapter 5 of this series."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Install packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pip 21.2.4 from /Users/michielbontenbal/opt/anaconda3/lib/python3.9/site-packages/pip (python 3.9)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip --version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install duckdb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Sqlite3\n",
    "\n",
    "Sqlite3 is a built in module in Python3, so you only have to import this module and it works out of the box. \n",
    "\n",
    "To use it, you'll need to make two objects:\n",
    "1. a connection\n",
    "2. a cursor, which is used to interact with the database.\n",
    "\n",
    "We use the SQL language to interact with the database. See https://en.wikipedia.org/wiki/SQL for more info.\n",
    "Or find an overview of SQL statements here: https://s3.amazonaws.com/assets.datacamp.com/email/other/SQL+for+Data+Science.pdf\n",
    "\n",
    "Sqlite3 library automatically creates a database for us, if it does not exist already.\n",
    "You can download a database viewer from https://sqlitebrowser.org/ to check your database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('michiel.db') #database is created\n",
    "cur = conn.cursor()\n",
    "cur.execute('CREATE TABLE if not exists store(item TEXT, quantity INTEGER, price REAL)')\n",
    "cur.execute(\"INSERT INTO store VALUES('fiets', 10, 1234.56)\")\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('fiets', 10, 1234.56), ('auto', 12, 10.5), ('fiets', 10, 1234.56)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('michiel.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute('SELECT * FROM store')\n",
    "rows = cur.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('michiel.db')\n",
    "cur = conn.cursor()\n",
    "\n",
    "def insert(item, quantity, price):\n",
    "    cur.execute(\"INSERT INTO store VALUES(?,?,?)\", (item, quantity, price))\n",
    "    conn.commit()\n",
    "\n",
    "insert('auto', 12, 10.5)\n",
    "#conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('fiets', 10, 1234.56)\n",
      "('fiets', 10, 1234.56)\n"
     ]
    }
   ],
   "source": [
    "#You can search the database by using LIKE\n",
    "search = cur.execute(\"SELECT * FROM store WHERE item LIKE 'fiets'\")\n",
    "for x in search:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM store WHERE item LIKE 'item'\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM store WHERE item LIKE '\"+ str('item')+\"'\"\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM store WHERE item LIKE 'item'\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "'function' object is not iterable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [38]\u001b[0m, in \u001b[0;36m<cell line: 10>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[38;5;28mprint\u001b[39m(sql)\n\u001b[1;32m      9\u001b[0m search(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfiets\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m---> 10\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m search:\n\u001b[1;32m     11\u001b[0m     \u001b[38;5;28mprint\u001b[39m(x)\n",
      "\u001b[0;31mTypeError\u001b[0m: 'function' object is not iterable"
     ]
    }
   ],
   "source": [
    "#You can search the database by using LIKE\n",
    "\n",
    "def search(item):\n",
    "    sql = \"SELECT * FROM store WHERE item LIKE '\"+ str(item)+\"'\"\n",
    "    search = cur.execute(sql)\n",
    "    #conn.commit()\n",
    "\n",
    "print(sql)\n",
    "search('fiets')\n",
    "for x in search:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. MySQL connector Python\n",
    "\n",
    "- pypi: https://pypi.org/project/mysql-connector-python/\n",
    "- source: https://www.w3schools.com/python/python_mysql_getstarted.asp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install MySQL\n",
    "\n",
    "Make sure you have installed MySQL on your device!\n",
    "For Apple see this instruction using brew: https://flaviocopes.com/mysql-how-to-install/\n",
    "For Windows, you can install it from here: https://dev.mysql.com/downloads/installer/        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'mysql'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[1], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m#check if MySQL is installed, if this code runs without errors it is installed.\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mmysql\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mconnector\u001b[39;00m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'mysql'"
     ]
    }
   ],
   "source": [
    "#check if MySQL is installed, if this code runs without errors it is installed.\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection.MySQLConnection object at 0x7fd349a260a0>\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "import config #i've stored the password in the config file\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=config.mysql_pw\n",
    ")\n",
    "\n",
    "print(mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#next create a cursor object to interact with the database and then crate a database\n",
    "mycursor = mydb.cursor()\n",
    "mycursor.execute(\"CREATE DATABASE if not exists mydatabase\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mydatabase',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('sys',)\n"
     ]
    }
   ],
   "source": [
    "#show databases\n",
    "mycursor.execute(\"SHOW DATABASES\")\n",
    "for i in mycursor:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a connection with the database selected\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=config.mysql_pw,\n",
    "  database='mydatabase'   \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "#create a CURSOR object and with it create a table 'customers'\n",
    "mycursor = mydb.cursor()\n",
    "mycursor.execute(\"CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))\")\n",
    "print('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('customers',)\n"
     ]
    }
   ],
   "source": [
    "#show all tables\n",
    "mycursor.execute(\"SHOW TABLES\")\n",
    "for x in mycursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert data into table\n",
    "mycursor.execute(\"INSERT INTO customers VALUES('Henk', 'Wibautstraat')\")\n",
    "#mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Henk', 'Wibautstraat', None)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#select records\n",
    "mycursor.execute('SELECT * FROM customers')\n",
    "rows = mycursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#delete record\n",
    "sql = 'DELETE FROM customers WHERE name=\"Henk\"'\n",
    "mycursor.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop a table\n",
    "mycursor.execute('DROP TABLE customers')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. MariaDB\n",
    "\n",
    "source: https://mariadb.com/resources/blog/how-to-connect-python-programs-to-mariadb/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting mariadb\n",
      "  Downloading mariadb-1.1.6.tar.gz (83 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m83.7/83.7 kB\u001b[0m \u001b[31m1.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m\n",
      "\u001b[?25h  Installing build dependencies ... \u001b[?25ldone\n",
      "\u001b[?25h  Getting requirements to build wheel ... \u001b[?25lerror\n",
      "  \u001b[1;31merror\u001b[0m: \u001b[1msubprocess-exited-with-error\u001b[0m\n",
      "  \n",
      "  \u001b[31m×\u001b[0m \u001b[32mGetting requirements to build wheel\u001b[0m did not run successfully.\n",
      "  \u001b[31m│\u001b[0m exit code: \u001b[1;36m1\u001b[0m\n",
      "  \u001b[31m╰─>\u001b[0m \u001b[31m[30 lines of output]\u001b[0m\n",
      "  \u001b[31m   \u001b[0m /bin/sh: mariadb_config: command not found\n",
      "  \u001b[31m   \u001b[0m Traceback (most recent call last):\n",
      "  \u001b[31m   \u001b[0m   File \"/Users/michielbontenbal/anaconda3/lib/python3.10/site-packages/pip/_vendor/pep517/in_process/_in_process.py\", line 351, in <module>\n",
      "  \u001b[31m   \u001b[0m     main()\n",
      "  \u001b[31m   \u001b[0m   File \"/Users/michielbontenbal/anaconda3/lib/python3.10/site-packages/pip/_vendor/pep517/in_process/_in_process.py\", line 333, in main\n",
      "  \u001b[31m   \u001b[0m     json_out['return_val'] = hook(**hook_input['kwargs'])\n",
      "  \u001b[31m   \u001b[0m   File \"/Users/michielbontenbal/anaconda3/lib/python3.10/site-packages/pip/_vendor/pep517/in_process/_in_process.py\", line 118, in get_requires_for_build_wheel\n",
      "  \u001b[31m   \u001b[0m     return hook(config_settings)\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-build-env-jv3y4ad7/overlay/lib/python3.10/site-packages/setuptools/build_meta.py\", line 341, in get_requires_for_build_wheel\n",
      "  \u001b[31m   \u001b[0m     return self._get_build_requires(config_settings, requirements=['wheel'])\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-build-env-jv3y4ad7/overlay/lib/python3.10/site-packages/setuptools/build_meta.py\", line 323, in _get_build_requires\n",
      "  \u001b[31m   \u001b[0m     self.run_setup()\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-build-env-jv3y4ad7/overlay/lib/python3.10/site-packages/setuptools/build_meta.py\", line 487, in run_setup\n",
      "  \u001b[31m   \u001b[0m     super(_BuildMetaLegacyBackend,\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-build-env-jv3y4ad7/overlay/lib/python3.10/site-packages/setuptools/build_meta.py\", line 338, in run_setup\n",
      "  \u001b[31m   \u001b[0m     exec(code, locals())\n",
      "  \u001b[31m   \u001b[0m   File \"<string>\", line 27, in <module>\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-install-92weisbz/mariadb_120adb5886e7458e9f780bd54d3035f7/mariadb_posix.py\", line 62, in get_config\n",
      "  \u001b[31m   \u001b[0m     cc_version = mariadb_config(config_prg, \"cc_version\")\n",
      "  \u001b[31m   \u001b[0m   File \"/private/var/folders/cj/qtbz9fvd3svc0x28yv2756mh0000gn/T/pip-install-92weisbz/mariadb_120adb5886e7458e9f780bd54d3035f7/mariadb_posix.py\", line 28, in mariadb_config\n",
      "  \u001b[31m   \u001b[0m     raise EnvironmentError(\n",
      "  \u001b[31m   \u001b[0m OSError: mariadb_config not found.\n",
      "  \u001b[31m   \u001b[0m \n",
      "  \u001b[31m   \u001b[0m This error typically indicates that MariaDB Connector/C, a dependency which\n",
      "  \u001b[31m   \u001b[0m must be preinstalled, is not found.\n",
      "  \u001b[31m   \u001b[0m If MariaDB Connector/C is not installed, see installation instructions\n",
      "  \u001b[31m   \u001b[0m If MariaDB Connector/C is installed, either set the environment variable\n",
      "  \u001b[31m   \u001b[0m MARIADB_CONFIG or edit the configuration file 'site.cfg' to set the\n",
      "  \u001b[31m   \u001b[0m  'mariadb_config' option to the file location of the mariadb_config utility.\n",
      "  \u001b[31m   \u001b[0m \n",
      "  \u001b[31m   \u001b[0m \u001b[31m[end of output]\u001b[0m\n",
      "  \n",
      "  \u001b[1;35mnote\u001b[0m: This error originates from a subprocess, and is likely not a problem with pip.\n",
      "\u001b[1;31merror\u001b[0m: \u001b[1msubprocess-exited-with-error\u001b[0m\n",
      "\n",
      "\u001b[31m×\u001b[0m \u001b[32mGetting requirements to build wheel\u001b[0m did not run successfully.\n",
      "\u001b[31m│\u001b[0m exit code: \u001b[1;36m1\u001b[0m\n",
      "\u001b[31m╰─>\u001b[0m See above for output.\n",
      "\n",
      "\u001b[1;35mnote\u001b[0m: This error originates from a subprocess, and is likely not a problem with pip.\n",
      "\u001b[?25h"
     ]
    }
   ],
   "source": [
    "!pip3 install mariadb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'mariadb'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[3], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# Module Imports\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mmariadb\u001b[39;00m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01msys\u001b[39;00m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;66;03m# Connect to MariaDB Platform\u001b[39;00m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'mariadb'"
     ]
    }
   ],
   "source": [
    "# Module Imports\n",
    "import mariadb\n",
    "import sys\n",
    "\n",
    "# Connect to MariaDB Platform\n",
    "try:\n",
    "    conn = mariadb.connect(\n",
    "        user=\"db_user\",\n",
    "        password=\"db_user_passwd\",\n",
    "        host=\"192.0.2.1\",\n",
    "        port=3306,\n",
    "        database=\"employees\"\n",
    "\n",
    "    )\n",
    "except mariadb.Error as e:\n",
    "    print(f\"Error connecting to MariaDB Platform: {e}\")\n",
    "    sys.exit(1)\n",
    "\n",
    "# Get Cursor\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Duckdb\n",
    "source: https://duckdb.org/docs/api/python/overview.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#setting up a database\n",
    "import duckdb\n",
    "con = duckdb.connect(database='my-db.duckdb', read_only=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('jeans', Decimal('20.00'), 1), ('hammer', Decimal('42.20'), 2)]\n"
     ]
    }
   ],
   "source": [
    "#PLEASE NOTE THAT YOU DON\"T HAVE TO CREATE A CURSOR OBJECT!\n",
    "# create a table\n",
    "con.execute(\"CREATE TABLE items(item VARCHAR, value DECIMAL(10,2), count INTEGER)\")\n",
    "# insert two items into the table\n",
    "con.execute(\"INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)\")\n",
    "\n",
    "# retrieve the items again\n",
    "con.execute(\"SELECT * FROM items\")\n",
    "print(con.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}