93

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

I am using postgresql-8.4 psycopg2.

Kurt Peek
  • 52,165
  • 91
  • 301
  • 526
user1395784
  • 931
  • 1
  • 6
  • 3

7 Answers7

125

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)
kalu
  • 2,594
  • 1
  • 21
  • 22
51

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',)]
Sravan
  • 789
  • 6
  • 5
9

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
koopajah
  • 23,792
  • 9
  • 78
  • 104
rirwin
  • 199
  • 2
  • 4
  • The line `"select exists(select relname from pg_class where relname='" + table_str + "')"` works to check if a table exists – wordsforthewise Aug 15 '18 at 15:26
  • 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`. – smci Apr 19 '21 at 18:54
  • Missing the brackets after the print statement :) – Freek Oct 28 '21 at 14:22
2

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.
NYCeyes
  • 5,215
  • 6
  • 57
  • 64
1

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()
Vijay Rathore
  • 593
  • 8
  • 16
1

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])
AnuSub
  • 11
  • 2
-9

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()
Iman Zhylkaidarov
  • 21
  • 1