Can someone please explain how I can get the tables in the current database?
I am using postgresql-8.4 psycopg2.
Add a comment
|
7 Answers
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)
1 Comment
hansrajswapnil
also check out this link kb.objectrocket.com/postgresql/…
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',)]
2 Comments
code-8
How do I get in an array formoat ?
GregarityNow
@code8888 table_names = cursor.fetchall() works for me
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
3 Comments
wordsforthewise
The line
"select exists(select relname from pg_class where relname='" + table_str + "')" works to check if a table exists
smci
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.
Freek
Missing the brackets after the print statement :)
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])
1 Comment
Rony Armon
Thanks, it worked for me and seems like the simplest and most straight forward solution proposed here
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.
Comments
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()
1 Comment
Chroma Funk
please don't write strings like that. f"" formatting in python serves a purpose
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()
1 Comment
d3corator
The OP asked for a way to fetch all the tables of a database, not to fetch records from a table