1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/6 21:29 6 #__author__='Administrator' 7 from sqlalchemy.ext.declarative import declarative_base 8 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 9 from sqlalchemy.orm import sessionmaker, relationship10 from sqlalchemy import create_engine11 from sqlalchemy import func #统计12 13 # 用户 密码 主机 库14 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test",encoding="utf-8",echo=False)15 16 Base = declarative_base()#生成orm 基类17 class User_2(Base):18 __tablename__='user' #表名19 id = Column(Integer,primary_key=True)#整数类型 设为主键20 name = Column(String(32))#字符串类型21 age = Column(Integer)22 addr= Column(String(256))23 24 def __repr__(self):#输出查询25 return '%s>name:%s--age:%saddrs:%s'%(self.id,self.name,self.age,self.addr)26 27 28 Session_class=sessionmaker(bind=engine)#创建与数据库的会话 类29 Session=Session_class()#生成实例30 31 #增加32 '''33 user_obj =User(name="sa2",addrs="1234")#生成你要创建的数据对象34 Session.add(user_obj)# 添加记录35 '''36 #添加一组数据37 '''38 Session.add_all([39 User(name="abcg",age=34,addr="sdfsdf"),40 User(name="bcdq",age=11,addr="chaense")41 ])42 '''43 #删除44 '''45 Session.query(User).filter(User.id>3).filter(User.id<6).delete()46 '''47 # 查询 条件 所有48 data =Session.query(User_2).filter_by(id=2).all()49 # 多个条件50 data2 =Session.query(User_2).filter(User_2.id>2).filter(User_2.id<5).all()51 52 53 #改54 55 Session.query(User_2).filter(User_2.id ==2).update({ "name" : "099"})56 print(data)57 print(data2)58 '''59 print(Session.query(User).filter(User.id>2).filter(User.name.in_(["sa",'sa2'])).all())60 Session.rollback()#回滚操作61 print(Session.query(User).filter(User.id>2).filter(User.name.in_(["sa",'sa2'])).all())62 '''63 print(Session.query(User_2).filter(User_2.id>2).filter(User_2.name.in_(["sa",'sa2'])).count())#统计符合条件出现的次数64 counts=Session.query(User_2).filter(User_2.id>2).filter(User_2.name.in_(['bcd'])).count()#统计符合条件出现的次数65 print(counts)66 #分组查询 字段 统计次数 字段67 gurps=Session.query(User_2.name,func.count(User_2.name)).group_by(User_2.name).all()68 print(gurps)69 # 以addrs为条件70 gurps1=Session.query(User_2.name,func.count(User_2.name)).group_by(User_2.addr).all()71 print(gurps1)72 73 74 Session.commit()#关闭事务 sqlalchemy 基本操作
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 # 用户 密码 主机 库 6 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test",encoding="utf-8",echo=True) 7 8 Base = declarative_base()#生成orm 基类 9 class User(Base):10 __tablename__='info_l' #表名11 id = Column(Integer,primary_key=True)#整数类型 设为主键12 name = Column(String(32))#字符串类型13 addrs= Column(String(256))14 15 Base.metadata.create_all(engine)#创建表结构16 17 Session_class=sessionmaker(bind=engine)#创建与数据库的会话 类18 Session=Session_class()#生成实例19 20 user_obj =User(name="sa",addrs="1234")#生成你要创建的数据对象21 print(user_obj.name,user_obj.addrs)#此时并未创建对象22 Session.add(user_obj)#开启事务进行连接 添加记录23 Session.commit()#关闭事务
外键操作示例
1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/7 10:28 6 #__author__='Administrator' 7 import sqlalchemy 8 from sqlalchemy.ext.declarative import declarative_base 9 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,DATE10 from sqlalchemy.orm import sessionmaker, relationship11 from sqlalchemy import create_engine12 # 用户 密码 主机 库13 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test",encoding="utf-8",echo=False)14 15 Base = declarative_base()#生成orm 基类16 17 class Studes(Base):#学生类18 __tablename__ ='studes'19 id = Column(Integer,primary_key=True)#整数类型 设为主键20 name = Column(String(32),nullable=False)#字符串类型 不能为空21 register_day= Column(DATE,nullable=False)#日期22 23 def __repr__(self):#输出查询24 return '%s>name:%s--register_day :%s'%(self.id,self.name,self.register_day)25 26 27 class Studes_Cr(Base):#课程类28 __tablename__ ='stud_cr'29 id = Column(Integer,primary_key=True)#整数类型 设为主键30 day= Column(Integer,nullable=False)31 status= Column(String(35),nullable=False)#上课记录32 stu_id= Column(Integer,ForeignKey("studes.id",))#外键关联33 studes=relationship("Studes",backref="m_study_reg")#自定义关联反查34 35 def __repr__(self):#输出查询 自定义 外键关联调用36 return '%s>day:%s status:%s'%(self.studes.name,self.day,self.status)37 38 Base.metadata.create_all(engine)#创建表结构39 40 Session_class=sessionmaker(bind=engine)#创建与数据库的会话 类41 Session=Session_class()#生成实例42 43 '''44 s1 = Studes(name="Alex",register_day='2017-05-06')#学生45 s2 = Studes(name="bolg",register_day='2017-03-06')46 s3 = Studes(name="calm",register_day='2017-01-07')47 s4 = Studes(name="Defr",register_day='2017-03-16')48 s5 = Studes(name="Eale",register_day='2017-02-24')49 50 study_obj1=Studes_Cr(day=1,status="YES",stu_id=1)#上课记录51 study_obj2=Studes_Cr(day=1,status="YES",stu_id=2)#上课记录52 study_obj3=Studes_Cr(day=1,status="NO",stu_id=3)#上课记录53 study_obj4=Studes_Cr(day=1,status="YES",stu_id=4)#上课记录54 study_obj5=Studes_Cr(day=2,status="NO",stu_id=1)#上课记录55 study_obj6=Studes_Cr(day=2,status="YES",stu_id=2)#上课记录56 study_obj7=Studes_Cr(day=2,status="YES",stu_id=3)#上课记录57 58 #一次全部添加59 Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5,study_obj6,study_obj7])60 '''61 62 63 #查询 学生对应的信息64 stu_obj= Session.query(Studes).filter(Studes.name=="Alex").first()65 print(stu_obj)66 #通过外键进行相关信息查询67 print(stu_obj.m_study_reg)68 69 Session.commit()
外键 多外键关联示例: 多外键关联.py 多外键_操作.py
多外键关联.py
1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/7 13:06 6 #__author__='Administrator' 7 8 from sqlalchemy import Integer, ForeignKey, String, Column 9 from sqlalchemy.ext.declarative import declarative_base10 from sqlalchemy.orm import relationship11 from sqlalchemy import create_engine12 13 Base = declarative_base()#生成orm 基类14 class Customer(Base):15 __tablename__ = 'customer'16 id = Column(Integer, primary_key=True)17 name = Column(String(32),nullable=False)18 19 billing_address_id = Column(Integer, ForeignKey("address.id"))#帐单地址外键关联20 shipping_address_id = Column(Integer, ForeignKey("address.id"))#收货地址外键关联21 22 billing_address = relationship("Address",foreign_keys=[billing_address_id])#帐单地址自定义反查 对应外键23 shipping_address = relationship("Address",foreign_keys=[shipping_address_id])#收货地址24 25 class Address(Base):26 __tablename__ = 'address'27 id = Column(Integer, primary_key=True)28 street = Column(String(32))#详细29 city = Column(String(32))#市30 state = Column(String(255))#省31 32 def __repr__(self):33 return self.state+self.city+self.street34 35 # 用户 密码 主机 库36 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test",encoding="utf-8",echo=False)37 38 39 Base.metadata.create_all(engine)#创建表结构 多外键_操作.py
1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/7 13:21 6 #__author__='Administrator' 7 import os ,sys 8 from day66 import 多外键关联 9 from sqlalchemy.orm import relationship,sessionmaker10 from sqlalchemy import create_engine11 Session_class=sessionmaker(bind=多外键关联.engine)#创建与数据库的会话 类12 Session=Session_class()#生成实例13 '''14 addr1=多外键关联.Address(street="btxw",city='xy',state="fj")15 addr2=多外键关联.Address(street="jjqy",city='qz',state="fj")16 addr3=多外键关联.Address(street="ml",city='pt',state="fj")17 18 Session.add_all([addr1,addr2,addr3])#写入数据库19 20 c1=多外键关联.Customer(name='calmyan',billing_address=addr1,shipping_address=addr1)21 c2=多外键关联.Customer(name='alex',billing_address=addr3,shipping_address=addr2)22 Session.add_all([c1,c2])23 '''24 25 #查询26 sobj=Session.query(多外键关联.Customer).filter(多外键关联.Customer.name=='calmyan').first()27 28 print(sobj.name,sobj.billing_address,sobj.shipping_address)29 30 Session.commit()
多对多外键示例:
orm_s.py
1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/7 14:07 6 #__author__='Administrator' 7 8 #一本书可以有多个作者，一个作者又可以出版多本书 9 10 11 from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey12 from sqlalchemy.orm import relationship13 from sqlalchemy.ext.declarative import declarative_base14 from sqlalchemy import create_engine15 from sqlalchemy.orm import sessionmaker16 17 Base = declarative_base()18 19 #创建第三张表,自动维护20 book_m2m_author = Table('book_m2m_author', Base.metadata,21 Column('book_id',Integer,ForeignKey('books.id')),#关联外键,书id22 Column('author_id',Integer,ForeignKey('authors.id')),#关联外键,作者id23 )24 25 class Book(Base):#书名26 __tablename__ = 'books'27 id = Column(Integer,primary_key=True)28 name = Column(String(64))29 pub_date = Column(DATE)30 authors = relationship('Author',secondary=book_m2m_author,backref='books')31 32 def __repr__(self):33 return self.name34 35 class Author(Base):#作者36 __tablename__ = 'authors'37 id = Column(Integer, primary_key=True)38 name = Column(String(32))39 40 def __repr__(self):41 return self.name42 43 # 用户 密码 主机 库44 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/test?charset=utf8",encoding="utf-8",echo=False)45 46 47 Base.metadata.create_all(engine)#创建表结构 orm_s_aut.py
1 #!usr/bin/env python 2 #-*-coding:utf-8-*- 3 # Author calmyan 4 #python 5 #2017/7/7 14:38 6 #__author__='Administrator' 7 8 from day66 import orm_s 9 from sqlalchemy.orm import relationship,sessionmaker10 Session_class = sessionmaker(bind=orm_s.engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例11 s = Session_class() #生成session实例12 '''13 #book名14 b1 = orm_s.Book(name="跟Alex学Python")15 b2 = orm_s.Book(name="跟Alex学把妹")16 b3 = orm_s.Book(name="跟Alex学装逼")17 b4 = orm_s.Book(name="跟Alex学开车")18 19 #作者名20 a1 = orm_s.Author(name="Alex")21 a2 = orm_s.Author(name="Jack")22 a3 = orm_s.Author(name="Rain")23 24 #关联关系25 b1.authors = [a1,a2]26 b2.authors = [a1,a2,a3]27 28 s.add_all([b1,b2,b3,b4,a1,a2,a3])29 '''30 31 32 '''33 #查询 作者表 条件 作者名==alex34 aut_obj=s.query(orm_s.Author).filter(orm_s.Author.name=='Alex').first()#返回一个对象35 print(aut_obj,aut_obj.books,aut_obj.books[0])36 '''37 38 #更新39 s.query(orm_s.Book).filter(orm_s.Book.id ==2).update({ "pub_date" : "2017-06-07"})40 41 #查询 书表 条件 书id==242 book_obj=s.query(orm_s.Book).filter(orm_s.Book.id==2).first()43 book_obj2=s.query(orm_s.Book).filter(orm_s.Book.id==1).first()44 print(book_obj.authors,book_obj)#输出书的作者 书名45 #book_obj.authors.remove(aut_obj)#删除书中的一个作者46 print(book_obj2.authors,book_obj2)#输出书的作者 书名47 48 #删除作者,会把这个作者跟所有书的关联关系数据也自动删除49 '''50 s.delete(aut_obj)51 '''52 book_obj=s.query(orm_s.Book).filter(orm_s.Book.id==2).first()53 book_obj2=s.query(orm_s.Book).filter(orm_s.Book.id==1).first()54 print(book_obj.authors,book_obj)#输出书的作者 书名55 print(book_obj2.authors,book_obj2)#输出书的作者 书名56 57 58 s.commit()