Retrieving Data From Sql Using Pyodbc

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

Solution 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")
forrowin cursor.fetchall():
    print row

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

Solution 2:

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()
forrowinrows:
    print row.user_id, row.user_name

Solution 3:

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 sheetdefcopia (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!

Solution 4:

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]')

forrowincursor:
    print('row = %r'% (row,))

Solution 5:

Just do this:

import pandas as pd
importpyodbccnxn= pyodbc.connect("Driver={SQL Server}\
                    ;Server=SERVER_NAME\
                    ;Database=DATABASE_NAME\
                    ;Trusted_Connection=yes")

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