Pythonでpsycopg2を使ってみる

今日はPythonを使ってローカルのPostgreSQLのデータベースとつなげてみます。

今日の環境

• windows10（OSは何でもOKです。）
• PostgreSQLのDBにアクセスできる状態
• Python3

psycopg2のインストール

ではpipを使って、psycopg2をインストールしていきます。これは、PostgreSQLのデータベースのアダプターになります。

pip install psycopg2

OKですね。

DBに接続する

ではPgAdmin4、もしくはコマンドラインからデータベースと接続したテーブルがあることを確認しましょう。

接続したらちゃんと接続を閉じるのでそこまで書きます。

import psycopg2
  
conn = psycopg2.connect(
            host='127.0.0.1', 
            database="postgres",
            user='postgres', 
            password='postgres', 
            # port='5432'
)
  
conn.close()

DBに接続できなかった時にエラーを出す方法

import psycopg2
import sys

try:
    conn = psycopg2.connect(
                host='127.0.0.1', 
                database="postgres",
                user='postgres', 
                password='postgres'
                # port='5432'
    )
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')

ダミーのデータを入れる

次にPythonでテーブルのデータを表示させる前にデータを入れておきます。

INSERT INTO "listing" (title, price, time_left) VALUES ('Subaru', '$80000', '5 hours')

PostgreSQLのデータベースのデータを表示

では下記のコードで実際のデータベースのlistingテーブルのデータを表示させます。

import psycopg2
import sys

try:
    conn = psycopg2.connect(
                host='127.0.0.1', 
                database="postgres",
                user='postgres', 
                password='postgres'
                # port='5432'
    )
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')

# 仮のワークステーション（メモリ）のこと
cur = conn.cursor()

#クエリを実行させる 
select_query = "SELECT title, price, time_left FROM listing"
cur.execute(select_query)
print(select_query)

rocords = cur.fetchall()
for row in rocords:
    print("Title = ", row[0], )
    print("Price = ", row[1])
    print("Time Left  = ", row[2], "\n")

cur.close()
conn.close()

では、コマンドプロンプトで試すとデータが表示されました！

psycopg2でレコードを追加する

では、PyhtonからPostgreSQLのデータベースにデータ（レコード）を追加しましょう。

下記のコードを追加しましょう。

insert_query = """ INSERT INTO listing (title, price, time_left) VALUES (%s,%s,%s)"""
record_to_insert = ('Ford', '$10,000', '7 Days')
cur.execute(insert_query, record_to_insert)
conn.commit()

レコードが追加されました！

CSVファイルからデータベースに書き込む

ではCSVファイルを使って、Postgresのターブルにデータをコピーさせます。

import psycopg2
import sys

try:
    conn = psycopg2.connect(
                host='127.0.0.1', 
                database="postgres",
                user='postgres', 
                password='postgres'
    )
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')

cur = conn.cursor()
csv_path = 'listings.csv' 

with open(csv_path, 'r', encoding="utf-8-sig") as f:
    next(f) # Skip the header row.
    cur.copy_from(f, 'listing', sep=',')
conn.commit()

cur.close()
conn.close()

該当するカラムにデータを流し込む

先ほどのやり方だと、データベースのテーブルのカラムとCSVファイルのヘッダーの数が一致しないとシンタックスエラーとかになります。

これで解決します。

import psycopg2
import sys
import os
dirname = os.path.dirname(__file__)
csv_path = os.path.join(dirname, 'listings.csv')

try:
    conn = psycopg2.connect(
                host='127.0.0.1', 
                database="postgres",
                user='postgres', 
                password='postgres'
    )
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')

cur = conn.cursor()

sql = ('''COPY listing(title, price, time_left)
FROM ''' + "'" + csv_path + "'" + ''' 
DELIMITER ','
CSV HEADER;'''
)

cur.execute(sql)
conn.commit()

cur.close()
conn.close()

とりあえず、今日はこれくらいで！

お疲れ様です。