Reflected Table FK

[Matthew Graham]

Hi I am new to SQLAlchemy,

I am trying to extract from reflected tables:

1. the column name of the current table
2. the referred table name and
3. the column name of the referred table

now I can manage to do this using (and for sake only using first forein key)

    metadata = sqlalchemy.MetaData()
    metadata.reflect(engine)
    for table in metadata.tables.values():
        print(table.name) --- Current table name
        l = list(table.foreign_keys)
        if len(l) > 0:
            print(l[0].column) --- Referred table column name (but is prepended with table name and a .)
        for fk in table.foreign_key_constraints:
            print(fk.column_keys[0]) --- current table column name but
            print(fk.referred_table) --- Referred table name

There must surely be a cleaner way to do this preferably:

• not needing to convert table.foreign_keys to a list and then check list length
• getting referred table column name without the foreign table prepended as would rather not have to add in regex replacements
• get the current table column name without having to index with column keys[0]

Thanks you

[Mike Bayer]

hey there -

no need to deal with MetaData, Table, etc. just to get information about tables.  Have a look at the inspector interface and get the information directly:

https://docs.sqlalchemy.org/en/13/core/reflection.html#fine-grained-reflection-with-inspector

Table, MetaData etc. objects are only useful when you want to use them to compose SQL queries.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/093cb4b3-381a-4476-aff9-02471d61efa1n%40googlegroups.com.

[Mike Bayer]

regarding "lists of columns", FOREIGN KEY constraints refer to sets of columns between local table and referred table, not just a single pair of columns.   That a typical foreign key constraint might only link a single column in the local and referred tables is a special case.    an application that is looking at foreign key constraints and the columns they represent necessarily needs to look at local and referred columns as variable length sets. 

On Thu, Dec 31, 2020, at 4:05 AM, Matthew Graham wrote:

[Matthew Graham]

Are you saying with the multiple foreign keys that for each key, multiple columns can be used? I am aware that if you had table A and it had columns B_id and C_id that B_id may be a fk to B and C_id may be a reign key to C, but are you essentially saying there is an equivalence to a "composite" foreign key so that you can have a foreign key on columns B_id1 and B_id2 in A to refer to, only in conjunction, B?

Also thanks for referring me to inspector, I will give that a go

[Mike Bayer]

A particular column may be constrained by more than one foreign key constraint (although this is very uncommon), and a particular foreign key constraint may be "composite" in that it refers to multiple columns.  All primary and foreign key constructs in SQLAlchemy are inherently composite.    having APIs that refer to single-column keys, like "table.primary_key_column" and stuff like that, encourages applications to hardcode themselves to be non-composite, and there's really no point in doing so.    If I'm writing a program that looks at primary and foreign key constraints in an abstract sense I would want it to assume composite in all cases.

On Thu, Dec 31, 2020, at 3:53 PM, Matthew Graham wrote:

Are you saying with the multiple foreign keys that for each key, multiple columns can be used? I am aware that if you had table A and it had columns B_id and C_id that B_id may be a fk to B and C_id may be a reign key to C, but are you essentially saying there is an equivalence to a "composite" foreign key so that you can have a foreign key on columns B_id1 and B_id2 in A to refer to, only in conjunction, B?

Also thanks for referring me to inspector, I will give that a go

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAP-Gp5PoX7vvcfOUN2j3RS1LM8Cv300YmT7Ne3e1YzXc3J3Gkg%40mail.gmail.com.

[Matthew Graham]

It makes sense that as foreign keys can be composite, that all foreign keys would be considered composite, thank you, I just had no idea that foreign keys could be composite in the same way that primary keys could be. Thanks again

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/12781196-61f2-4ac7-9df3-762982e5cac4%40www.fastmail.com.