Retrieve data from sql server database using Python

Question

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

Dysthymia

Answer 1

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

Macaronic

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() ? – Dysthymia

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. – Macaronic

I mean is it possible just to loop over the bare cursor object ? – Dysthymia

as far as i know, you can't. It'll end up causing a RunTimeError – Macaronic

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 :), – Dysthymia

Answer 2

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

Compute

Answer 3

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

Francis

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. – Yentai

The question is specifically about SQL Server and PyODBC. MySQL and MySQL Connector are entirely irrelevant here. – Nicholenicholl