create_db.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  passwd=\"1234\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"CREATE DATABASE Plants\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mydata',)\n",
      "('mydb',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('plants',)\n",
      "('sakila',)\n",
      "('sys',)\n",
      "('test',)\n",
      "('world',)\n"
     ]
    }
   ],
   "source": [
    "#List of all availabel databases\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "mycursor.execute('SHOW DATABASES')\n",
    "for cursor in mycursor:\n",
    "    print(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Connecting to the database\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  passwd=\"1234\",\n",
    "  database=\"plants\"\n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "mycursor = mydb.cursor()\n",
    "mycursor.execute(\"CREATE TABLE images_plants (sci_name VARCHAR(255), images VARCHAR(255))\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('SHOW TABLES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('images_plants',)\n"
     ]
    }
   ],
   "source": [
    "for i in mycursor:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import mysql.connector\n",
    "\n",
    "\n",
    "mydb = mysql.connector.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "\n",
    "cursor.execute(\"CREATE TABLE images_plants (sci_name VARCHAR(255), images VARCHAR(2000))\")\n",
    "\n",
    "data = pd.read_csv('Data/images_plants.csv')\n",
    "for index,value in data.iterrows():\n",
    "    sql = 'INSERT INTO images_plants (sci_name,images) VALUES (%s,%s)'\n",
    "    #cursor.execute('INSERT INTO images_plants (sci_name,images) VALUES (%s,%s)')\n",
    "    val = (data['sci_name'][index],data['images'][index])\n",
    "    cursor.execute(sql,val)\n",
    "mydb.commit()\n",
    "\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('Data/images_plants.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "subset = data[['sci_name', 'images']]\n",
    "tuples = [tuple(x) for x in subset.to_numpy()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = mysql.connector.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute('DROP TABLE images_plants')\n",
    "cursor.execute(\"CREATE TABLE images_plants (sci_name VARCHAR(255), images TEXT)\")\n",
    "sql = 'INSERT INTO images_plants (sci_name,images) VALUES (%s,%s)'\n",
    "cursor.executemany(sql, tuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('Data/endangered_plants.csv')\n",
    "subset = data[['sci_name', 'Common Name','Threatened status','Profile']]\n",
    "tuples = [tuple(x) for x in subset.to_numpy()]\n",
    "mydb = mysql.connector.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute(\"CREATE TABLE plants (sci_name VARCHAR(255), comm_name VARCHAR(255), status VARCHAR(255), Profile VARCHAR(500))\")\n",
    "sql = 'INSERT INTO plants (sci_name,comm_name,status,Profile) VALUES (%s,%s,%s,%s)'\n",
    "cursor.executemany(sql, tuples)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    471\u001b[0m                                \u001b[0mraw\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mraw\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mbuffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 472\u001b[1;33m                                raw_as_string=raw_as_string)\n\u001b[0m\u001b[0;32m    473\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-19-beda45dfca45>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      7\u001b[0m     db='plants')\n\u001b[0;32m      8\u001b[0m \u001b[0mcursor\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmydb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"CREATE TABLE plant_suburbs (sci_name VARCHAR(255),suburb VARCHAR(255)\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     10\u001b[0m \u001b[0msql\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m'INSERT INTO plant_suburbs (sci_name,suburb) VALUES (%s,%s)'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtuples\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    264\u001b[0m             result = self._cnx.cmd_query(stmt, raw=self._raw,\n\u001b[0;32m    265\u001b[0m                                          \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 266\u001b[1;33m                                          raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[0;32m    267\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    268\u001b[0m             raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    473\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    474\u001b[0m             raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[1;32m--> 475\u001b[1;33m                                              sqlstate=exc.sqlstate)\n\u001b[0m\u001b[0;32m    476\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    477\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_unix_socket\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mProgrammingError\u001b[0m: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('Data/plants_suburbs.csv')\n",
    "subset = data[['sci_name', 'suburb']]\n",
    "tuples = [tuple(x) for x in subset.to_numpy()]\n",
    "mydb = mysql.connector.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute(\"CREATE TABLE plant_suburbs (sci_name VARCHAR(255),suburb VARCHAR(200)\")\n",
    "sql = 'INSERT INTO plant_suburbs (sci_name,suburb) VALUES (%s,%s)'\n",
    "cursor.executemany(sql, tuples)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymysql\n",
      "  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)\n",
      "Installing collected packages: pymysql\n",
      "Successfully installed pymysql-0.9.3\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "mydb = pymysql.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor=mydb.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"DROP TABLE suburbs\")\n",
    "cursor.execute('CREATE TABLE suburbs (sci_name VARCHAR(255), suburb TEXT)')\n",
    "sql = 'INSERT INTO suburbs (sci_name,suburb) VALUES (%s,%s)'\n",
    "cursor.executemany(sql,tuples)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = mydb.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('SHOW TABLES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = mysql.connector.connect(host='localhost',\n",
    "    user='root',\n",
    "    passwd='1234',\n",
    "    db='plants')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('table3_final.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "tuples = [tuple(x) for x in data[[\"sci_name\",\"location\"]].to_numpy()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor=mydb.cursor()\n",
    "sql = 'INSERT INTO locations (sci_name,location) VALUES (%s,%s)'\n",
    "cursor.executemany(sql,tuples)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}