ポスグレのDBからデータを取得して一覧に表示させたい

Question

ポスグレのDBからデータを取得して一覧に表示させたいです。
ポスグレのDBには接続されます。

実行すると
for r in conn.execute(sql):
AttributeError: 'psycopg2.extensions.connection' object has no attribute 'execute'
というエラーが表示されます。

改善方法を調べていると
cur = conn.cursor()
cur.execute()
が必要と書いている記事がありましたが、
executeが参照？できていないのでエラーが出ているのかなと思っていますが
改善方法が分かりません。

どなたかご教授ください。

追記）
cursor= conn.cursor()
cursor.execute(aql)追加

'NoneType' object is not iterableというエラーがでる

# -*- coding: utf-8 -*-
import sys
import tkinter
from tkinter import ttk
from tkinter import font
import psycopg2

# connect postgreSQL
conn = psycopg2.connect(host="ホスト", database="データベース名", user="postgres", port=5432,
                        password="パスワード")
# 追記）
cursor= conn.cursor()

# 最初の画面に戻るのだ
def return_view():
    root_new_csv.destroy()

root_new_csv = tkinter.Tk()
# 画面サイズ
root_new_csv.geometry('800x480')
#画面タイトル
root_new_csv.title(u"一覧")

# ツリービューの作成
tree = ttk.Treeview(root_new_csv, height=10)
tree["columns"] = (1, 2, 3, 4, 5)
tree["show"] = "headings"
tree.column(1, width=120)
tree.column(2, width=120)
tree.column(3, width=120)
tree.column(4, width=120)
tree.column(5, width=70)
# tree.column(6, width=170)
tree.heading(1, text="ID")
tree.heading(2, text="名")
tree.heading(3, text="よみ")
tree.heading(4, text="生年月日")
tree.heading(5, text="年齢")
# tree.heading(6, text="メモ")

# ツリービューのスタイル変更
style = ttk.Style()
# TreeViewの全部に対して、フォントサイズの変更
style.configure("Treeview", font=("", 10))
# TreeViewのHeading部分に対して、フォントサイズの変更と太字の設定
style.configure("Treeview.Heading", font=("", 10, "bold"))

# SELECT文の作成
sql = """
SELECT id,name,kana,birth,memo
FROM gs_table
"""

# ツリービューに要素の追加
# cursor.execute(sql) 追記）
i = 0
for r in cursor.execute(sql):
        # 金額(r[2])を通貨形式に変換
        r = (r[0], r[1], r[2],r[3],r[4])
        tree.insert("", "end", tags=i, values=r)
        if i & 1:
                tree.tag_configure(i, background="#CCFFFF")
        i += 1

# ツリービューの配置
tree.place(x=40, y=200)

root_new_csv.mainloop()

Answer 1

forで回して処理するなら以下のようになります。

import psycopg2

conn = psycopg2.connect(host="localhost", database="test", user="koji", password="password", port=5432)

# カーソルを取得
cursor = conn.cursor()

# SQLの実行
sql = "SELECT * FROM product"
cursor.execute(sql)

# SQLの実行結果を取得
for r in cursor.fetchall():
    print(r)
print("終了")

なお、以下は余談ですが、conn.cursor()でカーソルをopenすると、自分でcloseしてあげる必要があります。
もしcloseしないと、アプリケーションがどんどんopen済みのカーソルを溜め込んでいって最終的にDBに繋げなくなったり正常に動作しなくなったりしてしまいます。
以下のようにwithを使えばPythonが自動的にcursorをcloseしてくれるのでおすすめです。

import psycopg2

conn = psycopg2.connect(host="localhost", database="test", user="koji", password="password", port=5432)

# カーソルを取得して、処理終了時には自動でカーソルをcloseする。
with conn.cursor() as cursor:

    # SQLの実行
    sql = "SELECT * FROM product"
    cursor.execute(sql)

    # SQLの実行結果を取得
    for r in cursor.fetchall():
        print(r)

# withブロックが終わったので、ココでcursorが自動的にcloseされる。
print("終了")

Comments

[MATLIB]

一覧に表示できました。
ありがとうございます。

psycopg2のバージョンや、使用するDBの種類によって
作り方が違うのですね。
すごく勉強になりました。

Answer 2

ご自身で検討を付けられている通り、connではなくて、cur = conn.cursor()を実行してからcur.execute()を利用する必要があります。

以下、PostgreSQLをpsycopg2で利用するサンプルコードです。

import psycopg2

conn = psycopg2.connect(host="localhost", database="test", user="koji", password="password", port=5432)

# カーソルを取得
cursor = conn.cursor()
sql = "SELECT * FROM product"

# SQLの実行
cursor.execute(sql)

while True:
    record = cursor.fetchone()
    if record == None:
        break
    print(record)

print("終了")

私の環境のテスト用データですが、上記コードを実行するとPostgreSQLからデータが取得できていることがわかります。

(1, 'keyboard', None, None)
(2, 'mouse', None, None)
(3, 'tablet', 'black', 'white')
(4, 'mouse', None, 'black')
(5, 'keyboard', None, None)
(6, 'tablet', 'white', 'black')
(7, 'mouse', 'white', 'black')
終了

executeが参照できていない、というのはまさしくそのとおりで、psycopg2.connectの結果のconnはexecuteという関数を知りません。
その代わり、connはcursorという関数を知っていて、このcursorの実行結果として取得できる値が、execute関数を知っている、という流れです。

Comments

[MATLIB]

ご回答ありがとうございます。
内容を追記したとおりに変更したら、でてたエラーはなくなりました。

ですが、
for r in cursor.execute(sql):
TypeError: 'NoneType' object is not iterable
というエラーがでました。

これは取得したデータがNULLだったり、データに空白が入っていると
起きるエラーなのでしょうか？

[saba1024]

いま手元に確認する環境がないので予想ですが、cursor.execute()は何も有効な値は返さないはずです。
executeを実行したあとで、fetcheoneなどを実行して値を取り出す必要があるはずです。

[MATLIB]

http://memopy.hatenadiary.jp/entry/2017/06/02/230723
上記のサイトを参考にしています。

上記サイトでは、本内容のfor文で
ツリービューに要素を追加できています。
execute(sql)で取得したデータをrに渡せているのですが、
私のやつだとTypeError: 'NoneType' object is not iterableでエラーが出ます。

fetcheoneなどでも試してみますが、
できれば参考のサイトのように使用したいです。

[saba1024]

そのページのPythonとPsycopg2のバージョンが分からないのでなんとも言えませんが、少なくとも最新のpsycopg2では、
「execute関数はNoneを返す。結果はfetch*()関数で取得できる」と説明されています。
（よく見るとそもそも参考にされているそのページではSQLiteを利用しているようですので、PostgreSQL用のデータベースライブラリは、関数名や利用方法などは全く別物になります。）

https://www.psycopg.org/docs/cursor.html#cursor.execute

そのため、executeの結果を直接forに渡して処理することは少なくとも最新のpsycopg2では不可能です。
ドキュメントどおり、fetchall関数などを利用する必要があります。

コードを貼るので、詳細は別の回答をご覧ください。