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

User · Answer

Just do this  import pandas as pd import pyodbc  cnxn   pyodbc connect  quot Driver  SQL Server                        Server SERVER NAME                       Database DATABASE NAME                       Trusted Connection yes quot    df   pd read sql  quot SELECT   FROM myTableName quot   cnxn   df head

User · Answer

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  lt  100   rows   cursor fetchall   for row in rows      print row user id  row user name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] Retrieving Data from SQL Using pyodbc

Examples related to python

Examples related to sql

Examples related to sql-server

Examples related to pyodbc