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)
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 aconn
object to execute asql
string and return the result as a Pandas DataframeImport is
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 elementsSo the code is
3. Execution
Given a
sql
string, it can be executed both3.1 using the native library by instantiating a Cursor using a
cursor()
method, and the cursor has anexecute()
method, example heresqlio.read_sql_query()
method that returns a Pandas DataframeAppunto