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

実現したいこと

作成したデータベースへ値をインポートしたいです。仮にインポートする値は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が入っておらず、ターミナルを見ても特に変化が見られません(プログラムが終わっても進んでもないように見える)。

エラーメッセージ

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

該当のソースコード

Python3.0
1import RPi.GPIO as GPIO 
2import time
3import binascii
4import nfc
5import os
6import datetime
7import csv
8import sys
9sys.path.append('/home/pi/.local/lib/python3.7/site-packages')
10import psycopg2
11
12# データベースに接続 
13connection = psycopg2.connect(host='Aを入力',
14                                               user='postgres',
15                                               password='Bを入力',
16                                               database='Cを入力')
17
18# Postgres SQLへのインサート
19with connection:
20    with connection.cursor() as cursor:
21        # レコードを挿入
22        sql = "insert into Schemas.C(ID) values (133)";
23        cursor.execute(sql)
24
25        # コミットしてトランザクション実行
26        connection.commit()
27
28        # 接続を閉じる
29        cursor.close()
30        connection.close()
31
32sys.exit()

試したこと・調べたこと

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

上記の詳細・結果

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

補足

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

satokei

2024/04/19 11:20

> sql = "insert into Schemas.C(ID) values (133)"; Schemas.C(ID) とは何でしょうか？画像の select文を見ると、insert文は以下のようになるかと。　insert into public.ID(ID) values (133)

sazi

2024/04/20 12:51

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

myao_12

2024/04/24 04:44

コメントありがとうございました。プログラムの中身をコメントいただいた通りの 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

2024/04/24 05:06

localhostで指定する場合はIPは不要です。データベース名の指定が相変わらずありません。 host=loalhost, dbname=c localhostに接続するなら、postgresql.confもpg_hba.confもデフォルトから変更する必要はありません。

myao_12

2024/04/24 05:29

コメントと回答ありがとうございます。プログラムの database='C' →　　dbname='C'　　　に変更してやってみました。やはり同じエラーがでました。あと、localhostで接続したいわけではなく、最初にも記載した通りデータベースサーバが置いてあるPCのhostnameがlocalhostです。プログラムを実行しているデータベースサーバに接続しにいきたいPCはまた別のPCです(最初の実現したいことの欄に記載)。

sazi

2024/04/24 06:10

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

行動規範の内容に同意します

回答1件

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

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

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

変更前

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

変更後

python
1        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)

投稿2024/04/20 09:20

総合スコア1396

2024/04/24 04:42

2024/04/24 23:46

＞ could not translate host name "接続先のIPアドレス/localhost" to address: ＞ Name or service not known 「そんなホストねえよ」って言われてますので、接続情報が間違ってるんだと思います。 "接続先のIPアドレス/localhost" の部分は、実際にはどんな感じで書かれてるのでしょうか。見せられない情報があれば、「*」などでマスクしていただいて、書いてもらえると、イメージが湧くので助かります。おそらく、host に指定したデータベース・サーバーのホスト名が、正引きできない状態なのかもしれません。正引きは、お使いの環境のDNSサーバーに設定するか、クライアント側の /etc/hosts などに記述する必要がありますので、ご確認ください。また、PostgreSQLが正常に起動していることもご確認ください。

myao_12

2024/04/25 02:15

回答ありがとうございました。 "接続先の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のドライバか何かをインストールしなければいけないのでしょうか。分かる範囲で良いのでご教示いただけますと幸いです。

take88

2024/04/26 03:19

まずは、localhostを消したことで、接続の問題は解消されて、現象が変わったようですね。現象が変わるのは前進している証拠なのでポジティブです。さて、「リレーション"public.id"は存在しません」というエラーは、私の回答の内容が、良くなかったためだと思います。回答を書き直しますので、そちらを参考に、INSERT文を修正してみてもらえますか。

myao_12

2024/04/26 04:23 編集

お忙しいところ回答ありがとうございました。コメント頂いた点を反映してコード全文を以下に書き換えて試してみましたが、やはりだめでした。～～～～～～～～～～～～コード全文～～～～～～～～～～～～～～～～～～～～～～ 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で)。

take88

2024/04/26 11:04

@myao_12 すみません、私の回答が間違っていました。 Pythonでは、`"～"`までが文字列になります。文字列の中に「`"ID"`」を追加しちゃったので、`"insert into public."` という文字列になってしまい、その後に `ID`と続いたのでSyntax Errorになったんだと思います。文字列の中に「"」記号を記述するには、エスケープ文字を書かなければなりません。回答のコードを修正したのでご確認ください。

myao_12

2024/05/01 07:03

回答ありがとうございます。試したところ問題が解決しました！大変ありがとうございました。最後にもうひとつ質問で申し訳ないのですが例えば、その人固有(人によって変わる)の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) ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～データベースは立ちあげておりますが、その人固有の整数はデータベース上に書き込まれていません。どうしたらいいのか回答いただけますと幸いです。

take88

2024/05/01 12:19 編集

" ～ " の部分は文字列になりますので、「insert～(cc)」という文字列と認識されます。cc は変数ですが、文字列の中に変数名を書いてもダメなんですね。 SQLにパラメータを埋め込むやり方は複数あります。次のURLを確認していただいて、それぞれやり方を練習してみてもらえれば、良いんじゃないかなって思いますので、ご覧になってください。 https://www.psycopg.org/docs/sql.html

行動規範の内容に同意します