sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from tabulate import tabulate"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Login"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_sql = 'USER'\n",
    "pass_sql = 'PWD'\n",
    "server = 'SQNPRC001'\n",
    "database = 'DBSV001'\n",
    "\n",
    "string_conec = 'mssql+pyodbc://'+user_sql+':'+pass_sql+'@'+server+'/'+database+'?driver=SQL+Server+Native_Client+11.0'\n",
    "engine = create_engine(string_conec,echo=False,pool_size=50,max_overflow=50)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Search Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def search_table(substring):\n",
    "\n",
    "    sql_tables = pd.read_sql('SELECT * FROM information_schema.tables', string_conec)\n",
    "\n",
    "    sql_tables = sql_tables[sql_tables['TABLE_NAME'].str.contains(substring)]\n",
    "\n",
    "    def concat_values(row):\n",
    "        return f\"{row['TABLE_SCHEMA']}.{row['TABLE_NAME']}\"\n",
    "    \n",
    "    sql_tables['TABLE_NAME'] = sql_tables.apply(concat_values, axis = 1)\n",
    "\n",
    "    sql_tables = sql_tables.reset_index(drop=True)\n",
    "\n",
    "    sql_tables = sql_tables[['TABLE_NAME']]\n",
    "\n",
    "    print(tabulate(sql_tables, headers='keys', tablefmt='pretty'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "search_table('substring')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Select Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = 'dbo.tb_tablename'\n",
    "schema, tablename = table.split('.')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# List Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def list_columns(tablename):\n",
    "\n",
    "    schema, table = tablename.split('.')\n",
    "\n",
    "    types = pd.read_sql('exec sp_columns \\''+table+'\\', \\''+schema+'\\'', string_conec)\n",
    "    types = types[['COLUMN_NAME', 'TYPE_NAME']]\n",
    "\n",
    "    print(tabulate(types, headers='heys', tablefmt='pretty'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_script(table)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Check Primary Keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def primary_key(tablename):\n",
    "\n",
    "    tablename = 'dbo.tb_tablename'\n",
    "\n",
    "    schema, table = tablename.split('.')\n",
    "\n",
    "    pk = pd.read_sql(f'''\n",
    "    \n",
    "    SELECT COLUMN_NAME\n",
    "    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n",
    "    WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1\n",
    "    AND TABLE_SCHEMA = '{schema}'\n",
    "    AND TABLE_NAME = '{table}'\n",
    "\n",
    "    ''')\n",
    "\n",
    "    print(tabulate(pk, headers='keys', tablefmt='pretty'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "primary_key(table)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql('''\n",
    "\n",
    "SELECT\n",
    "    col1,\n",
    "    col2\n",
    "\n",
    "FROM table\n",
    "WHERE date >= '2023-03-14'\n",
    "ORDER BY id\n",
    "\n",
    "''', string_conec)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine.execute(f'DELETE FROM {table} WHERE ')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Upload"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(name = table, con = engine, schema = schema, index = False, if_exists='append')"
   ]
  }
 ],
 "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.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}