最新消息:20190717 VPS服务器:Vultr新加坡,WordPress主题:大前端D8,统一介绍入口:关于

【已解决】SQLAlchemy去flush时出错:IntegrityError _mysql_exceptions.IntegrityError 1062 Duplicate entry

Flask crifan 2976浏览 0评论

在Flask中使用SQLAlchemy去创建对应的数据库表的对象

然后去flush:

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])
        db.session.add(newInitiatorStartLocation)
        # db.session.flush()
        newInitiatorEndLocation = Location(longitude=initiatorEndLocation["longitude"],
                                             latitude=initiatorEndLocation["latitude"],
                                             shortStr=initiatorEndLocation["shortStr"],
                                             fullStr=initiatorEndLocation["fullStr"])
        db.session.add(newInitiatorEndLocation)
        # db.session.flush()
        newTask = Task(
            itemType = itemType,
            initiatorId = userId,
            initiatorStartLocationId = newInitiatorStartLocation.id,
            initiatorEndLocation = newInitiatorEndLocation.id
        )
        db.session.add(newTask)
        db.session.flush()
        gLog.debug("after  flush newTask=%s", newTask)

结果出错:

[2016-10-20 21:19:04 +0000] [25967] [ERROR] Error handling request /runningfast/api/v1.0/users/user-bb22f24e-3c27-4e7b-867a-b855e139b295/tasks
Traceback (most recent call last):
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/gunicorn/workers/sync.py", line 135, in handle
    self.handle_request(listener, req, client, addr)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/gunicorn/workers/sync.py", line 176, in handle_request
    respiter = self.wsgi(environ, resp.start_response)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 268, in error_router
    return self.handle_error(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 268, in error_router
    return self.handle_error(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 477, in wrapper
    resp = resource(*args, **kwargs)
  File "/root/RunningFast/staging/runningfast/resources/Accesstoken.py", line 134, in decorated_function
    return f(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 587, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/root/RunningFast/staging/runningfast/resources/Task.py", line 175, in post
    db.session.flush()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2136, in flush
    self._flush(objects)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in _flush
    transaction.rollback(_capture_exception=True)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2218, in _flush
    flush_context.execute()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 386, in execute
    rec.execute(self)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 545, in execute
    uow
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 176, in save_obj
    mapper, table, insert)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 824, in _emit_insert_statements
    execute(statement, params)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/root/Envs/RunningFast/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/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 462, in do_execute
    cursor.execute(statement, parameters)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry ‘location-5240175b-d6bf-4f9a-b2dd-45cdd2ab5e4e’ for key ‘PRIMARY’") [SQL: u’INSERT INTO locations (id, longitude, latitude, `shortStr`, `fullStr`) VALUES (%s, %s, %s, %s, %s)’] [parameters: (‘location-5240175b-d6bf-4f9a-b2dd-45cdd2ab5e4e’, 31.278478, 120.733463, ‘\xe4\xb8\xad\xe5\x9b\xbd\xe4\xba\xba\xe6\xb0\x91\xe5\xa4\xa7\xe5\xad\xa6\xe8\x8b\x8f\xe5\xb7\x9e\xe6\xa0\xa1\xe5\x8c\xba’, ‘\xe6\xb1\x9f\xe8\x8b\x8f\xe7\x9c\x81\xe8\x8b\x8f\xe5\xb7\x9e\xe5\xb8\x82\xe5\x90\xb4\xe4\xb8\xad\xe5\x8c\xba\xe6\xb0\xb8\xe7\x8f\x8a\xe8\xb7\xaf\xe4\xb8\xad\xe5\x9b\xbd\xe4\xba\xba\xe6\xb0\x91\xe5\xa4\xa7\xe5\xad\xa6\xe8\x8b\x8f\xe5\xb7\x9e\xe6\xa0\xa1\xe5\x8c\xba’)]

看起来时location出错

好像是:

前后两次调用了新建Location对象

内部都是同一个Location的ID

所以flush时,id是主键,出错了。

Flask  SQLAlchemy flush() IntegrityError  1062 Duplicate entry

sqlalchemy: how to add a table row with autoincrement in mysql – Stack Overflow

python – SQLAlchemy – bulk insert ignore: "Duplicate entry" – Stack Overflow

Small bug while syncing all clans · Issue #61 · ceari/whyattend · GitHub

自己去试试:

新建Location对象后,主动flush一次,估计就可以自动生成内部的id了

这辆两次的Location对象,分别都会使用自己的id的。

估计就可以了。

结果不行:

即使第一个location,先flush再commit

然后第二个再去flush的时候:

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])
        db.session.add(newInitiatorStartLocation)
        db.session.flush() # use generated unique id
        db.session.commit()
        gLog.debug(‘after flush and commit newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        newInitiatorEndLocation = Location(longitude=initiatorEndLocation["longitude"],
                                             latitude=initiatorEndLocation["latitude"],
                                             shortStr=initiatorEndLocation["shortStr"],
                                             fullStr=initiatorEndLocation["fullStr"])
        db.session.add(newInitiatorEndLocation)
        db.session.flush() # use generated unique id
        db.session.commit()

就会出错:

IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry ‘location-300aff2d-f56f-4c82-a36d-ac1b16af9c3c’ for key ‘PRIMARY’") [SQL: u’INSERT INTO locations (id, longitude, latitude, `shortStr`, `fullStr`) VALUES (%s, %s, %s, %s, %s)’] [parameters: (‘location-300aff2d-f56f-4c82-a36d-ac1b16af9c3c’, 31.278478, 120.733463, ‘\xe4\xb8\xad\xe5\x9b\xbd\xe4\xba\xba\xe6\xb0\x91\xe5\xa4\xa7\xe5\xad\xa6\xe8\x8b\x8f\xe5\xb7\x9e\xe6\xa0\xa1\xe5\x8c\xba’, ‘\xe6\xb1\x9f\xe8\x8b\x8f\xe7\x9c\x81\xe8\x8b\x8f\xe5\xb7\x9e\xe5\xb8\x82\xe5\x90\xb4\xe4\xb8\xad\xe5\x8c\xba\xe6\xb0\xb8\xe7\x8f\x8a\xe8\xb7\xaf\xe4\xb8\xad\xe5\x9b\xbd\xe4\xba\xba\xe6\xb0\x91\xe5\xa4\xa7\xe5\xad\xa6\xe8\x8b\x8f\xe5\xb7\x9e\xe6\xa0\xa1\xe5\x8c\xba’)]

Flask  SQLAlchemy flush again IntegrityError  1062 Duplicate entry

SQLAlchemy flush twice IntegrityError

python – Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style) – Stack Overflow

python – How to catch error 1062 "duplicate entry" independent from used database/engine? – Stack Overflow

python – Trying to catch integrity error with SQLAlchemy – Stack Overflow

SQLAlchemy and Race Conditions: Follow Up on Commits and Flushes – skien.cc

-》

感觉是:

像是每次生成的时候,多次都是同一个值

所以去试试:

    # id = db.Column(db.String(64), primary_key=True, default = generateUUID("location-"), nullable=False)
    id = db.Column(db.String(64), primary_key=True, server_default = generateUUID("location-"), nullable=False)

结果:

db.session.flush()

却又出现其它错误:

  File "/root/RunningFast/staging/runningfast/resources/Task.py", line 149, in post
    db.session.flush() # use generated unique id
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2136, in flush
    self._flush(objects)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in _flush
    transaction.rollback(_capture_exception=True)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2224, in _flush
    flush_context.finalize_flush_changes()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 408, in finalize_flush_changes
    self.session._register_newly_persistent(other)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1596, in _register_newly_persistent
    % state_str(state)
FlushError: Instance <Location at 0x7faa4bece990> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such aswithin a load() event.

SQLAlchemy flush twice Duplicate entry key PRIMARY

Python + SQLAlchemy + MySQL出现IntegrityError, Duplicate entry – 1062 – 软件开发程序员博客文章收藏网

SQLAlchemy db.session.flush() Duplicate entry key PRIMARY

SQLAlchemy db session flush IntegrityError

SQLAlchemy db Column default

Column Insert/Update Defaults — SQLAlchemy 1.1 Documentation

python – why sqlalchemy default column value not work – Stack Overflow

python – SQLAlchemy: how should I define a column’s default value computed using a reference to the table containing that column? – Stack Overflow

python – How do I set attribute default values in sqlalchemy declarative? – Stack Overflow

算了去看看上面的那个错误:

server_default FlushError Instance has a NULL identity key

SQLAlchemy server_default FlushError Instance has a NULL identity key

python – SQL Alchemy NULL identity key – Stack Overflow

python – sqlalchemy.orm.exc.FlushError: Instance has a NULL identity key – Stack Overflow

database – FlushError: Instance <XYZ at 0xc971bac> has a NULL identity key. Not able to insert in sqlalchemy? – Stack Overflow

怀疑之前的default变为server_default没有及时更新数据库,所以去更新数据库:

(RunningFast) ➜  staging python db_manager.py db migrate -m "Location default change to server_default"  

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

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:71]:
app=<Flask ‘runningfast.app’>, api=<flask_restful.Api object at 0x7f4bdba8a550>, redis_store=<flask_redis.FlaskRedis object at 0x7f4bdd0ff250>, db=<SQLAlchemy engine=’mysql://runningfast:Jiandao123@localhost/runningfast_dev’>, server_mode=staging, server_type=develop, rq=<flask_rq2.app.RQ object at 0x7f4bdba8a810>

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=promotion-63963b67-48cb-4c45-b156-0a633fd3319f

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=bill-464a9b32-c1c4-453f-9cac-824f5414fabf

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=resource-df912c79-8f44-4113-a78f-5188008075a6

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-9d31d509-fd02-4513-aff5-7971e4fd398d

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=task-aa0240b4-ddc4-49cd-affa-16c232cd0568

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=user-4b2c277f-f28a-4f30-a9ef-c23670e99886

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

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

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:165]:
API_VERSION=1.0, API_URL_PREFIX=/runningfast/api/v1.0, OPEN_API_URL_PREFIX=/runningfast/api/v1.0/open

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

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added foreign key (errandorBillTaskId)(id) on table bills
INFO  [alembic.autogenerate.compare] Detected added foreign key (initiatorBillTaskId)(id) on table bills
INFO  [alembic.autogenerate.compare] Detected added foreign key (initiatorTaskId)(id) on table resources
INFO  [alembic.autogenerate.compare] Detected added foreign key (errandorTaskId)(id) on table resources
INFO  [alembic.autogenerate.compare] Detected added foreign key (errandorId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (errandorEndLocationId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (initiatorEndLocationId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (initiatorStartLocationId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (errandorStartLocationId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (initiatorId)(id) on table tasks
INFO  [alembic.autogenerate.compare] Detected added foreign key (task_id)(id) on table tasks_promotions
INFO  [alembic.autogenerate.compare] Detected added foreign key (promotion_id)(id) on table tasks_promotions
  Generating /root/RunningFast/staging/migrations/versions/648e304bcaa5_location_default_change_to_server_.py … done
(RunningFast) ➜  staging python db_manager.py db upgrade

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

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:71]:
app=<Flask ‘runningfast.app’>, api=<flask_restful.Api object at 0x7f62f5c32550>, redis_store=<flask_redis.FlaskRedis object at 0x7f62f72a7250>, db=<SQLAlchemy engine=’mysql://runningfast:Jiandao123@localhost/runningfast_dev’>, server_mode=staging, server_type=develop, rq=<flask_rq2.app.RQ object at 0x7f62f5c32810>

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=promotion-c9dacac0-3d3a-4ea7-b3c3-7f17903a9a3b

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=bill-dd748099-45d1-458e-a01b-3562869e0a18

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=resource-b3010498-e2d5-4a8f-84e7-4b6988eac4cf

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-a60a47f2-0390-429c-87c4-6568a9440c28

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=task-f4df9bc0-c83d-4528-8f5a-24bd43c64fcb

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=user-7fd4af98-1fa8-490d-992d-489901141d92

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

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

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:165]:
API_VERSION=1.0, API_URL_PREFIX=/runningfast/api/v1.0, OPEN_API_URL_PREFIX=/runningfast/api/v1.0/open

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

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade f743d16020db -> 648e304bcaa5, Location default change to server_default

然后再去试试

错误依旧:

FlushError: Instance <Location at 0x7f78ea223a90> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such aswithin a load() event.

server_default FlushError Instance NULL identity key

Describing Databases with MetaData — SQLAlchemy 1.1 Documentation

  • 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.

See also

Server Side Defaults – complete discussion of server side defaults

"

Column Insert/Update Defaults — SQLAlchemy 1.1 Documentation

class sqlalchemy.schema.FetchedValue(for_update=False)

Bases: sqlalchemy.schema._NotAColumnExpr, sqlalchemy.sql.expression.SchemaEventTarget

A marker for a transparent database-side default.

Use FetchedValue when the database is configured to provide some automatic default for a column.

E.g.:

Column(‘foo’, Integer, FetchedValue())

Would indicate that some trigger or default generator will create a new value for the foo column during an INSERT.

See also

Triggered Columns

"

试试,不flush,直接commit

错误依旧。

db session default IntegrityError

sqlalchemy default flusherror instance has a null

看起来感觉是:

对于:

    id = db.Column(db.String(64), primary_key=True, default = generateUUID("location-"), nullable=False)

用了default的话,则每次启动flask的app时,都会去生成一次:

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-babce4f4-723a-4b10-9a67-253e49348c73

 

然后后来再去flush(),结果就又没有自动调用generateUUID去新生成对应的值

像是:

flush时,此处的default的函数,没有执行

sqlalchemy flush but default function not called

sqlalchemy column default not work

Why isn’t SQLAlchemy default column value available before object is committed? – Stack Overflow

->

当使用ORM时,server_default不太适合用于主键PK=Primary Key

-》

换成在init时候初始化

试试:

class Location(db.Model):
    __tablename__ = ‘locations’
    gLog.debug("Location")
    # id = db.Column(db.String(64), primary_key=True, default = generateUUID("location-"), nullable=False)
    id = db.Column(db.String(64), primary_key=True, nullable=False)
    #id = db.Column(db.String(64), primary_key=True, server_default = generateUUID("location-"), nullable=False)
    def __init__(self):
        self.id = generateUUID("location-")

结果提示出错:

TypeError: __init__() got an unexpected keyword argument ‘latitude’

因为初始化时传入了多个参数:

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])

-》

感觉需要:

把__init__中的所有参数,都要写进入才行。。。

也很麻烦啊。。。

SQLAlchemy Column default not working with python functions · Issue #80 · RedTurtle/eve-sqlalchemy · GitHub

zzzeek / sqlalchemy / issues / #3576 – SQLAlchemy generated table schema doesn’t take default value into consideration — Bitbucket

Default value doesn’t work · Issue #49 · aio-libs/aiopg · GitHub

How to Initialize Database With Default Values in SQLAlchemy Once After Database Creation – DZone Database

按照SQLAlchemy的官网解释:

Python-Executed Functions

是支持此处的python的函数的。

The Column.default and Column.onupdate keyword arguments also accept Python functions. These functions are invoked at the time of insert or update if no other value for that column is supplied, and the value returned is used for the column’s value.

注意到说是:

insert或update,才会调用

-》此处的flush,确认不是insert,好像也不是update?

所以不会调用此default的函数,所以没有值?

-》

算了,还是手动设置值吧:

然后手动设置的话:

        newInitiatorStartLocation = Location(id=generateUUID(“location-"),
                                              longitude=initiatorStartLocation["longitude"],
                                              latitude=initiatorStartLocation["latitude"],
                                              shortStr=initiatorStartLocation["shortStr"],
                                              fullStr=initiatorStartLocation["fullStr"])
        newInitiatorEndLocation = Location(id=generateUUID(“location-"),
                                           longitude=initiatorEndLocation["longitude"],
                                           latitude=initiatorEndLocation["latitude"],
                                           shortStr=initiatorEndLocation["shortStr"],
                                           fullStr=initiatorEndLocation["fullStr"])

的确肯定是可以的。不会出错的。location会有id的。

不过,后来去优化函数时,把上面的:

generateUUID(“location-")

优化为:

def generateLocationId():
    return generateUUID("location-")

然后table中的default设置为:

class Location(db.Model):
    __tablename__ = ‘locations’
    id = db.Column(db.String(64), primary_key=True, default = generateLocationId, nullable=False)

即:

default传递的是:函数名

而不是之前的:

generateUUID(“location-")

对于函数的调用。

由此而发现:

所有的逻辑,都是正常执行的了,就不会报错了。

(1)其中不带flush的话,则新建Location后,Location对象中的id是空的。

但是实际上内部已经创建了id了

可以看出:

在新建对应的对象:

Location或Task的时候,对应的

生成对应id的函数就执行了,输出了:

newUuid=xxx

只有在后续commit之后,才会有值:

(估计是commit内部触发对应的Location去insert时候才给Location的id去赋值)

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])
        db.session.add(newInitiatorStartLocation)
        gLog.debug(‘before flush newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        newInitiatorEndLocation = Location(longitude=initiatorEndLocation["longitude"],
                                           latitude=initiatorEndLocation["latitude"],
                                           shortStr=initiatorEndLocation["shortStr"],
                                           fullStr=initiatorEndLocation["fullStr"])
        db.session.add(newInitiatorEndLocation)
        gLog.debug(‘before flush newInitiatorEndLocation==%s’, newInitiatorEndLocation)
       newTask = Task(
            itemType = itemType,
            initiatorId = userId,
            initiatorStartLocationId = newInitiatorStartLocation.id,
            initiatorEndLocationId = newInitiatorEndLocation.id
        )
        db.session.add(newTask)
        db.session.flush()
        gLog.debug("after  flush newTask=%s", newTask)
        initiatorBill = Bill(errandFee = initiatorBill["errandFee"],
                             type = BillType.Initiator,
                             initiatorBillTaskId=newTask.id
                             )
        newTask.initiatorBill = initiatorBill
        # # for debug
        # gLog.debug(‘before flush newTask=%s’, newTask)
        # db.session.flush()
        # gLog.debug("after  flush newTask=%s", newTask)
        db.session.commit()
        gLog.debug(‘added newTask=%s’, newTask)
        gLog.debug(‘after added newTask, newInitiatorStartLocation=%s, newInitiatorEndLocation==%s’,
                   newInitiatorStartLocation, newInitiatorEndLocation)
        # for debug
        gLog.debug(‘added newTask.initiatorStartLocation=%s, newTask.initiatorBill=%s’,
                   newTask.initiatorStartLocation, newTask.initiatorBill)

log是:

<div–<——————————————————————————
DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:155]:
before flush newInitiatorStartLocation==Location:id=None,longitude=31.292745,latitude=120.719816,shortStr=xxx,fullStr=yyy

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:171]:
before flush newInitiatorEndLocation==Location:id=None,longitude=31.278478,latitude=120.733463,shortStr=xxx,fullStr=yyy

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-70f4ea6a-1349-4333-b13b-21e6c89b0b12

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-a604e760-733a-4bca-97ea-4d134afc4246

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=task-67e07aef-0f94-41b6-8216-3558cb5cf358

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:186]:
after  flush newTask=Task:id=task-67e07aef-0f94-41b6-8216-3558cb5cf358,itemType=ItemType.Small,promotionCode=,hasFinished=False,createdAt=2016-10-21 14:40:04.096303,updatedAt=2016-10-21 14:40:04.096325,initiatorId=user-bb22f24e-3c27-4e7b-867a-b855e139b295,errandorId=None,initiatorTipType=TipType.NoTip,initiatorRatingType=RatingType.OneStar,initiatorStartLocationId=None,initiatorEndLocationId=None,initiatorDescription=

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=bill-dfb11314-f7ac-4b34-b50f-e918526fb403

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:201]:
added newTask=Task:id=task-67e07aef-0f94-41b6-8216-3558cb5cf358,itemType=ItemType.Small,promotionCode=,hasFinished=False,createdAt=2016-10-21 14:40:04,updatedAt=2016-10-21 14:40:04,initiatorId=user-bb22f24e-3c27-4e7b-867a-b855e139b295,errandorId=None,initiatorTipType=TipType.NoTip,initiatorRatingType=RatingType.OneStar,initiatorStartLocationId=None,initiatorEndLocationId=None,initiatorDescription=

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:204]:
after added newTask, newInitiatorStartLocation=Location:id=location-70f4ea6a-1349-4333-b13b-21e6c89b0b12,longitude=31.2927,latitude=120.72,xxx, newInitiatorEndLocation==Location:id=location-a604e760-733a-4bca-97ea-4d134afc4246,longitude=31.2785,latitude=120.733,shortStr=xxx

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:208]:
added newTask.initiatorStartLocation=None, newTask.initiatorBill=Bill:id=bill-dfb11314-f7ac-4b34-b50f-e918526fb403,type=BillType.Initiator,totalFee=0.0errandFee=20.0,disbursementFee=0.0,extraFee=0.0,taxFee=0.0,promotionFee=0.0tipFee=0.0

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

(2)如果带了flush

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])
        db.session.add(newInitiatorStartLocation)
        gLog.debug(‘before flush newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        db.session.flush()  # to assign id value to location instance
        gLog.debug(‘after  flush newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        newInitiatorEndLocation = Location(longitude=initiatorEndLocation["longitude"],
                                           latitude=initiatorEndLocation["latitude"],
                                           shortStr=initiatorEndLocation["shortStr"],
                                           fullStr=initiatorEndLocation["fullStr"])
        db.session.add(newInitiatorEndLocation)
        gLog.debug(‘before flush newInitiatorEndLocation==%s’, newInitiatorEndLocation)
        db.session.flush() # to assign id value to location instance
        gLog.debug(‘after  flush newInitiatorEndLocation==%s’, newInitiatorEndLocation)
        newTask = Task(
            itemType = itemType,
            initiatorId = userId,
            initiatorStartLocationId = newInitiatorStartLocation.id,
            initiatorEndLocationId = newInitiatorEndLocation.id
        )
        db.session.add(newTask)
        db.session.flush()
        gLog.debug("after  flush newTask=%s", newTask)
        initiatorBill = Bill(errandFee = initiatorBill["errandFee"],
                             type = BillType.Initiator,
                             initiatorBillTaskId=newTask.id
                             )
        newTask.initiatorBill = initiatorBill
        db.session.commit()
        gLog.debug(‘added newTask=%s’, newTask)
        gLog.debug(‘after added newTask, newInitiatorStartLocation=%s, newInitiatorEndLocation==%s’,
                   newInitiatorStartLocation, newInitiatorEndLocation)
        # for debug
        gLog.debug(‘added newTask.initiatorStartLocation=%s, newTask.initiatorBill=%s’,
                   newTask.initiatorStartLocation, newTask.initiatorBill)

则对应的Location对象的id,就有值了:

before flush newInitiatorStartLocation==Location:id=None,longitude=31.292745,latitude=120.719816,xxxx

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-4ab34146-a410-404d-b17e-64aa42f69088

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:156]:
after  flush newInitiatorStartLocation==Location:id=location-4ab34146-a410-404d-b17e-64aa42f69088,longitude=31.292745,latitude=120.719816,shortStr=xxx

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:168]:
before flush newInitiatorEndLocation==Location:id=None,longitude=31.278478,latitude=120.733463,xxx

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=location-d183e63d-d8c7-4f0d-86e3-e2b7b6561230

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:170]:
after  flush newInitiatorEndLocation==Location:id=location-d183e63d-d8c7-4f0d-86e3-e2b7b6561230,longitude=31.278478,latitude=120.733463xxxx

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=task-51950748-ce8c-4ad3-8fd2-211e2b2c6fbe

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:181]:
after  flush newTask=Task:id=task-51950748-ce8c-4ad3-8fd2-211e2b2c6fbe,itemType=ItemType.Small,promotionCode=,hasFinished=False,createdAt=2016-10-21 14:53:22.775842,updatedAt=2016-10-21 14:53:22.775861,initiatorId=user-bb22f24e-3c27-4e7b-867a-b855e139b295,errandorId=None,initiatorTipType=TipType.NoTip,initiatorRatingType=RatingType.OneStar,initiatorStartLocationId=location-4ab34146-a410-404d-b17e-64aa42f69088,initiatorEndLocationId=location-d183e63d-d8c7-4f0d-86e3-e2b7b6561230,initiatorDescription=

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

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

DEBUG in utils [/root/RunningFast/staging/runningfast/common/utils.py:42]:
newUuid=bill-1d28e4c3-e022-4db7-84f6-bdb38b48858a

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:191]:
added newTask=Task:id=task-51950748-ce8c-4ad3-8fd2-211e2b2c6fbe,itemType=ItemType.Small,promotionCode=,hasFinished=False,createdAt=2016-10-21 14:53:22,updatedAt=2016-10-21 14:53:22,initiatorId=user-bb22f24e-3c27-4e7b-867a-b855e139b295,errandorId=None,initiatorTipType=TipType.NoTip,initiatorRatingType=RatingType.OneStar,initiatorStartLocationId=location-4ab34146-a410-404d-b17e-64aa42f69088,initiatorEndLocationId=location-d183e63d-d8c7-4f0d-86e3-e2b7b6561230,initiatorDescription=

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:194]:
after added newTask, newInitiatorStartLocation=Location:id=location-4ab34146-a410-404d-b17e-64aa42f69088,longitude=31.2927,latitude=120.72,zzzzz, newInitiatorEndLocation==Location:id=location-d183e63d-d8c7-4f0d-86e3-e2b7b6561230,longitude=31.2785,latitude=120.733,xxxx

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

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

DEBUG in Task [/root/RunningFast/staging/runningfast/resources/Task.py:198]:
added newTask.initiatorStartLocation=Location:id=location-4ab34146-a410-404d-b17e-64aa42f69088,longitude=31.2927,latitude=120.72,zzzzzzz, newTask.initiatorBill=Bill:id=bill-1d28e4c3-e022-4db7-84f6-bdb38b48858a,type=BillType.Initiator,totalFee=0.0errandFee=20.0,disbursementFee=0.0,extraFee=0.0,taxFee=0.0,promotionFee=0.0tipFee=0.0

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

【总结】

此处的SQLAlchemy中,之所以在flush时报错:

IntegrityError _mysql_exceptions.IntegrityError 1062 Duplicate entry

此处,之所以两次flush时,对应的default的值没有生效,主要原因是:

default之前设置为了:

generateUUID(“location-")

-》其属于:

函数的调用

-》而不是函数名

-》在对应的函数优化后,变为不带参数的:

def generateLocationId():
    return generateUUID("location-")

-》然后就可以直接设置default为函数名了:

    id = db.Column(db.String(64), primary_key=True, default = generateLocationId, nullable=False)

-》

这样的话:

后续flush,就会使得Location的id有值了。

也不会报错两个id值重复了。

其实,深层次的原因,此处还是没有完全搞清楚

-》只知道涉及到底层SQLAlchemy的代码所生成的:mysql语句的

以及default,和相关的server_default,底层所对应的sql代码

有待以后有机会再去深究。

此处贴上相关的代码:

/Users/crifan/dev/dev_root/daryun/Projects/RunningFast/sourcecode/RunningFast-Server/runningfast/common/utils.py

def generateUUID(prefix = ""):
    generatedUuid4 = uuid.uuid4()
    generatedUuid4Str = str(generatedUuid4)
    newUuid = prefix + generatedUuid4Str
    #gLog.debug("prefix=%s, generatedUuid4Str=%s, newUuid=%s", prefix, generatedUuid4Str, newUuid)
    gLog.debug("newUuid=%s", newUuid)
    return newUuid
def generateLocationId():
    return generateUUID("location-")

/Users/crifan/dev/dev_root/daryun/Projects/RunningFast/sourcecode/RunningFast-Server/runningfast/models/init.py

class Location(db.Model):
    __tablename__ = ‘locations’
    id = db.Column(db.String(64), primary_key=True, default = generateLocationId, nullable=False)

/Users/crifan/dev/dev_root/daryun/Projects/RunningFast/sourcecode/RunningFast-Server/runningfast/resources/Task.py

        newInitiatorStartLocation = Location(longitude=initiatorStartLocation["longitude"],
                                             latitude=initiatorStartLocation["latitude"],
                                             shortStr=initiatorStartLocation["shortStr"],
                                             fullStr=initiatorStartLocation["fullStr"])
        db.session.add(newInitiatorStartLocation)
        # gLog.debug(‘before flush newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        db.session.flush()  # to assign id value to location instance
        gLog.debug(‘after  flush newInitiatorStartLocation==%s’, newInitiatorStartLocation)
        newInitiatorEndLocation = Location(longitude=initiatorEndLocation["longitude"],
                                           latitude=initiatorEndLocation["latitude"],
                                           shortStr=initiatorEndLocation["shortStr"],
                                           fullStr=initiatorEndLocation["fullStr"])
        db.session.add(newInitiatorEndLocation)
        # gLog.debug(‘before flush newInitiatorEndLocation==%s’, newInitiatorEndLocation)
        db.session.flush() # to assign id value to location instance
        gLog.debug(‘after  flush newInitiatorEndLocation==%s’, newInitiatorEndLocation)
        newTask = Task(
            itemType = itemType,
            initiatorId = userId,
            initiatorStartLocationId = newInitiatorStartLocation.id,
            initiatorEndLocationId = newInitiatorEndLocation.id
        )
        db.session.add(newTask)
        db.session.flush()
        gLog.debug("after  flush newTask=%s", newTask)
        initiatorBill = Bill(errandFee = initiatorBill["errandFee"],
                             type = BillType.Initiator,
                             initiatorBillTaskId=newTask.id
                             )
        newTask.initiatorBill = initiatorBill
        db.session.commit()
        gLog.debug(‘added newTask=%s’, newTask)
        # for debug
        gLog.debug(‘added newTask.initiatorStartLocation=%s, newTask.initiatorBill=%s’,
                   newTask.initiatorStartLocation, newTask.initiatorBill)

转载请注明:在路上 » 【已解决】SQLAlchemy去flush时出错:IntegrityError _mysql_exceptions.IntegrityError 1062 Duplicate entry

发表我的评论
取消评论

表情

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

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
59 queries in 0.128 seconds, using 19.13MB memory