How to do a SELECT * following all foreign keys

Question

I want to do a SELECT statement that will get all the data in one table + follow all the foreign keys from that table with a LEFT OUTER JOIN. For example:

`orderitem`
id
name
title_id

`title`
id
name

In the above example, I would be able to use the statement:

SELECT * FROM orderitem LEFT OUTER JOIN title on orderitem.title_id=title.id

Is there a way that I could do this not knowing the table structure? That is, to have a function like the following:

def get_select_statement(table)
    ???

get_select_statement(orderitem)
==> "SELECT * FROM orderitem LEFT OUTER JOIN title on orderitem.title_id=title.id"

How would this be done?

To clarify this question, I think I'm looking for the following information from this function:

What are all the column names in the given table?
What tables do they reference in a ForeignKey relationship and what is the relationship to be able to join?

In addition, note that not all orderitems will have a title, so doing any sort of INNER JOIN would delete data.

very limited information to answer this. But your query should be inner join no left join — Juan Carlos Oropeza, Oct 23 '15 at 21:57
What if you did a `USING(name, id)` clause instead of the `ON` statement? — McGlothlin, Oct 23 '15 at 21:57
Possible duplicate of [MySQL: Get column name or alias from query](http://stackoverflow.com/questions/5010042/mysql-get-column-name-or-alias-from-query) — Alex, Oct 23 '15 at 22:05
You can query `INFORMATION_SCHEMA.TABLE_CONSTRAINTS` and `INFORMATION_SCHEMA.KEY_COLUMN_USAGE` to get the list of foreign keys, and then generate dynamic SQL that uses it in a join. — Barmar, Oct 23 '15 at 22:25

score 1 · Accepted Answer · answered Oct 24 '15 at 02:43

In MySQLDB you could retrieve column names by using describe statement:

DESCRIBE table_name;

And all info about foreign keys:

select *
from information_schema.KEY_COLUMN_USAGE
where TABLE_SCHEMA = "schema_name" 
      and TABLE_NAME="table_name" 
      and REFERENCED_TABLE_NAME IS NOT NULL

To evaluate this query and load the result in python you could use SQLAlchemy package, for example.

engine = sqlalchemy.create_engine("mysqldb://user:password@host/db")
res = engine.execute("DESCRIBE table_name;")
columns = [row["Field"] for row in res]

res = engine.execute("{}".format(query_for_foreign_keys))
foreign_keys = [row["COLUMN_NAME"] for row in res]
referenced_column_names = [row["REFERENCED_COLUMN_NAME"] for row in res]
referenced_table_names = [row["REFERENCED_TABLE_NAME"] for row in res]

Then you could generate the query using all the data above

How to do a SELECT * following all foreign keys

1 Answers1