kuroの覚え書き

96の個人的覚え書き

SQLite

SQLiteのINSERTをSQLAlchemyで書く

かなり久しぶりにSQLiteのデータを追加しようと以前に作成したスクリプトを持ち出してきて実行してみたところ sqlalchemy.exc.ArgumentError: Textual SQL expression 'rnaseq_temp' should be explicitly declared as text('rnaseq_temp')こんなエラーが出…

NULLと空白文字

SQLiteでテーブルにinsertするとき、一部のカラムを指定しないままに行うと、指定されていないカラムにはNULLが入る。 これをSQLAlchemyでクエリを実行し、Python/FlaskでHTMLにレンダリングすると、NULLのカラムにはすべてNoneと表示される。 このままで別…

select count distinct

重複せずに行を数えるには SELECT COUNT(DICTINCT column) FROM tablehttp://d.hatena.ne.jp/nyoe3/20100313/1268468670

SQLiteにインポートするファイルにIDを前もって入れておく

MacのSQLiteではIDカラム分の無いファイルをインポートしようとしたらカラムが足らないのでnullを埋めるよという警告が出た上でインポートされ、ついでにPRIMARY KEYなのでインクリメントされたIDを自動でふってくれるのだが、どういうわけかCentOSではエラ…

ユーザーページのデータベーステーブル

さて、ユーザーごとにカスタマイズしたページを作るならば、テーブルの構成も自由に設定できるようになるのでないかということでmodels.pyのテーブル設定を考えてみた。実際にコピーできるカラムは元のテーブルにすでに載っているカラムからだけなわけだが、…

速度向上の秘策

さて、いろいろ考えてみたものの、なかなか速度が改善しないSQLデータベース検索だが、そもそも、レコード数を前もって間引いとけばいいんじゃない?というアイデアが出た。 つまり、現在の検索の順序として Variant countでカットオフ Gene countを計算し、…

ディスク保存のデータベースからテーブルをメモリーデータベースに読み込んで利用するには

いろいろやってみているんだけど、なかなか成功しない。 https://stackoverflow.com/questions/4019081/how-to-copy-a-sqlite-table-from-a-disk-database-to-a-memory-database-in-python https://stackoverflow.com/questions/3850022/how-to-load-existin…

他のテーブルに載っている項目があったらアップデートでコメントを入れる

update exome_genes set gene_comments = "ad" where gene_wgEncodeGencodeCompV19 in(select gene from ad);前にもやったけど。 exome_genesというテーブルのgene_wgEncodeGencodeCompV19というカラムの項目がadというテーブルのgeneというカラムに 一致す…

Pythonを使ってSQLiteにTSVファイルをimport

sqlite3からは > .separator "\t" > .import hoge.txt hogeとすればhoge.txtというTSVファイルからデータをhogeテーブルにインポートできる。 これをshell scriptからやるには $ sqlite3 -separator $'\t' ./data.db ".import ./hoge.txt hoge"これでいい。 …

速度アップにはマシンパワー増強とインメモリデータベースの利用で

現状フルサイズ350カラム、実際に運用時30カラム程度で、165万件くらいのデータベースなんだが、ほぼすべてのカラムがTextとして構築されているためにMySQLに乗り切らないことは前に書いた。で、どうしてTextなんだということだが、要するにデータ解析の過程…

SQLiteのINSERTをSQLAlchemyで書くと

#sql>INSERT INTO Exome_sub(SampleName_phenotype, avinput_ref, avinput_alt, variant_id) SELECT SampleName_phenotype, avinput_ref, avinput_alt,variant_id FROM exome;こんな感じにテーブルexomeからテーブルexome_subにSampleName_phenotype, ref, a…

リレーションが上手くいかない

とにかくカラム数も多いし、行数も160万行を超えていて動作がもっさりしている。 本当はテーブルを分けてリレーションでjoinして運用するのがいいのだろうけど、やっぱりouter joinとか動作が非常に遅くて使い物にならない。 なので、テーブルを前もってつな…

データベースファイルが破損してしまったのでリカバリーを試みる

ちょっと無茶なSQL文でアップデートをしようとして、ちっとも処理が終わらないので、強制終了したら見事にデータベースファイルが破損してしまった。 sqlite> drop table exome; Error: database disk image is malformedとすげなくエラーメッセージがでて言…

カラムの内容から一部を切り取って別のカラムをアップデート

まずカラムから文字列の一部分を切り出すには select substr(name, 1, 4) from exome;これでexomeというテーブルのnameというカラムから、先頭〜4文字目までを選び出すことになる。 選びだした文字列でidというカラムをアップデートするなら、 update exome …

リストに載っているものを含む行を抽出する

https://www.dbonline.jp/sqlite/select/index5.html excelで重複を除く https://support.office.com/ja-jp/article/重複しない値を抽出する、または重複する値を削除する-ccf664b0-81d6-449b-bbe1-8daaec1e83c2 まあリストに重複があってもupdateにちょっと…

PythonでSQLiteのTableにtsvからインポート

https://pypi.python.org/pypi/swadr/1.2.1 これがかんたんに使えそうな感じ。 .importする代わりにINSERTで1行ずつ入れていくのが常法なようだけど、カラム数が多いとちょっと面倒だし。とおもったらpipでインストールできないよ。作業用データベース・テー…

tableへのデータアップデートを考える

とにかく行数が増えてくるとsqlのupdateのスピードがかなり負担になってくる。なのでテキスト処理だけで大方のカラム整形をやって、一部分だけsql文でなんとかする方法を考えた。特に、今後私の手を離れた後にメンテナンスが容易なように、スクリプトでちょ…

300カラムx160万件超になるとめっちゃ動作が遅くなってしまった。

データベースを構築するのにかかる時間も飛躍的にかかるようになったが、なにより検索、表示にものすごく時間がかかるようになってしまった。カラムは300くらいあるけど、実際に参照して表示に使っているのは30くらい。 そういう場合、データベースとしては…

テーブルの正規化、リレーション、結合

sqlite> .schema CREATE TABLE sample ( "N_id" VARCHAR NOT NULL, "Alternative_Sample_name" VARCHAR(64), phenotype VARCHAR, clinical_info VARCHAR, PRIMARY KEY ("N_id") ); CREATE INDEX "ix_sample_Alternative_Sample_name" ON sample ("Alternativ…

group by でcountしてjoinしてselectしてupdate?

name test1 test2 id count ---------------------------------------- A001 x a 1 A001 x a 2 A001 x b 3 A001 x b 4 A001 x b 5 A001 y b 6 A001 y b 7 A001 y c 8 A001 y c 9 A001 y c 10 A002 x a 11 A002 y a 12 A002 x b 13 A002 y b 14 A002 x b 15 A…

SQLite3のtable作成覚書

FlaskでSQLAlchemyを使ってSQLiteのデータベースを運用している。 前提 model.pyでtableの定義をおこない、データベース上にテーブルが作成されるように設定しているが、データ自体はあとからtsvから読み込む。 primary keyとしてIDをふるが、それはデータベ…

GROUP BY でSELECTしてJOINしてSELECT

SELECT exome171001.variant_id, var_count.variant_count from exome171001 LEFT OUTER JOIN (SELECT pos, REF, ALT, COUNT(*) as variant_count from exome171001 GROUP BY pos, REF, ALT) AS var_count ON exome171001.pos=var_count.pos and exome171001…