Connecting to Database¶
We will be using JupyterHub based environment to master Postgresql. Let us go through the steps involved to get started using JupyterHub environment.
- We will use Python Kernel with sql magic command and for that we need to first load the sql extension.
- Create environment variable
DATABASE_URLusing SQL Alchemy format. - Write a simple query to get data from information schema table to validate database connectivity.
- Here is the information you can leverage to connect to the database.
- User Name: YOUR_OS_USER_sms_user
- Database Name: YOUR_OS_USER_sms_db
- Password: Your lab password provided by us
In [4]:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
In [5]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [6]:
%sql SELECT * FROM information_schema.tables LIMIT 10
10 rows affected.
Out[6]:
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | pg_catalog | pg_type | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | pg_catalog | pg_foreign_table | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | pg_catalog | pg_roles | VIEW | None | None | None | None | None | NO | NO | None |
| itversity_sms_db | pg_catalog | pg_settings | VIEW | None | None | None | None | None | NO | NO | None |
| itversity_sms_db | pg_catalog | pg_prepared_statements | VIEW | None | None | None | None | None | NO | NO | None |
| itversity_sms_db | pg_catalog | pg_stat_progress_vacuum | VIEW | None | None | None | None | None | NO | NO | None |
| itversity_sms_db | pg_catalog | pg_subscription | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | pg_catalog | pg_attribute | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | pg_catalog | pg_proc | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | pg_catalog | pg_class | BASE TABLE | None | None | None | None | None | YES | NO | None |
In [7]:
%sql SELECT * FROM users
In [ ]: