def get_foreign_keys(self, table, schema=None): cursor = self.execute_sql( f'PRAGMA "{schema or "main"}".foreign_key_list("{table}")') return [ ForeignKeyMetadata(row[3], row[2], row[4], table) for row in cursor.fetchall() ]
def stash_foreign_keys(self): where, params = self.where_table_parm('kcu.table_schema', 'kcu.table_name') query = ( "SELECT DISTINCT " "kcu.table_schema, kcu.table_name, kcu.column_name, ccu.table_schema, ccu.table_name, ccu.column_name " "FROM information_schema.table_constraints AS tc " "JOIN information_schema.key_column_usage AS kcu " "ON tc.constraint_name = kcu.constraint_name " "AND tc.constraint_schema = kcu.constraint_schema " "JOIN information_schema.constraint_column_usage AS ccu " "ON ccu.constraint_name = tc.constraint_name " "AND ccu.constraint_schema = tc.constraint_schema " f"WHERE tc.constraint_type = 'FOREIGN KEY' AND {where}" ) cursor = self.db.execute_sql(query, params) fetch = cursor.fetchall() foreign_keys = {} for schema, g1 in groupby(fetch, key=itemgetter(0)): foreign_keys[schema] = {} for table, g2 in groupby(g1, key=itemgetter(1)): foreign_keys[schema][table] = [ ForeignKeyMetadata(fc, pt, pc, ft) for (fs, ft, fc, ps, pt, pc) in list(g2) ] return foreign_keys
async def get_foreign_keys(self, table, schema=None): query = """ SELECT column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = %s AND table_schema = DATABASE() AND referenced_table_name IS NOT NULL AND referenced_column_name IS NOT NULL""" cursor = await self.execute_sql(query, (table, )) return [ ForeignKeyMetadata(column, dest_table, dest_column, table) for column, dest_table, dest_column in cursor.fetchall() ]
def get_foreign_keys(self, table, schema=None): res = self.execute_sql( '''SELECT COL_NAME(fc.parent_object_id, fc.parent_column_id), OBJECT_NAME (f.referenced_object_id), COL_NAME(fc.referenced_object_id, fc.referenced_column_id) FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID=fc.constraint_object_id WHERE OBJECT_NAME(f.parent_object_id)=?''', (table, )) return [ ForeignKeyMetadata(col_name, ref_table, ref_col, table) for col_name, ref_table, ref_col in res.fetchall() ]
def get_foreign_keys(self, table, schema='public'): sql = """ SELECT kcu.column_name, ccu.table_name, ccu.column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON (tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema) JOIN information_schema.constraint_column_usage AS ccu ON (ccu.constraint_name = tc.constraint_name AND ccu.constraint_schema = tc.constraint_schema) WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = %s AND tc.table_schema = %s""" cursor = yield self.execute_sql(sql, (table, schema)) raise gen.Return([ForeignKeyMetadata(row[0], row[1], row[2], table) for row in cursor.fetchall()])
def get_foreign_keys(self, table, schema='dbo'): return [ ForeignKeyMetadata(name, dest_tab, dest_col, table) for name, *_, dest_tab, dest_col, con in self.md_cache( table, schema) if con == 'FOREIGN KEY' ]