表结构
表名： vaguelabeluser 用户模糊标签表
字段：
vaguelabel_id 标签id
user_id 用户id
value 标签的值
原sql语句
select
b
.
user_id
from
(
vaguelabeluser b
inner
join
vaguelabeluser a
on
a
.
user_id
=
b
.
user_id
)
inner
join
vaguelabeluser c
on
b
.
user_id
=
c
.
user_id
where
(
b
.
vaguelabel_id
=
7
)
and
(
a
.
vaguelabel_id
=
2
)
and
(
c
.
vaguelabel_id
=
3
)
group
by
b
.
user_id
;
Python语句
from
app
.
BehaviorLabels
import
sqlModel
#自己写的，详见下方【附录1】
from
from
sqlalchemy
import
create_engine
,
and_
,
or_
from
sqlalchemy
.
orm
import
sessionmaker
,
aliased
# 链接数据库
engine
=
create_engine
(
'mysql+pymysql://root:root@localhost:3306/behaviorDB?charset=utf8'
)
Session
=
sessionmaker
(
bind
=
engine
)
dbsession
=
Session
(
)
#由于是同表，需要定义表的别名
vg
=
sqlModel
.
VagueLabelUser
#已经定义过的 类，详见下方【附录1】
vg1
=
aliased
(
sqlModel
.
VagueLabelUser
)
vg2
=
aliased
(
sqlModel
.
VagueLabelUser
)
#查询
#这里查询了 数量，和上面的sql语句有些区别
count
=
dbsession
.
query
(
vg
.
user_id
)
.
join
(
vg1
,
vg
.
user_id
==
vg1
.
user_id
)
.
join
(
vg2
,
vg
.
user_id
==
vg2
.
user_id
)
.
filter
(
and_
(
vg
.
vaguelabel_id
==
7
,
vg1
.
vaguelabel_id
==
2
,
vg2
.
vaguelabel_id
==
3
)
)
.
group_by
(
vg
.
user_id
)
.
count
(
)
【附录1】app.BehaviorLabels sqlModel.py
# coding:utf8
import
datetimefrom sqlalchemy
import
create_engine
# 会话
from
sqlalchemy
.
orm
import
sessionmaker
# 定义
from
sqlalchemy
import
Column
,
String
,
Integer
,
DateTime
,
Float
,
Boolean
,
TIMESTAMP
,
func
,
PickleType
from
sqlalchemy
.
ext
.
declarative
import
declarative_base
from
sqlalchemy
.
orm
import
relationship
from
sqlalchemy
import
ForeignKey
# 链接数据库
engine
=
create_engine
(
'mysql+pymysql://root:root@localhost:3306/behaviorDB?charset=utf8'
)
print
(
">>"
,
engine
)
# 定义数据库
Base
=
declarative_base
(
)
class
VagueLabelUser
(
Base
)
:
__tablename__
=
"VagueLabelUser"
id
=
Column
(
Integer
,
primary_key
=
True
)
# 外键
user_id
=
Column
(
Integer
,
ForeignKey
(
'InitialUser.user_id'
)
,
comment
=
"关联User表"
)
vaguelabel_id
=
Column
(
Integer
,
ForeignKey
(
'VagueLabels.id'
)
,
comment
=
"关联标签表"
)
# 值
vague_value
=
Column
(
Float
,
default
=
0
)
comment
=
Column
(
String
(
30
)
)
type
=
Column
(
Integer
,
comment
=
"用于扩展"
)
# 时间戳
createtime
=
Column
(
TIMESTAMP
(
True
)
,
server_default
=
func
.
now
(
)
,
comment
=
'创建时间'
)
updatetime
=
Column
(
TIMESTAMP
(
True
)
,
nullable
=
False
,
server_default
=
func
.
now
(
)
,
onupdate
=
func
.
now
(
)
,
comment
=
'修改时间'
)
# 标志位
validflag
=
Column
(
Boolean
,
default
=
1
,
comment
=
"有效位标志 0：无效 1：有效"
)
def
__repr__
(
self
)
:
return
'%s(%r)'
%
(
self
.
__class__
.
__name__
,
self
.
id
)
