如何从 MySQLdb.cursors.Cursor python2.6 获取匹配的行
我正在使用 python2.6 和 MySQLdb。我有一个包含这些数据的表，
+----+--------+
| id | status |
+----+--------+
| 1 | A |
| 2 | B |
| 3 | B |
+----+--------+
我想像这个例子一样进行 mysql 更新：
UPDATE my_table SET status = "A" where id in (1,2,3,10001);
Query OK, 2 rows affected (0.03 sec)
Rows matched: 3 Changed: 2 Warnings: 0
并且我需要知道更新中的所有 id 是否都存在于数据库中。我获取此信息的想法是比较我尝试更新的项目数与匹配行数。在示例中，数字是 4 vs 3。
问题是我不知道如何从游标信息中获取“匹配行”。我只在cursor._info = 'Rowsmatched:3Changed:2Warnings:0'中看到此信息。
cursor.rowcount 是更改的行数，所以 =(
谢谢！
I'm working with python2.6 and MySQLdb. I have a table with this data
+----+--------+
| id | status |
+----+--------+
| 1 | A |
| 2 | B |
| 3 | B |
+----+--------+
I want to do an mysql update like this example:
UPDATE my_table SET status = "A" where id in (1,2,3,10001);
Query OK, 2 rows affected (0.03 sec)
Rows matched: 3 Changed: 2 Warnings: 0
And I need to know if all the ids in the update exits in the database. My idea to get this information was to compare the number of items I tried to update vs the number of matched rows. In the example the numbers are 4 vs 3.
The problem is that i don't know how to get the "Matched Rows" from the cursor information. I only see this information in cursor._info = 'Rows matched: 3 Changed: 2 Warnings: 0'.
The cursor.rowcount is the number of changed rows, so =(
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果cursor._info包含该字符串，那么您可以使用正则表达式提取3：
re.search(r'Rowsmatched:(\d+)',cursor._info).group(1)或者，如果您使用 InnoDB 表（支持事务），则可以执行两个查询：首先
SELECT id FROM my_table WHERE id in (1,2,3,10001)然后获取cursor.rowcount将返回匹配行的数量。然后执行您的更新。在同一游标中运行的所有查询都是同一事务的一部分，因此可以保证没有其他进程会在查询之间写入数据库。来源：参见 http://zetcode.com/databases/mysqlpythontutorial/
If cursor._info contains that string, then you can just extract the 3 with a regex:
re.search(r'Rows matched: (\d+)', cursor._info).group(1)Alternatively, if you are using InnoDB tables (which support transactions), you can execute two queries: first just
SELECT id FROM my_table WHERE id in (1,2,3,10001)and then getcursor.rowcountwhich will return the number of matching rows. Then execute your update. All queries run in the same cursors are part of the same transaction, so you are guaranteed that no other process will write the database between the queries.Sources: see http://zetcode.com/databases/mysqlpythontutorial/
FOUND_ROWS 选项使
cursor.rowcount返回匹配的行数：文档：
http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.constants.CLIENT-module.html
http://dev.mysql.com/doc/refman /5.6/en/mysql-real-connect.html
（MySQLdb 文档中有一个拼写错误。“client_flags”应该是“client_flag”）
The FOUND_ROWS option makes
cursor.rowcountreturn the number of matched rows instead:Docs:
http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.constants.CLIENT-module.html
http://dev.mysql.com/doc/refman/5.6/en/mysql-real-connect.html
(There's a typo in the MySQLdb docs. "client_flags" should be "client_flag")