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