CODEWITHSUNDEEP
pythonsqlpandaspyodbc
JimmyBuffet_Express
JimmyBuffet_Express

Reputation: 311

Append pyodbc.Rows into a data frame using a loop

I am trying to import some data from a sql server to python. I would like to import the data row by row and append it to a data frame iteratively.

My code so far is:

data_NS = pd.DataFrame(columns=Fields_NS)
i=0

while(i < 10):
    row = cursor.fetchone()
    rowAsList = list(row)
    data_NS.append(rowAsList)
    i = i+1
conn.close()

The result for data_NS is and empty data frame. I am not receiving any error messages so any information will be greatly appreciated.

Upvotes: 1

Views: 3270

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123484

Your DataFrame is remaining empty because DataFrame.append will

Append rows of other to the end of this frame, returning a new object.

(Emphasis mine.) So simply doing

data_NS.append(...)

will take the existing data_NS frame, append stuff to it, and return the result as a new frame, which is then discarded because it is not assigned to a variable.

I think you're actually trying to do something more like this:

fields_NS = ["id", "txt"]
data_NS = pd.DataFrame(columns=fields_NS)

sql = """\
SELECT 1 AS id, 'foo' as txt
UNION ALL
SELECT 2 AS id, 'bar' as txt
"""
crsr.execute(sql)

i = 0
while i < 10:
    row = crsr.fetchone()
    if not row:
        break
    row_as_dict = dict(zip(fields_NS, row))
    data_NS = data_NS.append(row_as_dict, ignore_index=True)
    i += 1

but beware that such row-by-row appends might be rather slow.

Upvotes: 2

kjmerf
kjmerf

Reputation: 4345

I would try it like this:

connection = pyodbc.connect('your server information')
query = 'SELECT TOP 100 * FROM table'
df = pd.read_sql_query(query, connection)

To avoid a memory error, just adjust your query and only select the columns you need, or adjust the number of rows you bring back using "TOP" or something similar depending on the type of database you are querying.

Upvotes: 2

Related Questions