mysql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database Connection with Python using MySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A database connection is essential for allowing your application (such as a Python program) to interact with a database. Without a connection, your program wouldn't be able to read, write, or manipulate data stored in the database. Here are some of the key reasons why a database connection is needed:\n",
    "\n",
    "1. Data Storage and Retrieval\n",
    "2. Enabling SQL Queries\n",
    "3. Transaction Management\n",
    "4. Concurrency and Multi-user Access\n",
    "5. Security and Authentication\n",
    "6. Efficiency and Performance\n",
    "7. Data Integrity and Consistency\n",
    "8. Communication Between Application and Database\n",
    "9. Managing Complex Queries and Relationships\n",
    "10. External Applications and APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do Database Connection\n",
    "\n",
    "To connect to a database using Python, you'll need to use a database connector or driver specific to the type of database you're working with (e.g., SQLite, MySQL, PostgreSQL).\n",
    "\n",
    "**1. Import required library:** You'll need to install a MySQL driver like mysql-connector-python or PyMySQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install mysql-connector-python\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Establish the connection to MySQL Database:** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='your_username',\n",
    "    password='your_password',\n",
    "    database='your_database'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Create Cursor Object using the connection:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connect.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Create Table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Insert Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"INSERT INTO users (name) VALUES ('nitin')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('rucha')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('neha')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('timish')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('amol')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6. Commit the Changes:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7. Retrieve Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT * FROM users\")\n",
    "\n",
    "rows = cursor.fetchall\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8. Close the connection:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "connect.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "database_connection",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}