sqlalchemy使用

1. sqlalchemy安装

easy_install sqlalchemy

2. sqlalchemy链接数据库，初始化需要的对象

from sqlchemy import *

from sqlchemy.orm import sessionmaker

from sqlchemy.ext.declarative import declarative_base

Base = declarative_base()

SQL_HOST = '127.0.0.1:5000'

engine = create_engine(SQL_HOST)

DBSession = sessionmaker(bind=engine)

使用时创建session实例进行数据库操作

3. 创建表

metadata = MetaData(engine)

group = Table('group2', metadata,

                       Column('id', Integer, primary_key=True),

                       Column('members', Integer),

                       Column('comers', Integer),

                       Column('send_screenshot_comers', Integer),

                       Column('create_time', DateTime),

                       Column('update_time', DateTime))

metadata.create_all(engine)

4. 创建moduel

class Group(Base):

__tablename__ = 'group'

id = Column(Integer, primary_key=True)

members = Column(Integer)

comers = Column(Integer)

send_screenshot_comers = Column(Integer)

create_time = Column(DateTime)

update_time = Column(DateTime)

5. insert

session = DBSession()

new_group = Group(id=room_id,members=members,create_time=datetime.datetime.utcnow())

session.add(new_group)

session.commit()

session.close()

6. select

session = DBSession()

query = session.query(Group).filter(Group.id == int(group_id)).order_by('id').limit(pageSize).offset((page - 1) * pageSize)

groups = query.all() （查询到的所有的行）

group = query.one() （查询到的行里面的一个）

7. update

groups[0].members = 100

session.flush()

session.commit()

session.close()