2021-09-06 Get_postgreSQL_tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bb6e7ab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cf7f4074",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(database=\"postgres\",\n",
    "                        user=\"postgres\",\n",
    "                        password=\"postgres\",\n",
    "                        host=\"localhost\",\n",
    "                        port=\"5432\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7c635174",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT TABLE_NAME\n",
    "FROM INFORMATION_SCHEMA.TABLES\n",
    "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='postgres'\n",
    "'''\n",
    "table_type = ['base_table', 'view']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "e7fdea0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "query2 = '''\n",
    "SELECT COLUMN_NAME, DATA_TYPE, UDT_NAME\n",
    "FROM INFORMATION_SCHEMA.COLUMNS\n",
    "WHERE TABLE_NAME = 'pg_statistic' AND TABLE_SCHEMA='pg_catalog'\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "deed9ec9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('pg_statistic',)\n",
      "('pg_type',)\n",
      "('pg_foreign_table',)\n",
      "('pg_authid',)\n",
      "('pg_statistic_ext_data',)\n",
      "('pg_largeobject',)\n",
      "('pg_user_mapping',)\n",
      "('pg_subscription',)\n",
      "('pg_attribute',)\n",
      "('pg_proc',)\n",
      "('pg_class',)\n",
      "('pg_attrdef',)\n",
      "('pg_constraint',)\n",
      "('pg_inherits',)\n",
      "('pg_index',)\n",
      "('pg_operator',)\n",
      "('pg_opfamily',)\n",
      "('pg_opclass',)\n",
      "('pg_am',)\n",
      "('pg_amop',)\n",
      "('pg_amproc',)\n",
      "('pg_language',)\n",
      "('pg_largeobject_metadata',)\n",
      "('pg_aggregate',)\n",
      "('pg_statistic_ext',)\n",
      "('pg_rewrite',)\n",
      "('pg_trigger',)\n",
      "('pg_event_trigger',)\n",
      "('pg_description',)\n",
      "('pg_cast',)\n",
      "('pg_enum',)\n",
      "('pg_namespace',)\n",
      "('pg_conversion',)\n",
      "('pg_depend',)\n",
      "('pg_database',)\n",
      "('pg_db_role_setting',)\n",
      "('pg_tablespace',)\n",
      "('pg_auth_members',)\n",
      "('pg_shdepend',)\n",
      "('pg_shdescription',)\n",
      "('pg_ts_config',)\n",
      "('pg_ts_config_map',)\n",
      "('pg_ts_dict',)\n",
      "('pg_ts_parser',)\n",
      "('pg_ts_template',)\n",
      "('pg_extension',)\n",
      "('pg_foreign_data_wrapper',)\n",
      "('pg_foreign_server',)\n",
      "('pg_policy',)\n",
      "('pg_replication_origin',)\n",
      "('pg_default_acl',)\n",
      "('pg_init_privs',)\n",
      "('pg_seclabel',)\n",
      "('pg_shseclabel',)\n",
      "('pg_collation',)\n",
      "('pg_partitioned_table',)\n",
      "('pg_range',)\n",
      "('pg_transform',)\n",
      "('pg_sequence',)\n",
      "('pg_publication',)\n",
      "('pg_publication_rel',)\n",
      "('pg_subscription_rel',)\n",
      "('sql_implementation_info',)\n",
      "('sql_parts',)\n",
      "('sql_sizing',)\n",
      "('sql_features',)\n"
     ]
    }
   ],
   "source": [
    "with conn:\n",
    "    with conn.cursor() as curs:\n",
    "        curs.execute(query)\n",
    "        data = curs.fetchall()\n",
    "        for d in data:\n",
    "            print(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "84b0074b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('starelid', 'oid', 'oid')\n",
      "('staattnum', 'smallint', 'int2')\n",
      "('stainherit', 'boolean', 'bool')\n",
      "('stanullfrac', 'real', 'float4')\n",
      "('stawidth', 'integer', 'int4')\n",
      "('stadistinct', 'real', 'float4')\n",
      "('stakind1', 'smallint', 'int2')\n",
      "('stakind2', 'smallint', 'int2')\n",
      "('stakind3', 'smallint', 'int2')\n",
      "('stakind4', 'smallint', 'int2')\n",
      "('stakind5', 'smallint', 'int2')\n",
      "('staop1', 'oid', 'oid')\n",
      "('staop2', 'oid', 'oid')\n",
      "('staop3', 'oid', 'oid')\n",
      "('staop4', 'oid', 'oid')\n",
      "('staop5', 'oid', 'oid')\n",
      "('stacoll1', 'oid', 'oid')\n",
      "('stacoll2', 'oid', 'oid')\n",
      "('stacoll3', 'oid', 'oid')\n",
      "('stacoll4', 'oid', 'oid')\n",
      "('stacoll5', 'oid', 'oid')\n",
      "('stanumbers1', 'ARRAY', '_float4')\n",
      "('stanumbers2', 'ARRAY', '_float4')\n",
      "('stanumbers3', 'ARRAY', '_float4')\n",
      "('stanumbers4', 'ARRAY', '_float4')\n",
      "('stanumbers5', 'ARRAY', '_float4')\n",
      "('stavalues1', 'anyarray', 'anyarray')\n",
      "('stavalues2', 'anyarray', 'anyarray')\n",
      "('stavalues3', 'anyarray', 'anyarray')\n",
      "('stavalues4', 'anyarray', 'anyarray')\n",
      "('stavalues5', 'anyarray', 'anyarray')\n"
     ]
    }
   ],
   "source": [
    "with conn:\n",
    "    with conn.cursor() as curs:\n",
    "        curs.execute(query2)\n",
    "        data = curs.fetchall()\n",
    "        for d in data:\n",
    "            print(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ab843229",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afeda6d8",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}