In Python, display output of SQL query as a table, just like it does in SQL

Question

this seems like a basic function, but I'm new to Python so maybe I'm not googling this correctly.

In Microsoft SQL Server, when you have a statement like

SELECT top 100 * FROM dbo.Patient_eligibility

you get a result like

 Patient_ID | Patient_Name | Patient_Eligibility

     67456  | Smith, John  | Eligible
     ...         
     etc.

Etc.

I am running a connection to SQL through Python as such, and would like the output to look exactly the same as in SQL. Specifically - with column names and all the data rows specified in the SQL query. It doesn't have to appear in the console or the log, I just need a way to access it to see what's in it.

Here is my current code attempts:

import pyodbc
conn = pyodbc.connect(connstr)
cursor = conn.cursor()

sql = "SELECT top 100 * FROM [dbo].[PATIENT_ELIGIBILITY]"
cursor.execute(sql)
data = cursor.fetchall()

#Query1
for row in data :
    print (row[1])

#Query2
print (data)

#Query3
data

My understanding is that somehow the results of PATIENT_ELIGIBILITY are stored in the variable data. Query 1, 2, and 3 represent methods of accessing that data that I've googled for - again seems like basic stuff.

The results of #Query1 give me the list of the first column, without a column name in the console. In the variable explorer, 'data' appears as type List. When I open it up, it just says 'Row object of pyodbc module' 100 times, one for each row. Not what I'm looking for. Again, I'm looking for the same kind of view output I would get if I ran it in Microsoft SQL Server.

Running #Query2 gets me a little closer to this end. The results appear like a .csv file - unreadable, but it's all there, in the console.

Running #Query3, just the 'data' variable, gets me the closest result but with no column names. How can I bring in the column names?

More directly, how do i get 'data' to appear as a clean table with column names somewhere? Since this appears a basic SQL function, could you direct me to a SQL-friendly library to use instead?

Also note that neither of the Queries required me to know the column names or widths. My entire method here is attempting to eyeball the results of the Query and quickly check the data - I can't see that the Patient_IDs are loading properly if I don't know which column is patient_ids.

Thanks for your help!

Answer 1

We don't need any external libraries. Refer to this for more details.

Print results in MySQL format with Python

However, the latest version of MySQL gives an error to this code. So, I modified it. Below is the query for the dataset

stri = "select * from table_name"
cursor.execute(stri) 
data = cursor.fetchall() 
mycon.commit()

Below it will print the dataset in tabular form

def columnnm(name):
    v = "SELECT LENGTH("+name+") FROM table_name WHERE LENGTH("+name+") = (SELECT MAX(LENGTH("+name+")) FROM table_name) LIMIT 1;"
    cursor.execute(v) 
    data = cursor.fetchall() 
    mycon.commit()
    return data[0][0]

widths = []
columns = []
tavnit = '|'
separator = '+' 
for cd in cursor.description:
    widths.append(max(columnnm(cd[0]), len(cd[0])))
    columns.append(cd[0])

for w in widths:
    tavnit += " %-"+"%ss |" % (w,)
    separator += '-'*w + '--+'

print(separator)
print(tavnit % tuple(columns))
print(separator)
for row in data:
    print(tavnit % row)
print(separator)

Answer 2

It's more than 1 question, I'll try help you and give advice.

1. I am running a connection to SQL through Python as such, and would like the output to look exactly the same as in SQL.

You are mixing SQL as language and formatted output of some interactive SQL tool. SQL itself does not have anything about "look" of data.

2. Also note that neither of the Queries required me to know the column names or widths. My entire method here is attempting to eyeball the results of the Query and quickly check the data - I can't see that the Patient_IDs are loading properly if I don't know which column is patient_ids.

Correct. cursor.fetchall returns only data.

Field informations can be read from cursor.description.

Read more in PEP-O249

3. how do i get 'data' to appear as a clean table with column names somewhere?

It depends how do you define "appear".

You want: text output, html page or maybe GUI?

• For text output: you can read column names from cursor.description and print them before data.

• If you want html/excel/pdf/other - find some library/framework suiting your taste.

• If you want an interactive experience similar to SQL tools - I recommend to look on jupyter-notebook + pandas.

Something like:

pandas.read_sql_query(sql)

will give you "clean table" nothing worse than SQLDeveloper/SSMS/DBeaver/other gives.