SQLiteのINSERTをSQLAlchemyで書く

かなり久しぶりにSQLiteのデータを追加しようと以前に作成したスクリプトを持ち出してきて実行してみたところ

sqlalchemy.exc.ArgumentError: Textual SQL expression 'rnaseq_temp' should be explicitly declared as text('rnaseq_temp')

こんなエラーが出て先に進まない。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select, func, insert
engine = create_engine('sqlite:///rnaseq.db', echo=False)


# rnaseq_tempテーブルからrnaseqテーブルにcufflinksのデータをコピーする
conn = engine.connect()
q = select(["""rnaseq_temp.tracking_id,
                    rnaseq_temp.class_code,
                    rnaseq_temp.nearest_ref_id,
                    rnaseq_temp.gene_id,
                    rnaseq_temp.gene_short_name,
                    rnaseq_temp.tss_id,
                    rnaseq_temp.locus,
                    rnaseq_temp.length,
                    rnaseq_temp.coverage,
                    rnaseq_temp.FPKM,
                    rnaseq_temp.FPKM_conf_lo,
                    rnaseq_temp.FPKM_conf_hi,
                    rnaseq_temp.FPKM_status,
                    rnaseq_temp.id,
                    rnaseq_temp.sample_id,
                    rnaseq_temp.xtr_id,
                    rnaseq_temp.gene_id""")],
                    from_obj=['rnaseq_temp'])
 ins = insert(Rnaseq).from_select(
                        (Rnaseq.tracking_id,
                        Rnaseq.class_code,
                        Rnaseq.nearest_ref_id,
                        Rnaseq.gene_id,
                        Rnaseq.gene_short_name,
                        Rnaseq.tss_id,
                        Rnaseq.locus,
                        Rnaseq.length,
                        Rnaseq.coverage,
                        Rnaseq.FPKM,
                        Rnaseq.FPKM_conf_lo,
                        Rnaseq.FPKM_conf_hi,
                        Rnaseq.FPKM_status,
                        Rnaseq.id,
                        Rnaseq.sample_id,
                        Rnaseq.xtr_id,
                        Rnaseq.gene_id2),
                        q)
conn.execute(ins)

こんな感じのスクリプトだったんだけど、どうもSQLAlchemyのテキスト表現の方法に何らかの変更があったらしい。

    from sqlalchemy import text
    conn = engine.connect()
    q = select([text("rnaseq_temp.tracking_id,\
                    rnaseq_temp.class_code,\
                    rnaseq_temp.nearest_ref_id,\
                    rnaseq_temp.gene_id,\
                    rnaseq_temp.gene_short_name,\
                    rnaseq_temp.tss_id,\
                    rnaseq_temp.locus,\
                    rnaseq_temp.length,\
                    rnaseq_temp.coverage,\
                    rnaseq_temp.FPKM,\
                    rnaseq_temp.FPKM_conf_lo,\
                    rnaseq_temp.FPKM_conf_hi,\
                    rnaseq_temp.FPKM_status,\
                    rnaseq_temp.id,\
                    rnaseq_temp.sample_id,\
                    rnaseq_temp.xtr_id,\
                    rnaseq_temp.gene_id")],
                    from_obj=[text('rnaseq_temp')])
     ins = insert(Rnaseq).from_select(
                        (Rnaseq.tracking_id,
                        Rnaseq.class_code,
                        Rnaseq.nearest_ref_id,
                        Rnaseq.gene_id,
                        Rnaseq.gene_short_name,
                        Rnaseq.tss_id,
                        Rnaseq.locus,
                        Rnaseq.length,
                        Rnaseq.coverage,
                        Rnaseq.FPKM,
                        Rnaseq.FPKM_conf_lo,
                        Rnaseq.FPKM_conf_hi,
                        Rnaseq.FPKM_status,
                        Rnaseq.id,
                        Rnaseq.sample_id,
                        Rnaseq.xtr_id,
                        Rnaseq.gene_id2),
                        q)
    conn.execute(ins)

こういうふうに書き換えたらちゃんと走るようになった。