Execute queries with Python using Psycopg

When you get your DB connection, you need to start pulling data. To do that you will need to execute some SQL query to narrow down the amount of data you will get based on the type of analysis you want to perform. To perform that with Psycopg, you need to make the following steps:

• First, get a cursor from your DB connection:

cur = con.cursor()

• Execute a select query to pull data where tableis the table you want to get data from :

cur.execute("SELECT * FROM `table`;")

• After the successful execution of your query, you need to instruct Psycopg how to fetch your data from the database. There are plenty of options there, depending on how you plan to process your data. In most cases in analytics, you will need the complete dataset for analysis so the most appropriate command would be the following:

cur.fetchall()

• Next, do not forget to close your cursor & connection 

cur.close() 
conn.close()

The most important part here is the SQL query that you execute. SQL is a compelling and expressive language, and you can do a large part of your data pre-processing using it.

For example, you might join different tables, or even create new features by using the aggregation functions that are supported by Redshift and PostgreSQL.

Load data to NumPy

To turn your data into a NumPy array is pretty straight forward. All you need to do is to initialize a new NumPy array passing as a parameter our query results.

import numpy as np
data = np.array(cur.fetchall())

Where cur is the cursor we have created previously. That’s all, your data from Redshift as a NumPy array

Load data to pandas

If instead of NumPy you plan to work with pandas, you can avoid using the previous steps altogether. You can use the read_sql method with which you can read an SQL query or database table directly into a DataFrame. To do that you will also need to use SQLAlchemy.

from sqlalchemy import create_engine
import pandas as padas
engine = create_engine('postgresql://scott:tiger@hredshift_host:5439/mydatabase')
data_frame = padas.read_sql_query('SELECT * FROM `table`;', engine)

With the above code, you will end up having a pandas data frame that contains the results of the SQL query that you have provided to it.

R

If Python is not your cup of tea and you prefer R instead, you are still covered. Getting your data from Amazon Redshift or PostgreSQL is equally easy as in Python. As in Python, we again need to first take care of how we will connect to our database and execute queries to it. To do that we will need the “RPostgreSQL” package.

install.packages("RPostgreSQL")
require("RPostgreSQL")

With the above code, we make the package available and visible by R. Now we need to proceed in creating connections and executing queries. In the end, the command to close the connection, don’t do that before you execute queries though :).

Always remember to close the connection when you are done with pulling data out of the database.

drv 
Getting your data from the database into an R data frame is just one command away:
df_postgres 
You may also want to read: If you are looking to access your data from Google BigQuery with Python and R.
Conclusion
As you can see, getting your data from Redshift or PostgreSQL for further analysis in Python and R is easy. The real power of a database that stores your data in comparison with CSV files etc. is that you have SQL as an additional tool.
Investing some time learning how to work with SQL will give you results soon. Having structured data in a database and using SQL to pre-process your data before you start building your statistical models will save you time and resources.

Although in this article, we focused mainly on Amazon Redshift and PostgreSQL, using any other database is equally easy. The main difference will be the selection of the appropriate library for Python and R.
The usage of SQLAlchemy by Pandas makes it easier for you as the only change required is the configuration string for the database engine.
Would love to hear your thoughts in the comments bellow.