store sql result in a variable in python

Question

I need help in storing the query result in a variable after executing the select statement. this is my code below.. there are no error but i need help in storing it in a variable in PYTHON. i am using pymssql and python 2.7.9. thanks in advance

conn = pymssql.connect(host='localhost', user='xx', password='xx', database='dbpython')
    c = conn.cursor()
    row = c.execute("select fid, fname, lname, contact from tblcontact where fname='maiji'")
    row = c.fetchall()
    while row:
        print row
        row = c.fetchall()

Answer 1

Stripping down your code, this is what you're doing in short.

row = c.ex...
row = c.fe..
    row = c.fe...

In programming, this is called assigning a value to a variable. In short, row is the container and you're replacing it over and over multiple times.

We'll ignore optmizations for now and just go with the basics.

conn = pymssql.connect(host='localhost', user='xx', password='xx', database='dbpython')
c = conn.cursor()
row = c.execute("select fid, fname, lname, contact from tblcontact where fname='maiji'")
rows = c.fetchall()
for row in rows:
    print(row)

First of all, c.fetchall() retrieves ALL the results from your query, we'll put them in a variable called rows. Then we create a iterator (the thing you tried to do with the while loop) by doing for row in rows. Then we simply print each row.

How you should do it tho

This is called context managers.

with pymssql.connect(host='localhost', user='xx', password='xx', database='dbpython') as conn:
    c = conn.cursor()
    rows = c.execute("select fid, fname, lname, contact from tblcontact where fname='maiji'")
    for row in c.fetchall():
        print(row)

This opens a context handle called conn which will automatically close the connection when you're done with your code (leaving no open connections which could be a problem) and secondly it's less code iterating directly over c.fetchall() and potentially be faster if the function supports iterations, which it probably will in later versions.

---

Here's one where you just add upp all the results into a hughe string (for dumping out to a file etc for instance). Note that this might fill your memory if the results are many, sequentially storing this to the file in such a case would be better, hence why you normally work with each database row individually for memory reasons.

with pymssql.connect(host='localhost', user='xx', password='xx', database='dbpython') as conn:
    c = conn.cursor()
    rows = c.execute("select fid, fname, lname, contact from tblcontact where fname='maiji'")
    string_result = ''
    for row in c.fetchall():
        string_result = row + '\n'