How do I get tables in postgres using psycopg2?

Question

Can someone please explain how I can get the tables in the current database?

I am using postgresql-8.4 psycopg2.

Tideland

Answer 1

This did the trick for me:

cursor.execute("""SELECT table_name FROM information_schema.tables
       WHERE table_schema = 'public'""")
for table in cursor.fetchall():
    print(table)

Obadiah

also check out this link kb.objectrocket.com/postgresql/… – Apathetic

Answer 2

pg_class stores all the required information.

executing the below query will return user defined tables as a tuple in a list

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
print cursor.fetchall()

output:

[('table1',), ('table2',), ('table3',)]

Slocum

How do I get in an array formoat ? – Chondro

@code8888 table_names = cursor.fetchall() works for me – Euryale

Answer 3

The question is about using python's psycopg2 to do things with postgres. Here are two handy functions:

def table_exists(con, table_str):

    exists = False
    try:
        cur = con.cursor()
        cur.execute("select exists(select relname from pg_class where relname='" + table_str + "')")
        exists = cur.fetchone()[0]
        print exists
        cur.close()
    except psycopg2.Error as e:
        print e
    return exists

def get_table_col_names(con, table_str):

    col_names = []
    try:
        cur = con.cursor()
        cur.execute("select * from " + table_str + " LIMIT 0")
        for desc in cur.description:
            col_names.append(desc[0])        
        cur.close()
    except psycopg2.Error as e:
        print e

    return col_names

Cursed

The line "select exists(select relname from pg_class where relname='" + table_str + "')" works to check if a table exists – Hyps

Your return type exists from table_exists seems a bit confused: it returns False if the table doesn't exist, not sure if you get a falsey value if table does exist but is empty, and the first row of table if it exists and is non-empty. It might be better to default to None rather than False. – Sandon

Missing the brackets after the print statement :) – Sequester

Answer 4

Here's a Python3 snippet that includes connect() parameters as well as generate a Python list() for output:

conn = psycopg2.connect(host='localhost', dbname='mySchema',
                        user='myUserName', password='myPassword')
cursor = conn.cursor()

cursor.execute("""SELECT relname FROM pg_class WHERE relkind='r'
                  AND relname !~ '^(pg_|sql_)';""") # "rel" is short for relation.

tables = [i[0] for i in cursor.fetchall()] # A list() of tables.

Subclass

Answer 5

Try this after opening cursor

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

# Fetch all the table names
table_names = cur.fetchall()

# Print the table names
for table_name in table_names:
    print(table_name[0])

Inoculation

Thanks, it worked for me and seems like the simplest and most straight forward solution proposed here – Apparently

Answer 6

Although it has been answered by Kalu, but the query mentioned returns tables + views from postgres database. If you need only tables and not views then you can include table_type in your query like-

        s = "SELECT"
        s += " table_schema"
        s += ", table_name"
        s += " FROM information_schema.tables"
        s += " WHERE"
        s += " ("
        s += " table_schema = '"+SCHEMA+"'"
        s += " AND table_type = 'BASE TABLE'"
        s += " )"
        s += " ORDER BY table_schema, table_name;"

        db_cursor.execute(s)
        list_tables = db_cursor.fetchall()

Latishalatitude

please don't write strings like that. f"" formatting in python serves a purpose – Harhay

Answer 7

you can use this code for python 3

import psycopg2

conn=psycopg2.connect(database="your_database",user="postgres", password="",
host="127.0.0.1", port="5432")

cur = conn.cursor()

cur.execute("select * from your_table")
rows = cur.fetchall()
conn.close()

Appointed

The OP asked for a way to fetch all the tables of a database, not to fetch records from a table – Ess