def list_columns(self, table_name):
columns_list = []
cur = self.db.cursor()
# get lists of column_name, type, charLen for CHAR, precision for numeric entries, nullable
query_string = "SELECT column_name, data_type, character_maximum_length, numeric_precision, \
is_nullable from information_schema.columns WHERE table_name = %s;"
cur.execute(query_string, (table_name,))
columns_tuple = cur.fetchall()
# http://stackoverflow.com/questions/12379221
cur.execute("SELECT k.COLUMN_NAME \
FROM information_schema.table_constraints t \
INNER JOIN information_schema.key_column_usage k \
USING ( constraint_name, table_schema, table_name ) \
WHERE t.constraint_type = 'PRIMARY KEY' \
AND t.table_name = %s;", (table_name,))
# fetch list of primary keys
columns_prim_tuple = cur.fetchall()
#http://www.tocker.ca/2013/05/02/fastest-way-to-count-rows-in-a-table.html
#cur.execute("SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s", (table_name,))
cur.execute("SELECT COUNT(*) FROM {0}".format (table_name))
row_count = cur.fetchone()
cur.close()
# traverese tuple of tuples to list of strings
# http://stackoverflow.com/questions/1663807
for col, col_prim in itertools.izip_longest(columns_tuple, columns_prim_tuple):
# create Column object
column = Column()
# convert tuple item into string
col = list(col)
col[0] = col[0].strip("(),'")
# col_prim is None, do not append assign None
if col_prim:
col_prim = list(col_prim)
col_prim[0] = col_prim[0].strip("(),'")
column.primary_key = col_prim[0]
else:
column.primary_key = ''
# populate column object
column.name = col[0]
column.type = col[1]
column.charLen = col[2]
column.precision = col[3]
column.nullable = "NULL" if col[4] == "YES" else "NOT NULL"
columns_list.append(column)
return columns_list, row_count[0]
def list_columns(self, table_name):
columns_list = []
cur = self.conn.cursor()
# get lists of column_name, type, charLen for CHAR, precision for numeric entries, nullable
# http://stackoverflow.com/questions/2146705
cur.execute("SELECT column_name, data_type, character_maximum_length, numeric_precision, is_nullable from information_schema.columns \
WHERE table_name = %s;", (table_name,))
columns_tuple = cur.fetchall()
cur.execute("SELECT a.attname FROM pg_index i \
JOIN pg_attribute a ON a.attrelid = i.indrelid \
AND a.attnum = ANY(i.indkey) \
WHERE i.indrelid = %s::regclass \
AND i.indisprimary;", (table_name,))
columns_prim_tuple = cur.fetchall()
#https://wiki.postgresql.org/wiki/Count_estimate
#cur.execute("SELECT reltuples FROM pg_class WHERE oid = %s::regclass; ", (table_name,))
cur.execute("SELECT COUNT(*) FROM %s;", (AsIs(table_name),))
row_count = cur.fetchone()
cur.close()
# traverese tuple of tuples to list of strings
# http://stackoverflow.com/questions/1663807
for col, col_prim in itertools.izip_longest(columns_tuple, columns_prim_tuple):
# create Column object
column = Column()
col = list(col)
col[0] = col[0].strip("(),'")
# col_prim is None, do not append assign None
if col_prim:
col_prim = list(col_prim)
col_prim[0] = col_prim[0].strip("(),'")
column.primary_key = col_prim[0]
else:
column.primary_key = ''
column.name = col[0]
column.type = col[1]
column.charLen = col[2]
column.precision = col[3]
column.nullable = "NULL" if col[4] == "YES" else "NOT NULL"
columns_list.append(column)
#row_count = list(row_count_tuple)
return columns_list, row_count[0]