<p dir="auto">Currently <code class="notranslate">pd.read_sql_query()</code> accepts an <code class="notranslate">sqlalchemy.engine.base.Engine</code> instance as the <code class="notranslate">con</code> parameter.  Using that, it presumably creates a new <code class="notranslate">sqlalchemy.engine.base.Connection</code> to connect to the DB server and issue the query.<br>
However, as most (all?) DB servers associate a session with a connection, this precludes issuing queries for an existing session/connection.</p>
<p dir="auto">For example, it is common to break up large queries into a series of steps that create TEMPorary intermediate result tables, possibly joining some into further intermediate tables, and then querying for the final result.  The lifetime and scope of a TEMP table is that of the session/connection.</p>
<p dir="auto">Hence, something like:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="con = engine.connect()
con.execute('CREATE TEMP TABLE tmptable ...')
result = pd.read_sql_query('SELECT * FROM tmptable ..', engine)"><pre class="notranslate"><span class="pl-s1">con</span> <span class="pl-c1">=</span> <span class="pl-s1">engine</span>.<span class="pl-en">connect</span>()
<span class="pl-s1">con</span>.<span class="pl-en">execute</span>(<span class="pl-s">'CREATE TEMP TABLE tmptable ...'</span>)
<span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-en">read_sql_query</span>(<span class="pl-s">'SELECT * FROM tmptable ..'</span>, <span class="pl-s1">engine</span>)</pre></div>
<p dir="auto">will fail as the tmptable isn't available to the connection/session created by read_sql_query().</p>
<p dir="auto">Concatenating the SQL statements into one query string with ';' statement seperator, or using the SQLAlchemy <code class="notranslate">text</code> class to attempt to create the table and query it in one SQL <em>query</em> also fails as leads to SQLAlchemy claiming the query doesn't return results (perhaps because it doesn't being with "SELECT"?)</p>
<p dir="auto">This, and other similar use-cases involving multiple queries within a single session/connection context, could be easily supported by allowing a <code class="notranslate">sqlalchemy.engine.base.Connection</code> to be passed to the <code class="notranslate">read_sql_query()</code> and similar functions, whereby they would use the supplied connection rather than creating a new one.  An added benefit would be the reduction in overhead of repeatedly creating and tearing down connections to the DB server and also allowing users to implement connection pooling when necessary.</p>
<p dir="auto">(Issue created at suggestion of <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/joris/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/joris">@joris</a> in this <a href="http://stackoverflow.com/questions/26286615/how-can-pandas-read-sql-query-query-a-temp-table" rel="nofollow">StackOverflow question</a> )</p>
<p dir="auto">related <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="36794262" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/7615" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/7615/hovercard" href="https://github.com/pandas-dev/pandas/issues/7615">#7615</a></p>
<p dir="auto">Bit of a UI problem here (although it's behaving as the docstring says it does, so it doesn't quite qualify as a bug):</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; df = pd.DataFrame({&quot;A&quot;: [1,2,3], &quot;B&quot;: [4,5,6]})
&gt;&gt;&gt; df.to_csv(&quot;tmp.csv&quot;, sep=&quot;;&quot;)
&gt;&gt;&gt; !cat tmp.csv
;A;B
0;1;4
1;2;5
2;3;6
&gt;&gt;&gt; df.to_csv(&quot;tmp.csv&quot;, delimiter=&quot;;&quot;)
&gt;&gt;&gt; !cat tmp.csv
,A,B
0,1,4
1,2,5
2,3,6"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; df = pd.DataFrame({"A": [1,2,3], "B": [4,5,6]})
&gt;&gt;&gt; df.to_csv("tmp.csv", sep=";")
&gt;&gt;&gt; !cat tmp.csv
;A;B
0;1;4
1;2;5
2;3;6
&gt;&gt;&gt; df.to_csv("tmp.csv", delimiter=";")
&gt;&gt;&gt; !cat tmp.csv
,A,B
0,1,4
1,2,5
2,3,6
</code></pre></div>
<p dir="auto"><code class="notranslate">read_csv</code> accepts both <code class="notranslate">sep</code> and <code class="notranslate">delimiter</code> but <code class="notranslate">to_csv</code> silently ignores <code class="notranslate">delimiter</code>.  Someone was recently tripped up by this on SO.  I'm fine with either teaching <code class="notranslate">to_csv</code> to behave the same way <code class="notranslate">read_csv</code> does or, alternatively, raising if <code class="notranslate">delimiter</code> is found as a keyword.</p>
<p dir="auto">That is, I'm less bothered by the inconsistency than the silent unexpected behaviour.</p>