How can pandas.read_sql_query() query a TEMP table?
You can now pass SQLAlchemy connectable to pandas.read_sql
. From the docs:
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
...
con : SQLAlchemy connectable (engine/connection) or database string URI
or DBAPI2 connection (fallback mode)
Using SQLAlchemy makes it possible to use any DB supported by that > library. If a DBAPI2 object, only sqlite3 is supported.
So, this should work:
engine = sqlalchemy.create_engine('netezza://@mydsn')
connection = engine.connect()
sql = 'CREATE TEMP TABLE tmptable AS SELECT ...'
connection.execute(sql)
result = pd.read_sql('SELECT * FROM tmptable WHERE ...', con=connection)
All you need to do is add 'SET NOCOUNT ON' at the beginning of your query, that way pandas read_sql will read everything as one statement.
sSQL = '''SET NOCOUNT ON
CREATE TABLE ...... '''