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