[Python, DB] Getting the result of a SQL Query in a Pandas Daraframe

[NicolaBernini]

Overview

Connect to a DB --> Execute a SQL Query --> Get the result in a Pandas Dataframe

1. Import

1.1 Pandas DB Connector

The pandas.io.sql.sqlio allows connecting to a remote DB identified by a conn object to execute a sql string and return the result as a Pandas Dataframe

Import is

import psycopg2
import pandas as pd
import pandas.io.sql as sqlio

2. Connection

In this example, the DB is Postgres and the Python Library to connect to it is PsycoPG
The typical Interface for this API consists of a connect() method where it is possible to specify the 4 elements

• host
• db
• username
• password

So the code is

# NOTE 
# The notebook need to be in the same docker network `pgnetwork` to see the DB 
conn = psycopg2.connect(
    host=host,
    port=port,
    database=db,
    user=user,
    password=password)

3. Execution

Given a sql string, it can be executed both

3.1 using the native library by instantiating a Cursor using a cursor() method, and the cursor has an execute() method, example here

cur = conn.cursor()
cur.execute("SELECT * FROM primarytable ORDER BY key")
print("The number of parts: ", cur.rowcount)
for x in cur:
    print(x)

• or by using the sqlio.read_sql_query() method that returns a Pandas Dataframe

res = sqlio.read_sql_query("""SELECT * FROM test1""", conn)

Appunto