之前已有：
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)
python 如何使用Sqlalchemy 保存列表对象到Sql数据库？ – Python – 知乎
flask-SQLAlchemy怎么添加多对多数据_问答_ThinkSAAS
然后再去考虑：
然后，此处，目前通过：
/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也是可以得到对应的活动的创建者的。