最新消息:20210917 已从crifan.com换到crifan.org

[已解决]SQLAlchemy插入新列时出错:IntegrityError sqlite3.IntegrityError column id is not unique

SQLAlchemy crifan 4768浏览 0评论

代码:

/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

python – sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.login – Stack Overflow

sqlite – struck at sqlalchemy.exc.IntegrityError: (IntegrityError) constraint failed ‘INSERT INTO users – 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

发表我的评论
取消评论

表情

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

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
99 queries in 0.192 seconds, using 23.55MB memory