Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can SQLAlchemy automatically create relationships from a database schema?

Starting from an existing (SQLite) database with foreign keys, can SQLAlchemy automatically build relationships?

SQLAlchemy classes are automatically created via __table_args__ = {'autoload': True}.

The goal would be to easily access data from related tables without having to add all the relationships one by one by hand (i.e. without using sqlalchemy.orm.relationship() and sqlalchemy.orm.backref).

like image 430
Eric O Lebigot Avatar asked Jan 18 '13 11:01

Eric O Lebigot


People also ask

Is SQLAlchemy relational database?

ORM, which stands for Object Relational Mapper, is the specialization of the Data Mapper design pattern that addresses relational databases like MySQL, Oracle, and PostgreSQL.

What does SQLAlchemy relationship do?

The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.

How do you create a relationship in a SQLAlchemy flask?

You then create a Flask application instance called app , which you use to configure two Flask-SQLAlchemy configuration keys: SQLALCHEMY_DATABASE_URI : The database URI to specify the database you want to establish a connection with. In this case, the URI follows the format sqlite:/// path/to/database. db .


2 Answers

[Update] As of SQLAlchemy 0.9.1 there is Automap extension for doing that.

For SQLAlchemy < 0.9.0 it is possible to use sqlalchemy reflection.

SQLAlchemy reflection loads foreign/primary keys relations between tables. But doesn't create relations between mapped classes. Actually reflection doesn't create mapped classes for you - you have to specify mapped class name.

Actually I think that reflection support for loading foreign keys is a great helper and time saving tool. Using it you can build a query using joins without need to specify which columns to use for a join.

from sqlalchemy import *
from sqlalchemy import create_engine, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship



metadata = MetaData()
Base = declarative_base()
Base.metadata = metadata

db = create_engine('<db connection URL>',echo=False)
metadata.reflect(bind=db)

cause_code_table = metadata.tables['cause_code']
ndticket_table = metadata.tables['ndticket']

sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

q = session.query(ndticket_table,cause_code_table).join(cause_code_table)
for r in q.limit(10):
    print r

Also when I was using reflection to run queries to existing database - I had to define only mapped classes names, table bindings, relations, BUT there were no need to define table columns for these relations.

class CauseCode(Base):
    __tablename__ = "cause_code"

class NDTicket(Base):
    __tablename__ = "ndticket"
    cause_code = relationship("CauseCode", backref = "ndticket")


q = session.query(NDTicket)
for r in q.limit(10):
    print r.ticket_id, r.cause_code.cause_code

Overall SQLAlchemy reflection is already powerful tool and save me time, so adding relations manually is a small overhead for me.

If I would have to develop functionality that will add relations between mapped objects using existing foreign keys, I would start from using reflection with inspector. Using get_foreign_keys() method gives all information required to build relations - referred table name, referred column name and column name in target table. And would use this information for adding property with relationship into mapped class.

insp = reflection.Inspector.from_engine(db)
print insp.get_table_names()
print insp.get_foreign_keys(NDTicket.__tablename__)
>>>[{'referred_table': u'cause_code', 'referred_columns': [u'cause_code'], 'referred_schema': None, 'name': u'SYS_C00135367', 'constrained_columns': [u'cause_code_id']}]
like image 61
vvladymyrov Avatar answered Sep 26 '22 02:09

vvladymyrov


As of SQLAlchemy 0.9.1 the (for now experimental) Automap extension would seem to do just that: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html

like image 27
Gord Stephen Avatar answered Sep 27 '22 02:09

Gord Stephen