Improving database query speed with Python
<p>Edit - I am using Windows 10</p>

<p>Is there a faster alternative to pd._read_sql_query for a MS SQL database?</p>

<p>I was using pandas to read the data and add some columns and calculations on the data. I have cut out most of the alterations now and I am basically just reading (1-2 million rows per day at a time; my query is to read all of the data from the previous date) the data and saving it to a local database (Postgres). </p>

<p>The server I am connecting to is across the world and I have no privileges at all other than to query for the data. I want the solution to remain in Python if possible. I'd like to speed it up though and remove any overhead. Also, you can see that I am writing a file to disk temporarily and then opening it to COPY FROM STDIN. Is there a way to skip the file creation? It is sometimes over 500mb which seems like a waste.</p>

<pre><code>engine = create_engine(engine_name)
query = 'SELECT * FROM {} WHERE row_date = %s;'
df = pd.read_sql_query(query.format(table_name), engine, params={query_date})
df.to_csv('../raw/temp_table.csv', index=False)
df= open('../raw/temp_table.csv')
process_file(conn=pg_engine, table_name=table_name, file_object=df)
</code></pre>

<p><strong>UPDATE:</strong></p>

<p>you can also try to unload data using <a href="https://technet.microsoft.com/en-us/library/ms162802(v=sql.110).aspx" rel="nofollow">bcp utility</a>, which might be lot faster compared to <code>pd.read_sql()</code>, but you will need a local installation of <code>Microsoft Command Line Utilities for SQL Server</code></p>

<p>After that you can use PostgreSQL's <code>COPY ... FROM ...</code>...</p>

<p><strong>OLD answer:</strong></p>

<p>you can try to write your DF directly to PostgreSQL (skipping the <code>df.to_csv(...)</code> and <code>df= open('../raw/temp_table.csv')</code> parts):</p>

<pre><code>from sqlalchemy import create_engine

engine = create_engine(engine_name)
query = 'SELECT * FROM {} WHERE row_date = %s;'
df = pd.read_sql_query(query.format(table_name), engine, params={query_date})

pg_engine = create_engine('postgresql+psycopg2://user:password@host:port/dbname')
df.to_sql(table_name, pg_engine, if_exists='append')
</code></pre>

<p>Just test whether it's faster compared to <code>COPY FROM STDIN</code>...</p>

python pandas sqlalchemy