mssqlserver.ipynb

{
 "cells": [
  {
   "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": [
    "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:** To connect to a Microsoft SQL Server (MSSQL) database from Python, you can use the pyodbc library or pymssql. The most common choice is pyodbc because it provides a flexible and powerful way to work with MSSQL databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyodbc in c:\\users\\91960\\machine learning\\database connection\\database_connection\\lib\\site-packages (5.2.0)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip is available: 23.2.1 -> 24.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "!pip install pyodbc\n",
    "import pyodbc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Define your connection parameter:** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'NITIN'\n",
    "database = 'DB_Connection'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Create the Connection String:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};'\n",
    "                      f'SERVER={server};'\n",
    "                      f'DATABASE={database};'\n",
    "                      f'Trusted_Connection=yes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Creating a Cursor:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Creating a Table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table Created Successfully.\n"
     ]
    }
   ],
   "source": [
    "cursor.execute('CREATE TABLE Testing_Connection (Test_ID INT, Test_Name VARCHAR(50))')\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "print(\"Table Created Successfully.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6. Inserting Data to the Table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row Inserted.\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"INSERT INTO Testing_Connection (test_ID, Test_Name) VALUES (1, 'Nitin Testing')\")\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "print('Row Inserted.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7. Fetch and Display Results:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Nitin Testing')\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM Testing_Connection\")\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8. Close Cursor and Connection:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "database_connection",
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}