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)

回答1:

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)

回答2:

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.

回答3:

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.