PostgreSQLの準備

インストール

# yum install postgresql-server
# postgresql-setup initdb

設定

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

host    all             all             127.0.0.1/32            md5

起動

# systemctl start postgresql
# systemctl enable postgresql

ユーザ作成

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

データベース作成

CREATE DATABASE dbname OWNER dbuser ENCODING 'utf8';

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

pip install psycopg2

CREATE文

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文

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文

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()