def load_columns(conn, meta: Meta):
c = conn.cursor()
query = """SELECT distinct C.table_name, C.column_name, case B.constraint_type
when 'PRIMARY KEY' then '*'
when 'FORIEGN KEY' then ''
end as is_pk,
C.data_type from information_schema.columns C
FULL OUTER JOIN ( select distinct K.column_name, T.constraint_type from information_schema.key_column_usage as K, information_schema.table_constraints as T where K.constraint_name = T.constraint_name and T.constraint_type='PRIMARY KEY'
) as B ON B.column_name=C.column_name
where table_schema='public'
order by table_name;
"""
(header, rows) = dbexec.exec_query(conn, query)
for row in rows:
if row[0] in meta.tables:
# m.tables[tbl.name] = tbl
meta.tables[row[0]].columns.append(
Column(name=row[1],
table=row[0],
data_type=row[3],
is_pk=row[2]))
else:
# Table t
t = Table(name=row[0])
t.columns.append((Column(name=row[1],
table=row[0],
data_type=row[3],
is_pk=row[2])))
meta.tables[row[0]] = t
# TODO: create Table and Column objects and attach to meta.
c.close()
def load_columns(conn, meta: Meta):
c = conn.cursor()
# query = """select distinct table_name,column_name,case when constraint_type='PRIMARY KEY' then '*' else '' end as is_pk,data_type from information_schema.key_column_usage join information_schema.table_constraints using (table_name,constraint_name) join information_schema.columns using (table_name,column_name) order by table_name,is_pk desc,column_name;"""
query = """with temp as (select K.table_name, K.column_name from information_schema.table_constraints as T, information_schema.key_column_usage as K where T.constraint_type = 'PRIMARY KEY' and T.constraint_name = K.constraint_name), columns as (SELECT TABLE_NAME, COLUMN_NAME, data_type FROM information_schema.COLUMNS where table_schema = 'public') select columns.table_name, columns.column_name, case when columns.column_name = temp.column_name then '*' else '' end as is_pk, columns.data_type from columns left join temp using (table_name,column_name) order by table_name,column_name;"""
(header, rows) = dbexec.exec_query(conn, query)
col_list = []
prev_tbl_name = ""
tempc = 0
table_list = {}
for row in rows:
if row[0] in table_list:
if row[2] == '*':
table_list[row[0]].append([row[1],row[3],True])
else:
table_list[row[0]].append([row[1],row[3],False])
else:
if row[2] == '*':
table_list[row[0]] = [[row[1],row[3],True]]
else:
table_list[row[0]] = [[row[1],row[3],False]]
for table in table_list:
# tbl =
# m = Meta()
tbl = Table(name=table)
tbl.columns = []
for col in table_list[table]:
tbl.columns.append(Column(name=col[0], table=tbl, data_type=col[1], is_pk=col[2]))
meta.tables[table] = tbl
# TODO: create Table and Column objects and attach to meta.
c.close()
def load_relationships(conn, meta, constraints):
c = conn.cursor()
# TODO : query referential_constraints table, which maps constraint in one table
# : to unique constraint in another table
# TODO : for each row create meta.tables[from_table].refersTo += meta.tables[to_table]
query = """select constraint_name, unique_constraint_name from information_schema.referential_constraints ;"""
(header, rows) = dbexec.exec_query(conn, query)
for row in rows:
meta.tables[constraints[row[0]]].refersTo.append(
meta.tables[constraints[row[1]]])
c.close()
def load_constraints(conn):
table_constraints = dict(
) # Map of constraint name -> containing table name
c = conn.cursor()
query = """select constraint_name, table_name from information_schema.table_constraints where table_schema='public';"""
(header, rows) = dbexec.exec_query(conn, query)
# TODO: Load table_constraints table for primary and foreign keys, and record
# the constraint_table and the containing table name.
for row in rows:
table_constraints[row[0]] = row[1]
c.close()
return table_constraints
def load_table_constraints(conn):
table_constraints = dict() # Map of constraint name -> containing table name
c = conn.cursor()
# query = """select A.table_name as table1,B.table_name as table2 from table_constraints as A,table_constraints as B,referential_constraints as R where A.constraint_name=R.constraint_name and B.constraint_name=R.unique_constraint_name;"""
query = """select constraint_name, table_name from information_schema.table_constraints where constraint_type in ('PRIMARY KEY','FOREIGN KEY') and table_schema='public';"""
(header, rows) = dbexec.exec_query(conn, query)
for row in rows:
table_constraints[row[0]] = row[1]
# # TODO: Load table_constraints table for primary and foreign keys, and record
# # the constraint_table and the containing table name.
# c.close()
return table_constraints
def check_record_in_db():
try:
err = None
points = 0
conn = dbexec.connect()
(header, rows) = dbexec.exec_query(conn, "select count(*) from student where id = '77777'")
if len(rows) == 1 and rows[0][0] == 1:
c = conn.cursor()
c.execute("delete from student where id = '77777'")
c.close()
points = 5
else:
err = "Could not find student id '77777' in the database\n" + str(rows)
conn.commit()
conn.close()
return (points, err)
except Exception as e:
# Unrecoverable error.
traceback.print_exc()
conn.close()
sys.exit(1)