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)