Пример #1
0
	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]
Пример #2
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]