PostgreSQL（データーベース）へのデータの挿入について

Question

実現したいこと

作成したデータベースへ値をインポートしたいです。仮にインポートする値は133という整数とします。

作成したデータベースのIPアドレスを仮にAとします。
データベースのHost name/addressをA、portが5432、user nameをpostgres、passwordをBと設定しました。データベース上のデータベース名称はCとしました。
なお、データベースの置かれているPC(IPアドレスはA)とプログラムを走らせるPCは別とします。

データベースを開いた状態で、別のPCのプログラムを実行してデータベースのIDという列に133という
整数をインポートするプログラムを走らせたのですが、データベースの置いてあるPCのデータベースを見ると整数133という値がきちんと入っていません。プログラムのコードの問題なのか、データベース上の設定の問題なのかが不明です。

ご教示いただける範囲でいいので、ご教示いただけますと幸いです。
プログラムはPython3.0で書きました。プログラムを走らせているPCはrasberryPi4です。

発生している問題・分からないこと

rasberryPiのターミナル上からプログラムを実行しましたが、データベース上のテーブルには
整数133が入っておらず、ターミナルを見ても特に変化が見られません(プログラムが終わっても進んでもないように見える)。

エラーメッセージ

添付画像の通りで、コマンドプロンプト上のプログラムは実行完了していないように感じる。

該当のソースコード

import RPi.GPIO as GPIO 
import time
import binascii
import nfc
import os
import datetime
import csv
import sys
sys.path.append('/home/pi/.local/lib/python3.7/site-packages')
import psycopg2

# データベースに接続 
connection = psycopg2.connect(host='Aを入力',
                                               user='postgres',
                                               password='Bを入力',
                                               database='Cを入力')

# Postgres SQLへのインサート
with connection:
    with connection.cursor() as cursor:
        # レコードを挿入
        sql = "insert into Schemas.C(ID) values (133)";
        cursor.execute(sql)

        # コミットしてトランザクション実行
        connection.commit()

        # 接続を閉じる
        cursor.close()
        connection.close()

sys.exit()

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

googleや知人にも聞きましたが、原因が不明でした。ご教示いただけますとありがたいです。。

補足

pgAdmin 4 のversionは7.8です。データベースの置いてあるPCはwindows10のproです。

Comments

[satokei]

> sql = "insert into Schemas.C(ID) values (133)";

Schemas.C(ID) とは何でしょうか？

画像の select文を見ると、insert文は以下のようになるかと。
　insert into public.ID(ID) values (133)

[sazi]

psycopg2.connect()でデータベース名やportの指定がありませんが、接続できていますか？

[退会済みユーザー]

コメントありがとうございました。
プログラムの中身をコメントいただいた通りの
sql = "insert into public.ID(ID) values (133)";　　　に変更して実行してみました。

なお、データベースサーバのhost name(質問のソースコードでいうAにあたる)はlocalhost です。

実行してみるとやはりエラーが出るので自分で調べてみると、他端末からデータベースサーバへ接続しようとするときは、データベースサーバ側に設定をしなければいけないことが分かりました。
①Cドライブの中にあるpostgresql.confというファイルの中の
　#listen_addresses = 'localhost'　　→　　listen_addresses = '*'　　に変更すること
②同じくCドライブの中にあるpg_hba.confというファイルの中に１行追加する(以下のように)
　host all all 0.0.0.0/0 md5(scram-sha-256)
　※　上記の設定はどの端末からでもつなげるような設定らしい
③接続されるデータベースサーバ側のファイヤーウォールのポート番号を開放する必要があるらしいので
　ポート番号5432を開放の後、PCを再起動した。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
これを踏まえた上で質問した時のプログラムの中身を以下に変更した上でプログラムを実行した
connection = psycopg2.connect(host=' 接続先のIPアドレス(XXX.XX.XXX.XXX)/localhost',
　　　　　　　　　　　　　　　　port='5432',
user='postgres',
password='Bを入力',
database='Cを入力')
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "接続先のIPアドレス/localhost" to address: Name or service not known

このようなエラーが出ました。
どのように修正すれば、無事にデータベースへの接続と整数133のインポートが可能になるのでしょうか。
ご教示いただけますと幸いです。

[sazi]

localhostで指定する場合はIPは不要です。データベース名の指定が相変わらずありません。
host=loalhost, dbname=c

localhostに接続するなら、postgresql.confもpg_hba.confもデフォルトから変更する必要はありません。

[退会済みユーザー]

コメントと回答ありがとうございます。
プログラムの
database='C' →　　dbname='C'　　　に変更してやってみました。
やはり同じエラーがでました。

あと、localhostで接続したいわけではなく、最初にも記載した通りデータベースサーバが置いてあるPCのhostnameがlocalhostです。プログラムを実行しているデータベースサーバに接続しにいきたいPCはまた別のPCです(最初の実現したいことの欄に記載)。

[sazi]

先ずは接続できるところまでを確認された方が良いですね。
.confなどはプログラムと関係なくDBツールなどでも確認できる事ですから。

Answer 1

おそらく、何かしらのエラーが発生していて、with ステートメントによって自動的に connection.rollback() が行われていると思われます。

原因を特定するには、try-catch でエラー処理を行い、エラーの内容を確認していただくのが一番確実だと思いますが、ご質問の内容を見ると、SQLが間違っている可能性があるんじゃないかと思います。

次のようにSQLを書き換えてみて、試してみてもらえますか。

変更前

        sql = "insert into Schemas.C(ID) values (133)";

変更後

        sql = "insert into public.\"ID\"(\"ID\") values (133)";

---

追記：
キャプチャを拝見すると「ID」テーブルは大文字のようです。
大文字を含む場合はクォーテーションで囲ってください。なので ID ではなくて "ID" と記述します。
変更前）insert into public.ID(ID) values(133)
変更後）insert into public."ID"("ID") values(133)

追記２：
「変更後」のコードを修正しました。
「"」をエスケープして「\"」と記述するように変更しました。
変更前）insert into public.ID(ID) values(133)
変更後）insert into public.\"ID\"(\"ID\") values(133)

Comments

[退会済みユーザー]

コメントありがとうございました。
プログラムの中身をコメントいただいた通りの
sql = "insert into public.ID(ID) values (133)";　　　に変更して実行してみました。

なお、データベースサーバのhost name(質問のソースコードでいうAにあたる)はlocalhost です。

実行してみるとやはりエラーが出るので自分で調べてみると、他端末からデータベースサーバへ接続しようとするときは、データベースサーバ側に設定をしなければいけないことが分かりました。
①Cドライブの中にあるpostgresql.confというファイルの中の
　#listen_addresses = 'localhost'　　→　　listen_addresses = '*'　　に変更すること
②同じくCドライブの中にあるpg_hba.confというファイルの中に１行追加する(以下のように)
　host all all 0.0.0.0/0 md5(scram-sha-256)
　※　上記の設定はどの端末からでもつなげるような設定らしい
③接続されるデータベースサーバ側のファイヤーウォールのポート番号を開放する必要があるらしいので
　ポート番号5432を開放の後、PCを再起動した。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
これを踏まえた上で質問した時のプログラムの中身を以下に変更した上でプログラムを実行した
connection = psycopg2.connect(host=' 接続先のIPアドレス(XXX.XX.XXX.XXX)/localhost',
　　　　　　　　　　　　　　　　port='5432',
user='postgres',
password='Bを入力',
database='Cを入力')
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "接続先のIPアドレス/localhost" to address: Name or service not known

このようなエラーが出ました。
どのように修正すれば、無事にデータベースへの接続と整数133のインポートが可能になるのでしょうか。
ご教示いただけますと幸いです。

[Umeeeh]

＞ could not translate host name "接続先のIPアドレス/localhost" to address:
＞ Name or service not known

「そんなホストねえよ」って言われてますので、接続情報が間違ってるんだと思います。

"接続先のIPアドレス/localhost" の部分は、実際にはどんな感じで書かれてるのでしょうか。見せられない情報があれば、「*」などでマスクしていただいて、書いてもらえると、イメージが湧くので助かります。

おそらく、host に指定した データベース・サーバーのホスト名が、正引きできない状態なのかもしれません。正引きは、お使いの環境のDNSサーバーに設定するか、クライアント側の /etc/hosts などに記述する必要がありますので、ご確認ください。

また、PostgreSQLが正常に起動していることもご確認ください。

[退会済みユーザー]

回答ありがとうございました。

"接続先のIPアドレス/localhost"　は、具体的にいうと　"XXX.XX.XX.XX/localhost"のように書きました。
これでうまくいかなかったので、localhostを消して"XXX.XX.XX.XX"に書き換えて再度プログラムを実行すると、今度はpsycopg2.errors.UndefinedTable: リレーション"public.id"は存在しません
LINE 1: insert into public.ID(ID) values(133)というエラーがでました。

上記を調べてみると、スキーマ名(デフォルトではpublicに設定されているらしい)も書けとのことだったので、sql = "insert into public.public.ID(ID) values(133)"; と書き換えて再度プログラムを実行すると

psycopg2.errors.FeatureNotSupported: データベース間の参照は実装されていません: "public.public.id"
LINE 1: insert into public.public.ID(ID) values(133)
^
上記のようなエラーが出てしまいました。
どのようにすればよろしいのでしょうか。

あと追加で質問ですいませんが、プログラムを実行している側のPC(ラズベリーパイ)にも
PostgreSQLのドライバか何かをインストールしなければいけないのでしょうか。

分かる範囲で良いのでご教示いただけますと幸いです。

[Umeeeh]

まずは、localhostを消したことで、接続の問題は解消されて、現象が変わったようですね。現象が変わるのは前進している証拠なのでポジティブです。

さて、「リレーション"public.id"は存在しません」 というエラーは、私の回答の内容が、良くなかったためだと思います。回答を書き直しますので、そちらを参考に、INSERT文を修正してみてもらえますか。

[退会済みユーザー]

お忙しいところ回答ありがとうございました。コメント頂いた点を反映して
コード全文を以下に書き換えて試してみましたが、やはりだめでした。
～～～～～～～～～～～～コード全文～～～～～～～～～～～～～～～～～～～～～～
import RPi.GPIO as GPIO
import time
import binascii
import nfc
import os
import datetime
import csv
import sys
sys.path.append('/home/pi/.local/lib/python3.7/site-packages')
import psycopg2

# データベースに接続
connection = psycopg2.connect(host='XXX.XX.XX.XX',
port='5432',
user='postgres',
password='soccer12',
dbname='TT-toshiki')

# Postgres SQLへのインサート
with connection:
with connection.cursor() as cursor:
# レコードを挿入
sql = "insert into public."ID"("ID") values(133)"
cursor.execute(sql)

# コミットしてトランザクション実行
connection.commit()

# 接続を閉じる
cursor.close()
connection.close()

sys.exit()
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

エラー内容としては、sql = "insert into public."ID"("ID") values(133)"
^
SyntaxError: invalid syntax　　といったエラーがでました。

自分で試してみた点としては、
①全角スペースがないかを確認した。あったところは半角スペースに変更した
②指摘していただいたコメントを反映した
③sql = "insert into public."ID"("ID") values (133)";　　　
　最後の;を消した(いらなくても良いと判断したため)

以上の３点を試してみたのがコード全文の欄です。
どうしてもわかりません。。。

IPアドレスの部分だけは、隠させていただきました。どうぞ分かる範囲で良いのでよろしくお願いいたします。

なお当然ですが、ホスト側でデータベースは起動しています(pgAdmin4で)。

[Umeeeh]

@myao_12 すみません、私の回答が間違っていました。

Pythonでは、`"～"`までが文字列になります。文字列の中に「`"ID"`」を追加しちゃったので、`"insert into public."` という文字列になってしまい、その後に `ID`と続いたのでSyntax Errorになったんだと思います。

文字列の中に「"」記号を記述するには、エスケープ文字を書かなければなりません。回答のコードを修正したのでご確認ください。

[退会済みユーザー]

回答ありがとうございます。
試したところ問題が解決しました！大変ありがとうございました。

最後にもうひとつ質問で申し訳ないのですが
例えば、その人固有(人によって変わる)の10桁のバイナリデータを読み込んで、
先ほどまでに質問していたvaluesの中の133の部分に置き換えたいとします。

10桁のバイナリデータをデータベース上では10進数の整数で表記したいので、
一度プログラム上で　aa = (読み込んだバイナリデータの部分)　と変数aaを設定して
さらに　cc = int（aa） と変数ccを設定した上で（バイナリデータを整数に変換）、
一度ターミナル上でprintをしてみると、問題なく整数が表示されました。

これでいけたと思い、
プログラムの中身を以下のように変更した上で、プログラムを実行しました。
sql = "insert into public.\"ID\"(\"aaa\") values(cc)"

これを実行したところ、以下のようなエラーがでました。。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
psycopg2.errors.UndefinedColumn: 列"cc"は存在しません
insert into public."ID"("aaa") values(cc)
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

データベースは立ちあげておりますが、その人固有の整数はデータベース上に書き込まれていません。
どうしたらいいのか回答いただけますと幸いです。

[Umeeeh]

" ～ " の部分は文字列になりますので、「insert～(cc)」という文字列と認識されます。cc は変数ですが、文字列の中に変数名を書いてもダメなんですね。

SQLにパラメータを埋め込むやり方は複数あります。次のURLを確認していただいて、それぞれやり方を練習してみてもらえれば、良いんじゃないかなって思いますので、ご覧になってください。
https://www.psycopg.org/docs/sql.html

[退会済みユーザー]

再度の質問で申し訳ありません。添付いただいたURLデータを基にプログラムを書いてみました。
プログラムの途中に以下を追加しました。
aa = reader.data[6:16] →　読み取った各人固有のバイナリデータに変数aaを代入した
dd = int(aa) → バイナリデータを整数に変換した

いただいたURLから文字列の中で変数を使うには、f-stringsを使えばよいとのことだったので以下のように変更した。
sql = "insert into public.\"ID\"(\"aaa\") values(f'{dd}')"

この上でプログラムを実行すると、以下のようなエラーがまた出ました。
psycopg2.errors.UndefinedObject: 型"f"は存在しません
LINE 1: insert into public."ID"("aaa") values(f'{dd}')

なお
sql = "insert into public.\"ID\"(\"aaa\") values(500)"　　　　のように整数を入れれば
問題なくプログラムは実行できて、エラーも出ないですが
やりたいことは、読み取った任意のバイナリデータを、データベースの列aaa(型はintegerを指定)に入れたいです。valuesの中を500のように適当な整数を入れた時はデータベースの列aaaには問題なく入ります。

sql = "insert into public.\"ID\"(\"aaa\") values(f'{dd}')"　のように変数を入れようとすると
上手くいきません。

聞ける人がいないので、度々で申し訳ないですが教えていただけないでしょうか。

[退会済みユーザー]

なお補足ですが、実際はプログラムのコード全文はもっと長いのですが、
情報開示できる部分だけ抜粋しています。。。

バイナリデータの部分はICカードを読み取っていて
print(dd) をプログラムに追加した上で実行すると、問題なく任意の整数がターミナル上に表示されます。
プログラムはターミナルから実行しています。

[Umeeeh]

f-string の使い方を勘違いされてますね。

````````````````````````````````````````````````````
>>> sql = "insert into public.\"ID\"(\"aaa\") values(f'{dd}')"
>>> print( sql )
insert into public."ID"("aaa") values(f'{dd}')
````````````````````````````````````````````````````

このようになりますので、dd の値が展開出来ていません。
その結果、SQLの文法的に、エラーになってしまいます。

F-stringは、Pythonの文字列の先頭に f を入力します。
````````````````````````````````````````````````````
>>> dd = 12345
>>> fsql = f"insert into public.\"ID\"(\"aaa\") values({dd})"
>>> print( fsql )
insert into public."ID"("aaa") values(12345)
````````````````````````````````````````````````````
そうすると、このように dd の値に展開されます。

[退会済みユーザー]

回答ありがとうございます。とても詳しいですね。
コメントいただいた通りプログラムのコードを変更して、実行したところ無事に動作しました。

ちなみにですが、フェリカカードとかのIdm(英数字が混じった16桁)を一旦バイナリデータでダンプして
printで表示すると、 b'XXXXXXXXXXXXXXXX ' のように表示されます。

XXXXXXXXXXXXXXXX(16桁の英数字)の部分だけを、データベースの列に挿入したいのですが
いったんプログラムのｘにバイナリデータを代入し、values にｘを追記すると
x = (idmのバイナリデータ) →　これを追加
fsql = f"insert into public.\"ID\"(\"aaa\") values({x}) →　これも追加

これを実行すると以下のようなエラーが出ます。
psycopg2.errors.InvalidTextRepresentation: "2"は有効な2進数の数字ではありません
LINE 1: insert into public."ID"("aaa") values(b'XXXXXXXXXXXXXXXX(個人情報の為隠してます)')
　　　　　　 ^

データベースの列aaaの型がintegerなのに、そこにバイナリデータを入れようとしているから
エラーが出てるのではないかと推察するのですが

データベースの列aaaに　b'XXXXXXXXXXXXXXXX' の内部のXXXXXXXXXXXXXXXXを入れる方法はないでしょうか？　ご存知だったら教えていただきたいです。

[Umeeeh]

PostgreSQL の integer は 4バイトの整数型ですので、-2147483648 ～ [phone] の範囲の整数を入力可能です。

一方で、Pythonの b'...' はバイト列のリテラルで、データ型が異なりますので、期待通りには動かないでしょう。

基本的にデータの型をあわせてみてはどうでしょうか。

つまり、Byte列を整数値に変換して、DBに登録。DBから参照する際は、整数値からByte列に変換、という処理を入れてみると良いかもしれません。

バイト列から整数に変換するのは int.from_bytes() を使います。

詳しくはドキュメントをご覧いただいて、サンプルコードなどで動作を確認してもらえればと思います。
https://docs.python.org/ja/3/library/stdtypes.html

[退会済みユーザー]

ご回答ありがとうございます。もしお時間があったらでいいのですが

例えばテーブル(public.ID)のレコード(aaa)に、valuesの中の変数ddの値が
既に存在していたら、その行ごと消す。変数ddが存在していなかったらinsertする。といった挙動のものを作る時ってこのように作るものかと思い以下のようにコードを編集してみたのですが
またエラーが出たのですが分かりますでしょうか。

プログラムで編集した部分
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
with connection:
with connection.cursor() as cursor:
if exists(select * from public.ID where aaa = 'dd');
# レコードを消去
delete from public.ID where aaa = 'dd';
else:
# レコードを挿入
fsql = f"insert into public.\"ID\"(\"aaa\") values({dd})"
cursor.execute(fsql)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

おそらくまた宣言する型が合ってない？ような話かと思いますがどこが悪いでしょうか？
エラー内容は以下の通りです。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
if exists(select * from public.ID where aaa = 'dd');
　 　　　　　　　　　^
SyntaxError: invalid syntax
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

[Umeeeh]

エラーは「SyntaxError」ですので、構文エラーという意味ですね。
構文エラーは、書き間違っていて、プログラム構文として認識出来ない事を意味しています。

おそらく原因は、SQLは、文字列で表現する必要があるので、「"」や「'」で囲む必要があるんじゃないかなと思います。

他には、レコードがすでに存在しているかの判定方法が正しくないように思います。
判定するには、最初に、レコードが存在するか判定するためのSELECT分を cursor.execute() で実行してください。

次に、cursor.fetchone() で結果を取り出します。 取り出した結果が None の場合は、当該レコードが存在していません。None以外の場合は、レコードが存在しています。

レコードを削除する場合の記述方法も、正しくなさそうです。cursor.execute("delete from ...略") のように SQLを実行します。

Pythonで Postgres を操作するのは、psycopg2 というライブラリを使用します。このライブラリの基本的な使い方を、今一度、おさらいして置くといいかもしれません。サンプルコードを書くなどして、SELECT、INSERT、UPDATE、DELETE のやり方を一通り押させておくと、今後の役に立つと思いますので、ご参考になさってください。

公式ドキュメントはこちら
https://www.psycopg.org/docs/usage.html

日本語の解説を書かれている記事
https://python-work.com/postgresql-psycopg2/

[退会済みユーザー]

コメントありがとうございます。

プログラムを以下のように編集して実行してみたのですが、
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
# レコードが存在するかを確認
afs = ("SELECT * FROM public.\"ID\" where aaa = ?", "dd")
cursor.execute(afs)
result = cursor.fetchone()
if result == None:
# レコードを挿入
fsql = f"insert into public.\"ID\"(\"aaa\") values({dd})"
cursor.execute(fsql)
else:
# レコードを消去
abc = "delete from public.\"ID\" where aaa = dd"
cursor.execute(abc)
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

エラー内容はこんな感じなのですが、どこをどう直せばよいかわかりますでしょうか？？
cursor.execute(afs)
TypeError: argument 1 must be a string or unicode object: got tuple instead