如何将 SQLite 查询结果格式化为 HTML 表格

在 Web 开发和数据分析中，将数据库查询结果以 HTML 表格形式展示是最常见的需求之一。SQLite 虽然不直接提供 HTML 输出功能，但通过巧妙的 SQL 查询和字符串拼接，我们可以轻松实现查询结果到 HTML 表格的转换。这种技术特别适合生成动态报告、数据可视化预览或简单的 Web 应用开发。

使用字符串连接构建基础 HTML 表格

最基本的 HTML 表格可以通过 SQL 字符串拼接实现：

SELECT '<table border="1"><tr><th>' ||
       group_concat(name, '</th><th>') ||
       '</th></tr></table>' AS html_table
FROM pragma_table_info('your_table');

这个查询会生成包含表头的简单 HTML 表格框架。要进一步添加数据行：

SELECT '<table border="1">' ||
       (SELECT group_concat('<tr><td>' ||
        group_concat(column_name, '</td><td>') ||
        '</td></tr>', '')
       FROM your_table) ||
       '</table>' AS html_table;

使用 printf 实现更复杂的表格格式化

printf 函数可以创建更专业的 HTML 表格：

SELECT printf('<table class="data-table">
                <thead><tr>%s</tr></thead>
                <tbody>%s</tbody>
              </table>',
              (SELECT group_concat(printf('<th>%s</th>', name), '')
               FROM pragma_table_info('employees')),
              (SELECT group_concat(printf('<tr><td>%s</td><td>%d</td></tr>',
                name, age), '')
               FROM employees)) AS html_table;

这种格式化方式可以添加 CSS 类、分拆表头和表体等高级功能。

通过 Python 脚本生成完整 HTML 文档

在 Python 中可以更灵活地生成完整 HTML：

import sqlite3
from html import escape

def query_to_html(db_path, query, output_file):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute(query)

    with open(output_file, 'w', encoding='utf-8') as f:
        f.write('<!DOCTYPE html>\n<html>\n<head>\n<title>Query Results</title>\n')
        f.write('<style>table {border-collapse: collapse;} td, th {border: 1px solid #ddd; padding: 8px;}</style>\n')
        f.write('</head>\n<body>\n<table>\n')

        # 写入列名
        f.write('<tr>')
        for desc in cursor.description:
            f.write(f'<th>{escape(desc[0])}</th>')
        f.write('</tr>\n')

        # 写入数据行
        for row in cursor:
            f.write('<tr>')
            for col in row:
                f.write(f'<td>{escape(str(col)) if col is not None else ""}</td>')
            f.write('</tr>\n')

        f.write('</table>\n</body>\n</html>')

    conn.close()

# 使用示例
query_to_html('company.db', 'SELECT * FROM employees', 'report.html')

添加表格样式和交互功能

生成的 HTML 表格可以通过 CSS 和 JavaScript 增强：

SELECT printf('
<div class="table-container">
  <table id="data-table" class="sortable">
    <thead><tr>%s</tr></thead>
    <tbody>%s</tbody>
  </table>
</div>
<script src="sorttable.js"></script>
<style>
  .table-container {overflow-x: auto;}
  #data-table {width: 100%%; margin: 20px 0;}
  #data-table th {cursor: pointer; background: #f5f5f5;}
</style>',
(SELECT group_concat(printf('<th>%s</th>', name), '')
 FROM pragma_table_info('sales_data')),
(SELECT group_concat(printf('<tr><td>%s</td><td>%.2f</td></tr>',
  product, amount), '')
 FROM sales_data)) AS interactive_table;

处理特殊字符和安全性问题

在生成 HTML 时，必须注意转义特殊字符：

# 在 Python 中使用 html.escape
from html import escape
formatted_value = escape(str(db_value))

或者在 SQL 中使用替换：

SELECT replace(replace(replace(
       '<td>' || description || '</td>',
       '&', '&amp;'),
       '<', '&lt;'),
       '>', '&gt;') AS safe_html
FROM product_reviews;

分页和性能优化

对于大型结果集，考虑分页显示：

# Python 分页示例
def generate_paged_html(cursor, page=1, per_page=100):
    offset = (page - 1) * per_page
    cursor.execute(f'SELECT * FROM large_table LIMIT {per_page} OFFSET {offset}')
    # ...生成当前页HTML...

总结

将 SQLite 查询结果转为 HTML 表格有多种实现方式。简单的需求可以直接用 SQL 字符串拼接实现；复杂的场景建议使用 Python 等编程语言生成，这样可以更好地控制格式、处理特殊字符和添加交互功能。关键是根据实际应用场景选择合适的方法——简单的数据预览可以用纯 SQL 方案，而正式的 Web 应用则应该采用更健壮的程序化方案。无论哪种方式，都要特别注意 HTML 特殊字符的转义，确保生成的内容安全可靠。