How to automatically reflect database to sqlalchemy declarative?

Question

sqlautocode - has issues with many-to-many relations

sqlsoup - doesn't support relations

elixir - it's note auto-generate

Is there something else I could try?

Answer 1

In theory reflection in sqlalchemy should work for you. In this case I'm using an mssql database with two tables which have a simple Many-to-one relation:

"Tests" with fields:

• id
• testname
• author_id (foreign key to the Users table, Users.id field)

"Users" with fields:

• id
• fullname

So the following should reflect the database:

from sqlalchemy import * from sqlalchemy.orm import create_session from sqlalchemy.schema import Table, MetaData from sqlalchemy.ext.declarative import declarative_base  #Create and engine and get the metadata Base = declarative_base() engine = create_engine('put your database connect string here') metadata = MetaData(bind=engine)  #Reflect each database table we need to use, using metadata class Tests(Base):     __table__ = Table('Tests', metadata, autoload=True)  class Users(Base):     __table__ = Table('Users', metadata, autoload=True)  #Create a session to use the tables     session = create_session(bind=engine)  #Here I will just query some data using my foreign key relation,  as you would #normally do if you had created a declarative data mode. #Note that not all test records have an author so I need to accomodate for Null records testlist = session.query(Tests).all()      for test in testlist:     testauthor = session.query(Users).filter_by(id=test.author_id).first()       if not testauthor:         print "Test Name: {}, No author recorded".format(test.testname)     else:         print "Test Name: {}, Test Author: {}".format(test.testname, testauthor.fullname) 

So this appears to work with table relations. Although you still haven't given much detail to exactly what you are trying to do.