在 Python 中，将 SQL 查询的输出显示为表，就像在 SQL 中一样 [英] In Python, display output of SQL query as a table, just like it does in SQL

问题描述

这似乎是一个基本函数，但我是 Python 新手，所以可能我没有正确使用谷歌搜索.

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

在 Microsoft SQL Server 中，当您有类似这样的语句时

In Microsoft SQL Server, when you have a statement like

SELECT top 100 * FROM dbo.Patient_eligibility

你会得到类似

 Patient_ID | Patient_Name | Patient_Eligibility

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

等

我正在通过 Python 运行与 SQL 的连接，并且希望输出看起来与 SQL 中的完全相同.特别是 - 使用列名和 SQL 查询中指定的所有数据行.它不必出现在控制台或日志中，我只需要一种方法来访问它以查看其中的内容.

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

我的理解是，PATIENT_ELIGIBILITY 的结果以某种方式存储在变量数据中.查询 1、2 和 3 代表访问我在 google 上搜索过的数据的方法 - 再次看起来像是基本的东西.

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.

#Query1 的结果给了我第一列的列表，控制台中没有列名.在变量资源管理器中，data"显示为 List 类型.当我打开它时，它只说pyodbc 模块的行对象"100 次，每行一次.不是我要找的.同样，我正在寻找与在 Microsoft SQL Server 中运行它时获得的相同类型的视图输出.

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.

运行#Query2 让我更接近这个目标.结果看起来像一个 .csv 文件 - 无法读取，但它就在控制台中.

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.

运行#Query3，只是'data' 变量，可以得到最接近的结果，但没有列名.如何引入列名?

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

更直接地，我如何让数据"显示为一个干净的表，在某处带有列名?由于这看起来是一个基本的 SQL 函数，你能指导我使用一个对 SQL 友好的库来代替吗?

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?

另请注意，这两个查询都不需要我知道列名或列宽.我在这里的整个方法是试图观察查询的结果并快速检查数据 - 如果我不知道哪一列是 Patient_ids，我就看不到 Patient_IDs 正在正确加载.

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.

感谢您的帮助！

推荐答案

问题不止 1 个，我会尽力帮助您并提供建议.

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

我正在通过 Python 运行与 SQL 的连接，并且希望输出看起来与 SQL 中的完全相同.

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

您将 SQL 作为语言和某些交互式 SQL 工具的格式化输出混合使用.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.

另请注意，这两个查询都不需要我知道列名或列宽.我在这里的整个方法是试图观察查询的结果并快速检查数据 - 如果我不知道哪一列是 Patient_ids，我就看不到 Patient_IDs 正在正确加载.

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.

正确.cursor.fetchall 只返回数据.

可以从cursor.description读取字段信息.

在 PEP-O249

如何让数据"显示为带有列名的干净表格?

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

这取决于你如何定义出现".

It depends how do you define "appear".

您想要:文本输出、html 页面或 GUI?

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

• 对于文本输出:您可以从 cursor.description 读取列名并在数据之前打印它们.

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

如果你想要 html/excel/pdf/other - 找一些适合你口味的库/框架.

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

如果您想要类似于 SQL 工具的交互体验 - 我建议您查看 jupyter-notebook + pandas.

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

类似于:

pandas.read_sql_query(sql)

不会比 SQLDeveloper/SSMS/DBeaver/其他提供的更糟糕的干净表".

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

这篇关于在 Python 中，将 SQL 查询的输出显示为表，就像在 SQL 中一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！