5

I am trying to execute the following script. but I don't get neither the desired results nor a error message ,and I can't figure out where I'm doing wrong.

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=mySRVERNAME;"
                        "Database=MYDB;"
                        "uid=sa;pwd=MYPWD;"
                        "Trusted_Connection=yes;")


cursor = cnxn.cursor()
cursor.execute('select DISTINCT firstname,lastname,coalesce(middlename,\' \') as middlename from Person.Person')

for row in cursor:
    print('row = %r' % (row,))

any ideas ? any help is appreciated :)

Improve this question

3 Answers 3

Reset to default
6

You have to use a fetch method along with cursor. For Example

for row in cursor.fetchall():
    print('row = %r' % (row,))

EDIT :

The fetchall function returns all remaining rows in a list. 

    If there are no rows, an empty list is returned. 
    If there are a lot of rows, *this will use a lot of memory.*

Unread rows are stored by the database driver in a compact format and are often sent in batches from the database server.

Reading in only the rows you need at one time will save a lot of memory.

If we are going to process the rows one at a time, we can use the cursor itself as an interator Moreover we can simplify it since cursor.execute() always returns a cursor :

for row in cursor.execute("select bla, anotherbla from blabla"): 
    print row.bla, row.anotherbla

Documentation

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

5 Comments

it work but I want to know what is the difference between using only cursor and cursor.fetchall(), Because in some examples only cursor do the same as cursor.fetchall() ?
cursor is just a pointer to the database. After the executing a query we have explicitly fetch the rows that has been affected by the query so we use fetchall() method to do that.
I mean is it possible just to loop over the bare cursor object ?
as far as i know, you can't. It'll end up causing a RunTimeError
I'll keep searching for the reason, I know it's possible cuz I've been using it and also I know it's efficient with large results sets. once I found something I'll share it :),
0

I found this information useful to retrieve data from SQL database to python as a data frame.

import pandas as pd
import pymssql

con = pymssql.connect(server='use-et-aiml-cloudforte-aiops- db.database.windows.net',user='login_username',password='login_password',database='database_name')
cursor = con.cursor()

query = "SELECT * FROM <TABLE_NAME>"
cursor.execute(query)
df = pd.read_sql(query, con)
con.close()

df
Improve this answer

Comments

-2 
import mysql.connector as mc

# connection creation
conn = mc.connect(host='localhost', user='root', passwd='password')
print(conn)

#create cursor object

cur = conn.cursor()
print(cur)

cur.execute('show databases')

for i in cur:
    print(i)

query = "Select * from employee_performance.employ_mod_recent"
emp_data = pd.read_sql(query, conn)
emp_data
Improve this answer

2 Comments

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
The question is specifically about SQL Server and PyODBC. MySQL and MySQL Connector are entirely irrelevant here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.