1 from sqlalchemy.ext.declarative import declarative_base
2 from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index,VARCHAR
3 from sqlalchemy.orm import sessionmaker,relationship
4 from sqlalchemy import create_engine
5 # 创建对象的基类
6 Base=declarative_base()
7
8 """
9 1 白金
10 2 黑金
11 obj.xx ==> [obj,obj...]
12 """
13 class UserType(Base):
14 __tablename__='usertype'
15 id = Column(Integer,primary_key=True,autoincrement=True)
16 title=Column(VARCHAR(32),nullable=True,index=True)
17
18 """
19 1 方少伟 1
20 2 成套 1
21 3 小白 2
22 ut = relationship(backref='xx')
23 obj.ut ==> 1 白金
24 """
25 class Users(Base):
26 __tablename__='users'
27 id=Column(Integer,primary_key=True,autoincrement=True)
28 name=Column(String(32),nullable=True,index=True)
29 email=Column(String(16),unique=True)
30 user_type_id = Column(Integer,ForeignKey('usertype.id'))
31
32 # 与生成表结构无关,仅用于查询方便
33 user_type=relationship('UserType',backref='xxoo')
34
35 __table_args__ = (
36 UniqueConstraint('id','name',name='uni_id_name'),
37 Index('ix_n_ma','name','email')
38 )
39 #创建引擎 引擎调用dialect来选择相应的模块来操作数据库
40 engine=create_engine('mysql+pymysql://root:123@127.0.0.1:3306/db1?charset=utf8',max_overflow=5)
41 Base.metadata.create_all(engine) # 创建模块中所有继承base的class的表
42
43 Session= sessionmaker(bind=engine) # 从连接池中取得一个连接
44 session=Session() # 实例化出来一个会话连接,session用于创建程序和数据库之间的会话,所有对象的载入和保存都需要通过session对象
45 ###########增加########
46 obj1=UserType(title='普通')
47 session.add(obj1)
48
49 objs=[
50 UserType(title='超级'),
51 UserType(title='白金'),
52 UserType(title='黑金'),
53 ]
54 session.add_all(objs)
55 session.commit()
56
57 ###########查找########
58 print(session.query(UserType))
59 user_type_list = session.query(UserType).all()
60 print(type(user_type_list[0])) # 对象
61 for row in user_type_list:
62 print(row.id,row.title)
63
64 #############删除###########
65 session.query(UserType.id,UserType.title).filter(UserType.id>2).delete()
66 session.commit()
67 #############修改#############
68 session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({'title':'黑金'})
69 session.query(UserType.id,UserType.title).filter(UserType.id> 0).update({UserType.title:UserType.title+"x"},synchronize_session=False)
70 session.query(UserType.id,UserType.title).filter(UserType.id>0).update({'num':Users.num+1},synchronize_session='evaluate')
71 session.commit()
72
73 # 分组,排序,连表,通配符,子查询,limit,union,where,原生sql
74
75
76 # 条件
77 ret = session.query(Users).filter_by(name='alex').all() # filter_by 里面放参数
78 ret = session.query(Users).filter(Users.id <9,Users.name=='alex').all() #默认为and的关系 filter 里面放表达式
79 # 不加.all() 返回迭代器对象 加.all() 返回所有的对象,放在一个列表里
80 print(ret[0].name)
81 ret = session.query(Users).filter(Users.id.between(1,3),Users.name=='alex')
82 for row in ret:
83 print(row.name)
84 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
85 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() #表示非的意思
86
87 from sqlalchemy import and_,or_
88 ret = session.query(Users).filter(and_(Users.id <9,Users.name=='alex'))
89
90 ret = session.query(Users).filter(
91 or_(
92 Users.id<2,
93 and_(Users.name == 'eric', Users.id > 3),
94 Users.email != ''
95 )
96 )
97
98
99 # 通配符
100 ret = session.query(Users).filter(~Users.name.like('e%'))
101 #限制
102 ret = session.query(Users)[1:2]
103 # 排序
104 ret = session.query(Users).order_by(Users.name.desc())
105 ret = session.query(Users).order_by(Users.name.desc(),Users.id.asc())
106 #
107 # #分组
108 from sqlalchemy import func
109 ret = session.query(Users).group_by(Users.email).all()
110
111 ret = session.query(
112 func.max(Users.id),
113 func.min(Users.id),
114 func.sum(Users.id),
115 ).group_by(Users.name).having(func.min(Users.id)>2).all()
116
117
118
119 #连表的两种方式:
120 ret=session.query(Users,UserType) #系统自动补全Users.user_type_id==UserType.id
121 ret1=session.query(Users,UserType).filter(Users.user_type_id==UserType.id)
122
123 result=session.query(Users).join(UserType)
124 result1=session.query(Users).join(UserType,isouter=True) #相当于left join
125 print(result1)
126
127
128 ###########临时表的用法###########
129 1
130 ret1=session.query(Users,UserType).filter(Users.user_type_id==UserType.id)
131 for row in ret1:
132 print(row.Users.id,row.UserType.title)
133 2
134 q1=session.query(UserType).filter(UserType.id>0).subquery()
135 result=session.query(q1).all()
136 print(result)
137 3
138 result=session.query(UserType.id,session.query(Users.id).as_scalar())
139 print(result)
140 for row in result:
141 print(row[0])
142
143
144 result1=session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar())
145 print(result1)
146
147 #组合
148
149 q1 = session.query(Users.name).filter(Users.id <3)
150 q2= session.query(UserType.title).filter(UserType.id <3)
151 ret = q1.union(q2).all()
152 print(ret)
153
154 q1 = session.query(Users.name).filter(Users.id <3)
155 q2= session.query(UserType.title).filter(UserType.id <3)
156 ret = q1.union_all(q2).all()
157 print(ret)
158
159
160 #########################relationship***************************
161 # 问题:获取用户信息以及与其关联的用户类型名称(fk,relationship ==> 正向操作)
162 user_list1=session.query(Users,UserType).join(UserType,isouter=True)
163 print(user_list1)
164 for row in user_list1:
165 print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)
166
167 user_list2=session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
168 print(user_list2)
169 for row in user_list2:
170 print(row.name,row.title)
171
172 user_list=session.query(Users)
173 for row in user_list:
174 print(row.id,row.name,row.user_type.title)
175
176
177 # 问题二:获取用户类型下的用户名 反向操作
178 type_list1=session.query(UserType)
179 for row in type_list1:
180 print(row.id,row.title,session.query(Users).filter(Users.user_type_id==row.id).all())
181
182 type_list=session.query(UserType)
183 for row in type_list:
184 print(row.id,row.title)
185 for li in row.xxoo:
186 list=li.name
187 print(list)
188
189 session.close()