Category: psql

【Python】psycopg2で複数のsql文を同時に実行する

psycopg2 の execute　は、commitの前に連続して実行できる。そのため、以下のように、sql文をforループで回すことができる。

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv('../.env')
PSQL_PASSWORD = os.getenv('PSQL_PASSWORD')

connection = psycopg2.connect(
    host='localhost',
    user='postgres',
    password=PSQL_PASSWORD
)
cur = connection.cursor()

sql1 = "CREATE TABLE IF NOT EXISTS person1 ( \
            id SERIAL PRIMARY KEY, \
            name TEXT NOT NULL, \
            data BYTEA \
        );"
sql2 = "CREATE TABLE IF NOT EXISTS person2 ( \
            id SERIAL PRIMARY KEY, \
            name TEXT NOT NULL, \
            data BYTEA \
        );"
sqls = [sql1, sql2]

for sql in sqls:
    cur.execute(sql)

connection.commit()
cur.close()
connection.close()

$ python3 migration.py

# \dt
List of relations
Schema | Name | Type | Owner
——–+———-+——-+———-
public | person1 | table | postgres
public | person2 | table | postgres

なるほどー

【Rust】tokio_postgresの接続を外出しにする

「ＲｕｓｔによるＷｅｂアプリケーション開発(豊田優貴著)」を読むと、axumのルーティングでwith_state(conn_pool)で接続情報を持たせてますね。まぁそういう方法もありかと思いますが、今回は、関数で呼び出す方法を採用することにした。
参考のソースコード:
https://github.com/kingluo/tokio-postgres-hello-world/blob/master/src/main.rs

async fn psql_connect() -> Result<Client, Box<dyn std::error::Error>>  {
    let (client, connection) = tokio_postgres::connect("host=localhost user=postgres password=hogehoge", NoTls).await?;

    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });
    Ok(client)
}

async fn check_users()-> Result<(), Box<dyn std::error::Error>>  {
    
    let client = psql_connect().await.unwrap();
    for row in client.query("SELECT id, username, password From users", &[]).await? {
        let id: i32 = row.get(0);
        let name: String = row.get(1);
        let password: String = row.get(2);

        println!("{} {} {}", id, name, password);
    }
    Ok(())
}

PostgreSQL シーケンスの利用方法

### シーケンスとは
シーケンスとは連番を払い出してくれるもの。テーブルのIDなどを挿入する際に使用することができる

### シーケンスの使用例
テーブルのIDにシーケンスを使用する例
(1)シーケンスを作成する
(2)シーケンスをテーブルのIDとして挿入する

### シーケンスは以下のように作成
オプションには一番最初に払い出す番号をしているSTARTオプション(START 10)がある。開始番号を指定しなかった場合は1となる

### (1)シーケンスを作成する
CREATE SEQUENCE test_seq;

### (2)シーケンスの確認
testdb=# \d test_seq;
Sequence “public.test_seq”
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
——–+——-+———+———————+———–+———+——-
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1

### (3)シーケンスをテーブルのIDとして挿入する
testdb=# CREATE TABLE test_table (id int, name varchar(20));
CREATE TABLE
testdb=# INSERT INTO test_table VALUES (nextval(‘test_seq’), ‘suzuki’);
INSERT 0 1
testdb=# INSERT INTO test_table VALUES (nextval(‘test_seq’), ‘ito’);
INSERT 0 1
testdb=# INSERT INTO test_table VALUES (nextval(‘test_seq’), ‘sato’);
INSERT 0 1
testdb=# SELECT * FROM test_table;
id | name
—-+——–
1 | suzuki
2 | ito
3 | sato
(3 rows)

### (4)シーケンスの確認
testdb=# select currval(‘test_seq’);
currval
———
3
(1 row)

### シーケンスの値をリセット
select setval(‘test_seq’, 1);

なるほど、auto_incrementとは別に値を持つのね

sudo passwd postgres

[vagrant@localhost ~]$ sudo find / -name pg_hba.conf
/var/lib/pgsql/9.6/data/pg_hba.conf
[vagrant@localhost ~]$ sudo cat /var/lib/pgsql/9.6/data/pg_hba.conf
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

[vagrant@localhost ~]$ sudo passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
よくないパスワード: 辞書の単語に基づいています
新しいパスワードを再入力してください:
passwd: 全ての認証トークンが正しく更新できました。
[vagrant@localhost ~]$ su – postgres
パスワード:
-bash-4.1$ psql
psql (9.6.15)
“help” でヘルプを表示します.

postgres=# \q
-bash-4.1$ exit
logout

来た！事前準備にどれだけ時間かかってるんじゃ、という感じ。

postgresをインストールする

sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm
sudo yum update
sudo yum install postgresql96 postgresql96-server postgresql96-libs
[vagrant@localhost ~]$ rpm -qa | grep postgres
postgresql96-server-9.6.15-1PGDG.rhel6.x86_64
postgresql96-9.6.15-1PGDG.rhel6.x86_64
postgresql96-libs-9.6.15-1PGDG.rhel6.x86_64

[vagrant@localhost ~]$ service postgresql-9.6 initdb
データベースを初期化中: mkdir: ディレクトリ `/var/lib/pgsql’ を作成できません: 許可がありません
[vagrant@localhost ~]$ sudo service postgresql-9.6 initdb
データベースを初期化中: [ OK ]

[vagrant@localhost ~]$ sudo service postgresql-9.6 start
postgresql-9.6 サービスを開始中: [ OK ]
[vagrant@localhost ~]$ service postgresql-9.6 status
postgresql-9.6 (pid 20665) を実行中…
[vagrant@localhost ~]$ chkconfig postgresql-9.6 –list
postgresql-9.6 0:off 1:off 2:off 3:off 4:off 5:off 6:off
[vagrant@localhost ~]$ chkconfig postgresql-9.6 on
この操作を実行するためには権限が十分ではありません。
[vagrant@localhost ~]$ sudo chkconfig postgresql-9.6 on
[vagrant@localhost ~]$ chkconfig postgresql-9.6 –list
postgresql-9.6 0:off 1:off 2:on 3:on 4:on 5:on 6:off

[vagrant@localhost ~]$ su – postgres
パスワード:
su: パスワードが違います
なにいいいいいいいいいいいい