如何使用pyscopg2编写程序获取数据库表结构

Question

当我执行程序application.py时，出现了以下错误：

psycopg2.ProgrammingError
ProgrammingError: ERREUR:  erreur de syntaxe sur ou près de « "/d" »
LINE 1: "/d" carto."BADGES_SFR"

（英文原文为 "ProgrammingError: ERREUR: syntax error at or near « "/d" » "）我的目标是获取表结构。

以下是代码application.py：

#!/usr/bin/python 2.7.6
# -*- coding:utf-8 -*-
import os
import sys
from flask import Flask,render_template
import psycopg2
reload(sys)  
sys.setdefaultencoding('utf8')
app = Flask(__name__)

@app.route('/')
def fihoum():
    conn = psycopg2.connect(database="carto", user="postgres", password="daed5Aemo", host="192.168.12.54")
    cur = conn.cursor()
    #cur.execute("SELECT * FROM carto.\"BADGES_SFR\"")
    cur.execute("/d carto.\"BADGES_SFR\"")
    rows = cur.fetchall()
    return render_template('hello.html', titre="Données du client BADGES_SFR !",mots=rows)

if __name__=="__main__":
    app.run(host=os.getenv('IP', '0.0.0.0'), 
            port=int(os.getenv('PORT',5000)),
            debug=True)

- Zaaf Lafalaise

---

它是否能够运行cur.execute("SELECT * FROM carto."BADGES_SFR"")这一行代码？ - Martin Gottweis

是的，它可以运行，使用cur.execute("SELECT * FROM carto."BADGES_SFR"")。 - Zaaf Lafalaise

像@bakkal建议的那样，cur.execute("\d carto."BADGES_SFR"")能解决它吗？ - Martin Gottweis

我不这么认为，因为\d table_name是无效的SQL语法，会出现ProgrammingError: syntax error at or near "\"。 - bakkal

Answer 1

首先是 \d <table_name>（请注意反斜杠\d而不是/d），但这只在psql交互式终端中可用。

我使用此程序的目的是获取表结构。

您可以使用SQL表information_schema.columns，其中包含您想要的信息。

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = '<table_name>';

    column_name     |     data_type     | is_nullable 
--------------------+-------------------+-------------
 column_a           | integer           | YES
 column_b           | boolean           | NO

以下是可用于查询的列列表：https://www.postgresql.org/docs/9.4/static/infoschema-columns.html。 片段

import psycopg2
conn = psycopg2.connect(database='carto', user=..., password=...)

q = """                              
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = %s;
"""

cur = conn.cursor()
cur.execute(q, ('BADGES_SFR',))  # (table_name,) passed as tuple
cur.fetchall()

# Example Output 
[('column_a', 'integer', 'YES'),
 ('column_b', 'boolean', 'NO'),
 ...,
]

Jinja2/Flask集成技巧:

考虑使用psycopg2.extras.DictCursor，这将使您在Flask模板中基于列名（dict key）更容易地提取信息，因为您将获得一个可访问的字典，例如使用row['data_type']、row['column_name']等。

- bakkal

---

@ZaafLafalaise，我可以问一下你具体是怎么做的吗？ - bakkal

Answer 2

如果您只需要列描述：

>>> cur.execute('select * from t')
>>> description = cur.description
>>> print description
(Column(name='record_timestamptz', type_code=1184, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None),)
>>> columns = description[0]
>>> print columns.name, columns.type_code
record_timestamptz 1184

在应用程序启动时将数据类型名称缓存到pg_type关系中，并将其转换为字典：

cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cursor.execute ('select oid, * from pg_type')

pg_type = dict([(t['oid'], t['typname']) for t in cursor.fetchall()])

cursor.execute ('select * from t')

for t in cursor.description:
    print t.name, t.type_code, pg_type[t.type_code]

- Clodoaldo Neto