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

【已解决】Python的Pymysql中INSERT没有返回值希望获取刚插入的记录的对象ID

Python crifan 2075浏览 0评论
折腾:
【记录】设计测评系统的用户注册接口
期间,遇到调用之前自己写的:
https://github.com/crifan/crifanLib/blob/master/python/crifanLib/
中的
crifanMysql.py
但是发现:此处对于Insert的话,返回的值是空的:
而相关代码是:
util/crifanLib/crifanMysql.py
            executeReturn = cursor.execute(sqlStr)
            sqlResult = cursor.fetchall()
            self.connection.commit()
            if self.isUseLog:
                logging.debug("+++ Ok to execute sql %s for %s -> return=%s, result=%s", sqlStr, actionDescription, executeReturn, sqlResult)
            executeOk = True
            resultDict["code"] = 0
            resultDict["message"] = "OK"
            resultDict["data"] = sqlResult
所以加上去打印看看:
            print(“executeReturn=%s” % executeReturn)
结果返回的是1
executeReturn=1
好像是表示没问题,但是不是我最新插入的记录的id
此处想要:
刚刚insert插入的记录的id
python pymysql insert return
How do I get the “id” after INSERT into MySQL database with Python? – Stack Overflow
mysql – Python how to know if a record inserted successfully or not – Stack Overflow
Python MySQL Insert Into
python插入记录后取得主键id的方法(cursor.lastrowid和conn.insert_id()) – xqy1522的专栏 – CSDN博客
好像:
>>> import MySQLdb
>>> connection = MySQLdb.connect(user='root')
>>> cursor = connection.cursor()
>>> cursor.execute('INSERT INTO sometable VALUES (...)')
1L
>>> connection.insert_id()
3L
>>> cursor.lastrowid
3L
>>> cursor.execute('SELECT last_insert_id()')
1L
>>> cursor.fetchone()
(3L,)
>>> cursor.execute('select @@identity')
1L
>>> cursor.fetchone()
(3L,)
  • lastrowid
  • connection.insert_id()
  • SELECT last_insert_id()
就可以得到最新插入的id了。
但是别人也提到了缺点:
万一是多个线程插入呢?
那其实是没有保证的
mysql – Python how to know if a record inserted successfully or not – Stack Overflow
Python MySQL Insert Into
python插入记录后取得主键id的方法(cursor.lastrowid和conn.insert_id()) – xqy1522的专栏 – CSDN博客
所以还是没有保证
所以此处只能从业务逻辑上加上额外判断去保证:
刚刚插入后,通过last row id得到的是我刚插入的user用户信息
通过额外判断此处的,和用户唯一绑定且存在的openid去找到
但是发现此处不行:
因为user下面的字段,没有合适的用于判断的,上面提到的openid是wechatUser的
想要先创建wechatUser,结果却又非要user的Id,但是user却还没创建,变成死循环了。。。
所以现在:
要么是新建user,然后用createTime去查询出来
但是考虑到如果是多线程,也无法完全保证两个createTime是肯定不同的,但是比last row id的逻辑要稍微更严谨些
那就去试试吧。
selectCreatedUserSql=SELECT `id` FROM `user` WHERE createTime = '2018-12-29 11:40:09.927562'
其中的时间:
2018-12-29 11:40:09.927562
都到了微秒了-》相对来说,多线程,去创建用户,且在同一微秒的概率,还是很低的,保证了逻辑上基本上是不会冲突的了
但是此处好像找不到啊
然后去试了试:
SELECT `id` FROM `user` WHERE createTime = '2018-12-29 11:40:09.927562'
在Sequel中也是返回空
但是:
SELECT `id` FROM `user` WHERE id = 5;
是可以查到结果的:
算了,还是用lastrowid吧,懒得折腾了。
通过代码调试发现:
        cursor = self.connection.cursor()
        if self.isUseLog:
            logging.debug("cursor=%s", cursor)

        try:
            executeReturn = cursor.execute(sqlStr)
            print("cursor.lastrowid=%s" % cursor.lastrowid)
            print("self.connection.insert_id()=%s" % self.connection.insert_id())
            sqlResult = cursor.fetchall()
            print("after fetchall: cursor.lastrowid=%s" % cursor.lastrowid)
            print("after fetchall: self.connection.insert_id()=%s" % self.connection.insert_id())
            self.connection.commit()
            print("after commit: cursor.lastrowid=%s" % cursor.lastrowid)
            print("after commit: self.connection.insert_id()=%s" % self.connection.insert_id())
输出:
cursor.lastrowid=12
self.connection.insert_id()=12
after fetchall: cursor.lastrowid=12
after fetchall: self.connection.insert_id()=12
after commit: cursor.lastrowid=12
after commit: self.connection.insert_id()=0
即:commit后,insert_id是空,不过lastrowid还可以拿到值。
但是此处代码始终无法获取。
去看type
        try:
            executeReturn = cursor.execute(sqlStr)
            print("type(cursor)=%s" % type(cursor))
            print("type(self.connection)=%s" % type(self.connection))
输出:
type(cursor)=<class 'pymysql.cursors.DictCursor'>
type(self.connection)=<class 'pymysql.connections.Connection'>
而此处:
sqlConnection = sqlConn.connection
log.info("type(sqlConnection)=%s", type(sqlConnection))
newUserId = sqlConnection.insert_id()
log.debug("newUserId=%s", newUserId)

log.info("type(sqlConn)=%s", type(sqlConn))
sqlCursor1 = sqlConn.cursor()
log.info("type(sqlCursor1)=%s", type(sqlCursor1))
sqlCursor2 = sqlConn.connection.cursor()
log.info("type(sqlCursor2)=%s", type(sqlCursor2))
也是正常的pymysql.cursors.DictCursor:
INFO:EvaluationSystemServer:type(sqlConnection)=<class 'pymysql.connections.Connection'>
DEBUG:EvaluationSystemServer:newUserId=0
[20181229 01:30:48 INFO 25435 MainProcess 123145340940288 Thread-6 user.py:195 post] type(sqlConn)=<class 'util.crifanLib.crifanMysql.MysqlDb'>
INFO:EvaluationSystemServer:type(sqlConn)=<class 'util.crifanLib.crifanMysql.MysqlDb'>
[20181229 01:30:48 INFO 25435 MainProcess 123145340940288 Thread-6 user.py:197 post] type(sqlCursor1)=<class 'pymysql.cursors.DictCursor'>
INFO:EvaluationSystemServer:type(sqlCursor1)=<class 'pymysql.cursors.DictCursor'>
[20181229 01:30:48 INFO 25435 MainProcess 123145340940288 Thread-6 user.py:199 post] type(sqlCursor2)=<class 'pymysql.cursors.DictCursor'>
INFO:EvaluationSystemServer:type(sqlCursor2)=<class 'pymysql.cursors.DictCursor'>
但是去用:sqlCursor.lastrowid
却出错:
  File "/Users/crifan/dev/dev_root/company/xxx/projects/EvaluationSystem/server/EvaluationSystemServer/resources/user.py", line 203, in post
    newUserId = sqlCursor.lastrowid
AttributeError: 'DictCursor' object has no attribute 'lastrowid'
pymysql AttributeError: ‘DictCursor’ object has no attribute ‘lastrowid’
Cursor.lastrowid issue · Issue #429 · PyMySQL/PyMySQL
PyMySQL/cursors.py at master · PyMySQL/PyMySQL
MySQL for Python / Discussion / Help:DictCursor object has no attribute insert_id
python – AttributeError: ‘MySQLCursor’ object has no attribute ‘commit’ – Stack Overflow
MySQL :: MySQL 5.5 Reference Manual :: 23.8.21.3 How to Get the Unique ID for the Last Inserted Row
“23.8.21.3 How to Get the Unique ID for the Last Inserted Row
If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.”
算了,懒得研究了。去试试:
SELECT last_insert_id()
结果是可以的。
【总结】
最后用:
createUserSql = """INSERT INTO `user`
    (createTime,modifyTime,lastActiveTime)
    VALUES('%s','%s','%s')""" % (curTime, curTime, curTime)
log.debug("createUserSql=%s", createUserSql)
createUserOk, resultDict = sqlConn.executeSql(createUserSql)
log.debug("%s -> %s, %s", createUserSql, createUserOk, resultDict)

lastInsertIdSql = "SELECT LAST_INSERT_ID()"
log.debug("lastInsertIdSql=%s", lastInsertIdSql)
getLastInsertIdOk, resultDict = sqlConn.executeSql(lastInsertIdSql)
log.debug("%s -> %s, %s", lastInsertIdSql, getLastInsertIdOk, resultDict)

if getLastInsertIdOk:
    # {'code': 0, 'message': 'OK', 'data': [{'LAST_INSERT_ID()': 17}]}
    lastInsertId = resultDict["data"][0]["LAST_INSERT_ID()"]
    log.debug("lastInsertId=%s", lastInsertId)
效果:
可以获取刚插入的id:
缺点:
  • 如果多线程同时操作,去Insert插入mysql的话,则此处获得的ID就不对了。
一些优化尝试:
  • 根据业务逻辑,去根据某个业务上的字段去select 查询出user才是准的
    • 但是此处没有合适的字段,用了创建时间去查:
    • SELECT `id` FROM `user` WHERE createTime = ‘2018-12-29 11:40:09.927562’
    • 结果查出来是空的
      • 具体原因未知,有机会再深究
  • 想要去通过cursor.lastrowid或connection.insert_id()去获取
    • 结果只能在myslq封装的executeSql的内部得到对应的值
    • 且commit后,connection.insert_id()就得不到了,只能返回0
    • 而executeSql的函数调用这里,用了同样的connection或cursor,结果都得不到要的id
      • 用connect,结果(由于是commit之后)connection.insert_id()返回0
      • 用sqlCursor.lastrowid,结果报错:AttributeError: ‘DictCursor’ object has no attribute ‘lastrowid’
        • 具体原因未知,有空再深究

转载请注明:在路上 » 【已解决】Python的Pymysql中INSERT没有返回值希望获取刚插入的记录的对象ID

发表我的评论
取消评论

表情

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

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