特定の月だけが入ったレコードだけを取り出すことはできるか?
例えば6月をtimestampやdateのフィールドからselectで抽出できるか?
今まで列の値をwhereを使って=かlikeを使って取得してきた。dateやtimestampでwhere句を使う方法を知らないので、まずそれを調べてみようと思う。
参考:https://stackoverflow.com/questions/8863156/selecting-by-month-in-postgresql
上記の例ではちょうどpostgresqlを使ってtimestampまたはdate型の月を指定してselectする方法について議論している。
要するにEXTRACTを使えば解決できるようだ。 WHERE句にEXTRACTを用いる。
SELECT id, name, birthday FROM employee.person WHERE EXTRACT(MONTH FROM birthday) > 10;
https://www.postgresql.org/docs/9.1/functions-datetime.html
select userid, date, zahyo from table_a WHERE EXTRACT(MONTH from table_a) = 6
conn = get_connection() cur = conn.cursor() #8月のデータを取得したい SELECT_SQL = """SELECT EXTRACT(month FROM date) FROM demo""" SELECT_SQL = """SELECT * FROM demo WHERE EXTRACT(month FROM date) = 8""" cur.execute(SELECT_SQL) res = cur.fetchall() print(res)
QGISについて分かったことメモ
タイルレイヤプラグインについて
国土地理院の地図を表示するために以下の記事を参考にした。
http://ymcity.hatenablog.com/entry/2016/10/05/234431
このサイトでは国土地理院の地図を表示させるために、上部メニューバーのプラグインからTileLayerプラグインを選んで導入するとあった。しかしながらこのTileLayerプラグインが自分にはない。どうしてないのだろうと困っていた。ブログの内容はQGIS2系を扱う記事であり、自分はQGIS3系であった。ここが意外にも重要だった。
https://note.sngklab.jp/?p=627
今回はQGISでよく使う機能の一つであろう、地理院地図タイルの表示について紹介したいと思います。背景地図としてとても役立つ地理院地図タイルですが、QGIS version 2.X系ではTileLayerPluginなどを用いて呼び出していましたが、QGIS version 3.X系ではデフォルトでタイルレイヤを読み込む機能が備わっています。
postgis環境構築について
windowsで環境構築に詰まって、macでも詰まってしまった。どうすればよいのやら... 結局詰まっている場所は同じ場所だ。 CREATE EXTENSION postgis; を入力すると、windows,mac両者ともエラーが出てしまう。 windowsの場合はまずpostgresqlのインストーラでpostgresqlをインストールする。終わりがけにスタッグビルダが起動するので、そこでpostgisをインストールする流れを踏む。そして新たにデータベースを作成する。これは、SQLとしてCREATE DATABASE hoge;としても良いし、createdb -U postgres hogeでも良い。そのあと作成したデータベースに接続してCREATE EXTENSION postgis;と入力すればよいはずである。しかし、それに関わるファイルがないとエラーが出る。実際にそのファイルが有るにも関わらず。macもCREATE EXTENSION postgis;で詰まってしまっている。
ERROR: could not load library "/Library/PostgreSQL/11/lib/postgresql/rtpostgis-2.5.so": dlopen(/Library/PostgreSQL/11/lib/postgresql/rtpostgis-2.5.so, 10): Library not loaded: @loader_path/../../lib/libssl.1.0.0.dylib Referenced from: /Library/PostgreSQL/11/lib/postgresql/libgdal.20.dylib Reason: image not found
上記のエラーが出てしまっている。魔境。。。
postgis2.5の利用要件
https://postgis.net/docs/manual-2.5/postgis_installation.html#install_requirements
postgreSQLを9.4以上を使っていること。postgisとpostgresqlの対応状況はこちらを確認すること。 https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
createlangコマンドについて
createlangはcreatedbコマンドの後に実行するとドキュメントに書いてある。
createdb yourdatabase createlang plpgsql yourdatabase psql -d yourdatabase -f postgis.sql psql -d yourdatabase -f postgis_comments.sql psql -d yourdatabase -f spatial_ref_sys.sql psql -d yourdatabase -f topology.sql psql -d yourdatabase -f topology_comments.sql -- only if you compiled with raster (GDAL) psql -d yourdatabase -f rtpostgis.sql psql -d yourdatabase -f raster_comments.sql --if you built with sfcgal support -- psql -d yourdatabase -f sfcgal.sql psql -d yourdatabase -f sfcgal_comments.sql
しかしwindowsにインストールしたpostgresqlにはなかった。これについてはどうドキュメントに以下のように書いてある。
https://postgis.net/docs/manual-2.5/postgis_installation.html#create_new_db
Many of the PostGIS functions are written in the PL/pgSQL procedural language. As such, the next step to create a PostGIS database is to enable the PL/pgSQL language in your new database. This is accomplish by the command below command. For PostgreSQL 8.4+, this is generally already installed
要するに8.4以上のバージョンにはすでにインストールされているらしい。11を使っているのでcreatelangは使わないってことでオッケイなんだよね?
Postgresqlのコマンドライン:creatdbについてスッキリしたこと
データベースを作成するときのコマンドcreatdbについて
CREATE DATABASE MYDB;
上記のようにコマンドを叩くとデータベースを作ることができるのは当たり前として、createdb hogeと書いてもデータベースを作ることができる。このコマンドは天から急に降ってきたかのごとく突拍子もなく使っている人がいる。例えばこれ。
https://lets.postgresql.jp/documents/tutorial/PostGIS/3
でもSQLの学習書を読んでいてもcreatedbのコマンドをついに見つけることができずにいた。
しかしこの急に出てくるコマンドに終止符を打つことができた。
localでwindowsに入れてみて分かった
今までpostgresqlはさくらVPSで使うのみだったので、ディレクトリの構造やどんなファイルが入っているか詳しく知ることはなかった。見ても良くわからなかったし。。。ただ今回はGISを試すためにwindowsに入れてみてた。psqlコマンドがコマンドプロンプトやターミナルで使えるのはbinディレクトリにpsqlの実行ファイルが存在するから。実はcreatedbコマンドもbinディレクトリにcreatedb実行ファイルが存在していた。てことで、createdbコマンドはpsqlでデータベースに接続した後に使うコマンドでもないことは自明となったし、この理由は知りたかったひとつの項目であった。
windowsのコマンドプロンプトからPostgreSQLを触る
今回の試み
今回はwindowsにpostgresqlを入れて、そしてコマンドプロンプトからログインできるかを試す。今までwebアプリケーションを作成するにはpostgresqlを使っていたもののサクラVPSで行っていた。だからLinuxかwindowsかの違いや、リモートやローカルといった作業場所の違いがある。
参考 :
https://qiita.com/wb773/items/c2fd0e1e0349a41b5844
https://lets.postgresql.jp/documents/tutorial/windows/
実行環境
- windows10
- postgresql11
インストール
https://lets.postgresql.jp/documents/tutorial/windows/
ログイン
コマンドプロンプトでpsqlコマンドを実行してpostgresでログインする。
psql -U postgres -d postgres
上記ではパスが通っていないので、C:\Program Files\PostgreSQL\11\binで実行する。psql.exeはbinディレクトリにあるから。
ユーザを作成してデータベースの権限を帰属させる
参考:https://qiita.com/wb773/items/248e6e083b2fe12e820a
http://torajirousan.hatenadiary.jp/entry/2018/09/10/054545
windowsでもpsqlコマンド実行後の流れは特にwindowsであろうとlinuxであろうと違いがないって感想だった。手順はpostgresでログイン後、新たにデータベースを作成する。そしてデータベース操作権限のあるユーザーを作成する。
C:\Program Files\PostgreSQL\11\bin>psql -U postgres -d postgres postgres=# CREATE DATABASE test_database; CREATE DATABASE postgres=# CREATE USER new_user WITH PASSWORD 'PASSW0RD'; CREATE ROLE postgres=# ALTER ROLE new_user SET client_encoding TO 'utf8'; ALTER ROLE postgres=# ALTER ROLE new_user SET default_transaction_isolation TO 'read committed'; ALTER ROLE postgres=# ALTER ROLE new_user SET timezone TO 'Asia/Tokyo'; ALTER ROLE postgres=# GRANT ALL PRIVILEGES ON DATABASE test_database TO new_user; GRANT postgres=# \q
binにあるコマンドについて分かったこと
psql.exeはデータベースの接続やsqlファイルの読込みやcreate table等のSQLの実行を行うことができる。
psql -U username -d dbname #データベースの接続
psql -U username -d dbname -f "file\path\**.sql" #sqlファイルを読みこんで、ファイル内のsqlを実行する
psql -U username -d dbname -c "CREATE TABLE test_table (userid varchar(20), address varchar(20));" #SQLの実行
pg_ctl.exeはpostgresqlのサービスの起動、停止などを実行する。これはservices.mscでもサービスの起動、停止をすることができるようだ。
https://www.postgresql.jp/document/9.3/html/app-pg-ctl.html
データベースのデータを複製する場合はpg_dump.exeを用いる。
時間関係のオブジェクトを扱う
時間関係のモジュール
timeとdatetimeが存在する。また時間についてはエポックタイムという概念も存在する。
time
time.time()でエポックタイムを取得できる。
http://sweng.web.fc2.com/ja/program/python/time.html
datetime
今の時間をとる。それはdatetime.datetime.now()でできる。
datetime.datetime.now()をUNIX時間に変換するにはdatetimeオブジェクトのtimestampメソッドを使えば良い。
https://note.nkmk.me/python-unix-time-datetime/
import datetime now = datetime.datetime.now() now # datetime.datetime(2019, 8, 10, 12, 8, 12, 636212) now.timestamp() # 1565406492.636212
これで636212を削除するにはどうすればよいのか?そこが疑問。
今のUNIX時間をとるにはどうすればいいのか?それはまずdatetimeの今の時間を取得する。その時間をtimestamp()メソッドを使うとエポックタイム(UNIX時間)に変換することができる。
エポックタイム
まだ詳しくはわからないが、エポックタイムはエポックタイムオブジェクトってのはないのか?time.time()で返されるのはミリ秒を含むfloat型データだし、int(time.time())はミリ秒を除くint型データである。これらはエポックタイムを表すので、datetime.datetime.fromstamp(floatかint型のエポックタイム)で成立する。
次気になるのはdatetime型データからどうstrデータに変換するか。datetimeオブジェクト.strftime(フォーマット)を使えば良い。
軽くまとめるとエポックタイムからデイトタイム型に変換してそれを文字列化するには以下の手順を踏む。
import time , datetime now_u = time.time() #floatで取得 now_u = int(now_u) #ミリ秒を削除してint型にした now = datetime.datetime.fromtimestamp(now_u) # datetime型に変換した format = "%Y-%m-%d %H:%M:%S" t = datetime.datetime.strftime(now, format) #これで文字列に変換できた t1 = datetime.datetime.strptime(t, format) #datetimeオブジェクトに戻す t1.timestamp() #エポックに戻す
#再利用コード mytools/treat.py import datetime, time def change2datetime(timestamp): """ UTC時間からdatetimeオブジェクトに変換する関数 time(timestamp) → datetime """ d = datetime.datetime.fromtimestamp(timestamp) return d def change2d_str(d): """ datetimeオブジェクトから文字列"2019-8-11 17:15:13"のような形に変換する関数 datetime → str """ dstr_format = "%Y-%m-%d %H:%M:%S" d_str = datetime.datetime.strftime(d, dstr_format) return d_str def timestamp2dstr(timestamp): """ UTC時間から時間を表す文字列に変換する関数 timestamp → str """ d = change_datetime(timestamp) d_str = change_d_str(d) return d_str
def get_rowlst(): row_list = [] with open("demo.csv", "r", encoding="utf-8") as f: reader = csv.reader(f) for row in reader: row_list.append(row) return row_list def get_staytime(lst, index): if index == len(lst)-1: return print("finish") timestamp = get_timestamp(lst[index]) next_timestamp = get_timestamp(lst[index+1]) staytime = int(next_timestamp) - int(timestamp) return staytime def extract_data(stay_time=None,seconds=10): if stay_time != None and stay_time > seconds: return "EXTRACT" else: return "IGNORE" def output_pdata(seconds=10): row_list = get_rowlst() for index,row in enumerate(row_list): userid = get_userid(row) timestamp = get_timestamp(row) date = change_datetime(timestamp) stay_time = get_staytime(row_list, index) file_name = get_file_name(row) result = extract_data(stay_time=stay_time, seconds=950) if result == "EXTRACT": print(userid,timestamp,date,stay_time) t = (userid, date, stay_time, file_name,) cur.execute("""INSERT INTO registered (userid, date, stay_time, filename) VALUES (%s,%s,%s,%s)""", t) conn.commit() conn.close()
SQLite3で複合キーを使ってみる
参考:https://ts0818.hatenablog.com/entry/2017/02/04/162513
http://isakado.blog106.fc2.com/blog-entry-17.html
使用例
# 複合主キーを設定したテーブルを作成 conn = sqlite3.connect("practice.sqlite") cur = conn.cursor() create_table_sql =""" CREATE TABLE Registered (USERID TEXT, DATE TEXT, FILE_NAME TEXT, PRIMARY KEY(USERID, DATE)) """ cur.execute(create_table_sql)
どうやらクリエイトテーブルしたあとにPRIMARY KEYを記述してその引数に複合キーとしたいカラム名を渡せば良いようだ。
この記法はsqliteだけでなくpostgresqlでも同じ。
http://www.ksknet.net/postgresql/post_179.html
https://www.dbonline.jp/postgresql/table/index6.html
kml:linestringファイル作成
ele1 = ["road1", 0, 120, 60] ele2 = ["road1", 1, 122, 65] ele3 = ["road1", 2, 125, 63] ele4 = ["road2", 0, 123, 61] ele5 = ["road2", 1, 113, 41] ele6 = ["road3", 0, 173, 69] ele7 = ["road3", 1, 113, 23] ele8 = ["road4", 0, 123, 67] ele8 = ["road4", 1, 143, 97] ele9 = ["road5", 0, 133, 61] ele10= ["road5", 1, 173, 91] parent_list = [ele1, ele2, ele3, ele4, ele5, ele6, ele7 ,ele8, ele9, ele10] key_list = [] for ele in parent_list: if ele[0] not in key_list: key_list.append(ele[0]) all_data = [] for ele in key_list: ele_dict = {} ele_dict.setdefault(ele,[]) all_data.append(ele_dict) for e in parent_list: road_name = e[0] for dic in all_data: key = [key for key in dic.keys()][0] if key == road_name: dic[key] = dic[key] + e[1:] kml = "" kml_head = """<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://earth.google.com/kml/2.0"> <Document>""" kml += kml_head string = """ <Placemark> <LineString> <name>{road_name}</name> <!--altitudeMode>clampToGround</altitudeMode--> <altitudeMode>absolute</altitudeMode> <!--altitudeMode>clampToGround</altitudeMode> <altitudeMode>clampToGround</altitudeMode--> <coordinates> {contents} </coordinates> </LineString> </Placemark>""" kodo = "0.0" for road in all_data: road_name = [key for key in road.keys()][0] info_base = road[road_name] set_quantity = int(len(info_base)/3) contents = "" for n in range(0,set_quantity): keido = str(info_base[3*n+1]) ido = str(info_base[3*n+2]) #print(keido, ido) info = ",".join([keido, ido, kodo])+"\n" contents = contents + info placemark = string.format(road_name=road_name,contents=contents[:-2]) kml += placemark kml += "\n"+"</Document></kml>" print(kml) with open("output.kml", "a", encoding="utf-8") as f: f.write(kml)
kmlファイルテンプレート
全般
標高については以下に参照先がある
https://developers.google.com/kml/documentation/altitudemode?hl=ja
Point
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://earth.google.com/kml/2.2"> <Document> <Placemark> <Point> <coordinates> 経度,緯度,高度 </coordinates> </Point> </Placemark> </Document> </kml>
LineString
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.2"> <Document> <Placemark>
<LineString>
<name>ポリライン名</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> </Document></kml>
複数のポリラインを表現したい場合にはPlacemarkを繰り返して挿入すると複数種のポリラインを表現することができる。
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.2"> <Document> <Placemark>
<LineString>
<name>ポリライン1</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> <Placemark>
<LineString>
<name>ポリライン2</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> <Placemark>
<LineString>
<name>ポリライン3</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> </Document></kml>
メモ:linestringに関しては厳密にLineStringと表記しないとgoogleearthが読み込まない。例えばLinestringだと駄目。
dictの値にリストを使うときの注意点
dict[key] = dict[key].append("要素")の結果はNone
dict型データの値にリストを設ける。そして必要な情報をリストに格納しようと思い以下のコードを書いた。
sample = dict(column1=[]) key = [key for key in sample.keys()][0] print(key) #1 column1 value = [value for value in sample.values()][0] print(value) #2 [] print(type(value)) #3 <class 'list'> '''疑問:valueの[]に要素を加えることができるか?検証する''' print("入れる前 : ",sample[key]) #4 入れる前 : [] sample[key] = value.append("要素1") print("入れた後 : ",sample[key]) #5 入れた後 : None print(sample) #6 {'column1': None}
ここで注目すべきは、dictの値としてリストを設け、そのリストにappendを使うと値はNoneになってしまうことだ。期待していたことは{'column1': "要素1"}となることだったのに。。。
期待の結果を得る方法
上記では期待の結果が得られなかったのはappendに問題があるようだ。appendメソッドを実行せずにリストにリストを加えるとdictにリストの要素を加えることができる。
my_list = [1,2,3] test_list = [4,5] my_list + test_list # [1, 2, 3, 4, 5] """ リスト同士の+は結果としてappendの形になる。解決にこれを利用する """
test_list = [1] sample = dict(column1=[]) sample[key] = sample[key] + ["新たな要素"] print("入れた後 : ",sample[key]) # 入れた後 : ['新たな要素'] print(sample) # {'column1': ['新たな要素']}
appendについて補足情報
https://codeday.me/jp/qa/20190205/212233.html
append()は破壊的な操作であるらしく、非破壊的な操作は + であるらしい。
https://stackoverflow.com/questions/1682567/why-does-list-append-evaluate-to-false-in-a-boolean-context
Most Python methods that mutate a container in-place return None -- an application of the principle of Command-query separation. (Python's always reasonably pragmatic about things, so a few mutators do return a usable value when getting it otherwise would be expensive or a mess -- the pop method is a good example of this pragmatism -- but those are definitely the exception, not the rule, and there's no reason to make append an exception).
コンテナをその場で変更するほとんどのPythonメソッドはNoneを返します。これは、コマンドとクエリの分離の原則を適用したものです。みたいなことが書いてあり、リストの中身を変えるメソッドを使うと、Noneが返されるのが一般的らしい。これはappendに限らず、remove()も同様の結果になる。
my_list = [1,2,3,4,5] print(my_list.append(6)) # None r = my_list.append(7) print(r) # None print(my_list.remove(1)) # None r = my_list.remove(2) print(r) # None
SQLエラー:must appear in the GROUP BY clause or be used in an aggregate function
エラーコード
must appear in the GROUP BY clause or be used in an aggregate function
原因
GROUP BYを使って以上のようなエラーが出た場合には、以下の原因によるエラーの可能性がある。
それはGROUP BYを使っているにも関わらず、集約関数(SUMなど)を使っていない場合であるとき。
GROUP BY は集約関数を使う際に用いられる前提を確認したい。
Flaskメモ
日本語ドキュメント
https://a2c.bitbucket.io/flask/
インストール方法
https://a2c.bitbucket.io/flask/installation.html#installation
pip install flask
SQL UNIONについて
UNION ALL とUNION
UNION ALLを使うと集合で重複行をすべて返すのに対し、UNIONは重複行を1行にまとめる。
ORDER BYとの関係
UNIONを使った場合、ORDER BYはUNIONを使ったあと最後に実行する。これは集合の内容をORDER BYによって再編集するものであり、単純に各集合を並び替えて結果を集合ごとに並べるものではない。