Pythonでpsycopg2を使ってみる

Python Psycopg2 使い方

今日はPythonを使ってローカルのPostgreSQLのデータベースとつなげてみます。 今日の環境 psycopg2のインストール ではpipを使って、psycopg2をインストールしていきます。これは、PostgreSQLのデータベースのアダプターになります。 OKですね。 DBに接続する ではPgAdmin4、もしくはコマンドラインからデータベースと接続したテーブルがあることを確認しましょう。 接続したらちゃんと接続を閉じるのでそこまで書きます。 DBに接続できなかった時にエラーを出す方法 ダミーのデータを入れる 次にPythonでテーブルのデータを表示させる前にデータを入れておきます。 PostgreSQLのデータベースのデータを表示 では下記のコードで実際のデータベースのlistingテーブルのデータを表示させます。 では、コマンドプロンプトで試すとデータが表示されました! psycopg2でレコードを追加する では、PyhtonからPostgreSQLのデータベースにデータ(レコード)を追加しましょう。 下記のコードを追加しましょう。 レコードが追加されました! CSVファイルからデータベースに書き込む ではCSVファイルを使って、Postgresのターブルにデータをコピーさせます。 該当するカラムにデータを流し込む 先ほどのやり方だと、データベースのテーブルのカラムとCSVファイルのヘッダーの数が一致しないとシンタックスエラーとかになります。 これで解決します。 とりあえず、今日はこれくらいで! お疲れ様です。

PostgreSQLでクエリを書こう②

クエリの書き方

前回はクエリでCRUD(Create,Read,Update,Delete)を行いました。今日はその続きです。 リレーショナルなデータとは データ同士でつながっているものがありますね。例えば、レストランなら、会社の情報、メニューの情報、お客さんの情報は、それぞれ別のデータベースのテーブルに格納されますが、そのうちのカラムは他のテーブルに依存することになります。 下記に、その関係となる3つのタイプを紹介します。 Foreign key (外部キー)を作る では、FOREIGN KEYのあるテーブルを作ってみましょう。 CONSTRAINTはデータベースにこのカラムが特別であることを教えてあげます。慣習的に外部キーを使う場合はfk_で名称を付けてあげます。 FOREIGN KEYが先ほど説明した外部キーのことです。user_idが外部キーになることを教えてあげます。 REFERENCEはどのテーブルのどのカラムが対象になるか指定してあげています。 テーブルができました。 データを追加する データ外部キーの部分をuserテーブルidの1としてサンプルのデータを追加してみます。 データが入りました。 しかし、このままだとユーザー1が誰か分かりませんね。 JOINのコマンドでデータを結合して表示させてみます。 これで外部キーが結合されたデータが表示されました。

PostgreSQLでCRUDのクエリを書こう①

クエリの書き方

お疲れ様です。今日はPostgreSQLについて紹介します。PostgreSQLは1996年に市場に出てから多くの参考書や動画で色々説明しているので詳しい説明は省きます。 知っておくべきことを順番にまとめたので分からないところは自分で調べてください。 PostgreSQLがインストールされているところまで進めてもらえると分かりやすいです。 PostgreSQLってなに? リレーショナルデータベースのことです。 SQLのクエリを使ってデータベースを操作することができます。 テーブルって何? MSのエクセルやスプレッドシートをイメージしてください。 テーブルはカラム(行)とロウ(段)でできています。そこにデータが保管されます。 PgAdmin4 PgAdmin4はPostgreSQLが提供するUIツールです。ここでデータベースの操作を画面を使って操作できます。PostgreSQLのインストールの際に一緒にインストールしたかと思います。 スキーマ(Schema)って何? テーブルをまとめているものです。 なぜSQLを書く必要があるの もし、UIパネルから同じことができるならなぜSQLを書く必要があるのでしょうか? それは、PythonなどでPosgreSQLに指示をする際にコードで書いてあげないといけないからです。 これらを自動化するためにはやはりクエリを覚えておかないといけないのですね。 SQLを書く では、PgAdmin4からQueryToolを開きましょう。 ;(セミコン)を忘れない ;(セミコロン)はクエリの最後に必ずつけます。これでクエリが終わりであることを宣言してあげます。 これがないとエラーが出るので忘れないように。 テーブルを作る では、クエリツールを開いたら下記のようにクエリを書いていきます。プロフィールのテーブルを例とします。 SQLの世界では、CREATE TABLEやPRIMARY KEYなどのキーワードは大文字で書きます。(大文字でなくても実行できますが) テーブルに関係するもの、自分で命名したもの(カラム名など)は小文字で書きます。 この場合、id、name、email、password、ageはカラム名になります。その右にあるのがデータタイプです。 VARCHARはストリング(文字、テキスト)のデータタイプになります。(255)は最大で255文字まで入力できるという事になります。 INTは数字のデータになります。 指定した以外のデータはそれぞれのカラムに入力できないようになっています。 SERIALはデータが作成されるとデータベース側で自動で数字が+1されます。 PRIMARY KEYはユニークなデータで他のデータと被らないものになります。 クエリを実行する では、クエリツールの再生ボタンを押して、書いたクエリを実行します。 ※テーブルを作成するデータベースが選択されていることを確認してね。 サクセス(成功)の返事が返ってきました。ありがとう。 作成したテーブルを見る では、スキーマの下にTable(テーブル)があるのでそれをクリックして先ほど作成したProfileテーブルを右クリックしてViewを押し、表示させます。 データが空のテーブルが表示されました! テーブル名は慣習的にsの付かない単数形で記載することが多いです。なのでprofilesではなくprofileになります。(どっちでもいいんだけどね) ユーザーテーブルを作成する では、先ほどのProfileテーブルを削除します。単純にテーブルのアイコンを右クリックしてDelete/Dropを押すだけです。データがある場合はそれも全部消えてしまいます。 次にユーザーテーブルを同じクエリで作成してみます。 ここで気が付いてほしいのが”user”とダブルクオーテーションで囲っていることです。userやtableはpostgresのキーワードなので、このようにキーワードをストリングにしてあげないといけないのですね。 ちなみにシングルクォーテーションではエラーが出ます。 データを追加する では下記のようにデータを追加するクエリを書きます。 VALUESの後に、指定した順に、データを教えてあげます。ストリングのデータはシングルクオートで囲ってあげます。ここはダブルクォーテーションだとエラーが出ます。 で、実行してあげると同じようにテーブルにデータが追加されました。 IDも自動で追加されましたね。 データを表示させる では追加したデータを表示させる(選択する)クエリを書きます。 … Read more

WindowsにPostgreSQLをインストールする

PostgreSQLをインストールしよう

今日はPostgreSQLのデータベースをWindowsにインストールする手順を説明します。 では下記のサイトからインストーラーをダウンロードします。 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 特に指定がない場合は最新版のバージョン15をダウンロードしましょう。 ダウンロードできたら、.exeの実行ファイルをクリックしてインストールを始めます。 Nextを押します。 ファイルの保管場所を教えてあげます。通常はこのままでOKです。 Stack Builderは使わないかもしれないのでチェックをはじしてもOkです。 後は使うと思うのでそのままにしてNextを押します。 データを保管するディレクトリです。これもこのままNextです。 postgresユーザーのパスワードを設定します。 後からつかうので忘れないものに! 次にポートの設定です。 外からアクセスする場合にポート番号を指定してあげます。(データベースがアパートのビルなら、ポートはそのドア番号みたいなものです。) Localです。 これはデフォルトのままでOKです。 で、Next。 インストールを開始だっ。 インストールが始まります。。。 あとちょっとだね。 インストールが完了しました。 最後のチェックボックスは外してFinishして大丈夫です。 とくに追加でインストールしたツールとかはないので。 おまけ もし、コマンドラインからPSQLを実行したい場合は環境変数にPostgresのパスを登録してあげる必要があります。 それは調べてやってみてください。

Postgresのユーザーのパスワードを忘れた場合

PostgreSQL

サーバーで色々操作していてPostgresのデータベースで使うユーザーのパスワードを忘れた場合は下記の方法で新しいパスワードを再設定しなおしましょう。 今日の環境 Ubuntu22.04 PostgreSQL 14 今日の目的 Postgresの設定ファイルから仮でパスワードなしで接続できるようにします。 その後にコマンドから、新しいパスワードを設定しなおします。 最後にパスワードでデータベースに接続させるように再設定します。 ※作業にリスクがあると感じた場合はバックアップを取るなり自己責任で操作をしてください。 pg_hba.confを探す Postgrtesをインストールした後にpg_hba.confのコンフィグファイルも作成されます。ここでユーザーがどのようにデータベースに接続できるようにするか設定できます。 通常は下記のファイルパスにあります。 PostgreSQLのバージョンによりファイルの場所が少し変わるので注意します。 /etc/postgresql-9.1/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf rootで入っている場合はpwdのコマンドでどこにいるか確認しましょう。 cd のコマンドとlsのコマンドを使いながらPostgresの場所を探します。 pg_hba.confを見つけました。 pg_hba.confのバックアップをとる では下記のコマンドでオリジナルのファイルをコピーしておきます。 ※パスワードをリセットしたらこっちに戻します。 ファイルがコピーで来たことを確認しました。 認証方法を変更する ではテキストエディタを使ってこのpg_hba.confを開きます。 もともとはこんな感じになっていますね。 これを下記のように変えます。 Ctl +X で抜け出し Yで上書き、エンターでファイル名をそのままにします。 Postgresのサーバーをリスタートする ではファイルを更新しただけではコンフィグを読み取ってくれないのでPostgresのサービスを再起動します。 postgresのユーザーで接続する ここでコンフィグをtrustにしたのでどのユーザーでもパスワードなしで接続できるようになりました。 ではpostgresのユーザーで接続してみます。 パスワードなしで入れました。 パスワードを更新する では更新したいユーザーのパスワードを下記のコマンドで更新します。 元のコンフィグファイルに戻す パスワードなしで誰でも接続できてしまうのは危険なので、元のファイルに戻します。 Postgresサーバーのリスタート 同じようにPostgresのシステムを再起動します。 これでpsqlのコマンドでパスワードが聞かれたらもとに戻っているという事ですね。 ではお疲れ様です。

DB BrowserでSQLiteのデータベースを作ろう

SQLiteの使い方

日本のプログラマーの皆さんこんにちは。今日はDB Browserを使ってSQLiteのデータベースを管理していく方法を紹介します。 エクセルのような感覚で始められるのでリレーショナルデータベースを使ったことがない人には得にお勧めです。 SQLiteってなに? SQLiteは下記のような長所があります。 オープンソースです。(ソースコードが一般に公開されています。) データベースの一種です。(MySQLとかPostgreSQLとかと同じ) ポータブルです。(OSに依存せず、DBファイルにそのままアクセスすることができます。) SQLiteの欠点はこのようなものがあります。 DBサーバー無しで使えるのでネットワークからアクセスできない。 大規模なアプリケーションには向いていない。 データサイズに制限がある。 本日の環境 Windows10(MacOSでも、Linuxでも問題ありません。) 本日の目的 今日も目的はSQLiteのデータベースがどのような仕組みで作れるのか見ていくことが目的になります。慣れてきたら、次回以降はSQL(クエリ)を書いてデータベースを操作できるようにしたいですね。 SQLite DB Browserをダウンロード では、SQLiteのデータベースの作業をするために下記の公式リンクからSQLite Browserをダウンロードしましょう。 https://sqlitebrowser.org/dl/ では、ダウンロードページに行ったらWindowsのポータブル版をダウンロードしましょう。 windowsにインストールしちゃいたい人は64bitのインストーラーをダウンロードしてもOKです。MacOCの人はMacの方からダウンロードしてください。 DBBrowserを展開 ではダウンロードした.exeファイルをダブルクリックしてファイルを展開しましょう。 Nextをクリックします。 ポータブル版のファイルをおきたいファイルパス(場所)を指定します。これだとダウンロードに展開されることになります。 では、Instalをクリック。 Finishをクリックで完了です。 SQLiteDatabaseBrowserを実行 では先ほど展開したファイルを開くと.exe実行ファイルがあるのでそれをクリックしてアプリを起動します。 データベースを作成 では、NewDatabaseのボタンを押してデータベースを作成します。 それからデータベース名を決めて、Saveします。名前は後から変更できるのでご心配なく。 Addをクリックしてカラム(Column)を追加します。 まずは、テーブル名をInventoryとかにしておきましょう。 今回は在庫管理業務で使えそうなデータベースを作ってみましょう。 では下記のようにカラムを追加していきましょう。 Nameはカラムの名前です。クエリで指定する際に使います。 Typeはデータのタイプになります。Textは文字のデータです。(数字を入れると文字データの数字として扱われます。プログラミングを学習する際に習いますね。) INTEGERは整数のことになります。 その横にあるチェックボックスを説明します。 NN NotNull(Null=データがないことを許さないという事です。) PK プライマリーキー(他のデータと被らない行を区別するためのキーです。) AI auto increment (自動で数字が足されていくことです。) U ユニーク(PKと同じですが、PKはテーブルに1つしか設定できないことに対しUは複数設定可能です。 では、今回設定した内容を説明します。 idはプライマリーキーでそれぞれのデータの行を区別するために設定しておきます。またAIにチェックが入っているので新しいデータが作成されるたびに数が1,2と増えていくようになります。 最後にadded_date(追加日)のDefault(デフォルト)の部分に下記のテキストを追加します。 CURRENT_TIMESTAMP デフォルトで初期値を設定することができます。もし何もインプットがない場合にデフォルトの値を記録することになります。 この場合は現在の日時を自動で入力するように指示したことになります。 では右下のOKボタンを押してDB(データベース)が作成されました。 … Read more