knmkr

CREATE TEMPORARY TABLE foo (id int, weight int, height int);
INSERT INTO foo VALUES (1, 10, 100);
INSERT INTO foo VALUES (1, 11, 111);
INSERT INTO foo VALUES (2, 20, 200);
INSERT INTO foo VALUES (3, 30, 300);

CREATE TEMPORARY TABLE bar (id int, top int, bottom int);
INSERT INTO bar VALUES (1, 10, 100);
INSERT INTO bar VALUES (2, 20, 200);
INSERT INTO bar VALUES (2, 22, 222);

knmkr

WITH x AS (
    SELECT
        1 AS k1,
        2 AS k2,
        (SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) ) FROM generate_series(1,2) ) AS k3
)
SELECT
    to_json(x) AS record
FROM
    x;

knmkr

PostgreSQL で gzip された CSV ファイルを stdin から読み込む。

$ echo "1,2" > a.txt
$ gzip a.txt
$ createdb --owner=knmkr test-copy
$ gunzip -c a.txt.gz| psql test-copy knmkr -c "COPY test (a,b) FROM stdin DELIMITERS ','"
$ psql test-copy knmkr -c "SELECT * FROM test;"
 a | b
---+---

1 | 2

knmkr

nokogiri が MacOSX で libiconv のせいでインストール止まるときの対処。

公式: http://nokogiri.org/tutorials/installing_nokogiri.html を参照。

Homebrew で libiconv を入れる。

$ brew --version
0.9.5
$ brew install libxml2 libxslt

$ brew link libxml2 libxslt

knmkr

-- Load reference data
DROP TABLE IF EXISTS disease_reference;
CREATE TABLE disease_reference (
       name_en text UNIQUE,
       name_ja text UNIQUE);
INSERT INTO disease_reference
VALUES
('Age-related macular degeneration', '加齢黄斑変性症'),
('Alzheimer''s disease', 'アルツハイマー病'),
('Atrial fibrillation', '心房細動'),

knmkr

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import argparse
from wikitools import wiki  # https://github.com/alexz-enwp/wikitools
from wikitools import page

def search_snpedia(snp):
    """
    http://snpedia.com/index.php/Bulk

knmkr

import string
seq = 'CATCATCAT'
reverse_complement_seq = seq[::-1].translate(string.maketrans('ATGC', 'TACG'))

knmkr

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import argparse
import vcf

_GT2freq = {'0|0': 'hom_refs',
            '0|1': 'hets',
            '1|0': 'hets',
            '1|1': 'hom_alts',

knmkr

import java.util.ArrayList;

public class GetPrimes {
    public static void main(String[] args) {
        int i, j, end;
        boolean isPrime;
        ArrayList<Integer> primes = new ArrayList<Integer>();

        end = 100;

knmkr

def get_primes(n)
  primes = []

  (2..n).each do |x|
    is_prime = true
    
    (2..x-1).each do |y|
      if x % y == 0
        is_prime = false
        break