PostgreSQLをPythonで操作するには、pycopg2が使えます。
ここではJupyterでの記述を想定していますが、コマンドラインでも同様です。
ここではJupyterでの記述を想定していますが、コマンドラインでも同様です。
python 3.6
psql (PostgreSQL) 9.5.13
psycopg2 2.7.5
Pycopg2接続
接続に関しては別ページに書いていますので下記を参照のこと。
Ubuntu:Python PostgreSQLをUbuntuで使う インストール〜psycopg接続Windows:Python PostgreSQLをWindowsで使う。インストール〜接続
この例での設定はdb名:my_database、user名とpassはpostgresにしています。
juu#!/usr/bin/python
import psycopg2
conn_string = "host='localhost' dbname='my_database' user='postgres' password='postgres'"
print ("Connecting to database\n ->%s" % (conn_string))
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
print ("Connected!\n") Connecting to database
->host='localhost' dbname='my_database' user='postgres' password='postgres'
Connected!基本的には、cursor.execute('SQL command') を実行すればSQL操作ができます。
注意点など
・passwordは各自変更してください
・失敗したときは必ずロールバックする失敗したあと、もう1度実行するとabortされているというエラーがでます
InternalError: current transaction is aborted, commands ignored until end of transaction blockconn.rollback()保存するにはコミット。ロールバックすると、ここまで戻ります。
conn.commit()・SQLコマンドは、cursor.execute()の中に書きます。
クォートはシングルクォートでもダブルでもOKです。
ただし、where=stringで文字列を与える場合、文字列は必ずシングルクォートにします。
・大文字小文字の区別はありません。SQLコマンドは大文字にするのが慣例になっていますが小文字でも動作します。大文字小文字の区別をつけるときはダブルクォートで囲みます。 テーブルの設計次第ですが、タイポが増えるので区別なしの方が楽ではないかと個人的に考えています。
・プレースホルダを使うときに"{0}"といったクォーテーションが増えるので、テーブル名は大文字を使わない方がよいと思います。
テーブルの作成、レコード挿入、検索、更新、削除、リセット
テーブル名: table1
項目:text1 文字列、time タイムスタンプ、num1,num2は整数とします
# timestamptz はタイムゾーン付きのタイムスタンプです。
cursor.execute('CREATE TABLE table1 (text1 text, time timestamptz, num1 integer, num2 integer)')プレースホルダ%sを使ってレコードを入れます
cursor.execute('INSERT INTO table1 (text1 , time, num1, num2) VALUES(%s, %s, %s, %s)',('Taro','2000-08-01',42, 1000))
cursor.execute('INSERT INTO table1 (text1 , time, num1, num2) VALUES(%s, %s, %s, %s)',('Jiro','2010-08-01',90, 130))SELECTで確認します。fetchしないとデータは出てこないので注意。
cursor.execute('SELECT * FROM table1')
cursor.fetchall()[('Taro',
datetime.datetime(2000, 8, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=540, name=None)),
42,
1000),
('Jiro',
datetime.datetime(2010, 8, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=540, name=None)),
90,
130)]WHERE で選択するときはプレースホルダ%sを使うとSQLインジェクション対策になります。(PHPとPosgreSQLの例ですが、こちらに詳しく書かれています
https://lets.postgresql.jp/documents/tutorial/with_php/against_sql_injection/placeholder/1)
プレースホルダが1つのときは、下の (42,) のように、タプルにします。
cursor.execute('SELECT * FROM table1 WHERE num1 = %s',(42,))
cursor.fetchall()これは(42,)が(42)になっているのでエラーになります。
cursor.execute('SELECT * FROM table1 WHERE num1 = %s',(42))
cursor.fetchall()
TypeError: 'int' object does not support indexingレコードを更新する
名前がTaroの行のnum1 を 50に更新する
cursor.execute('UPDATE table1 SET num1 = %s WHERE text1 = %s', (50, 'Taro'))レコードを削除する
cursor.execute('DELETE FROM table1 WHERE num1 = %s',(42,))
cursor.execute('SELECT * FROM table1')
cursor.fetchall()プライマリキー
カラム(列)に同じ値が入らないようにするために主キー(PRIMARY KEY)を設定できます。主キーはテーブルに1つです。
cursor.execute('CREATE TABLE table1 (text1 text, time timestamptz PRIMARY KEY, num1 integer, num2 integer)')Pandas データフレームにSQL結果を入れる
Pandas.DataFrame.read_sql_query(SQL, conn)
SQL: str SQL文
conn: このページでは conn=psycopg2.connect(conn_string)
import pandas as pd
df = pd.read_sql_query(
'SELECT * FROM table1',
conn
)
dfにSQLの結果が入ります。
その他のコマンド
存在するデータベースの名前を出力するcursor.execute("SELECT datname FROM pg_database WHERE datistemplate=false")
cursor.fetchall()テーブルの列名を見る。cursorが当たっていないとエラー
[desc[0] for desc in cursor.description]Postgres DBに存在するテーブル名を全て出力
cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
cursor.fetchall()cursor.execute("SELECT tablename as objectname FROM pg_tables WHERE schemaname = 'public'")
cursor.fetchall()データベース名を調べる
cursor.execute("SELECT datid, datname FROM pg_stat_database;")
cursor.fetchall()Postgresでは複数の接続（multiconnection）ができますが、その接続数を調べる方法。
cursor.execute('SELECT sum(numbackends) FROM pg_stat_database;')
print('number of conn', cursor.fetchall() )
0 件のコメント:
コメントを投稿