CODEWITHSUNDEEP
pythonpandasdataframepsycopg2
maggs
maggs

Reputation: 813

Converting query results into DataFrame in python

I am trying to perform manipulation on the result from a query using psycog2. Thus I have to covert result into pandas DataFrame. But when i use the following code and print, only the columns name are printed not the rows. I used 'pd.DataFrame.from_records' too but that did not work.

import psycopg2
import pandas as pd
import numpy as np
conn_string = "Connect_Info"
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.execute(query)
rows=pd.DataFrame(cursor.fetchall(),columns=['page_num','Frequency'])

for row in rows:
   print row

conn.commit();
conn.close();

The result of cursor.fetchall() - 

(1L, 90990L)
(3L, 6532L)
(2L, 5614L)
(4L, 4016L)
(5L, 2098L)
(6L, 1651L)
(7L, 1158L)
(8L, 854L)
(9L, 658L)
(10L, 494L)
(11L, 345L)
(12L, 301L)
(13L, 221L)
(15L, 152L)
(14L, 138L)
(16L, 113L)
(17L, 93L)
(18L, 73L)
(20L, 62L)
(19L, 55L)
(22L, 44L)
(21L, 35L)
(23L, 29L)
(25L, 24L)
(27L, 19L)
(26L, 18L)

Upvotes: 4

Views: 25472

Answers (3)

joris
joris

Reputation: 139222

Maybe not directly an answer on your question, but you should use read_sql_query for this instead doing the fetchall and wrap in DataFrame yourself. This would look like:

conn = psycopg2.connect(...)
rows = pd.read_sql_query(query, conn)

instead of all your code above.

And for your actual question, see http://pandas.pydata.org/pandas-docs/stable/basics.html#iteration for an explanation and the different options.
The basics is that iterating over a dataframe, iterates over the column names. To iterate over the rows you can use other functions like .iterrows() and .itertuples(). But remember, in most cases iterating manually over the rows is not needed.

Upvotes: 11

omri_saadon
omri_saadon

Reputation: 10641

another suggestion is using itertuples, which yields (index, row_value1, row_value2...) tuples.

for tup in rows.itertuples():
    print tup

'(0, 1, 90990)
(1, 3, 6532)
(2, 2, 5614)
(3, 4, 4016)
...'

as you can see the first position is the index, the socend is the value of the first column and the second is the value of the second column.

Upvotes: 0

Padraic Cunningham
Padraic Cunningham

Reputation: 180471

That is exactly what should happen when you iterate over a dataframe, you see the column names. If you want to see the df just print the df. To see the rows:

for ind, row in df.iterrows(): 
    print(row.values)

Or .values:

for row in df.values:
   print(row)

Upvotes: 4

Related Questions