代码：
/Users/crifan/dev/dev_root/daryun/SIPEvents/sourcecode/sipevents/sipevents/models.py
def generateUUID(prefix = “”): generatedUuid4 = uuid.uuid4() generatedUuid4Str = str(generatedUuid4) newUuid = prefix + generatedUuid4Str gLog.debug(“prefix=%s, generatedUuid4Str=%s, newUuid=%s”, prefix, generatedUuid4Str, newUuid) return newUuid class Event(db.Model): __tablename__ = “events” # Columns #id = db.Column(db.Integer, primary_key = True, autoincrement = True, nullable=False) #id = db.Column(db.String(64), primary_key=True, default=uuid.uuid4().hex, nullable=False) id = db.Column(db.String(64), primary_key=True, default = generateUUID(“event-“), nullable=False) #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), default = “”) # start_date = db.Column(db.DateTime, default = datetime.utcnow) # end_date = db.Column(db.DateTime, default = datetime.utcnow) start_date = db.Column(db.DateTime, default = func.now()) end_date = db.Column(db.DateTime, default = func.now()) location = db.Column(db.String(256), default = “”) cur_user_num = db.Column(db.Integer, default = 1) max_user_num = db.Column(db.Integer, default = 1) is_public = db.Column(db.Boolean, default = False) description = db.Column(db.Text, default = “”) notification_time = db.Column(db.Enum(NotificationTime), default = NotificationTime.NotNotify) # created_at = db.Column(db.DateTime, default = datetime.utcnow) # modified_at = db.Column(db.DateTime, default = datetime.utcnow) created_at = db.Column(db.DateTime, server_default = func.now()) modified_at = db.Column(db.DateTime, onupdate = func.now()) # joiners = db.relationship(‘EventJoiner’, backref=’user’, lazy=’dynamic’) # joiners = db.relationship(‘User’, secondary = event_joiners, backref=’events’) # joiners = db.relationship(‘User’, secondary=event_joiners, backref=’joined_events’,lazy=’dynamic’) joiners = db.relationship(‘User’, secondary=event_joiners, backref=’joined_events’) |
出错：
| <div–<—————————————————————————— DEBUG in views [/root/html/SIPEvents/sipevents/views.py:1020]: added self to joiners, newEvent=<Event:id=None,title=u’\u6253\u4f18\u6b65\u53bb\u4e0a\u6d77′,user_openid=oswjmv-QiQp-_5pFB0thKxfCZ8Tc> <div–<—————————————————————————— <div–<—————————————————————————— DEBUG in views [/root/html/SIPEvents/sipevents/views.py:1022]: before flush newEvent=<Event:id=None,title=u’\u6253\u4f18\u6b65\u53bb\u4e0a\u6d77′,user_openid=oswjmv-QiQp-_5pFB0thKxfCZ8Tc> <div–<—————————————————————————— [2016-09-10 17:32:13 +0000] [3269] [ERROR] Error handling request /creat_event Traceback (most recent call last): File “/root/Envs/SIPEvents/lib/python2.7/site-packages/gunicorn/workers/sync.py”, line 135, in handle self.handle_request(listener, req, client, addr) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/gunicorn/workers/sync.py”, line 176, in handle_request respiter = self.wsgi(environ, resp.start_response) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 2000, in __call__ return self.wsgi_app(environ, start_response) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1991, in wsgi_app response = self.make_response(self.handle_exception(e)) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1567, in handle_exception reraise(exc_type, exc_value, tb) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1988, in wsgi_app response = self.full_dispatch_request() File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1641, in full_dispatch_request rv = self.handle_user_exception(e) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1544, in handle_user_exception reraise(exc_type, exc_value, tb) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1639, in full_dispatch_request rv = self.dispatch_request() File “/root/Envs/SIPEvents/lib/python2.7/site-packages/flask/app.py”, line 1625, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File “/root/html/SIPEvents/sipevents/views.py”, line 835, in decorated_function return f(*args, **kwargs) File “/root/html/SIPEvents/sipevents/views.py”, line 1024, in creat_event db.session.flush() File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py”, line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2065, in flush self._flush(objects) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2183, in _flush transaction.rollback(_capture_exception=True) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py”, line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2147, in _flush flush_context.execute() File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py”, line 386, in execute rec.execute(self) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py”, line 545, in execute uow File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py”, line 176, in save_obj mapper, table, insert) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py”, line 812, in _emit_insert_statements execute(statement, params) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 947, in execute return meth(self, multiparams, params) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/sql/elements.py”, line 262, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1055, in _execute_clauseelement compiled_sql, distilled_params File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1191, in _execute_context context) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1386, in _handle_dbapi_exception exc_info File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/util/compat.py”, line 202, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1184, in _execute_context context) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/engine/default.py”, line 462, in do_execute cursor.execute(statement, parameters) IntegrityError: (sqlite3.IntegrityError) column id is not unique [SQL: u’INSERT INTO events (id, user_openid, title, start_date, end_date, location, cur_user_num, max_user_num, is_public, description, notification_time, modified_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’] [parameters: (‘event-0e0efccf-f6f6-4b27-a232-2f36728c8a1a’, u’oswjmv-QiQp-_5pFB0thKxfCZ8Tc’, u’\u6253\u4f18\u6b65\u53bb\u4e0a\u6d77′, ‘2016-09-12 12:30:00.000000’, ‘2016-09-12 17:30:00.000000′, u’\u4e0a\u6d77\u6d66\u4e1c’, 1, 12, 1, u’\u8bb0\u5f97\u5750\u8f66′, ‘OneDayBefore’, None)] |
但是：
此处是输入信息没问题
内部在创建的时候，感觉没有去调用：
generateUUID
导致uuid为空，而出错的。
IntegrityError: (sqlite3.IntegrityError) column id is not unique
SQLAlchemy IntegrityError: (sqlite3.IntegrityError) column id is not unique
python – SQLAlchemy integrity error : Column is not Unique – Stack Overflow
sql – Integrity Error – Flask & SQLAlchemy – Stack Overflow
SQLAlchemy default not work
Describing Databases with MetaData — SQLAlchemy 0.9 Documentation
“default –
A scalar, Python callable, or ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert. This is a shortcut to using ColumnDefault as a positional argument; see that class for full detail on the structure of the argument.
Contrast this argument to server_default which creates a default generator on the database side.
server_default¶ –
A FetchedValue instance, str, Unicode or text() construct representing the DDL DEFAULT value for the column.
String types will be emitted as-is, surrounded by single quotes:
Column(‘x’, Text, server_default=”val”)
x TEXT DEFAULT ‘val’
A text() expression will be rendered as-is, without quotes:
Column(‘y’, DateTime, server_default=text(‘NOW()’))
y DATETIME DEFAULT NOW()
Strings and text() will be converted into a DefaultClause object upon initialization.
Use FetchedValue to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger.”
去改为server_default
id = db.Column(db.String(64), primary_key=True, server_default = generateUUID(“event-“), nullable=False) |
目前所能理解的是：
- default：Python代码端去执行的
- server_default：数据库，服务器端去执行的
同时也把其它值变成了server_default：
id = db.Column(db.String(64), primary_key=True, server_default = generateUUID(“event-“), nullable=False) #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), server_default = “”) # start_date = db.Column(db.DateTime, default = datetime.utcnow) # end_date = db.Column(db.DateTime, default = datetime.utcnow) start_date = db.Column(db.DateTime, server_default = func.now()) end_date = db.Column(db.DateTime, server_default = func.now()) location = db.Column(db.String(256), server_default = “”) cur_user_num = db.Column(db.Integer, server_default = 1) max_user_num = db.Column(db.Integer, server_default = 1) is_public = db.Column(db.Boolean, server_default = False) description = db.Column(db.Text, server_default = “”) notification_time = db.Column(db.Enum(NotificationTime), server_default = NotificationTime.NotNotify) # created_at = db.Column(db.DateTime, default = datetime.utcnow) # modified_at = db.Column(db.DateTime, default = datetime.utcnow) created_at = db.Column(db.DateTime, server_default = func.now()) modified_at = db.Column(db.DateTime, onupdate = func.now()) |
结果出错：
prefix=event-, generatedUuid4Str=36a5ce2e-7a7c-4ea9-9a28-9aa833f0105a, newUuid=event-36a5ce2e-7a7c-4ea9-9a28-9aa833f0105a <div–<—————————————————————————— Traceback (most recent call last): File “db_create.py”, line 4, in <module> from sipevents import db File “/usr/share/nginx/html/SIPEvents/sipevents/__init__.py”, line 65, in <module> from sipevents import views, models File “/usr/share/nginx/html/SIPEvents/sipevents/views.py”, line 38, in <module> from . import models File “/usr/share/nginx/html/SIPEvents/sipevents/models.py”, line 101, in <module> class Event(db.Model): File “/usr/share/nginx/html/SIPEvents/sipevents/models.py”, line 118, in Event cur_user_num = db.Column(db.Integer, server_default = 1) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/sql/schema.py”, line 1209, in __init__ args.append(DefaultClause(self.server_default)) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/sql/schema.py”, line 2362, in __init__ TextClause), ‘arg’) File “/root/Envs/SIPEvents/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py”, line 1091, in assert_arg_type (name, ‘ or ‘.join(“‘%s'” % a for a in argtype), type(arg))) sqlalchemy.exc.ArgumentError: Argument ‘arg’ is expected to be one of type ‘<type ‘basestring’>’ or ‘<class ‘sqlalchemy.sql.elements.ClauseElement’>’ or ‘<class ‘sqlalchemy.sql.elements.TextClause’>’, got ‘<type ‘int’>’ |
最后，暂时用：
# Columns #id = db.Column(db.Integer, primary_key = True, autoincrement = True, nullable=False) #id = db.Column(db.String(64), primary_key=True, default=uuid.uuid4().hex, nullable=False) # id = db.Column(db.String(64), primary_key=True, default = generateUUID(“event-“), nullable=False) # id = db.Column(db.String(64), primary_key=True, server_default = generateUUID(“event-“), nullable=False) # id = db.Column(db.String(64), primary_key=True, default= (“event-%s” % uuid.uuid4()), nullable=False) id = db.Column(db.String(64), primary_key=True, default = generateUUID(“event-“), nullable=False) |
貌似是解决问题了。暂时没有出现问题。
转载请注明：在路上 » ［已解决］SQLAlchemy插入新列时出错：IntegrityError sqlite3.IntegrityError column id is not unique