如何使用 Psycopg2在 postgres 中获得表格？

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

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',)]

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)

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()

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.

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()