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)
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With