表結構
表名： 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
)
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
