when I execute the program application.py ,I get this error :
psycopg2.ProgrammingError
ProgrammingError: ERREUR: erreur de syntaxe sur ou près de « "/d" »
LINE 1: "/d" carto."BADGES_SFR"
(in english "ProgrammingError: ERREUR: syntax error at or near « "/d" » "
) My objective for this program is to get the table structure
this is the following code 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)
First of all it's \d <table_name>
(note the backslash \d
not /d
), but that's only available in the psql
interactive terminal
My [objective] with this program is to get the table structure
You can use the SQL table information_schema.columns
which carries the information you want
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
Here's a list of the columns available for use: https://www.postgresql.org/docs/9.4/static/infoschema-columns.html
Snippet
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'),
...,
]
Tip for Jinja2/Flask integration:
Consider using psycopg2.extras.DictCursor
, it'll make it easier for you to pull the information based on the column name (dict key) in your Flask template, because your'll a have a dict that you can access e.g. using row['data_type']
, row['column_name']
etc.
If you only need the columns description:
>>> 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
For the data type name cache the pg_type relation at the application start and make it a dictionary:
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With