postgresqlにcopyでcsvデータを反映させる
copyを実行する方法
psqlコマンドでデータベースに接続した状態でcopyコマンドを実施する
psycopg2でpostgresqlに接続し、copy_fromメソッドを使ってcopyを実施する。
copy_expert(), execute()
できないこと、注意点1
csvファイルの指定カラムのみをテーブルにインポート
上記の記事を参考に色々試してみた。具体的には複数の列が存在するcsvファイルから特定の列のみコピーすることを試みた。上記の記事はテーブルにある複数のカラムから特定のカラムのみをcsvファイルのデータでコピーするということだと判断した。つまりcopyコマンドを実施する際にcsvファイルに含まれるデータはすべてコピー対象になってしまうのであって、csvファイルに含まれるデータから特定のデータのみを選んで反映させることではない、ということだ。
できないこと、注意点2
utf8には種類がありbomありとbomなしがある。bomありで保存することでintegerやdouble precisionなどのコピーが上手く出来ない事がわかった。一方charはコピーが上手く行った。windowsのメモ帳やエクセルでcsvを作成するときは注意すること。
psqlコマンドでデータベースに接続した状態でcopy
#データベースに接続する psql -U ユーザー名 -d データベース名 #ex psql -U postgres -d postgres #copyコマンドの実行 \copy table名(userid, user_name) from 'csvのパス' with csv encoding 'utf8' ; #ex \copy my_test_table(userid, user_name) from 'C:\Users\USER\Desktop\sushi.csv' with csv encoding 'utf8' ;
psycopg2でpostgresqlに接続し、copy_fromメソッドを使う
psycopg2で接続しcopy_fromメソッドでコピーする方法がある。
http://initd.org/psycopg/docs/cursor.html#cursor.copy_from
このメリットはプログラムの流れに沿ってcopyを実行することができることだ。しかしながらデメリットとしてcsvヘッダーを無視してコピーする事ができない事がある。コピーする前にヘッダーを削除してcopyを実行する事が必要だ。
#サンプル
import psycopg2
conn = psycopg2.connect(user="postgres", port=5432, dbname="postgres")
cur = conn.cursor()
f = open('C:\\Users\\USER\\Desktop\\sushi.csv', mode='r', encoding='utf-8')
cur.copy_from(f, 'my_test_table', sep=',', columns=('serid', 'user_name'))
conn.commit()
f.close()
copy_expert
実際にpsqlに接続してcopyコマンドを実施するのと同じ機能を提供するらしい
ある座標点からｘkm以内に存在するデータオブジェクトをmapに表示したい
PostGISを使う場合はST_DWithin()関数を使えばよいだろう。
ではgeodjangoではどうやって特定の範囲内のデータオブジェクトを取得するか？
psycopg2を使ってデータオブジェクトのみ取得することももやり方としてはできるだろう。
しかしここではgeodjangoの備え付けの機能を使うとしたらどのように書くかを記しておく。
参考：https://docs.djangoproject.com/en/2.2/ref/contrib/gis/tutorial/#automatic-spatial-transformations
https://docs.djangoproject.com/en/2.2/ref/contrib/gis/db-api/#distance-lookups
特定の範囲内に存在するデータオブジェクトはdistance_ltを使う
djangoではデータオブジェクトはfilterを使って取得してきた。icontainsとか使うやり方でdistance_ltやdistance_lteを使えば特定の範囲内のデータオブジェクトは取得できる。
問題はどのように使うかだ。ここで引っかかってしまったのできちんと記録しておく。
ドキュメントの例文では以下のように書いてある。
qs = SouthTexasCity.objects.filter(point__distance_lte=(pnt, D(km=7)))
はじめにこの意味を確認するとpntから7km以内のSouthTexasCityのクエリ結果を示している。Dはfrom django.contrib.gis.measure import Dで別途インポートする必要がある。そして当初pntはデータオブジェクトを用いるのだろうと思っていた。しかしながら使うのはデータオブジェクトではなくgeometryのオブジェクトで、GEOSGeometryやPointクラスのインスタンスでなければならないことが分かった。GEOSGeometryやPointについては現状違いがわからない。少なくともpointのデータを作ることに関しては両者どちらでも作成できると思われる。
GEOSGeometryの使い方
from django.contrib.gis.geos import GEOSGeometry, Point my_place = UserPointModel.objects.get(id=1) point = 'POINT({lon} {lat})'.format(lon=my_place.point.x,lat=my_place.point.y) pnt = GEOSGeometry(point, srid=4326)
Pointの使い方
from django.contrib.gis.geos import GEOSGeometry, Point my_place = UserPointModel.objects.get(id=1) pnt = Point(my_place.point.x, my_place.point.y, srid=4326)
本題のdistance_lteの使い方
サンプル
class MapsView(View):
def get(self, request):
my_place = UserPointModel.objects.get(id=1)
pnt = Point(my_place.point.x, my_place.point.y, srid=4326)
all_objects = UserPointModel.objects.filter(point__distance_lte=(pnt, D(km=2)))
context = {"all_objects":all_objects}
return render(request, "maps/list.html",context)
unittestのsys.exit()の抜け方
def tashizan(a,b):
try:
return int(a)+int(b)
except ValueError:
print("a,bは整数ではなく文字列の可能性があります")
sys.exit(1)
import unittest , tashizan
class TestTashizan(unittest.TestCase):
def test_tashizan_1(self):
expected = 8
actual = tashizan(2, 6)
self.assertEqual(expected, actual)
def test_tashizan_2(self):
expected = 8
actual = tashizan(6, "2")
self.assertEqual(expected, actual)
def test_tashizan_3(self):
with self.assertRaises(SystemExit):
tashizan("a", "6")
if __name__ == "__main__":
unittest.main(exit=False)
どうやらエラーのときはwith を使ってassertRaises()を使い、エラーの出る使い方をくるむようだ。
python csvファイルを結合
csvを結合する方法
- ライブラリpandasを使う方法
- open関数を使う方法
ライブラリpandasを使う方法
import pandas as pd csv_list = ['csv1_path', 'csv2_path', 'csv3_path', 'csv4_path'] pd_list = [pd.read_csv(file) for file in csv_list] df = pd.concat(pd_list) df.to_csv("hoge.csv", index=False, encoding="utf-8")
この方法は時間がかかる恐れがある。以下の方法もあるので試してみる。どちらが速いか分かったら追記しようと思う。
open関数を使う方法
下記の方法はpandasを使った場合と同様、一番目のファイルのヘッダーは残し、追加するファイルのヘッダーは削除して追加するやり方になる。ヘッダーを排除するためにreadlines()で各行をリスト化し、ヘッダーを削除したリストとして[1:-1]でスライスしている。この方法を使うとpandasより速くできるか検証が必要である。
merge_csv_list = ['csv1_path', 'csv2_path', 'csv3_path', 'csv4_path'] for n in range(0,len(merge_csv_list)): if n == 0: base_file = open(merge_csv_list[0], "a", encoding="utf-8") continue else: add_file = open(merge_csv_list[n], "r", encoding="utf-8") base_file.write(",".join(add_file.readlines()[1:-1])) add_file.close() continue base_file.close()
geodjangoの環境構築
https://docs.djangoproject.com/ja/2.2/ref/contrib/gis/install/ geodjangoを使うには、python,django,空間データベース、地理空間ライブラリが必要。 PostgreSQLで空間データベースを使うには、ライブラリであるGEOS,GDAL,PROJ.4,PostGISが必要である。
macでのgeodjangoを構築する
https://docs.djangoproject.com/ja/2.2/ref/contrib/gis/install/#macos
https://homata.gitbook.io/geodjango/qian-zhun-bei/install#mac-osx-1
windows postgresqlのデータのディレクトリを変更したい
postgresqlをwindowsにインストールする場合
windowsの場合はインストーラーを起動するとdataの置き場所をウィザードに従って自分で指定することができた。 しかしながらインストール後にデータディレクトリを変更（新たにデータクラスタも作成）する場合にはどのように実行するのかわからない。どうすればよいのか
memoを残しておく
まずservices smcでpostgresqlを見ると、実行ファイルパスは以下のようになっていた。
"C:\Program Files\PostgreSQL\11\bin\pg_ctl.exe" runservice -N "postgresql-x64-11" -D "C:\pgdata\11" -w
で、 -Dはデータの読込み先を表す。またpg_ctl.exeはpostgresqlのサービスの起動、停止、再起動を実行するためのコマンドである。だからdataの読込み先を新しく作ったdataの場所に変更してpostgresqlを起動しなおせば、新しいデータクラスタでdataを書くことができると思っていた。だが今のところできない・
成功した方法
現在稼働しているpostgresqlのサービスを停止する。これはservices mscを使ってpostgresqlの停止を行った。その他の方法としてGUIではなくCUIで
pg_ctl stop -U postgres
でやることができると思う。
ドロップボックス上にpostgres_dataディレクトリを作成した。
ここにテーブルやテーブルデータを入れることになる。 ディレクトリを作成しただけなので、このディレクトリをデータベースクラスタ化する。これはinitdbコマンドを使う。
initdb --encoding=UTF8 --no-locale --username=postgres -W -D "C:\Users\USER\Dropbox\postgres_data"
#コマンドの結果 データベースシステム内のファイルの所有者は"USER"となります。 このユーザがサーバプロセスも所有する必要があります。 データベースクラスタはロケール"C"で初期化されます。 デフォルトのテキスト検索設定はenglishに設定されました。 データベージのチェックサムは無効です。 新しいスーパユーザのパスワードを入力してください: 再入力してください: 既存のディレクトリC:/Users/USER/Dropbox/postgrestestの権限を修正します ... 完了 サブディレクトリを作成します ... 完了 max_connectionsのデフォルト値を選択します ... 100 shared_buffersのデフォルト値を選択します ... 128MB selecting default timezone ... Asia/Tokyo 動的共有メモリの実装を選択します ... windows 設定ファイルを作成します ... 完了 ブートストラップスクリプトを実行します ... 完了 ブートストラップ後の初期化を行っています ... 完了 データをディスクに同期します...完了 警告: ローカル接続で"trust"認証を有効にします。 この設定はpg_hba.confを編集するか、次回のinitdbの実行の際であれば-Aオプ ション、または、--auth-localおよび--auth-hostを使用することで変更するこ とができます。 成功しました。以下のようにしてデータベースサーバを起動できます。 pg_ctl -D ^"C^:^\Users^\USER^\Dropbox^\postgrestest^" -l <ログファイル> start
次にpostgresqlを稼働させる。これはpg_ctl start コマンドで動かす事ができる。
pg_ctl start -U postgres -D "C:\Users\USER\Dropbox\postgres_data" -w
これで動かすことができた。 参考http://koshian2.hatenablog.jp/entry/2018/02/17/213446
人にコードを見せるときには
まずコードだけ見せない。設計書、コード、テストをセットにする。
どの順番にモジュールを使うのかを図とか絵で説明するのは設計書のレベル。
設計書でもっと詳しく確認したいときにコードを確認する流れとなる。
感覚としてはコード無しで構造が分かるものを準備すること。
またコードに関しては、パラメータやDB情報などを記載した設定ファイルを準備すること、そしてコード全体としてメンテナンス性に優れた構造にする。
また設計書、コードの信頼性を高めるテストを添付する。
まずフローチャートを書く。これは人にどのような論理でコードを書いているか説明できるとともに、自分が書こうとするコードの論理構造を明確に理解できる。フローチャートを用いると、同じフローを視覚を通して発見できるので関数化しやすくなる。また関数化しなくとも冗長な部分を視覚的に発見するメリットもある。
納品物のドキュメントはエクセルなどのファイルにしておく。
納品物の構成もPROGRAM、設計書、INPUT_DIRにしておくと良い。
postgres create
import pandas as pd
import zipfile,os,time
ZIP_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) + "\\ZIP_DIR\\"
UNZIP_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) + "\\UNZIP_DIR\\"
MERGED_CSVS = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) + "\\DIR_DIR\\MERGED_CSVS\\"
zip_files = os.listdir(ZIP_DIR)
#zipファイルを解凍してrenameを実行。すべて解凍する。
userid_list = []
for zf in zipfiles:
with zipfile.ZipFile(ZIP_DIR+zf) as zip:
time.sleep(0.0000001)
timestamp = str(time.time()).replace(".", "_")
ziplst = zip.namelist()
for f in ziplst:
#チェック
print(f)
sys.exit()
userid = f.split("_","_")[X]
zip.extract(f, UNZIP_DIR)
os.rename("".join([UNZIP_DIR, f]), "".join([UNZIP_DIR, userid, span, timestamp, ".csv"]))
userid_list.append(userid)
#同一useridのcsvファイルをUNZIPディレクトリから取得
unzip_list = os.listdir(UNZIP_DIR)
for userid in set(userid_list):
csv_list = [ file for file in unzip_list if userid in file ]
csv_list.sort()
print(csv_list)
for f in csv_list:
unzip_list.pop(f)
sys.exit()
#マージファイルを作成
pd_list = [ pd.read_csv(csv_file) for csv_file in csv_list ]
df = pd.concat(pd_list)
#マージファイルタイトルの作成
merge_file = "".join([userid, span, ".csv" ])
df.to_csv("".join([MERGED_CSVS, merge_file]), index=False, encoding="utf-8")
windows便利ショートカットキー
新しいフォルダを作る
「Ctrl + Shift + N」
フォルダ、ファイル名を変更
フォルダ、ファイルをクリックしてからF2ボタンを押す
