Read SQL query from psycopg2 into pandas dataframe

connect_psycopg2_to_pandas.py

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

conn = psycopg2.connect("host='{}' port={} dbname='{}' user={} password={}".format(host, port, dbname, username, pwd))
sql = "select count(*) from table;"
dat = sqlio.read_sql_query(sql, conn)
conn = None

> The example does not work for me: `UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql_query(sql, conn)

It should actually work and extract your table though because it's just a UserWarning, a warning that you can ignore (but it's not recommended to ignore it)
In Pandas, Psycopg2 is not tested as the warning implies, and it prefers using SQLAlchemy instead.

You can remake the example above by running the following code:

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.sql import text

engine = create_engine(
    "{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}".format(
        dialect="postgresql",
        driver="psycopg2",
        username="john",
        password="12345",
        host="localhost",
        port=5432,
        database="items"
    )
)

with engine.connect() as db_conn:
    sql_query = "SELECT COUNT(*) FROM table_name"
    df = pd.read_sql_query(sql=text(sql_query), con=db_conn)

You can actually create an engine by passing a URL object instead of the string:

from sqlalchemy import create_engine, URL

url_object = URL.create(
    drivername="DIALECT+DRIVER",
    username="username",
    password="password",
    host="host",
    database="database_name",
)

engine = create_engine(url=url_object)

I'm not an expert but I hope it helps!

does anyone now if pd.read_sql blocks? im have a multi-thread process that uses it, and it seems to block