Python3でpostgresqlを使う

PostgreSQLの準備 Link to heading

インストール Link to heading

# yum install postgresql-server # postgresql-setup initdb

設定 Link to heading

/var/lib/pgsql/data/pg_hba.conf

host all all 127.0.0.1/32 md5

起動 Link to heading

# systemctl start postgresql
# systemctl enable postgresql

ユーザ作成 Link to heading

# su - postgres
$ psql
CREATE ROLE dbuser WITH LOGIN PASSWORD 'password';

データベース作成 Link to heading

CREATE DATABASE dbname OWNER dbuser ENCODING 'utf8';

Python用モジュールをインストール Link to heading

pip install psycopg2

CREATE文 Link to heading

conn = psycopg2.connect("host=127.0.0.1 port=5432 dbname=dbname user=dbuser password=password")
dict_cur = conn.cursor()
dict_cur.execute("CREATE TABLE table (id serial PRIMARY KEY, name varchar(23), password varchar(255))")
conn.commit()
dict_cur.close()
conn.close()

SELECT文 Link to heading

conn = psycopg2.connect("host=127.0.0.1 port=5432 dbname=dbname user=user password=password")
dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
dict_cur.execute("select name from table where (name)=(%s)", (name,))
for row in dict_cur:
  if row['name'] == name:
    flag = True
    break
dict_cur.close()
conn.close()

INSERT文 Link to heading

conn = psycopg2.connect("host=127.0.0.1 port=5432 dbname=dbname user=user password=password")
dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
dict_cur.execute("insert into table (name, password) values (%s, %s)", (name, password))
conn.commit()
dict_cur.close()
conn.close()