python sqlalchemy模块使用

sqlalchemy可连接多种数据库sqlite、oracle、mysql等，以下为sqlite举例：

import sqlite3
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import func
from datetime import datetime

#若有sudentsInfo.db3就直接连接，没有就生成
conn = sqlite3.connect('F:\软件下载\自动化测试软件\pycharmProfessional\workspace\c14\sudentsInfo.db3')

#echo=True打印创建过程
engine = create_engine('sqlite:///F:\软件下载\自动化测试软件\pycharmProfessional\workspace\c14\sudentsInfo.db3')
Base = declarative_base()

class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return '%s name:%s'%(self.id,self.name)

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)
    def __repr__(self):
        return '%s name:%s'%(self.id,self.name)

class StudyRecord(Base):
    __tablename__ = 'study_record'
    id = Column(Integer,primary_key=True)
    day = Column(Integer)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey('student.id')) #外键，关联到student表
    student = relationship('Student',backref = 'my_study_record') #student与study_record可通过该字段互查
    # student = Student()
    def __repr__(self):
        return '%s day:%s Student:%s'%(self.student.name,self.day,self.student)


Base.metadata.create_all(engine) #创建表结构

#创建于数据库的会话session class，这里返回的是一个类，不是实例
Session_class = sessionmaker(bind=engine)
session = Session_class() #生成session实例
# s1 = Student(name='Alex',register_date=datetime(2016,6,5))
# s2 = Student(name='Tim',register_date=datetime(2016,4,5))
# s3 = Student(name='Max',register_date=datetime(2016,3,5))
# s4 = Student(name='Eric',register_date=datetime(2016,2,5))
#
# study_obj1 = StudyRecord(day=1,status='Yes',stu_id=1)
# study_obj2 = StudyRecord(day=2,status='No',stu_id=1)
# study_obj3 = StudyRecord(day=3,status='Yes',stu_id=1)
# study_obj4 = StudyRecord(day=1,status='Yes',stu_id=2)
#
# session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])

stu_obj = session.query(Student).filter(Student.name == 'Alex').first()
print(stu_obj.my_study_record)
session.commit()

# User_obj = User(name='Tim',password='123456') #生成要创建的数据对象
# User_obj = User(name='Kite',password='123456')
# session.add(User_obj) #吧要创建的数据对象添加到session里
# session.commit() #提交刚创建的User_obj数据

#查询
# data= session.query(User).filter_by().all()
# data= session.query(User).filter(User.id>2).filter(User.id<4).first() #多条件查询
# print(data)

#修改
# data.name = 'Bob'
# session.commit()
# print(data)

#回滚
# fake_user = User(name = 'Rain',password='123456')
# session.add(fake_user)
# fake_data = Session.query(User).filter(User.name.in_(['Tim','Rain'])).all()
# print(fake_data)
# session.rollback()
# print(fake_data)
# session.commit()

#统计
# fake_data = session.query(User).filter(User.name.in_(['Tim','Rain'])).count()
# print(fake_data)

#f分组
# data = session.query(func.count(User.name),User.name).group_by(User.name).all()
# print(data)