以ORM方式操作SQL数据库(SQLite, MySQL, …) https://docs.sqlalchemy.org

基本用法

#!/usr/bin/env python
#-*- encoding: utf8 -*-
from sqlalchemy import Column, Integer, String, create_engine, PrimaryKeyConstraint, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 创建基类
Base = declarative_base()
# 自定义表类
class NovoWES(Base):
    __tablename__ = 'novo_wes'
    chrom = Column(String(5))
    start = Column(Integer)
    end = Column(Integer)
    ref = Column(String(50))
    alt = Column(String(50))
    freq = Column(Float)
    __table_args__ = (
        PrimaryKeyConstraint('chrom', 'start', 'end', 'ref', 'alt'),
    )
    def __str__(self):
        return '<NovoWES(chrom={chrom}, start={start}, end={end}, ref={ref}, alt={alt}, freq={freq})>'.format(**self.__dict__)
    __repr__ = __str__
# 连接数据库
dbpath = 'test.db'
engine = create_engine('sqlite:///' + dbpath, echo=True)
# 自动创建表
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 插入数据
context = {
    'chrom': '1',
    'start': 123456,
    'end': 123456,
    'ref': 'A',
    'alt': 'T',
    'freq': 0.001
}
session.add(NovoWES(**context))
# 查询
print session.query(NovoWES).filter_by(chrom='1', start=12345).first()
print session.query(NovoWES.chrom)
session.commit()
session.close()