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()

Austin Hanson · Accepted Answer · 2016-10-16 17:14:30Z

3

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'

edited Oct 16, 2016 at 17:14

Austin Hanson

22.1k6 gold badges38 silver badges43 bronze badges

answered Mar 11, 2015 at 9:15

Torxed

23.6k15 gold badges91 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

predator Over a year ago

I got the basics covered, well most of them, and programming is not one of my strengths, just starting out. So anyway the reason is I want the query result to be a string. @torxed

Torxed Over a year ago

@predator glad to hear, learning by doing is a good way to start :) in my last example, the result (each row) will be a string that you can work with. Meaning you'll have the oportunity to work with each individual result/row before moving on to the next result/row. I've updated with yet another example.

predator Over a year ago

hello! I tried the code you updated and it showed my the whole table. But it is returning results with the letter 'u', example: (u'29', u'maiji', u'chocolate', u'21321')

Torxed Over a year ago

@predator That's because the database results are in unicode format. You can decode each object in the touple by doing for item in row: item.decode('utf-8') or if it's .encode(..), i usually mix those two up but it's one of them :)

Alexandre · Accepted Answer · 2015-03-11 09:05:23Z

0

You should use an iterator instead of while loop.

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

for row in c:
    print(row)

c.close()

answered Mar 11, 2015 at 9:05

Alexandre

1,68314 silver badges22 bronze badges

2 Comments

predator Over a year ago

I tried your code but why is it showing results with a letter 'u' in them? (u'29', u'maiji', u'chocolate', u'21321')

Alexandre Over a year ago

As @Torxed said, it is because the returned results are in unicode format. But don't be worried, you can use them in this form, no decoding is needed unless you specifically need it.

Collectives™ on Stack Overflow

store sql result in a variable in python

2 Answers 2

How you should do it tho

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

How you should do it tho

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related