How to query a one to many/many to many relationship in Flask SQL Alchemy?
<p>These are two database models that are important in my problem.  </p>

<p><b>I have established a one to many relationship (a Conversation can have multiple Messages)  </b>
There is also a many to many relationship established between User and Conversation.  </p>

<p><b>After obtaining two User objects, say <i>user1</i> and <i>user2</i>, I need to find the conversation that contains both users, if it exists. After getting a conversation object, say <i>current_convo</i>, I also need to query all the messages in that conversation. How could these two queries be done?</b></p>

<pre><code>class Conversation(db.Model):
    __tablename__ = 'conversation'

    id = db.Column('id', db.Integer, primary_key=True)
    users = db.relationship("User", secondary=relationship_table)
    messages = db.relationship("Message", backref="conversation", lazy="dynamic")


class Message(db.Model):
    __tablename__ = 'message'

    id = db.Column('id', db.Integer, primary_key=True)
    message = db.Column('message', db.String)
    timestamp = db.Column('timestamp', db.String)
    sender = db.Column('sender', db.String)
    conversation_id = db.Column(db.Integer, db.ForeignKey('conversation.id'))

class User(db.Model, UserMixin):
    __tablename__ = 'user'

    id = db.Column('id', db.Integer, primary_key=True)
    username = db.Column('username', db.String(100), unique=True, index=True)
    password = db.Column('password', db.String(100))
    email = db.Column('email', db.String(100), unique=True, index=True)
    authenticated = db.Column('authenticated', db.Boolean, default=False)
</code></pre>

<p>This isn't a pure SQL query, but here's how I would accomplish what you're asking using Pandas.</p>

<pre><code>import pandas as pd
import sqlalchemy
import urllib

#setup vars and connection
server = 'myServer'
db = 'myDb'

user1 = 'someId1'
user2 = 'someId2'

#You'll have to maybe change this a little if you aren't using a trusted connection on SQL Server
connStr = 'DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes'
conn = sqlalchemy.create_engine(
            'mssql+pyodbc:///?odbc_connect=%s' % (urllib.quote_plus(connStr)))

#select all conversations that have one of the users
query = """select * from Conversation where users is in ('{0}','{1}')""".format(user1,user2)
conv_df = pd.read_sql(query,conn)

#unstack the users, so we can see which users are part of the same conversation
conv_users = conv_df.set_index(['id','users']).unstack().reset_index()

#filter conversations to those that have both users
conv_together = conv_users[(conv_users[user1].notnull()) &amp; (conv_users[user2].notnull())]

conv_list = conv_together['id'].tolist()
conv_str = "(" + ', '.join("'{0}'".format(w) for w in conv_list) +")"

#select all messages where the conv id matches your criteria (has both users)
query = """select * from Message where conversation_id is in {0}""".format(conv_str)

message_df = pd.read_sql(query,conn)
</code></pre>

<p>Its hard to show intermediate steps with no test data, so I can't run and QC this code, but hopefully it gives you the right idea.</p>

python flask sqlalchemy