DBMS/mysql-notebooks/MySQL using Python Connector.ipynb

MySQL using Python Connector.ipynb

{
  "cells": [
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "# MYSQL via Python\n* We will learn to use mysql via python\n* Please install the following :\n    1. [MySQL Community Server 8.x](https://dev.mysql.com/downloads/mysql/) (For those using windows, you can use windows installer)\n    2. mysql connector (pip install mysql-connector-python)\n    3. Pandas (pip install pandas)"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "import mysql.connector as sql\nimport pandas as pd\nfrom IPython.display import display, HTML",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Utility Functions"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "current_connection = None\ncurrent_cursor = None\n\ndef setup_current_connection(dbname=None):\n    global current_connection\n    if current_connection:\n        current_connection.close()\n        current_connection = None\n    if dbname:\n        current_connection = sql.connect(\n            host=\"localhost\",\n            user=\"root\",\n            passwd=\"root\",               #I named as root\n            database = dbname\n        )\n    else:\n        current_connection = sql.connect(\n            host=\"localhost\",\n            user=\"root\",\n            passwd=\"root\",\n        )\n\ndef setup_cursor(dbname):\n    global current_cursor\n    if (not current_connection) or current_connection.database != dbname:\n        setup_current_connection(dbname)\n        current_cursor = current_connection.cursor()\n    \ndef setup_db(dbname):\n    setup_cursor(dbname)\n\ndef execute_and_print_dml(dml,dbname=None):\n    if (not current_connection) or (not current_cursor) or current_connection.database != dbname:\n        setup_db(dbname)\n    current_cursor.execute(dml)\n    for x in current_cursor:\n        print(x)\n    current_connection.commit()\n\ndef execute_and_print_dml_as_df (dml, dbname=None):\n    if (not current_connection) or current_connection.database != dbname:\n        setup_current_connection(dbname)\n    df = pd.read_sql(dml, con = current_connection)\n    display(df)\n    current_connection.commit()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Testing Utility Functions"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml(\"CREATE DATABASE IF NOT EXISTS ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml(\"SHOW DATABASES\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"SHOW DATABASES\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"SELECT * FROM country\",\"world\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"SELECT Count(Name) FROM country WHERE Continent='North America'\",\"world\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "# The SQL CREATE TABLE Statement\nThe CREATE TABLE statement is used to create a new table in a database.\n## Syntax basic\n```mysql\nCREATE TABLE table_name (\n    column1 datatype,\n    column2 datatype,\n    column3 datatype,\n   ....\n);```\n## Syntax with constraints\nCREATE TABLE table_name (\n    column1 datatype constraint,\n    column2 datatype constraint,\n    column3 datatype constraint,\n    ....\n);\nThe column parameters specify the names of the columns(attributes) of the table(relations). The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).\n\nThe constraints can be:\n1. NOT NULL - Ensures that a column cannot have a NULL value\n2. UNIQUE - Ensures that all values in a column are different\n3. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table\n4. FOREIGN KEY - Uniquely identifies a row/record in another table\n5. CHECK - Ensures that all values in a column satisfies a specific condition\n6. DEFAULT - Sets a default value for a column when no value is specified\n7. INDEX - Used to create and retrieve data from the database very quickly\n\nLets Create the Customer table in W3schools with a few extra columns to highlight creation of constraints."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "create_table_command = \"\"\"\nCREATE TABLE Customers (\n    CustomerID bigint NOT NULL,\n    CustomerName varchar(255) NOT NULL,\n    ContactName varchar(255),\n    Address varchar(255),\n    City varchar(255),\n    PostalCode varchar(255),\n    Country varchar(255) DEFAULT 'India',\n    Age int,\n    CONSTRAINT UC_Customer UNIQUE (CustomerID),\n    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID),\n    CONSTRAINT CHK_Customer CHECK (Age>=18)\n)\n\"\"\"",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml(create_table_command,\"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml(\"SHOW TABLES\", \"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"DESC Customers\", \"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "<h2>The SQL INSERT INTO Statement</h2>\n<p>The INSERT INTO statement is used to insert new records in a table.</p>\n\n<h3>INSERT INTO Syntax</h3>\n<p>It is possible to write the INSERT INTO statement in two ways.</p>\n\n<p>The first way specifies both the column names and the values to be inserted:</p>\n<div class=\"w3-example\">\n<div class=\"w3-code notranslate sqlHigh\"><br>\n<!-- <font face=\"Lucida Console\" color=\"blue\">INSERT INTO <em>table_name</em> (<em>column1</em>,<em> column2</em>,<em> column3</em>, ...)<br>\n    VALUES (<em>value1</em>,<em> value2</em>,<em> value3</em>, ...);</font>\n    </div></div>\n -->\n    \n```mysql\nINSERT INTO table_name (column1, column2, column3, ...)\nVALUES (value1, value2, value3, ...);\n```\n\n\n<p>If you are adding values for all the columns of the table, you do not need to \nspecify the column names in the SQL query. However, make sure the order of the \nvalues is in the same order as the columns in the table. The INSERT INTO syntax \nwould be as follows:</p>\n<div class=\"w3-example\">\n<div class=\"w3-code notranslate sqlHigh\">\n<br>\n<!-- <font face=\"Lucida Console\" color=\"blue\">\nINSERT INTO <em>table_name</em><br>\n    VALUES (<em>value1</em>,<em> value2</em>,<em> value3</em>, ...);</font></div></div>\n     -->\n\n```mysql\nINSERT INTO table_name\nVALUES (value1, value2, value3, ...);\n```"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "insert_data_command=\"\"\"\nINSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country, Age)\nVALUES (92,'Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway',40);\n\"\"\"",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml(insert_data_command, \"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"SELECT * FROM customers\",\"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "insert_data_commands=[\n\"INSERT INTO Customers VALUES (1,'Alfreds Futterkiste','Maria Anders','Obere Str. 57','Berlin','12209','Germany',20);\",\n\"INSERT INTO Customers VALUES (2,'Ana Trujillo Emparedados y helados','Ana Trujillo','Avda. de la Constitución 2222','México D.F.','05021','Mexico',25);\",\n\"INSERT INTO Customers VALUES (3,'Antonio Moreno Taquería','Antonio Moreno','Mataderos 2312','México D.F.','05023','Mexico',30);\",\n\"INSERT INTO Customers VALUES (4,'Around the Horn','Thomas Hardy','120 Hanover Sq.','London','WA1 1DP','UK',20);\",\n\"INSERT INTO Customers VALUES (5,'Berglunds snabbköp','Christina Berglund','Berguvsvägen 8','Luleå','S-958 22','Sweden',25);\"\n]",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "for insert_command in insert_data_commands:\n    execute_and_print_dml(insert_command, \"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "execute_and_print_dml_as_df(\"SELECT * FROM customers\",\"ase1\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "# Collection of SQL Notebooks for w3schools.com\n1. [SQL Select](SQL_Select.ipynb)\n2. SQL Select Distinct\n3. SQL Where\n4. SQL And, Or, Not\n5. SQL Order By\n6. SQL Insert into\n7. SQL Null Values\n8. SQL Update\n9. SQL Delete\n10. SQL Select Top\n11. SQL Min and Max\n12. SQL Count, Avg, Sum\n13. SQL Like\n14. SQL Wildcards\n15. SQL In"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "toc": {
      "nav_menu": {},
      "number_sections": true,
      "sideBar": true,
      "skip_h1_title": false,
      "base_numbering": 1,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": false
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "gist": {
      "id": "",
      "data": {
        "description": "DBMS/mysql-notebooks/MySQL using Python Connector.ipynb",
        "public": true
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}

Utility function for python-connector and mysql