{"id":3864,"date":"2022-04-11T17:18:37","date_gmt":"2022-04-11T09:18:37","guid":{"rendered":"http:\/\/benjenq.ddns.net\/blog\/%e3%80%90%e7%a2%bc%e8%be%b2%e3%80%91%e8%a7%a3%e6%9e%90-python-sqlite-%e7%9a%84-select-%e6%93%8d%e4%bd%9c%ef%bc%88%e5%8f%a6%e8%a3%9c%e5%85%85-mysql-%ef%bc%89\/"},"modified":"2022-04-11T17:18:37","modified_gmt":"2022-04-11T09:18:37","slug":"%e3%80%90%e7%a2%bc%e8%be%b2%e3%80%91%e8%a7%a3%e6%9e%90-python-sqlite-%e7%9a%84-select-%e6%93%8d%e4%bd%9c%ef%bc%88%e5%8f%a6%e8%a3%9c%e5%85%85-mysql-%ef%bc%89","status":"publish","type":"post","link":"http:\/\/benjenq.ddns.net\/blog\/%e3%80%90%e7%a2%bc%e8%be%b2%e3%80%91%e8%a7%a3%e6%9e%90-python-sqlite-%e7%9a%84-select-%e6%93%8d%e4%bd%9c%ef%bc%88%e5%8f%a6%e8%a3%9c%e5%85%85-mysql-%ef%bc%89\/","title":{"rendered":"\u3010\u78bc\u8fb2\u3011\u89e3\u6790 Python SQLite \u7684 SELECT \u64cd\u4f5c\uff08\u53e6\u88dc\u5145 MySQL \uff09"},"content":{"rendered":"

\"pySqliteLogo.png\"<\/p>\n

\u5148\u8aaa\u660e\u9019\u7bc7\u53ef\u80fd\u4e0d\u662f\u4ec0\u9ebc\u624b\u628a\u624b\u65b0\u624b\u6559\u5b78\u6587\uff0c\u800c\u662f\u64b0\u5beb Python \u5b58\u53d6 SQLite \uff08\u6216\u5176\u4ed6\uff09\u8cc7\u6599\u5eab\u6642\uff0c\u767c\u73fe\u6709\u4e9b\u91cd\u8981\u4f46\u53ef\u80fd\u5bb9\u6613\u88ab\u5ffd\u7565\u7684\u7d30\u7bc0\uff0c\u6240\u4ee5\u5beb\u7bc7\u6587\u7ae0\u4f86\u8a18\u9304\u5fc3\u5f97\uff0c\u672c\u6587\u5167\u5bb9\u6703\u5118\u91cf\u7cbe\u7c21\uff08\u5148\u524d\u7684\u6280\u8853\u6027\u6587\u7ae0\uff0c\u6709\u4e9b\u5167\u5bb9\u5ee2\u8a71\u592a\u591a\uff0c\u8907\u7fd2\u8d77\u4f86\u6709\u9ede\u75db\u82e6…\uff09<\/p>\n

<\/p>\n

\u70ba\u4e86\u5feb\u901f\u9032\u5165\u6b63\u984c\uff0c\u5148\u7528 SQLite \u5de5\u5177\u5feb\u901f\u5efa\u7acb demo \u8cc7\u6599\u5eab\uff0c\u7522\u751f\u7c21\u55ae\u7684\u96fb\u73a9\u904a\u6232\u8cc7\u6599\u5982\u4e0b\uff1a<\/p>\n

\"tb_games.png\"<\/p>\n

\u63a5\u8457\u64b0\u5beb\u5c11\u8a31\u7a0b\u5f0f\u78bc\uff0c\u5c0d\u8cc7\u6599\u5eab\u9032\u884c SELECT \u8b80\u53d6\uff1a<\/p>\n

import sqlite3\nconn = sqlite3.connect(\"demo.db\")\nrows = conn.execute(\"select * from games;\")\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\nconn.close()\n<\/pre>\n
\u7d50\u679c\u8f38\u51fa\u5982\u4e0b\uff1a<\/p>\n
type(rows) : <class ‘sqlite3.Cursor’>
\nrows : <sqlite3.Cursor object at 0x7fa31296d180>
\ntype(row) : <class ‘tuple’>
\nrow : (1, ‘\u5730\u5e73\u7dda \u897f\u57df\u7981\u5730’, ‘PS4,PS5’, 1790)
\ntype(row) : <class ‘tuple’>
\nrow : (2, ‘\u8dd1\u8eca\u6d6a\u6f2b\u65c5 7’, ‘PS4,PS5’, 1990)
\ntype(row) : <class ‘tuple’>
\nrow : (3, ‘\u827e\u723e\u767b\u6cd5\u74b0’, ‘PS4,PS5,PC,XBOX’, 1690)<\/p>\n
\u5c07\u7a0b\u5f0f\u78bc\u8207\u7d50\u679c\u5408\u6210\uff0c\u53ef\u4ee5\u770b\u5f97\u66f4\u6e05\u695a\uff1a<\/p>\n
\"row.png\"<\/p>\n
\u7a0b\u5f0f\u78bc\u7e3d\u5171 9 \u884c\uff0c\u7b2c 2~3 \u884c\u7522\u751f\u7d50\u679c rows \uff0c\u7b2c 4~5 \u884c print \u6aa2\u8996 rows \u985e\u578b\u8207\u5167\u5bb9\uff0c\u7b2c 6 \u884c for \u8ff4\u5708\u5c07 rows \u9010\u7b46\u6311\u51fa\u70ba row\uff0c\u7b2c 7~8 \u884c print \u5206\u5225\u6aa2\u8996 row \u7684\u985e\u578b\u8207\u5167\u5bb9\uff0c\u6700\u5f8c\u4e00\u884c\u95dc\u9589\u8cc7\u6599\u5eab\u9023\u7dda\u3002<\/p>\n
\u7a0b\u5f0f\u78bc\u7684\u57fa\u672c\u908f\u8f2f\u770b\u8d77\u4f86\u4f3c\u4e4e\u6c92\u4ec0\u9ebc\u554f\u984c\uff0c\u4e5f\u80fd\u8dd1\u51fa\u9810\u671f\u7684\u7d50\u679c\u3002\u4e0d\u904e rows \u537b\u4e0d\u662f\u672c\u4ee5\u70ba\u7684\u8cc7\u6599\u96c6\u5408\u985e\u578b\uff0c\u800c\u662f\u300csqlite3.Cursor\u300d\u985e\u578b\uff0c\u6240\u4ee5 print \u5167\u5bb9\u4e5f\u770b\u4e0d\u51fa\u6240\u4ee5\u7136\u3002\u900f\u904e Debug \u5de5\u5177\u67e5\u770b rows \u88e1\u9762\u5230\u5e95\u9577\u4ec0\u9ebc\u6a23\u5b50\uff1a<\/p>\n
\"\u622a\u5716_2022-04-11_\u4e0b\u53483_21_49.png\"<\/p>\n
\u5766\u767d\u8aaa\u6709\u9ede\u96e3\u61c2\uff0c\u770b\u8d77\u4f86\u76f4\u89ba\u4e0d\u592a\u50cf\u662f\u8cc7\u6599\u96c6\u5408\u7684\u6a23\u5b50\uff0c\u4e0d\u904e\u795e\u5947\u7684\u662f for \u8ff4\u5708\u4ecd\u80fd\u9010\u7b46\u6311\u51fa\u8cc7\u6599\uff0c\u5230\u5e95 for \u4e86\u4ec0\u9ebc\u539f\u7406\u5be6\u5728\u7121\u5f9e\u5f97\u77e5\uff0c\u4e0d\u5f97\u4e0d\u5c0d python \u7684\u8a9e\u6cd5\u611f\u5230\u535a\u5927\u7cbe\u6df1\u3002\u96d6\u7136\u4e0d\u592a\u61c2\uff0c\u4f46\u53ef\u770b\u5230\u4e0a\u5716\u7684 sqlite3.Cousor \u4e2d\u6709\u5169\u500b\u5c6c\u6027 connection \u8207 description\uff0c\u5f8c\u9762\u6703\u63d0\u5230\u3002<\/p>\n
\u63a5\u4e0b\u4f86\u8981\u9032\u5165\u91cd\u9ede\u4e86\u3002\u7a0b\u5f0f\u908f\u8f2f\u57fa\u672c\u6c92\u5565\u554f\u984c\uff0c\u4f46\u662f\u5728\u5be6\u52d9\u9762\u4e0a\u6703\u9047\u5230\u5169\u500b\u5927\u554f\u984c\uff1a\u95dc\u9589\u8cc7\u6599\u5eab conn.close() \u7684\u6642\u6a5f\u4e0d\u5c0d\uff08\u592a\u665a\uff09\uff0c\u4ee5\u53ca\u53d6\u5f97\u7684 row \u8cc7\u6599\u65bc\u5f8c\u7e8c\u7684\u61c9\u7528\u4e0d\u65b9\u4fbf\u3002<\/p>\n
\u554f\u984c\u4e00\uff1a\u95dc\u9589\u8cc7\u6599\u5eab\u9023\u7dda\u7684\u6b63\u78ba\u6642\u6a5f<\/strong><\/span><\/p>\n
\u4ee5\u9019\u6b21 Demo \u7684\u7a0b\u5f0f\u6b65\u9a5f\u4f86\u8aaa\uff0c\u53d6\u5f97 rows \u4e4b\u5f8c\uff0c\u518d\u8dd1 for \u8ff4\u5708\u9010\u7b46\u8655\u7406\u6bcf\u4e00\u7b46\u8cc7\u6599\uff0c\u76f4\u5230\u6240\u6709\u8cc7\u6599\u90fd\u8655\u7406\u5b8c\u6642\u624d\u95dc\u9589\u3002\u4f46\u5be6\u52d9\u61c9\u7528\u6642\uff0cfor \u8ff4\u5708\u53ef\u80fd\u6703\u9700\u8981\u4e00\u6bb5\u6642\u9593\uff0c\u4f8b\u5982\u6311\u51fa\u6bcf\u4e00\u7b46\u8cc7\u6599\u51fa\u4f86\u5f8c\u7db2\u8def\u722c\u87f2\uff0c\u5230\u5404\u5927\u96fb\u5546\u8ce3\u5834\u641c\u5c0b\u904a\u6232\u8cc7\u8a0a\u7b49\u7b49\uff0c\u9019\u4e9b\u52d5\u4f5c\u80af\u5b9a\u6703\u82b1\u4e0a\u4e0d\u5c11\u6642\u9593\uff0c\u4e5f\u610f\u5473\u8457\u7a0b\u5f0f\u5728\u9019\u6bb5\u6642\u9593\u5167\uff0c\u6703\u6301\u7e8c\u4f54\u7528\u8457\u9023\u7dda\u8cc7\u6e90\u3002\u4ee5\u672c\u6a5f\u8cc7\u6599\u5eab SQLite \u800c\u8a00\u6216\u8a31\u9084\u53ef\u4ee5\u63a5\u53d7\uff0c\u4f46\u82e5\u662f\u7570\u5730\u7aef\u8cc7\u6599\u5eab\u5982 MySQL\u3001MS SQL\u3001Oracle \u9019\u985e\u63d0\u4f9b\u591a\u4eba\u9023\u7dda\u5b58\u53d6\u7684\u8cc7\u6599\u5eab\u670d\u52d9\uff0c\u90a3\u9ebc\u53ea\u8981\u591a\u5e7e\u500b\u9019\u7a2e\u9577\u4f54\u9023\u7dda\u8cc7\u6e90\u7684\u7528\u6236\u7aef\uff0c\u8cc7\u6599\u5eab\u670d\u52d9\u5c31\u5f88\u5bb9\u6613\u51fa\u73fe\u554f\u984c\u3002<\/p>\n
\u6240\u4ee5\u6b63\u78ba\u7684\u65b9\u5f0f\u662f\uff0c\u7528\u6236\u7aef\u4e00\u65e6\u6488\u5b8c\u8cc7\u6599\u6642\uff0c\u8cc7\u6599\u5eab\u7aef\u5c31\u5f97\u95dc\u9589\u3002\u90a3\u597d\u554a\uff5e\u5f88\u7c21\u55ae\u554a\uff5e\u65e2\u7136\u5982\u6b64\uff0c\u76f4\u63a5\u4fee\u6539\u7a0b\u5f0f\uff0c\u628a conn.close() \u76f4\u63a5\u79fb\u5230\u53d6\u5f97 rows \u8cc7\u6599\u5f8c\uff0c\u653e\u5728 for \u8ff4\u5708\u4e4b\u524d\uff0c\u9019\u6a23\u7e3d\u884c\u4e86\u5427\uff1f <\/p>\n
\u5f88\u907a\u61be\uff0c\u902e\u64e0\u4e0d\u662f\u61a8\u4eba\u60f3\u7684\u90a3\u9ebc\u7c21\u55ae\u3002\u6539\u5b8c\u5f8c\u7a0b\u5f0f\u76f4\u63a5\u5831\u932f\uff0c\u5982\u4e0b\uff1a<\/p>\n
\"err.png\"<\/p>\n
\u539f\u56e0\u5728\u65bc rows \u4e26\u975e\u55ae\u7d14\u7684\u8cc7\u6599\u96c6\u5408\uff0c\u800c\u662f sqlite3.Cousor \u985e\u578b\u7684\u7269\u4ef6\uff0c\u88e1\u9762\u7684 connection \u5c6c\u6027\u8207\u8cc7\u6599\u5eab\u9023\u7dda\u662f\u9023\u52d5\u7684\uff0c\u7576\u9023\u7dda\u95dc\u9589\u4e4b\u5f8c\uff0crows \u5c31\u4e0d\u5141\u8a31\u7a0b\u5f0f\u5c0d\u5167\u90e8\u7684\u8cc7\u6599\u9032\u884c\u64cd\u4f5c\u3002<\/p>\n
\u90a3\u9ebc\uff0c\u9019\u500b\u554f\u984c\u5230\u5e95\u8981\u600e\u9ebc\u89e3\u6c7a\uff1f\u5176\u5be6\u4e00\u822c\u7684\u7db2\u8def\u6559\u7a0b\uff0c\u5df2\u7d93\u544a\u8a34\u4f60\u8981\u4f7f\u7528 cursor \u64cd\u4f5c\u4e86\uff0c\u53ea\u662f\u5f88\u5c11\u6703\u544a\u8a34\u4f60\u70ba\u4ec0\u9ebc\u8981\u9019\u9ebc\u505a\uff0c\u4e0a\u9762 demo \u5831\u932f\u4f8b\u5b50\u6216\u8a31\u53ef\u4ee5\u8aaa\u660e\u539f\u56e0\u3002<\/p>\n
\u5e7e\u4e4e\u6240\u6709\u7684\u7db2\u8def\u6559\u5b78\uff0ccursor \u64cd\u4f5c\u7684\u7bc4\u4f8b\u90fd\u662f\u4e0b\u9762\u7684\u5beb\u6cd5\uff1a<\/p>\n
\"result.png\"<\/p>\n
\u9019\u7a2e\u5beb\u6cd5\u7684\u6982\u5ff5\u662f\uff0c\u6a6b\u7a7a\u51fa\u4e16\u5ba3\u544a\u4e00\u500b cursor \u7269\u4ef6 c = conn.cursor() \uff0c\u7531 c.execute(…) \u4f86\u66ff\u4ee3\u672c\u4f86\u7684 conn.execute(…) \u64cd\u4f5c\u3002cursor c \u5728 execute(…) \u53d6\u5f97\u8cc7\u6599\uff0c\u518d\u52a0\u4e0a\u4e00\u500b\u53eb fetchall() \u7684\u52d5\u4f5c\uff0c\u5c07\u8cc7\u6599\u56de\u50b3\u7d66 rows \u4e4b\u5f8c\uff0c\u5c31\u53ef\u4ee5\u95dc\u9589\u8cc7\u6599\u5eab\u9023\u7dda\u4e86\u3002<\/p>\n
\u5f9e\u4e0a\u5716\u4e2d\u4e5f\u770b\u5230\u4e86\uff0cfeatchall() \u4e4b\u5f8c\u56de\u50b3\u8cc7\u6599\u7684 rows \u4e5f\u4e0d\u518d\u662f sqlite3.Cursor \u985e\uff0c\u800c\u662f\u6a19\u6e96\u7684\u8cc7\u6599\u96c6\u5408\u300clist\u300d\u985e\u3002\u7528 print \u6aa2\u8996 rows \u7684\u5167\u5bb9\u662f 3 \u7b46 tuple \u985e\u578b\u7684\u904a\u6232\u8cc7\u6599\uff0c\u800c\u9019\u4e9b\u904a\u6232\u8cc7\u6599\u5f8c\u7e8c\u518d\u600e\u9ebc\u722c\u87f2\u61c9\u7528\u3001\u4e5f\u8207\u539f\u4f86\u7684\u8cc7\u6599\u5eab\u7121\u95dc\uff0c\u56e0\u70ba\u9023\u7dda\u65e9\u5c31\u95dc\u9589\u4e86\u3002<\/p>\n
\u7136\u800c\u7a0b\u5f0f\u78bc\u4e5f\u56e0\u6b64\u589e\u52a0 2 \u884c\uff0c\u53ea\u70ba\u4e86\u7279\u5225\u751f\u51fa\u4e00\u500b cursor \u4f86\u89e3\u6c7a\u9023\u7dda\u554f\u984c\uff0c\u7e3d\u89ba\u5f97\u7a0b\u5f0f\u78bc\u53c8\u8b8a\u56c9\u5506\u4e86\uff0c\u624d\u6488\u500b\u8cc7\u6599\u5f97\u9700\u8981\u9019\u6a23\u55ce\uff1f\u4e0a\u9762\u5176\u5be6\u4e5f\u63d0\u5230\uff0cconn.execute(…) \u56de\u50b3\u7d66 rows \u6642\uff0c\u5c31\u5df2\u7d93\u662f  sqlite3.Cursor \u985e\u578b\u7684\u7269\u4ef6\uff0c\u56e0\u6b64\u7a0b\u5f0f\u78bc\u5176\u5be6\u53ef\u4ee5\u9019\u6a23\u5beb\uff1a<\/p>\n
import sqlite3\nconn = sqlite3.connect(\"demo.db\")\nrows = conn.execute(\"select * from games;\").fetchall()<\/span>\nconn.close()\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\n<\/pre>\n
\u57f7\u884c\u7684\u7d50\u679c\u548c\u4e0a\u5716\u5b8c\u5168\u4e00\u6a23\uff0crows \u662f list \u985e\u8cc7\u6599\u96c6\u5408\uff0c\u5305\u542b 3 \u7b46 tuple \u985e\u7684\u904a\u6232\u8cc7\u6599\u3002<\/p>\n
\u5beb\u5230\u9019\u88e1\uff0c\u5927\u5bb6\u53ef\u4ee5\u6bd4\u8f03\u4e00\u4e0b\u672c\u6587\u6700\u539f\u59cb\u7684\u5beb\u6cd5\uff0c\u53ea\u662f\u628a conn.close() \u5f80\u524d\u79fb\u5230\u53d6\u5f97 rows \u8cc7\u6599\u7684\u4e0b\u4e00\u884c\uff0c\u4e26\u4e14\u5728 conn.execute(…) \u76f4\u63a5\u52a0\u4e0a .fetchall() \u800c\u5df2\uff0c\u7e3d\u884c\u6578 9 \u884c\u4e0d\u8b8a\u3002\u6216\u8a31\u5927\u5bb6\u4e5f\u767c\u73fe\u4e86\uff0c\u7a0b\u5f0f\u5167\u4e26\u6c92\u6709\u7279\u610f\u63d0\u5230 cursor \u9019\u500b\u73a9\u610f\uff0c\u4f46\u5be6\u969b\u4e0a\u5df2\u7d93\u5305\u542b cursor \u7684 fetchall() \u64cd\u4f5c\u3002<\/p>\n
\u9019\u500b\u5beb\u6cd5\u6211\u53cd\u800c\u89ba\u5f97\u6bd4\u8f03\u5bb9\u6613\u95b1\u8b80\u8207\u64b0\u5beb\uff0c\u4f46\u5947\u602a\u7684\u662f\uff0cconn.execute(…).fetchall() \u9019\u7a2e\u5beb\u6cd5\uff0c\u6211\u5e7e\u4e4e\u6c92\u5728\u6559\u5b78\u7db2\u7ad9\u4e0a\u770b\u904e\uff0c\u6240\u4ee5\u5beb\u51fa\u4f86\u7576\u7b46\u8a18\uff0c\u653e\u5728\u9019\u88e1\u4f9b\u5c07\u4f86\u8907\u7fd2\uff0c\u4e5f\u7576\u4f5c\u7d66\u5927\u5bb6\u53c3\u8003\u770b\u770b\u3002<\/p>\n
\u554f\u984c\u4e8c\uff1a\u53d6\u5f97\u7684 row \u8cc7\u6599\u7684\u5f8c\u7e8c\u61c9\u7528\u4e0d\u4fbf<\/p>\n
\u89e3\u6c7a\u5b8c rows \uff0c\u4f86\u770b\u770b\u88e1\u9762\u7684\u6bcf\u4e00\u7b46 row \u7684\u5167\u5bb9\u3002\u672c\u6587\u7684 demo \u70ba\u4f8b\uff0c\u4e09\u7b46\u8cc7\u6599\u90fd\u662f tuple \u985e\u578b\uff0c\u5167\u5bb9\u5982\u4e0b\uff1a<\/p>\n
(1, ‘\u5730\u5e73\u7dda \u897f\u57df\u7981\u5730’, ‘PS4,PS5’, 1790)
\n(2, ‘\u8dd1\u8eca\u6d6a\u6f2b\u65c5 7’, ‘PS4,PS5’, 1990)
\n(3, ‘\u827e\u723e\u767b\u6cd5\u74b0’, ‘PS4,PS5,PC,XBOX’, 1690)<\/p>\n
tuple \u7684\u7d50\u69cb\u7c21\u55ae\u76f4\u89c0\u7c97\u66b4\uff0c\u6700\u5927\u7684\u554f\u984c\u5728\u65bc\u5b83\u5c0d\u6bcf\u500b\u503c\u5c11\u4e86\u61c9\u6709\u7684\u63cf\u8ff0\uff0c\u9019\u88cf Demo \u7684\u6bcf\u500b tuple \u53ea\u6709 4 \u500b\u503c\uff0c\u6216\u8a31\u80fd\u7528\u7d93\u9a57\u6cd5\u5247\u731c\u51fa\u662f\u53ef\u80fd\u662f\u7de8\u865f\uff08 row[0] \uff09\u3001\u904a\u6232\u540d\u7a31\uff08 row[1]\uff09\u3001\u5e73\u53f0\uff08 row[2]\uff09\uff0c\u50f9\u683c\uff08row[3]\uff09\u3002\u7136\u800c\u771f\u6574\u7684\u5be6\u52d9\u7d93\u9a57\u4e2d\uff0c\u7528\u4ee5\u4e0a\u7684\u5beb\u6cd5\u6488\u51fa\u6578\u5341\u500b\u3001\u751a\u81f3\u4e0a\u767e\u500b\u503c\u7684 tuple \u8cc7\u6599\u5f88\u5e38\u898b\uff0c\u5149\u662f\u60f3\u627e\u51fa\u90a3\u500b\u503c\u5728\u7b2c\u5e7e\u500b\u4f4d\u7f6e\uff0c\u6574\u5929\u5728\u90a3\u88e1\u7b97\u9017\u865f\u683c\u5b50\u5c31\u98fd\u4e86\uff0c\u66f4\u4e0d\u7528\u8aaa\u5230 tuple \u8cc7\u6599\u96a8\u6642\u90fd\u6709\u53ef\u80fd\u589e\u6e1b\u3001\u578b\u614b\u3001\u9806\u5e8f\u8b8a\u5316\u7b49\u60c5\u6cc1\u3002\u7279\u5225\u662f demo \u7684\u4f8b\u5b50\u4e2d\u4f7f\u7528 “select * from games;” \u8a9e\u6cd5\u6642\uff0c\u66f4\u5bb9\u6613\u8b93 tuple \u5167\u5bb9\u767c\u751f\u975e\u9810\u671f\u7684\u8b8a\u5316\uff0c\u5f8c\u7e8c\u5f88\u5bb9\u6613\u5c0e\u81f4\u5176\u4ed6\u554f\u984c\u3002<\/p>\n
\u63d0\u5230\u300cSELECT * FROM…\u300d\uff0c\u5176\u5be6\u662f\u5f88\u4e0d\u597d\u7684\u5beb\u6cd5\u3002\u81ea\u5df1\u7df4\u7fd2\u5beb\u5beb\u7bc4\u4f8b\u9084\u53ef\u4ee5\uff0c\u5be6\u52d9\u7684\u958b\u767c\u7d93\u9a57\u4e0a\u5343\u842c\u5343\u842c\u4e0d\u80fd\u4f7f\u7528\uff0c\u5426\u5247\u5f8c\u679c\u5f88\u53ef\u80fd\u4e0d\u582a\u8a2d\u60f3\u3002\u6211\u500b\u4eba\u771f\u7684\u898b\u904e\u56e0\u70ba\u4f7f\u7528\u300cSELECT * FROM…\u300d\u800c\u9020\u6210\u7684\u53f2\u8a69\u7d1a\u707d\u96e3\uff0c\u672c\u6587\u6700\u5f8c\u7684\u300c\u8b1b\u53e4\u7bc7\u300d\u518d\u8ddf\u5927\u5bb6\u804a\u9019\u500b\u6545\u4e8b\u3002<\/p>\n
\u8aaa\u4e86\u9019\u9ebc\u591a\uff0c\u6211\u53ea\u662f\u60f3\u8868\u660e tuple \u9019\u985e\u578b\u7684\u8cc7\u6599\uff0c\u53ef\u80fd\u4e0d\u662f\u4ec0\u9ebc\u597d\u7528\u7684\u8cc7\u6599\u7d50\u69cb\uff0c\u5f8c\u7e8c\u8655\u7406\u8d77\u4f86\u5f88\u9ebb\u7169\u3002\u6240\u4ee5\u4e86\uff0c\u82e5\u771f\u7684\u4e0d\u5f97\u4e0d\u8655\u7406 tuple \u6642\uff0c\u62ff\u5230 tuple \u8cc7\u6599\u7684\u7b2c\u4e00\u6642\u9593\uff0c\u6700\u597d\u518d\u81ea\u884c\u6599\u7406\u4e00\u4e0b\u3001\u4f8b\u5982\u53e6\u5916\u5beb\u4e00\u500b class \u6216\u662f\u7528 dict \u52a0\u5165\u53ef\u8b58\u5225\u7684 key \u503c\uff0c\u5f8c\u7e8c\u61c9\u7528\u9019\u4e9b\u8cc7\u6599\u6642\u6703\u6bd4\u8f03\u70ba\u59a5\u7576\u3002<\/p>\n
\u6709\u9451\u65bc tuple \u8655\u7406\u8d77\u4f86\u7684\u4e0d\u4fbf\uff0c\u5176\u5be6 Python \u7684 Sqlite3 \u6709\u63d0\u4f9b\u300crow factory\u300d\u7684\u529f\u80fd\uff0c\u5728 SELECT \u53d6\u51fa\u8cc7\u6599\u96c6\u7684\u6642\u5019\u76f4\u63a5\u52a0\u5de5\uff0c\u4e0d\u6703\u76f4\u63a5\u786c\u751f\u751f\u5410\u51fa tuple \u8cc7\u6599\u3002<\/p>\n
\u800c\u4e00\u822c\u5e38\u898b\u7684\u7db2\u8def\u6559\u5b78\u4e5f\u6709\u6559 row factory\uff0c\u53ea\u8981\u591a\u4e00\u884c\u5ba3\u544a\u5c31\u884c\u4e86\u3002<\/p>\n
import sqlite3\nconn = sqlite3.connect(\"demo.db\")\nconn.row_factory = sqlite3.Row<\/span><\/strong><\/span>\nrows = conn.execute(\"select * from games;\").fetchall()\nconn.close()\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\n<\/pre>\n
\u65bc\u662f row \u5f9e\u539f\u4f86\u7684 tuple \u985e\u578b\u8b8a\u6210 sqlite3.Row \u985e\u578b\u3002<\/p>\n
\"row_factory.png\"<\/p>\n
\u7136\u800c\uff0c\u76f4\u63a5 print \u6aa2\u8996 row \u5167\u5bb9\u537b\u770b\u4e0d\u51fa\u6240\u4ee5\u7136\uff0c\u5230\u5e95 sqlite3.Row \u985e\u578b\u7684 row \u8981\u600e\u9ebc\u53d6\u51fa\u5167\u5bb9\u503c\u5462\uff1f\u7528 Debug \u5de5\u5177\u770b\u4e00\u4e0b\uff1a<\/p>\n
\"row_keys.png\"<\/p>\n
sqlite3.Row \u5305\u542b\u4e86\u4e00\u500b method \u53eb keys\uff0c\u6240\u4ee5\u6539\u5beb\u4e00\u4e0b\u770b\u770b\uff1a<\/p>\n
\"row_keys(2).png\"<\/p>\n
\u5217\u51fa row \u7684\u6240\u6709 key \u503c\u3002\u6240\u4ee5\u6309\u7167\u7d93\u9a57\u6cd5\u5247\uff0c\u53d6\u503c\u7684\u65b9\u6cd5\u53ef\u4ee5\u5beb\u6210 row[“key\u503c”] \u5c31\u884c\u4e86\u3002\u6b64 demo \u4e2d\u7684\u6240\u6709 key \u503c\uff0c\u5176\u5be6\u4e5f\u662f games \u8868\u683c\u7684\u6b04\u4f4d\u503c\u3002<\/p>\n
\"row_keys(3).png\"<\/p>\n
\u7d93 row_factory \u8655\u7406\u5f8c\uff0c\u53d6 row \u7684\u5167\u5bb9\u503c\u5c31\u65b9\u4fbf\u8a31\u591a\uff0c\u53ea\u9700\u586b\u5165\u5c0d\u61c9\u6b63\u78ba\u7684 key \u503c\u5373\u53ef\uff0c\u548c\u53d6\u7528 dict \u5167\u5bb9\u7684\u65b9\u5f0f\u4e00\u6a21\u4e00\u6a23\u3002<\/p>\n
\u96d6\u7136\u53d6\u7528 sqlite3.Row \u7684\u65b9\u5f0f\u8ddf dict \u96f7\u540c\uff0c\u4f46\u8aaa\u5230\u5e95 sqlite3.Row \u9084\u662f\u6c92\u90a3\u9ebc\u76f4\u89c0\uff0c\u4f60\u9084\u5f97\u900f\u904e keys() \u67e5\u8a62\u6709\u54ea\u4e9b key \u503c\u80fd\u7528\u3002\u90a3\u9ebc\uff0c\u5230\u5e95\u6709\u6c92\u6709\u66f4\u597d\u7684\u65b9\u5f0f\uff0c\u76f4\u63a5\u628a\u7d50\u679c\u8f38\u51fa\u70ba dict \u7684\u8cc7\u6599\u96c6\u5408\u5462\uff1f<\/p>\n
\u67e5\u4e86\u4e00\u4e0b\uff0c\u7b54\u6848\u662f\u6709\u7684\u3002\u5176\u5be6 conn.row_factory \u672c\u8eab\u5c31\u662f\u500b\u65b9\u6cd5\u51fd\u6578\uff0c\u6211\u5011\u4e5f\u53ef\u4ee5\u81ea\u5df1\u5ba2\u88fd\u5316\uff0c\u505a\u4e00\u500b row_factory\uff0c\u5b98\u65b9\u7684\u6587\u4ef6\u6709\u6559\u4f60\u600e\u9ebc\u5beb\uff08\u9023\u7d50<\/a>\uff09\u3002\u6240\u4ee5 demo \u7a0b\u5f0f\u6700\u7d42\u53ef\u4ee5\u6539\u5beb\u6210\u4ee5\u4e0b\u9019\u6a23\uff1a<\/p>\n
import sqlite3\ndef <\/span><\/strong><\/span>dict_factory<\/span><\/strong><\/span>(cursor, row):\nd:dict = {}\nfor idx, col in enumerate(cursor.description):\nd[col[0]] = row[idx]\nreturn d<\/span><\/strong><\/span>\nconn = sqlite3.connect(\"demo.db\")\nconn.row_factory = dict_factory<\/span><\/span>\nrows = conn.execute(\"select * from games;\").fetchall()\nconn.close()\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\n<\/pre>\n
\u4e0a\u9762\u7a0b\u5f0f\u78bc\u4e2d\u7684\u9ec3\u5e95\u5340\u5c31\u662f\u81ea\u88fd\u7684 row factory\uff0c\u88e1\u9762\u7528\u5230\u4e86\u524d\u9762\u63d0\u5230 cursor \u7684\u53e6\u4e00\u500b\u5c6c\u6027 description\uff0c\u57f7\u884c\u7d50\u679c\u5982\u4e0b\uff1a<\/p>\n
\"final.png\"<\/p>\n
\u5982\u6b64\uff0c\u5c31\u53ef\u4ee5\u628a rows \u76f4\u63a5\u8f38\u51fa\u70ba dict \u985e\u7684 list \u8cc7\u6599\u96c6\uff0c\u6709\u5229\u65bc\u8a72\u8cc7\u6599\u5f8c\u7e8c\u7684\u4f7f\u7528\u548c\u8f49\u63db\uff08\u4f8b\u5982 json\uff09\u3002\u548c\u6587\u7ae0\u4e00\u958b\u59cb\u7684\u7a0b\u5f0f\u78bc\u505a\u6bd4\u8f03\uff0c\u589e\u52a0\u4e86 6 \u884c\uff0c\u4e26\u89e3\u6c7a\u95dc\u9589\u9023\u7dda\u5f8c\u7121\u6cd5\u5b58\u53d6\u8cc7\u6599\u7684\u554f\u984c\uff0c\u4e26\u4e14\u8b93\u8cc7\u6599\u5eab\u7570\u52d5\u7522\u751f\u7684\u5f71\u97ff\u964d\u5230\u6700\u4f4e\u3002<\/p>\n
Python + MySQL \u8cc7\u6599\u5eab<\/span><\/strong><\/p>\n
\u984d\u5916\u88dc\u5145 Python + MySQL \u8cc7\u6599\u5eab\uff0cSELECT \u56de\u61c9 dict \u985e list \u8cc7\u6599\u96c6\u7684\u90e8\u5206\u3002\u6709\u5f88\u591a\u7a2e\u5beb\u6cd5\uff0c\u5e7e\u4e4e\u90fd\u9003\u4e0d\u958b cursor \u7684\u64cd\u4f5c\uff08conn \u6c92\u6709 execute \u65b9\u6cd5\uff0c\u4e14 cursor.execute \u56de\u50b3\u6578\u5b57<\u8cc7\u6599\u7b46\u6578>\uff09\uff0c\u7686\u4ee5 cursor.fetchall() \u63a5\u6536\u7d50\u679c\u3002\u4f8b\u8209\u5e38\u898b\u7684\u5169\u7a2e\uff1a<\/p>\n
\u5beb\u6cd5\u4e00\uff1a\u4f7f\u7528 pymysql<\/p>\n
import pymysql #pip3 install pymysql\nconn = pymysql.connect(host=\"localhost\", cursorclass=pymysql.cursors.DictCursor,\nuser=\"demo\", password=\"demo\", database=\"demo\", port=3306, charset=\"utf8\")\ncursor = conn.cursor()\n# \u7576 conn \u672a\u6307\u5b9a cursorclass = ... \u6642\uff0c\u4f7f\u7528\u4ee5\u4e0b\u5ba3\u544a\n# cursor = conn.cursor(pymysql.cursors.DictCursor) <\/span>\ncursor.execute(\"select * from games;\")\nrows = cursor.fetchall()\nconn.close()\ncursor.close()\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\n<\/pre>\n
\u5beb\u6cd5\u4e8c\uff1a\u4f7f\u7528 mysql.connector\u3002\u9019\u7a2e\u5beb\u6cd5\u5c0d\u65bc VS Code \u7684 IntelliSense\uff08Autocomplete\uff09\u529f\u80fd\u4e26\u4e0d\u53cb\u5584\uff0c\u6240\u4ee5\u4e0d\u63a8\u85a6\u3002<\/p>\n
import mysql.connector  # pip install mysql-connector-python\nconn = mysql.connector.connect(host=\"localhost\", user=\"demo\",\npasswd=\"demo\", database=\"demo\", port=3306, charset=\"utf8\")\ncursor = conn.cursor(dictionary=True)\ncursor.execute(\"select * from games;\")\nrows = cursor.fetchall()\nconn.close()\ncursor.close()\nprint('type(rows) : {}'.format(type(rows)))\nprint('rows : {}'.format(rows))\nfor row in rows:\nprint('type(row) : {}'.format(type(row)))\nprint('row : {}'.format(row))\n<\/pre>\n
 <\/p>\n
\u8b1b\u53e4\u7bc7\uff1a\u300cSELECT * FROM\u300d\u9020\u6210\u53f2\u8a69\u7d1a\u707d\u96e3<\/strong><\/span><\/p>\n
\u5f88\u591a\u5e74\u524d\uff0c\u5357\u90e8\u67d0\u516c\u52d9\u6a5f\u95dc\u7684\u516c\u6587\u7cfb\u7d71\uff0c\u767c\u751f\u4e0a\u842c\u7b46\u516c\u6587\u8cc7\u6599\u4e0d\u660e\u907a\u5931\u7684\u4e8b\u4ef6\u3002\u8a18\u5f97\u8a72\u7cfb\u7d71\u662f D2K + Oracle \u5beb\u7684\uff0c\u7b97\u662f\u6bd4\u8f03\u5c11\u898b\u7684\u90a3\u7a2e\uff0c\u6240\u4ee5\u6848\u4ef6\u5c31\u8f3e\u8f49\u5230\u6211\u9019\u908a\u4f86\u4e86\u3002\u6211\u8907\u88fd\u4e86\u4e00\u5957\u7cfb\u7d71\u505a\u529f\u80fd\u6e2c\u8a66\uff0c\u767c\u73fe\u662f\u8a72\u7cfb\u7d71\u4e2d\u300c\u6b77\u53f2\u6b78\u6a94\u300d\u7684\u529f\u80fd\u51fa\u73fe\u554f\u984c\u3002\u8981\u6b78\u6a94\u7684\u516c\u6587\u6c92\u6709\u9032\u5165\u6b77\u53f2\u6b13\uff0c\u7136\u5f8c\u672c\u4f86\u8981\u6b78\u6a94\u7684\u516c\u6587\u8cc7\u6599\u5c31\u88ab\u522a\u9664\u4e86\u3002\u7e7c\u7e8c\u6df1\u5165\u8abf\u67e5\uff0c\u6211\u6bd4\u5c0d\u4e86\u516c\u6587\u6a94\u8207\u6b77\u53f2\u6a94\uff0c\u767c\u73fe\u5169\u500b\u8cc7\u6599\u8868\u6b04\u4f4d\u5404\u6709\u4e00\u767e\u591a\u500b\uff0c\u6392\u5217\u5e7e\u4e4e\u76f8\u540c\uff0c\u552f\u4e00\u4e0d\u540c\u8655\u5728\u65bc\u516c\u6587\u6a94\u5c3e\u7aef\u6bd4\u6b77\u53f2\u6b13\u591a\u4e86\u5169\u500b\u6b04\u4f4d\u3002\u7576\u6211\u628a\u6b77\u53f2\u6a94\u4e5f\u88dc\u4e0a\u9019\u5169\u500b\u6b04\u4f4d\u5f8c\uff0c\u6b77\u53f2\u6b78\u6a94\u7684\u529f\u80fd\u5c31\u6062\u5fa9\u6b63\u5e38\u4e86\u3002<\/p>\n
\u6240\u4ee5\u6211\u7684\u4f30\u8a08\u662f\uff0c\u6b77\u53f2\u6b78\u6a94\u7a0b\u5f0f\u4e00\u5b9a\u662f\u7528\u4e86\u300cINSERT INTO \u6b77\u53f2\u6a94 SELECT * FROM \u516c\u6587\u6a94\u300d\u9019\u7a2e\u8a9e\u6cd5<\/a>\u3002\u5728\u5169\u500b\u8cc7\u6599\u8868 100% \u76f8\u540c\u6b04\u4f4d\u6642\u662f\u6c92\u554f\u984c\u7684\uff0c\u4e0d\u904e\u5728\u67d0\u6b21\u7cfb\u7d71\u6539\u7248\uff0c\u516c\u6587\u6a94\u4e0d\u660e\u539f\u56e0\u52a0\u4e86\u6b04\u4f4d\u5f8c\uff0c\u9019\u7a2e\u5beb\u6cd5\u7576\u7136\u5c31\u51fa\u554f\u984c\u4e86\uff0c\u52a0\u4e0a\u7a0b\u5f0f\u6c92\u6514\u622a\u932f\u8aa4\u5c31 DELETE \u516c\u6587\uff0c\u65bc\u662f\u6158\u5287\u5c31\u767c\u751f\u4e86\u3002\u66f4\u6158\u7684\u662f\u5099\u4efd\u6a5f\u5236\u4e5f\u6709\u554f\u984c\uff0c\u96d6\u7136\u4fee\u597d\u4e86\u6b77\u53f2\u6b78\u6a94\u529f\u80fd\uff0c\u4f46\u90a3\u4e9b\u4e0a\u842c\u7b46\u7d04\u4e00\u5e74\u591a\u4e1f\u5931\u7684\u516c\u6587\u8cc7\u6599\u537b\u518d\u4e5f\u627e\u4e0d\u56de\u4f86\uff0c\u771f\u7684\u662f\u4e00\u5834\u53f2\u8a69\u7d1a\u7684\u707d\u96e3\u3002<\/p>\n
\u5f8c\u4f86\u807d\u8aaa\u4ed6\u5011\u5f9e\u5009\u5eab\u88e1\u642c\u51fa\u5e7e\u5927\u7bb1\u7d19\u672c\u516c\u6587\uff0c\u6bcf\u5929\u627e\u4eba\u52a0\u73ed\u628a\u9019\u4e9b\u516c\u6587\u91cd\u65b0\u8f38\u5165\u7cfb\u7d71\uff0c\u7e3d\u7b97\u662f\u89e3\u6c7a\u4e86\u9019\u4ef6\u4e8b\u3002<\/p>\n