Getting data from table in database

Question

I want to extract data from a postgresql database and use that data (in a dataframe format) in a script. Here's my initial try:

from pandas import DataFrame
import psycopg2

conn = psycopg2.connect(host=host_address, database=name_of_database, user=user_name, password=user_password)

cur = conn.cursor()

cur.execute("SELECT * FROM %s;" % name_of_table)

the_data = cur.fetchall()

colnames = [desc[0] for desc in cur.description]

the_frame = DataFrame(the_data)
the_frame.columns = colnames

cur.close()
conn.close()

Note: I am aware that I should not use "string parameters interpolation (%) to pass variables to a SQL query string", but this works great for me as it is.

Would there be a more direct approach to this?

Edit: Here's what I used from the selected answer:

import pandas as pd
import sqlalchemy as sq

engine = sq.create_engine("postgresql+psycopg2://username:password@host:port/database")

the_frame = pd.read_sql_table(name_of_table, engine)

Answer 1

Pandas can load data from Postgres directly:

import psycopg2
import pandas.io.sql as pdsql

conn = psycopg2.connect(...)

the_frame = pdsql.read_frame("SELECT * FROM %s;" % name_of_table, conn)

If you have a recent pandas (>=0.14), you should use read_sql_query/table (read_frame is deprecated) with an sqlalchemy engine:

import pandas as pd
import sqlalchemy
import psycopg2

engine = sqlalchemy.create_engine("postgresql+psycopg2://...")

the_frame = pd.read_sql_query("SELECT * FROM %s;" % name_of_table, engine)
the_frame = pd.read_sql_table(name_of_table, engine)