在 Python 中打印格式正确的 SQLite 表 [英] Printing a properly formatted SQLite table in Python
问题描述
我编写了一个 Python 脚本来向我的表中添加行.我决定如果我也可以使用相同的脚本查看我的表,而不必退出脚本并运行 sqlite3 或切换到另一个 shell 并运行 sqlite3,那就太好了.所以我写了我期望的东西会给我我想要的东西并且它确实......这是有问题的脚本的一部分:
I've written a Python script to add rows to my tables. I decided it would be nice if I could also view my tables with the same script instead of having to either quit the script and run sqlite3 or switch to another shell and run sqlite3. So I wrote up what I expected would give me what I want and it sort of does... This is the part of the script in question:
import sqlite3
conn = sqlite3.connect('stu.db')
c = conn.cursor()
var = 1
while var == 1:
enquiry = raw_input("What would you like to do?> ")
enquiry == 'stu db' or enquiry == 'sd':
c.execute("SELECT * FROM stu")
conn.commit
在 sqlite3 中,当您运行 SELECT * FROM stu 时,您会得到一个格式良好的表格,其中包含统一的行和列.当我在这里运行它时,我会得到一长串括号中的信息.它看起来有点像这样(我没有打印实际结果,因为这会违反一些联邦法律):
In sqlite3 when you run SELECT * FROM stu you get a nicely formatted table with uniform rows and columns. When I run it here I get a long list of the information in parenthesis instead. It looks sort of like this (I didn't print the actual results as that would violate some Federal laws):
[(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)]
我想我知道发生了什么.Python 只是吐出对 sqlite 的查询返回的内容,但是有没有办法格式化这些信息以便于阅读?
I think I know what's going on. Python is just spitting out what the query to sqlite returns, but is there a way to format this information so that it is easily readable?
推荐答案
您可以使用 pandas
用于此:
You can use pandas
for this:
print pd.read_sql_query("SELECT * FROM stu", conn)
示例程序(python 2.7.6,pandas 0.18.0):
Sample program (python 2.7.6, pandas 0.18.0):
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('create table stu ( ID, Name, ShoeSize, Course, IQ, Partner )')
conn.commit()
c.executemany('insert into stu VALUES (?, ?, ?, ?, ?, ?)',
[(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),
(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)])
conn.commit()
# Ugly way
print list(c.execute("SELECT * FROM stu"))
# Pretty way
print pd.read_sql_query("SELECT * FROM stu", conn)
结果,包括丑陋和漂亮的输出:
Result, which includes both the ugly and the pretty output:
[(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)]
ID Name ShoeSize Course IQ Partner
0 1234567890 John Doe 3852 DEGR-AA 4 None
1 1234567890 John Doe 3852 DEGR-AA 4 None
2 1234567890 John Doe 3852 DEGR-AA 4 None
3 1234567890 John Doe 3852 DEGR-AA 4 None
4 1234567890 John Doe 3852 DEGR-AA 4 None
5 1234567890 John Doe 3852 DEGR-AA 4 None
6 1234567890 John Doe 3852 DEGR-AA 4 None
7 1234567890 John Doe 3852 DEGR-AA 4 None
8 1234567890 John Doe 3852 DEGR-AA 4 None
9 1234567890 John Doe 3852 DEGR-AA 4 None
这篇关于在 Python 中打印格式正确的 SQLite 表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!