sqlalchemy / pandas - How do I create an sqlalchemy `selectable` to pass to pd.read_sql?
<p>I'm completely new to sqlalchemy, and I've been trying to better understand how <code>pd.read_sql</code> can be used.</p>

<p>I've succesfully run the following:</p>

<pre><code>import sqlalchemy as sa
import pandas as pd
df = pd.DataFrame( index=range(10,30), data=np.random.rand(20, 10) )
eng = sa.create_engine('sqlite:///test.db')
df.reset_index().to_sql('test_table', eng, index=False)
df2 = pd.read_sql( 'test_table' , eng ) # Don't understand this function
</code></pre>

<p>I've figured out I can just load whatever I stored in the table by passing the table name as the first argument of <code>pd.read_sql</code> but what if I wanted to load only the elements for which column <code>index</code> is greater than some number.</p>

<p><strong>Question</strong></p>

<p>How do I create an sqlalchemy <code>selectable</code> for the first argument of  <code>pd.read_sql</code> to only load a subset of the database/table?</p>

<p><em>Comment:</em>
In this case I know that this is trivial to do after I've loaded df2 but if the size of the db is very large I'd like to avoid having to load the entire db in memory first.</p>

<p>Getting the table in 3 different ways. (Not sure what the differences are).</p>

<p>then using the <code>.select()</code> method on that table and the <code>.where</code> on that result gets what I want.</p>

<pre><code>import sqlalchemy as sa
eng = sa.create_engine('sqlite:///test.db')
# First way to load table
m = sa.MetaData()
m.reflect(bind=eng)
t1 = m.tables['test_table']

# Second way
m2 = sa.MetaData(bind=eng)
t2 = sa.Table('test_table', m2, autoload=True)

# Third way
t3 = sa.Table('test_table', sa.MetaData(), autoload_with=eng)

# I can then use either of the t's to do the following
df3 = pd.read_sql(t.select().where(t.c.index &gt; 15), eng)
</code></pre>

python pandas sqlalchemy