Retrieving Data from SQL Using pyodbc

Question

I am trying to retrieve data from an SQL server using pyodbc and print it in a table using Python. However, I can only seem to retrieve the column name and the data type and stuff like that, not the actual data values in each row of the column.

Basically I am trying to replicate an Excel sheet that retrieves server data and displays it in a table. I am not having any trouble connecting to the server, just that I can't seem to find the actual data that goes into the table.

Here is an example of my code:

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')
cursor = cnxn.cursor()

cursor.execute("SELECT * FROM sys.tables")
tables = cursor.fetchall()
#cursor.execute("SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER")

for row in cursor.columns(table='WORK_ORDER'):
    print row.column_name
    for field in row:
        print field

However the result of this just gives me things like the table name, the column names, and some integers and 'None's and things like that that aren't of interest to me:

STATUS_EFF_DATE
DATABASE
dbo
WORK_ORDER
STATUS_EFF_DATE
93
datetime
23
16
3
None
0
None
None
9
3
None
80
NO
61

So I'm not really sure where I can get the values to fill up my table. Would it should be in table='WORK_ORDER', but could it be under a different table name? Is there a way of printing the data that I am just missing?

Any advice or suggestions would be greatly appreciated.

Bantu

Why not just run cursor.execute('select * from WORK_TABLE')? – Gasparo

Answer 1

You are so close!

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')
cursor = cnxn.cursor()

cursor.execute("SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER")
for row in cursor.fetchall():
    print row

(the "columns()" function collects meta-data about the columns in the named table, as opposed to the actual data).

Analisaanalise

This code doesn't work for me, as I get a pyodbc.ProgrammingError: No results. Previous SQL was not a query. error. I've confirmed that the SQL code works in Rapid SQL. – Eldaelden

"Previous SQL was not a query" -- that's the clue right there. what is the last thing you ran with "cursor.execute()" just before "cursor.fetchall()" . (You might be well served to ask a new question, so that you can show your code.) – Analisaanalise

Answer 2

you could try using Pandas to retrieve information and get it as dataframe

import pyodbc as cnn
import pandas as pd

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')

# Copy to Clipboard for paste in Excel sheet
def copia (argumento):
    df=pd.DataFrame(argumento)
    df.to_clipboard(index=False,header=True)


tableResult = pd.read_sql("SELECT * FROM YOURTABLE", cnxn) 

# Copy to Clipboard
copia(tableResult)

# Or create a Excel file with the results
df=pd.DataFrame(tableResult)
df.to_excel("FileExample.xlsx",sheet_name='Results')

I hope this helps! Cheers!

Where

Answer 3

In order to receive actual data stored in the table, you should use one of fetch...() functions or use the cursor as an iterator (i.e. "for row in cursor"...). This is described in the documentation:

cursor.execute("select user_id, user_name from users where user_id < 100")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

Goop

Answer 4

Just do this:

import pandas as pd
import pyodbc

cnxn = pyodbc.connect("Driver={SQL Server}\
                    ;Server=SERVER_NAME\
                    ;Database=DATABASE_NAME\
                    ;Trusted_Connection=yes")

df = pd.read_sql("SELECT * FROM myTableName", cnxn) 
df.head()

Mythomania

Answer 5

Instead of using the pyodbc library, use the pypyodbc library... This worked for me.

import pypyodbc

conn = pypyodbc.connect("DRIVER={SQL Server};"
                    "SERVER=server;"
                    "DATABASE=database;"
                    "Trusted_Connection=yes;")

cursor = conn.cursor()
cursor.execute('SELECT * FROM [table]')

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

Haggi

Answer 6

import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                  'Server=db-server;'
                  'Database=db;'
                  'Trusted_Connection=yes;')
sql = "SELECT * FROM [mytable] "
cursor.execute(sql)
for r in cursor:
    print(r)

Nessus

Welcome to StackOverflow! Please edit your answer and add an explanation to your code, explaining why you wrote what your did, and how it's equivalent to or better than the other five answers here. This question is more than seven years old, and already has an accepted answer. New answers without explanations are likely to be downvoted or deleted; adding an explanation will help justify your answer's remaining on this question. – Centriole

Answer 7

Why pyodbc you can try with pymssql. For more information follow this link: https://mcmap.net/q/282141/-retrieve-data-from-sql-server-database-using-python.

import pandas as pd
import pymssql

con = pymssql.connect(<conncetion to the server and db>)
cursor = con.cursor()
query = "<Your query>"
cursor.execute(query)
df = pd.read_sql(query, con)
con.close()

Backrest

guess even on Microsoft Docs pyodbc is better documented / more often mentioned... but well it's always sweet to have options – Paroxysm

You don't need to execute the query through the cursor as well as through pandas. Either cursor.execute followed by cursor operations to get the data, or pd.read_sql followed by dataframe operations. – Epsom

Answer 8

Upvoted answer din't work for me, It was fixed by editing connection line as follows(replace semicolons with coma and also remove those quotes):

import pyodbc
cnxn = pyodbc.connect(DRIVER='{SQL Server}',SERVER=SQLSRV01,DATABASE=DATABASE,UID=USER,PWD=PASSWORD)
cursor = cnxn.cursor()

cursor.execute("SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER")
for row in cursor.fetchall():
    print row

Jovita

Answer 9

You could use this query as an example of what you need, here is the complete code:

import pyodbc

def sqlquery(query):
    cnxn = pyodbc.connect(r'Driver=SQL Server;Server=dc00sqlXX.production.somewhere.com\sql2014;Database=ABQtrx_202105;Trusted_Connection=yes;')
    cursor = cnxn.cursor()
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        print(row.mlsfeedid)
    cnxn.close()

sqlquery('select mlsfeedid from [ABQtrx_202105].dbo.retsfeed')

Chloroform