How to get tables from a database in cockroachdb with psycopg2?

Question

In cockroachdb if I run SHOW DATABASES; from within the SQL terminal I get:

    database_name   | owner | primary_region | regions | survival_goal
--------------------+-------+----------------+---------+----------------
  _a66df261120b6c23 | root  | NULL           | {}      | NULL
  defaultdb         | root  | NULL           | {}      | NULL
  postgres          | root  | NULL           | {}      | NULL
  sammy             | root  | NULL           | {}      | NULL
  system            | node  | NULL           | {}      | NULL
  test123           | root  | NULL           | {}      | NULL
  test3             | root  | NULL           | {}      | NULL
  test4             | root  | NULL           | {}      | NULL
  test5             | root  | NULL           | {}      | NULL
  test9             | root  | NULL           | {}      | NULL
(10 rows)

if I now run SHOW TABLES FROM _a66df261120b6c23, I get:

  schema_name |    table_name     | type  | owner | estimated_row_count | locality
--------------+-------------------+-------+-------+---------------------+-----------
  public      | __Auth            | table | root  |                   0 | NULL
  public      | tabDefaultValue   | table | root  |                   0 | NULL
  public      | tabDocType Action | table | root  |                   0 | NULL
  public      | tabDocType Link   | table | root  |                   0 | NULL
  public      | tabFile           | table | root  |                   0 | NULL
  public      | tabSeries         | table | root  |                   0 | NULL
  public      | tabSessions       | table | root  |                   0 | NULL
  public      | tabSingles        | table | root  |                   0 | NULL
  public      | tabdocfield       | table | root  |                   0 | NULL
  public      | tabdocperm        | table | root  |                   0 | NULL
  public      | tabdoctype        | table | root  |                   0 | NULL

Problem is that when I try to execute the same query,ie: SHOW TABLES FROM _a66df261120b6c23, from psycopg2 I get the error:

test1 = cur.execute("show tables from _a66df261120b6c23;")
psycopg2.errors.InvalidCatalogName: database "root" does not exist

Below is the python script:

with rdb_conn.cursor() as cur:
    # cur.execute('CREATE database test9;')
    test1 = cur.execute("show tables from _a66df261120b6c23;")
    # print("test1: ", test1)
    # result = cur.execute("CREATE DATABASE test9;")
rdb_conn.commit()

I should mention that creating a database is not a problem, for example, CREATE DATABASE test9 did not have any issues. So my question is how do I get the tables in a given database via psycopg2 in cockorachdb?

cursor.execute("""SELECT table_name FROM information_schema.tables
       WHERE table_schema = 'public'""")

does not work for me, none of the tables in _a66df261120b6c23 show up

Below is the list of users:

      username      | options | member_of
--------------------+---------+------------
  _a66df261120b6c23 |         | {}
  admin             |         | {}
  doug              |         | {admin}
  postgres          |         | {}
  root              |         | {admin}
  sammy             |         | {admin}
  sammy2            |         | {admin}
  sammy3            |         | {}
  test_user         |         | {}

Answer 1

Figured it out, problem is that psycog2's execute does not return anything, so instead I should also use fetchall.

cur.execute("show tables from _a66df261120b6c23")
result = cur.fetchall()