Setting up the Postgres Connection#

In order to create a connection to the database (from this Jupyter notebook), we gotta import sqlalchemy, and configure the engine, according to the settings we used when creating the user and the database.

import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/postgres')
engine.connect()

<sqlalchemy.engine.base.Connection at 0x7f3cfd41afe0>

Magic Commands#

Now we want to load the ipython-sql extension:

%load_ext sql

Thanks to the extension above, now we can run magic commands, aka magics (the ones that start with % or %%). For example, if we wanted to check the connection, we could run:

%sql $engine.url

'Connected: bob@postgres'

Another variation is to use %%sql on its own line (the first line), and the SQL below it:

%%sql
SELECT * FROM pg_database;

 * postgresql://bob:***@localhost:5432/postgres
6 rows affected.

oid	datname	datdba	encoding	datlocprovider	datistemplate	datallowconn	datconnlimit	datfrozenxid	datminmxid	dattablespace	datcollate	datctype	daticulocale	datcollversion	datacl
5	postgres	10	6	c	False	True	-1	717	1	1663	en_US.utf8	en_US.utf8	None	2.31	None
16384	bob	10	6	c	False	True	-1	717	1	1663	en_US.utf8	en_US.utf8	None	2.31	None
1	template1	10	6	c	True	True	-1	717	1	1663	en_US.utf8	en_US.utf8	None	2.31	{=c/bob,bob=CTc/bob}
4	template0	10	6	c	True	False	-1	717	1	1663	en_US.utf8	en_US.utf8	None	None	{=c/bob,bob=CTc/bob}
16389	dvdrental	10	6	c	False	True	-1	717	1	1663	en_US.utf8	en_US.utf8	None	2.31	None
17014	test_joins	10	6	c	False	True	-1	717	1	1663	en_US.utf8	en_US.utf8	None	2.31	None

Postgres Metacommands#

We can even run metacommands using the magic thing:

%sql \du

 * postgresql://bob:***@localhost:5432/postgres
13 rows affected.

rolname	rolsuper	rolinherit	rolcreaterole	rolcreatedb	rolcanlogin	rolconnlimit	rolvaliduntil	memberof	rolreplication
bob	True	True	True	True	True	-1	None	[]	True
pg_checkpoint	False	True	False	False	False	-1	None	[]	False
pg_database_owner	False	True	False	False	False	-1	None	[]	False
pg_execute_server_program	False	True	False	False	False	-1	None	[]	False
pg_monitor	False	True	False	False	False	-1	None	['pg_read_all_settings', 'pg_read_all_stats', 'pg_stat_scan_tables']	False
pg_read_all_data	False	True	False	False	False	-1	None	[]	False
pg_read_all_settings	False	True	False	False	False	-1	None	[]	False
pg_read_all_stats	False	True	False	False	False	-1	None	[]	False
pg_read_server_files	False	True	False	False	False	-1	None	[]	False
pg_signal_backend	False	True	False	False	False	-1	None	[]	False
pg_stat_scan_tables	False	True	False	False	False	-1	None	[]	False
pg_write_all_data	False	True	False	False	False	-1	None	[]	False
pg_write_server_files	False	True	False	False	False	-1	None	[]	False

We can also run the psql command (or any other shell command) prepending a !, but note that we don’t get an interactive prompt, just the static output.

!psql --username=bob --db=postgres -h localhost

Password for user bob:

Since the password prompt is something that comes up quite often, it’s more practical to get used to run these commands on the Jupyter Lab’s terminal instead of the cells.

SQL Lab

Setting up the Postgres Connection

Contents

Setting up the Postgres Connection#

Magic Commands#

Postgres Metacommands#