Connect to database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "720863ab-193a-4c9d-bec9-6ff8f2fdc763",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "85e64ec8-62bf-40aa-be25-ccfa67e10d22",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection.MySQLConnection object at 0x000001B17705B800>\n"
     ]
    }
   ],
   "source": [
    "mydb=mysql.connector.connect(\n",
    "host='localhost',\n",
    "user=\"root\",\n",
    "port='3307',\n",
    "passwd=\"\"\n",
    ")\n",
    "print(mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a2a7c4d-a7f5-41c7-b5b3-ef0b2c81e438",
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "1007 (HY000): Can't create database 'authorised_user'; database exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[4], line 2\u001b[0m\n\u001b[0;32m      1\u001b[0m mycursor\u001b[38;5;241m=\u001b[39mmydb\u001b[38;5;241m.\u001b[39mcursor()\n\u001b[1;32m----> 2\u001b[0m \u001b[43mmycursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mCREATE DATABASE Authorised_user\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\cursor.py:551\u001b[0m, in \u001b[0;36mMySQLCursor.execute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    549\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    550\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 551\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_result(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_connection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcmd_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstmt\u001b[49m\u001b[43m)\u001b[49m)\n\u001b[0;32m    552\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError:\n\u001b[0;32m    553\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_connection\u001b[38;5;241m.\u001b[39m_have_next_result:  \u001b[38;5;66;03m# pylint: disable=W0212\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:490\u001b[0m, in \u001b[0;36mMySQLConnection.cmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    488\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(query, \u001b[38;5;28mbytes\u001b[39m):\n\u001b[0;32m    489\u001b[0m     query \u001b[38;5;241m=\u001b[39m query\u001b[38;5;241m.\u001b[39mencode(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mutf-8\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m--> 490\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_result\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_send_cmd\u001b[49m\u001b[43m(\u001b[49m\u001b[43mServerCmd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mQUERY\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    492\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_have_next_result:\n\u001b[0;32m    493\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError(\n\u001b[0;32m    494\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mUse cmd_query_iter for statements with multiple queries.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:395\u001b[0m, in \u001b[0;36mMySQLConnection._handle_result\u001b[1;34m(self, packet)\u001b[0m\n\u001b[0;32m    393\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_eof(packet)\n\u001b[0;32m    394\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m packet[\u001b[38;5;241m4\u001b[39m] \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m255\u001b[39m:\n\u001b[1;32m--> 395\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mget_exception(packet)\n\u001b[0;32m    397\u001b[0m \u001b[38;5;66;03m# We have a text result set\u001b[39;00m\n\u001b[0;32m    398\u001b[0m column_count \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_protocol\u001b[38;5;241m.\u001b[39mparse_column_count(packet)\n",
      "\u001b[1;31mDatabaseError\u001b[0m: 1007 (HY000): Can't create database 'authorised_user'; database exists"
     ]
    }
   ],
   "source": [
    "mycursor=mydb.cursor()\n",
    "mycursor.execute(\"CREATE DATABASE authorised_user\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8c5916ac-cbf0-47e5-9d86-7e79153dae96",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('authorised_user',)\n",
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('phpmyadmin',)\n",
      "('test',)\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"SHOW DATABASES\")\n",
    "for x in mycursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "85192d4a-5262-49a3-a3e4-d684226bf383",
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "1008 (HY000): Can't drop database 'hello'; database doesn't exist",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[6], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mmycursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mdrop database hello\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\cursor.py:551\u001b[0m, in \u001b[0;36mMySQLCursor.execute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    549\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    550\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 551\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_result(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_connection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcmd_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstmt\u001b[49m\u001b[43m)\u001b[49m)\n\u001b[0;32m    552\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError:\n\u001b[0;32m    553\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_connection\u001b[38;5;241m.\u001b[39m_have_next_result:  \u001b[38;5;66;03m# pylint: disable=W0212\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:490\u001b[0m, in \u001b[0;36mMySQLConnection.cmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    488\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(query, \u001b[38;5;28mbytes\u001b[39m):\n\u001b[0;32m    489\u001b[0m     query \u001b[38;5;241m=\u001b[39m query\u001b[38;5;241m.\u001b[39mencode(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mutf-8\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m--> 490\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_result\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_send_cmd\u001b[49m\u001b[43m(\u001b[49m\u001b[43mServerCmd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mQUERY\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    492\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_have_next_result:\n\u001b[0;32m    493\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError(\n\u001b[0;32m    494\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mUse cmd_query_iter for statements with multiple queries.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:395\u001b[0m, in \u001b[0;36mMySQLConnection._handle_result\u001b[1;34m(self, packet)\u001b[0m\n\u001b[0;32m    393\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_eof(packet)\n\u001b[0;32m    394\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m packet[\u001b[38;5;241m4\u001b[39m] \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m255\u001b[39m:\n\u001b[1;32m--> 395\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mget_exception(packet)\n\u001b[0;32m    397\u001b[0m \u001b[38;5;66;03m# We have a text result set\u001b[39;00m\n\u001b[0;32m    398\u001b[0m column_count \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_protocol\u001b[38;5;241m.\u001b[39mparse_column_count(packet)\n",
      "\u001b[1;31mDatabaseError\u001b[0m: 1008 (HY000): Can't drop database 'hello'; database doesn't exist"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"drop database hello\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "03e62b7f-a811-476f-8976-853baa357b6f",
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1050 (42S01): Table 'my_table' already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[9], line 9\u001b[0m\n\u001b[0;32m      1\u001b[0m mydb\u001b[38;5;241m=\u001b[39mmysql\u001b[38;5;241m.\u001b[39mconnector\u001b[38;5;241m.\u001b[39mconnect(\n\u001b[0;32m      2\u001b[0m host\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlocalhost\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[0;32m      3\u001b[0m port\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m3307\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m      6\u001b[0m database\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAuthorised_user\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m      7\u001b[0m )\n\u001b[0;32m      8\u001b[0m mycursor\u001b[38;5;241m=\u001b[39mmydb\u001b[38;5;241m.\u001b[39mcursor()\n\u001b[1;32m----> 9\u001b[0m \u001b[43mmycursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mCREATE TABLE my_table (id INT PRIMARY KEY, Name VARCHAR(50), Age INT, Address VARCHAR(50))\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\cursor.py:551\u001b[0m, in \u001b[0;36mMySQLCursor.execute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    549\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    550\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 551\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_result(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_connection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcmd_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstmt\u001b[49m\u001b[43m)\u001b[49m)\n\u001b[0;32m    552\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError:\n\u001b[0;32m    553\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_connection\u001b[38;5;241m.\u001b[39m_have_next_result:  \u001b[38;5;66;03m# pylint: disable=W0212\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:490\u001b[0m, in \u001b[0;36mMySQLConnection.cmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    488\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(query, \u001b[38;5;28mbytes\u001b[39m):\n\u001b[0;32m    489\u001b[0m     query \u001b[38;5;241m=\u001b[39m query\u001b[38;5;241m.\u001b[39mencode(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mutf-8\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m--> 490\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_result\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_send_cmd\u001b[49m\u001b[43m(\u001b[49m\u001b[43mServerCmd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mQUERY\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    492\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_have_next_result:\n\u001b[0;32m    493\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mInterfaceError(\n\u001b[0;32m    494\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mUse cmd_query_iter for statements with multiple queries.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\mysql\\connector\\connection.py:395\u001b[0m, in \u001b[0;36mMySQLConnection._handle_result\u001b[1;34m(self, packet)\u001b[0m\n\u001b[0;32m    393\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_handle_eof(packet)\n\u001b[0;32m    394\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m packet[\u001b[38;5;241m4\u001b[39m] \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m255\u001b[39m:\n\u001b[1;32m--> 395\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m errors\u001b[38;5;241m.\u001b[39mget_exception(packet)\n\u001b[0;32m    397\u001b[0m \u001b[38;5;66;03m# We have a text result set\u001b[39;00m\n\u001b[0;32m    398\u001b[0m column_count \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_protocol\u001b[38;5;241m.\u001b[39mparse_column_count(packet)\n",
      "\u001b[1;31mProgrammingError\u001b[0m: 1050 (42S01): Table 'my_table' already exists"
     ]
    }
   ],
   "source": [
    "mydb=mysql.connector.connect(\n",
    "host='localhost',\n",
    "port='3307',\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"Authorised_user\"\n",
    ")\n",
    "mycursor=mydb.cursor()\n",
    "mycursor.execute(\"CREATE TABLE my_table (id INT PRIMARY KEY, Name VARCHAR(50), Age INT, Address VARCHAR(50))\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c91d7063-1889-4af5-9abd-b85c32ffb32e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('my_table',)\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"SHOW TABLES\")\n",
    "for x in mycursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37274530-d85b-4dd4-9e86-da8c56d218ed",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}