Metacommands

In Postgres can use psql metacommands also known as slash or backslash commands. Let’s create a connection to the database we created in the last section (dvdrental):

import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/dvdrental')
engine.execution_options(isolation_level="AUTOCOMMIT")
%load_ext sql
%sql $engine.url

'Connected: bob@dvdrental'

Listing Databases in the Server

To list the databases in a server, we could use \list [meta-command][0]:

%sql \list

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

Name	Owner	Encoding	Collate	Ctype	Access privileges
bob	bob	UTF8	en_US.utf8	en_US.utf8	None
dvdrental	bob	UTF8	en_US.utf8	en_US.utf8	None
postgres	bob	UTF8	en_US.utf8	en_US.utf8	None
template0	bob	UTF8	en_US.utf8	en_US.utf8	=c/bob bob=CTc/bob
template1	bob	UTF8	en_US.utf8	en_US.utf8	=c/bob bob=CTc/bob
test_joins	bob	UTF8	en_US.utf8	en_US.utf8	None

Listing Tables in a Database

Once we’re connected to a database, we may want to list the tables, which can be easily done using the \dt meta-command:

%sql \dt

 * postgresql://bob:***@localhost:5432/dvdrental
16 rows affected.

Schema	Name	Type	Owner
public	actor	table	bob
public	address	table	bob
public	category	table	bob
public	city	table	bob
public	country	table	bob
public	customer	table	bob
public	film	table	bob
public	film_actor	table	bob
public	film_category	table	bob
public	inventory	table	bob
public	language	table	bob
public	payment	table	bob
public	rental	table	bob
public	staff	table	bob
public	store	table	bob
public	tablea	table	bob

We could also use a proper SQL query, but as you can see, it’s way more verbose:

%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'information_schema' 
  AND schemaname != 'pg_catalog';

 * postgresql://bob:***@localhost:5432/dvdrental
16 rows affected.

schemaname	tablename	tableowner	tablespace	hasindexes	hasrules	hastriggers	rowsecurity
public	rental	bob	None	True	False	True	False
public	staff	bob	None	True	False	True	False
public	payment	bob	None	True	False	True	False
public	tablea	bob	None	False	False	False	False
public	actor	bob	None	True	False	True	False
public	film	bob	None	True	False	True	False
public	address	bob	None	True	False	True	False
public	category	bob	None	True	False	True	False
public	city	bob	None	True	False	True	False
public	country	bob	None	True	False	True	False
public	customer	bob	None	True	False	True	False
public	film_actor	bob	None	True	False	True	False
public	film_category	bob	None	True	False	True	False
public	inventory	bob	None	True	False	True	False
public	language	bob	None	True	False	True	False
public	store	bob	None	True	False	True	False

Meta-commands are psql shortcuts

We’ve shown a couple of meta-commands exampls, but you may be wondering where and how can you run them (outside a Jupyter notebook). For example, let’s log in into the PostgreSQL server we have running locally in our Docker container, using the credentials we set in our Docker compose file:

psql -U bob -d postgres -h 127.0.0.1                                     
Password for user bob: 
psql (12.9, server 15.2 (Debian 15.2-1.pgdg110+1))
WARNING: psql major version 12, server major version 15.
         Some psql features might not work.
Type "help" for help.

postgres=#

In the example above, we’re connecting to a PostgreSQL server running locally using the flags:

• -U to specify the username, bob.

• -d to specify the database.

• -h to connect to the server running at localhost.

By the way, you can exit the connection with the \q meta-command ;-)

Something that has me scratching my head is that even though the Docker service is running with the name postgres (which I use to set up a server in pgAdmin), I need to use localhost to connect to it via psql (and Alchemy too).