最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

[已解决]Flask的SQLAlchemy中给数据库的表中添加列表类型的字段

Flask crifan 4311浏览 0评论

之前已有:

class Event(db.Model):
    __tablename__ = “events”
    # Columns
    id = db.Column(db.Integer, primary_key = True, autoincrement = True)
    #user_openid = db.Column(db.String(64))
    #user_openid = db.Column(db.String(64), db.ForeignKey(‘user.openid’))
    user_openid = db.Column(db.String(64), db.ForeignKey(‘wechat_users.openid’))
    title = db.Column(db.String(128))
    start_date = db.Column(db.DateTime)
    end_date = db.Column(db.DateTime)
    location = db.Column(db.String(256))
    cur_user_num = db.Column(db.Integer)
    max_user_num = db.Column(db.Integer)
    is_public = db.Column(db.Boolean)
    description = db.Column(db.Text)
    def __init__(self,
                 # id,
                 user_openid,
                 title = “”,
                 location = “”,
                 start_date = None,
                 end_date = None,
                 cur_user_num = 0,
                 max_user_num = 0,
                 is_public = False,
                 description = “”):
        # self.id = id
        self.user_openid = user_openid
        self.title = title
        self.location = location
        self.start_date = start_date
        self.end_date = end_date
        self.cur_user_num = cur_user_num
        self.max_user_num = max_user_num
        self.is_public = is_public
        self.description = description
    def __repr__(self):
        return u'<Event id=%d user_openid=%s title=%r>’ % (self.id, self.user_openid, self.title)

现在想要给Event添加一个字段,是列表类型的,其中包含了对应的user_openid的列表

字符串的列表

flask sqlalchemy list

python – Using list as a data type in a column (SQLAlchemy) – Stack Overflow

Column and Data Types — SQLAlchemy 1.1 Documentation

里面有ARRAY类型

但是要确保顶层数据库支持这种类型

python – filter with more than one value on flask-sqlalchemy – Stack Overflow

Get all models from flask-sqlalchemy db – Stack Overflow

python – Where can I find a list of the Flask SQLAlchemy Column types and options? – Stack Overflow

python – Using list as a data type in a column (SQLAlchemy) – Stack Overflow

->可以考虑用:PickleType

Column and Data Types — SQLAlchemy 1.0 Documentation

-》好像是:

把一个对象,序列化称为二进制保存进去

然后读取的时候,再解析出来原先的对象?

-》由此可以把string的list,保存进去,解析出来?

或者还是:

用另外一个表去存储

此处的event的参与者joiner的list?

最后发现是:

用多对多点关系去存储

Declaring Models — Flask-SQLAlchemy Documentation (2.1)

去看具体的语法:

Relationships API — SQLAlchemy 1.1 Documentation

“relationship(argument, secondary=None, primaryjoin=None, secondaryjoin=None, foreign_keys=None, uselist=None, order_by=False, backref=None, back_populates=None, post_update=False, cascade=False, extension=None, viewonly=False, lazy=True, collection_class=None, passive_deletes=False, passive_updates=True, remote_side=None, enable_typechecks=True, join_depth=None, comparator_factory=None, single_parent=False, innerjoin=False, distinct_target_key=None, doc=None, active_history=False, cascade_backrefs=True, load_on_pending=False, bake_queries=True, strategy_class=None, _local_remote_pairs=None, query_class=None, info=None)”

SQLAlchemy 如何添加列表

python – flask-SQLAlchemy怎么添加多对多数据 – SegmentFault

具体操作,可以参考:

page.tags.append(tag_obj)

使用SQLAlchemy – 廖雪峰的官方网站

SQLAlchemy参考 – YS.Zou

python 如何使用Sqlalchemy 保存列表对象到Sql数据库? – Python – 知乎

flask-SQLAlchemy怎么添加多对多数据_问答_ThinkSAAS

然后再去考虑:

[基本解决]Framework7中如何实现列表项中添加列表

然后,此处,目前通过:

/Users/crifan/dev/dev_root/daryun/SIPEvents/sourcecode/flask/sipevents/models.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-
from sipevents import app
from . import db
# from app import db
# from datetime import datetime
event_joiners = db.Table(‘event_joiners’,
    db.Column(‘event_id’, db.Integer, db.ForeignKey(‘events.id’)),
    db.Column(‘joiner_openid’, db.Integer, db.ForeignKey(‘wechat_users.openid’))
)
class User(db.Model):
    __tablename__ = ‘wechat_users’
    # Columns
    openid = db.Column(db.String(64), primary_key=True, nullable=False)
    province = db.Column(db.String(16))
    。。。
    events = db.relationship(‘Event’, backref = ‘user’, lazy = ‘dynamic’)
    。。。
    def __repr__(self):
        return ‘<User nickname=%r openid=%s avatar_static_path=%s>’ % (self.nickname, self.openid, self.avatar_static_path)
class Event(db.Model):
    __tablename__ = “events”
    # Columns
    id = db.Column(db.Integer, primary_key = True, autoincrement = True, nullable=False)
    user_openid = db.Column(db.String(64), db.ForeignKey(‘wechat_users.openid’))
    。。。
    # joiners = db.relationship(‘EventJoiner’, backref=’user’, lazy=’dynamic’)
    #joiners = db.relationship(‘User’, secondary = event_joiners, backref=’events’)
    joiners = db.relationship(‘User’, secondary=event_joiners)
    。。。
    def __repr__(self):
        return u'<Event id=%d user_openid=%s title=%r>’ % (self.id, self.user_openid, self.title)

实现了,基本的,多对多的关系,

此处的逻辑是:

1.一个User用户,可能有用多个活动Event:

class User(db.Model):
    events = db.relationship(‘Event’, backref = ‘user’, lazy = ‘dynamic’)

2.一个活动,有多个用户User参与joiners

class Event(db.Model):
    joiners = db.relationship(‘User’, secondary=event_joiners)

注:

此处之所以没有写成:

joiners = db.relationship(‘User’, secondary = event_joiners, backref=’events’)

是考虑到:

此处的

backref=’events’

估计,就会冲突掉上面的User中的events

-》而上面的events中间,还带

backref = ‘user’

-》使得Event可以通过user去检索到所属于的用户是哪个

-》以便于对应的获得用户信息

3.两者的关系,通过对应关系的table去映射和定义:

event_joiners = db.Table(‘event_joiners’,
    db.Column(‘event_id’, db.Integer, db.ForeignKey(‘events.id’)),
    db.Column(‘joiner_openid’, db.Integer, db.ForeignKey(‘wechat_users.openid’))
)

然后之后,就可以在代码中调用了

测试代码是:

#!flask/bin/python
# -*- coding: UTF-8 -*-
from sipevents import db
from sipevents import app
from sipevents import models
from sipevents import User, Event
from datetime import  datetime, timedelta
db.drop_all()
db.create_all()
# isTestDeleteUser = True
# isTestDeleteEvent = True
isTestDeleteUser = False
isTestDeleteEvent = False
# test User create/query/delete
crifanUserJson = {
  “province”: u”江苏”,
  “openid”: “oswxxxxxxxxxxxxxxxxxxxxxxVVY”,
  “headimgurl”: “http://wx.qlogo.cn/mmopen/ajNVdqHZLLDYtIJicNl7MjwZK5c1lxAJZ253c9v3JzDib7GeE5OFrWiaRqsK1ruW1HmGaziaYETV5vQhIIbic6wHKFQ/0”,
  “language”: u”zh_CN”,
  “city”: u”苏州”,
  “country”: u”中国”,
  “sex”: 1,
  “privilege”: [],
  “nickname”: u”礼貌”
}
app.logger.debug(‘crifanUserJson=%s’, crifanUserJson)
existedUser = User.query.filter_by(openid=crifanUserJson[‘openid’]).first()
app.logger.debug(‘existedUser=%s’, existedUser)
if existedUser :
    # has exsited this user
    app.logger.debug(u’已存在此用户 existedUser=%s’, existedUser)
    db.session.delete(existedUser)
    db.session.commit()
    app.logger.debug(u”已删除该用户,重新添加”)
crifanUser = User(openid = crifanUserJson[‘openid’],
                  province = crifanUserJson[‘province’],
                  avatar_url = crifanUserJson[‘headimgurl’],
                  avatar_static_path = ‘img/avatar/%s.png’ % (crifanUserJson[‘openid’]),
                  language = crifanUserJson[‘language’],
                  city = crifanUserJson[‘city’],
                  country = crifanUserJson[‘country’],
                  sex = crifanUserJson[‘sex’],
                  nickname = crifanUserJson[‘nickname’])
app.logger.debug(‘crifanUser=%s’, crifanUser)
db.session.add(crifanUser)
db.session.commit()
app.logger.debug(‘added crifanUser=%s’, crifanUser)
currentUsers = User.query.all()
app.logger.debug(‘currentUsers=%s’, currentUsers)
currentUsersCount = len(currentUsers)
app.logger.debug(‘currentUsersCount=%s’, currentUsersCount)
if currentUsersCount > 0  :
    firstUser = currentUsers[0]
    app.logger.debug(‘firstUser=%s’, firstUser)
    if isTestDeleteUser :
        db.session.delete(firstUser)
        db.session.commit()
        app.logger.debug(‘deleted firstUser=%s’, firstUser)
# test Event create/query/delete
startDatetime = datetime.now()
app.logger.debug(‘startDatetime=%s’, startDatetime)
oneDayDelta = timedelta(days=1)
app.logger.debug(‘oneDayDelta=%s’, oneDayDelta)
endDatetime = startDatetime + oneDayDelta
app.logger.debug(‘endDatetime=%s’, endDatetime)
eventDict = {
    # ‘id’            : 1,
    ‘user_openid’   : crifanUserJson[‘openid’],
    ‘title’         : u”讨论日历的公众号名字”,
    ‘location’      : u’苏州园区林泉街299号东南大学南工院305′,
    ‘start_date’    : startDatetime,
    ‘end_date’      : endDatetime,
    ‘cur_user_num’  : 3,
    ‘max_user_num’  : 6,
    ‘is_public’     : False,
    ‘description’   : u”头脑风暴讨论公众号的名字”,
}
app.logger.debug(‘eventDict=%s’, eventDict)
existedAllEvent = Event.query.all()
app.logger.debug(‘existedAllEvent=%s’, existedAllEvent)
newEvent = Event(
                # id = eventDict[‘id’],
                user_openid = eventDict[‘user_openid’],
                title = eventDict[‘title’],
                location = eventDict[‘location’],
                start_date = eventDict[‘start_date’],
                end_date = eventDict[‘end_date’],
                cur_user_num = eventDict[‘cur_user_num’],
                max_user_num = eventDict[‘max_user_num’],
                is_public = eventDict[‘is_public’],
                description = eventDict[‘description’])
app.logger.debug(‘newEvent=%s’, newEvent)
app.logger.debug(‘type(crifanUser)=%s, crifanUser=%s’, type(crifanUser), crifanUser)
newEvent.joiners.append(crifanUser)
db.session.add(newEvent)
db.session.commit()
app.logger.debug(‘added newEvent=%s’, newEvent)
currentEvents= Event.query.all()
# app.logger.debug(‘currentEvents=%s’, currentEvents)
app.logger.debug(‘currentEvents=%r’, currentEvents)
currentEventsCount = len(currentEvents)
app.logger.debug(‘currentEventsCount=%s’, currentEventsCount)
if currentEventsCount > 0  :
    firstEvent = currentEvents[0]
    app.logger.debug(‘firstEvent=%s, type(firstEvent.joiners)=%s, firstEvent.joiners=%s’, firstEvent, type(firstEvent.joiners), firstEvent.joiners)
    if isTestDeleteEvent :
        db.session.delete(firstEvent)
        db.session.commit()
        app.logger.debug(‘deleted firstEvent=%s’, firstEvent)
if currentUsersCount > 0  :
    firstUser = currentUsers[0]
    app.logger.debug(‘firstUser=%s’, firstUser)
    #firstUserEvents = firstUser.events.all()
    firstUserEvents = firstUser.events
    app.logger.debug(‘type(firstUserEvents)=%s, firstUserEvents=%r’, type(firstUserEvents), firstUserEvents)
    # type(firstUserEvents)=<class ‘sqlalchemy.orm.collections.InstrumentedList’>, firstUserEvents=[<Event id=1 user_openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY title=u’\u8ba8\u8bba\u65e5\u5386\u7684\u516c\u4f17\u53f7\u540d\u5b57′>]

对应的输出是:

(SIPEvents) ➜  SIPEvents python db_create.py

<div–<——————————————————————————

DEBUG in __init__ [/usr/share/nginx/html/SIPEvents/sipevents/__init__.py:21]:
images=<flask_images.core.Images object at 0x7f78ba97dfd0>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in __init__ [/usr/share/nginx/html/SIPEvents/sipevents/__init__.py:24]:
db=<SQLAlchemy engine=’sqlite:////usr/share/nginx/html/SIPEvents/instance/sipevents.db’>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:60]:
crifanUserJson={‘province’: u’\u6c5f\u82cf’, ‘openid’: ‘oswjmv4X0cCXcfkIwjoDfCkeTVVY’, ‘headimgurl’: ‘http://wx.qlogo.cn/mmopen/ajNVdqHZLLDYtIJicNl7MjwZK5c1lxAJZ253c9v3JzDib7GeE5OFrWiaRqsK1ruW1HmGaziaYETV5vQhIIbic6wHKFQ/0’, ‘language’: u’zh_CN’, ‘city’: u’\u82cf\u5dde’, ‘privilege’: [], ‘country’: u’\u4e2d\u56fd’, ‘nickname’: u’\u793c\u8c8c’, ‘sex’: 1}

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:63]:
existedUser=None

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:81]:
crifanUser=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:85]:
added crifanUser=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:88]:
currentUsers=[<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>]

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:91]:
currentUsersCount=1

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:95]:
firstUser=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:104]:
startDatetime=2016-08-27 17:37:29.168406

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:107]:
oneDayDelta=1 day, 0:00:00

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:109]:
endDatetime=2016-08-28 17:37:29.168406

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:124]:
eventDict={‘location’: u’\u82cf\u5dde\u56ed\u533a\u6797\u6cc9\u8857299\u53f7\u4e1c\u5357\u5927\u5b66\u5357\u5de5\u9662305′, ‘end_date’: datetime.datetime(2016, 8, 28, 17, 37, 29, 168406), ‘title’: u’\u8ba8\u8bba\u65e5\u5386\u7684\u516c\u4f17\u53f7\u540d\u5b57′, ‘user_openid’: ‘oswjmv4X0cCXcfkIwjoDfCkeTVVY’, ‘cur_user_num’: 3, ‘description’: u’\u5934\u8111\u98ce\u66b4\u8ba8\u8bba\u516c\u4f17\u53f7\u7684\u540d\u5b57′, ‘start_date’: datetime.datetime(2016, 8, 27, 17, 37, 29, 168406), ‘is_public’: False, ‘max_user_num’: 6}

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:138]:
existedAllEvent=[]

<div–<——————————————————————————

Traceback (most recent call last):
  File “/usr/local/lib/python2.7/logging/__init__.py”, line 861, in emit
    msg = self.format(record)
  File “/usr/local/lib/python2.7/logging/__init__.py”, line 734, in format
    return fmt.format(record)
  File “/usr/local/lib/python2.7/logging/__init__.py”, line 465, in format
    record.message = record.getMessage()
  File “/usr/local/lib/python2.7/logging/__init__.py”, line 329, in getMessage
    msg = msg % self.args
  File “/usr/share/nginx/html/SIPEvents/sipevents/models.py”, line 105, in __repr__
    return u'<Event id=%d user_openid=%s title=%r>’ % (self.id, self.user_openid, self.title)
TypeError: %d format: a number is required, not NoneType
Logged from file db_create.py, line 151

<div–<——————————————————————————

DEBUG in db_create [db_create.py:153]:
type(crifanUser)=<class ‘sipevents.models.User’>, crifanUser=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:158]:
added newEvent=<Event id=1 user_openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY title=u’\u8ba8\u8bba\u65e5\u5386\u7684\u516c\u4f17\u53f7\u540d\u5b57′>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:169]:
currentEvents=[<Event id=1 user_openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY title=u’\u8ba8\u8bba\u65e5\u5386\u7684\u516c\u4f17\u53f7\u540d\u5b57′>]

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:172]:
currentEventsCount=1

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:176]:
firstEvent=<Event id=1 user_openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY title=u’\u8ba8\u8bba\u65e5\u5386\u7684\u516c\u4f17\u53f7\u540d\u5b57′>, firstEvent.user=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>, type(firstEvent.joiners)=<class ‘sqlalchemy.orm.collections.InstrumentedList’>, firstEvent.joiners=[<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>]

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:185]:
firstUser=<User nickname=u’\u793c\u8c8c’ openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY avatar_static_path=img/avatar/oswjmv4X0cCXcfkIwjoDfCkeTVVY.png>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in db_create [db_create.py:188]:
type(firstUserEvents)=<class ‘sqlalchemy.orm.dynamic.AppenderBaseQuery’>, firstUserEvents=<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x7f78bcdcf050>

<div–<——————————————————————————

->

可以看出,此处的:

type(firstEvent.joiners)

输出的是

<class ‘sqlalchemy.orm.collections.InstrumentedList’>

-》去搜了下Instrumented的含义是:adj. 仪表化的;增加了某些装置的

-》看来此处表示的是:对于已有的数据库模型,此处是SQLAlchemy额外添加的,增强的功能,此处是额外增加的一个列表。

->然后firstEvent.user也是可以得到对应的活动的创建者的。

转载请注明:在路上 » [已解决]Flask的SQLAlchemy中给数据库的表中添加列表类型的字段

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
82 queries in 0.175 seconds, using 22.14MB memory