Bye Bye Moore

猫マンション建築の野望を胸に零細事業主として資本主義の荒波に漕ぎ出したアラサー男の技術メモ

sql

BETWEEN演算子で特定の範囲内にある値をとる

sql

特定の範囲内にある値をとる場合、BETWEEN演算子が使えます。 SQL標準なので、SQLiteにすら入っています。 実際のところ いままで0以上50以下なんてクエリを書く場合、以下のようにしていました。 SELECT value_a FROM result WHERE value_a >= 0 AND value_…

【読書メモ】[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス作者: 朝井淳出版社/メーカー: 技術評論社発売日: 2017/02/18メディア: 単行本(ソフトカバー)この商品を含むブログを見るSQLのポケットリファレンス第四版です。SQLの歴史や文法にかるく触れつつ、関数や演算子といった…

sqlalchemyでデータを順番に並べる

sqlalchemyでデータを順番に並べる方法です 実際のところ idが5未満のデータをid順でソートするには以下のようにします。 for row in session.query(User).\ filter(text("id<3")).\ order_by(text("id")).all(): print(row) 参考もと Object Relational Tut…

sqlalchemyのfilter関数でANDやORな条件を指定する

sqlalchemyにおける検索条件について 実際のところ for user in session.query(User).\ filter(User.name=='ed').\ filter(User.fullname=='Ed Jones'): print(user) 上の例は、以下のように書くこともできます。 session.filter(User.name == 'ed', User.fu…

PythonのORマッパーSQLAlchemyを使ってみる

実際のところ $ pip install sqlalchemyimport sqlalchemy sqlalchemy.__version__ #>> '1.0.15' SQLiteのオンメモリDBをつかって、簡単な読み書きをするには以下のようにします。 from sqlalchemy import create_engine, Table, Column, Integer, String, M…

PostgreSQLで連番の数値を入れたい場合 その2:シーケンス関数

sql

shuzo-kino.hateblo.jp この記事では、SERIAL型を使って、DEFAULTで連番値を入れてくれる設定にしました。今回は前の記事でやったシーケンス関数を使ってみます。 実際のところ シーケンス関数を使うと以下のように設定できます。 CREATE TABLE products ( p…

PostgreSQLのシーケンス操作関数

sql

PostgreSQLのシーケンス操作関数は連番の数値を入れる際に便利な機能です。 実際のところ シーケンス型serialを3からはじめる場合、以下のようにします。 CREATE SEQUENCE serial START 3; ためしに中をみてみると、デフォで色々と値を持っている事がわかり…

PostgreSQLで連番の数値を入れたい場合 その1:SERIAL型

sql

PostgreSQLでの特殊なデータ型にSERIAL型というのがあります。 実際のところ CREATE TABLE samples ( product_id SERIAL, body text ); SERIAL型を使う場合、データ指定はDEFAULTとします。 具体的な数値を突っ込むと、その通りの値が入ります。 INSERT INTO…

PostgreSQLの-cコマンドで結果だけが欲しい場合

sql

PostgreSQLの-cコマンドで結果だけが欲しい場合、 commandオプションとAtオプションをつけると結果だけ出力することができます。 実際のところ 何らかの事情で $ psql template1 -c "SELECT to_char(2016,'RN') AS id;" id ----------------- MMXVI (1 row)…

COPYコマンドで結果をローカルファイルに保存する

sql

COPYコマンドで検索結果をローカルファイルに保存する方法です。 実際のところ 検索結果を保存する ホームディレクトリ(/Users/shuzo_kino/)に作ったcopytest.txtというファイルにbookテーブルのidを出力するケースを考えます。 パスは絶対でしか受け取っ…

COPYコマンドでデータを投入

sql

前の記事ではINSERTコマンドを使った挿入をためしました。 今回はCOPYというコマンドを使ったデータ投入方法です。 実際のところ railsでいうrake db seedsも $ cat /Users/shuzo_kino/sample.txt 12,book 42,foo 56,'bar'空のテーブルbookに対し、上記のも…

INSERTで一度に複数のデータを投入する

sql

こんな事も知らずにやってたのかって話ですが…… INSERTでは、VALUES咬ませることで複数のデータを投入することが出来ます。 実際のところ こんなデータがあったとします CREATE TABLE samples ( id int, body text ); これに3個ほどデータをぶっ込むには以…

postgresのto_charのフォーマット

sql

符号付き select to_char(-123,'SG999') as id; id ------ -123 (1 row) ローマ数字 > SELECT to_char(99,'RN') AS id; id————XCIXローマ数字に変換してくれるんですか〜こりゃ便利機能だ〜(ぐるぐる目#postgresql— 木野修造 (@shuzo_kino) 2016年12月9日 …

postgresでfizzbuzz……あるいはcase文の使い方

sql

postgresであるいはcase文の使い方 実際のところ この手のアレでおなじみのfizzbuzzっぽいの。 ……すみませんaをそのまんま表示する方法がわからなかたので空白文字です => 解決 SELECT a, CASE WHEN a % 15 = 0 THEN 'FizzBuzz' WHEN a % 3 = 0 THEN 'Fizz' …

Postgresには generete_seriesという連番の数値を出す関数がある

sql

Postgresには generete_seriesという連番の数値を出す関数があります 実際のところ 3飛びで1~7までの数値を出したい場合以下のようにします $ psql postgres -c "SELECT 'hello' AS val, id FROM generate_series(1,7,3) AS id;" val | id -------+---- hell…

psycopg2で画像データを読み書きする

psycopg2で画像データを読み書きする方法です。 実際のところ 画像データの格納にはbytea型を使います。 PostgreSQLのbytea型はmemorybuffer型を返すので、ちゃんとbytes関数なりで整形しないといけません。 import psycopg2 DSN = "dbname=postgres user=sh…

psycopg2でデータをINSERTする

前回に引き続き、psycopg2でデータをINSERTする方法です。 実際のところ imageというbytea型のbyte_dataカラムを持っているテーブルに対し、バイナリデータを叩き込む方法は以下の通り。 import psycopg2 DSN = "dbname=postgres user=shuzo_kino" SQL3 = "I…

psycopg2を使ってPostgreSQLにあるデータを読み出す

psycopg2を使ってPostgreSQLにあるデータを読み出す 実際のところ 以下のSQL文を叩くケースを考えます。 固定の時間値を返す。 imageテーブルからとりあえず一個引っ張り出す せっかくwith節を使う事ができるので使ってみます。 import psycopg2 DSN = "dbna…

psqlも-cオプションでSQL文を直接叩きつけることができる。

sql

psqlも-cオプションでSQL文を直接叩きつけることができます。 実際のところ 基本的な使い方 postgresというDBに対し、"select byte_data from image where id = 0;"というSQL文を叩きつけたい場合は以下の通り。 $ psql postgres -c "select byte_data from …

postgresをbrewで入れ直し

sql

postgresをbrewで入れ直し 実際のところ $ brew cleanup postgresql $ brew remove postgresql $ brew install postgresqlDBを新造 $ rm -rf /usr/local/var/postgres $ initdb /usr/local/var/postgres -E utf8$ pg_ctl -D /usr/local/var/postgres -l logf…

pythonで使えるORMapper「peewee」

今回はpythonで使えるORMapperの「peewee」を試してみます。 実際のところ 今回は既存のDBを流用するパターン。 from peewee import * from playhouse.sqlite_ext import SqliteExtData db = SqliteExtDatabase('blogtest.sqlite') db.connect() で、このpee…

bottleとsqlite3を連携させて簡単なメモ表示ツール その2:POSTする

前回の続きで、今回はPOST動作です。 実際のところ まず、子パッケージのpostとrequestを突っ込みます。 from bottle import get, post, request, run, template, static_file, error getの後にpostを追記。 @post('/blog') def postBlog(): body = request.…

bottleとsqlite3を連携させて簡単なメモ表示ツール その1:存在するデータの表示

bottleとsqlite3を連携させて簡単なメモ表示ツールをつくる方法です。 第一回目は存在するDBからデータの表示する方法です。 実際のところ あらかじめ、blogpost.sqliteというDBを作った上でtableも用意しておきます。 /blogにアクセスしたら、最新の記事 /b…

SQLiteでFIRSTやLASTみたいな事を実現する

sql

SQLiteには、FIRSTやLASTに相当する構文はありません。 内部的に印加されるrowidというデータをソートして、LIMITで一個だけ採ってきます。 select rowid,body,date from blog order by rowid desc limit 1; /* 5|it is a fine today.|2016-09-15 */ 実際の…

SQLiteの時間型……はないが関数はある。

sql

SQLiteには、明確な時間型というのがありません。 かわりに、文字列やユリウス暦、UNIX時間を数値化できる各種関数をもってサポートしています。 SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Da…

SQLite3の全文検索機能"FTS"をつかってみる その5:一致箇所の強調

sql

SQLite FTSの不思議な機能の一つにThe Snippet Functionというのがあります。 一致した箇所を強調してくれる奴です。 実際のところ デフォではbタグで囲ってくれます。 HTMLやXMLなんかの時には便利? SELECT snippet(eb2) FROM eb2 WHERE body MATCH 'all';…

SQLite3の全文検索機能"FTS"をつかってみる その4:論理条件

sql

この手のもののご多分に漏れず、論理条件を突っ込むことが可能です。 実際のところ eb2に含まれているサンプルは前回の記事と同様 OR SELECT docid,body FROM eb2 WHERE body MATCH 'to OR have'; /* 1|All your base are belong to us. 36|The truck have s…

SQLite3の全文検索機能"FTS"をつかってみる その3:NEAR節

sql

SQLiteのFTSにおけるMATCHでは、NEARという構文があります。 コレを使うと、近くにこの言葉があるものだけマッチという、 実際のところ たとえば、eb2(内容はその1とほぼ同様)にある"to"を含んだ文章を探すのは以下の通り。 SELECT docid,body FROM eb2 W…

SQLite3の全文検索機能"FTS"をつかってみる その2:既存のテーブルとの連携

sql

FTS編その2、既存のテーブルをひも付けする方法です。 実際のところ 導入 まずは元になるtableを捏造。 CREATE TABLE t1(name, value); INSERT INTO t1 VALUES("samplename", 123); INSERT INTO t1 VALUES("shuzo", 456); INSERT INTO t1 VALUES("taro", 78…

SQLite3の全文検索機能"FTS"をつかってみる その1:導入

sql

SQLite3には全文検索用の機能FTS3*1があります。 今回は導入と簡単な使い方。 下準備 全文検索対応カラムを作成します。 フツーのtableではなく、virtual tableていうものを使います。 既存のDBをコレに変換する方法については調査中……。 CREATE VIRTUAL TAB…