from sqlalchemy import create_engine,func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,DATE from sqlalchemy.orm import sessionmaker from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship engine = create_engine("mysql+pymysql://root:123456@192.168.181.128/ltest", encoding='utf-8', echo=False)#类似于pymysql 的connect，echo等于True要刷日志 Base = declarative_base() # 生成orm基类，定义一个实例，所有表必须继承该实例 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 "<name(name='%s')>"%self.name class StudyRecord(Base): __tablename__ = "study_record" id = Column(Integer,primary_key=True) day = Column(Integer,nullable=False)#nullable是不能为空 status =Column(String(32),nullable=False) stu_id = Column(Integer,ForeignKey("student.id")) student = relationship("Student", backref="my_study_record") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项 # student = query(Student).filter(Student.id == stu_obj.stu_id).first() relationship另一种介绍 def __repr__(self): return "<%s day :%s status %s>" % (self.student.name,self.day,self.status) Base.metadata.create_all(engine) # 创建表结构 #show create tables study_record; 查询表结构 Session_class = sessionmaker(bind=engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = Session_class() # 生成session实例,类似于pymysql的cursor #插入数据 # s1 = Student(name="Anne",register_date="2014-05-21") # s2 = Student(name="Jack",register_date="2015-05-21") # s3 = Student(name="Rain",register_date="2016-05-21") # s4 = Student(name="Eric",register_date="2017-05-21") # # # 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=1) # study_obj5 = StudyRecord(day=4,status="YES",stu_id=2) # session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5]) stu_obj = session.query(Student).filter(Student.name=="Anne").first() print(stu_obj.my_study_record) session.commit()
from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() engine = create_engine("mysql+pymysql://root:123456@192.168.181.128/ltest", encoding='utf-8', echo=False)#类似于pymysql 的connect，echo等于True要刷日志 class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64)) billing_address_id = Column(Integer, ForeignKey("address.id")) shipping_address_id = Column(Integer, ForeignKey("address.id")) billing_address = relationship("Address",foreign_keys=[billing_address_id]) shipping_address = relationship("Address",foreign_keys=[shipping_address_id]) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) def __repr__(self): return self.street Base.metadata.create_all(engine) # 创建表结构
from practice import orm_Multiple_foreign_keys from sqlalchemy.orm import sessionmaker Session_class =sessionmaker(bind=orm_Multiple_foreign_keys.engine) session = Session_class() #创建 # addr1 = orm_Multiple_foreign_keys.Address(street="tiankonggyuan",city="Pudongxinqu",state = "shang hai") # addr2 = orm_Multiple_foreign_keys.Address(street="yananxilu",city="Changling",state = "shang hai") # addr3 = orm_Multiple_foreign_keys.Address(street="tiananmen",city="Chaoyangqu",state = "Bei jing") # # session.add_all([addr1,addr2,addr3]) # c1 = orm_Multiple_foreign_keys.Customer(name="Anne",billing_address=addr1,shipping_address=addr2) # c2 = orm_Multiple_foreign_keys.Customer(name="Jack",billing_address=addr3,shipping_address=addr3) # # session.add_all([c1,c2]) obj =session.query(orm_Multiple_foreign_keys.Customer).filter(orm_Multiple_foreign_keys.Customer.name == "Anne").first() print(obj.name,obj.billing_address,obj.shipping_address) session.commit()
from sqlalchemy import Table,Column,String,Integer,DATE,ForeignKey from sqlalchemy.orm import relationship,sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine Base = declarative_base() engine = create_engine("mysql+pymysql://root:123456@192.168.181.128/ltest", encoding='utf-8', echo=False)#类似于pymysql 的connect，echo等于True要刷日志 #创建表 book_m2m_author = Table('book_m2m_author',Base.metadata, Column('book_id',Integer,ForeignKey('books.id')), Column('author_id',Integer,ForeignKey('authors.id')) ) class Book(Base): __tablename__ = 'books' id = Column(Integer,primary_key=True) name = Column(String(64)) pud_date = Column(DATE) authors = relationship('Author',secondary = book_m2m_author,backref = 'books')#secondary 查询第3张表关联，反向去查询 def __repr__(self): return self.name class Author(Base): __tablename__ = 'authors' id = Column(Integer,primary_key=True) name = Column(String(32)) def __repr__(self): return self.name Base.metadata.create_all(engine) session_class = sessionmaker(bind=engine) session = session_class() # b1 = Book(name="跟Anne学Python") # b2 = Book(name="跟Anne学把妹") # b3 = Book(name="跟Anne学装逼") # b4 = Book(name="跟Anne学开车") # # a1 = Author(name="Anne") # a2 = Author(name="Jack") # a3 = Author(name="Rain") # # # b1.authors = [a1,a2] # b2.authors = [a1,a2,a3] # # session.add_all([b1,b2,b3,b4,a1,a2,a3]) # session.commit() print('--------通过书表查关联的作者---------') book_obj = session.query(Book).filter_by(name="跟Anne学Python").first() print(book_obj.name, book_obj.authors) print('--------通过作者表查关联的书---------') author_obj = session.query(Author).filter_by(name="Anne").first() print(author_obj.name, author_obj.books) session.commit() #通过书删除作者 author_obj = session.query(Author).filter_by(name="Jack").first() book_obj = session.query(Book).filter_by(name="跟Anne学把妹").first() book_obj.authors.remove(author_obj) # 从一本书里删除一个作者 session.commit() #直接删除作者 author_obj =session.query(Author).filter_by(name="Anne").first() session.delete(author_obj) session.commit()
处理中文
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式
eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)
relationship的使用：使两个表之间产生管理，类似于合成一张表，可以直接取出关联的表，进行获取数据，而不需要join操作