{"id":300,"date":"2026-01-25T02:19:35","date_gmt":"2026-01-24T18:19:35","guid":{"rendered":"https:\/\/skwp.zeabur.app\/2026\/01\/25\/%e3%80%90asyncpg-%e6%95%99%e5%ad%b8%e3%80%9103-crud-%e6%93%8d%e4%bd%9c%e8%88%87%e5%8f%83%e6%95%b8%e5%8c%96%e6%9f%a5%e8%a9%a2\/"},"modified":"2026-01-25T02:19:35","modified_gmt":"2026-01-24T18:19:35","slug":"%e3%80%90asyncpg-%e6%95%99%e5%ad%b8%e3%80%9103-crud-%e6%93%8d%e4%bd%9c%e8%88%87%e5%8f%83%e6%95%b8%e5%8c%96%e6%9f%a5%e8%a9%a2","status":"publish","type":"post","link":"https:\/\/blog.itsmygo.uk\/wordpress\/2026\/01\/25\/%e3%80%90asyncpg-%e6%95%99%e5%ad%b8%e3%80%9103-crud-%e6%93%8d%e4%bd%9c%e8%88%87%e5%8f%83%e6%95%b8%e5%8c%96%e6%9f%a5%e8%a9%a2\/","title":{"rendered":"\u3010asyncpg \u6559\u5b78\u3011#03 CRUD \u64cd\u4f5c\u8207\u53c3\u6578\u5316\u67e5\u8a62"},"content":{"rendered":"\n<div id=\"quiz-basic\">\n<style>\n#quiz-basic .question {\n        background: white;\n        border-radius: 8px;\n        padding: 20px;\n        margin: 20px 0;\n        box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);\n      }#quiz-basic .question h3 {\n        margin-top: 0;\n        color: #333;\n      }#quiz-basic .options {\n        list-style: none;\n        padding: 0;\n      }#quiz-basic .options li {\n        padding: 12px 15px;\n        margin: 8px 0;\n        border: 2px solid #ddd;\n        border-radius: 6px;\n        cursor: pointer;\n        transition: all 0.2s;\n      }#quiz-basic .options li:hover:not(.disabled) {\n        border-color: #4caf50;\n        background: #f0fff0;\n      }#quiz-basic .options li.correct {\n        border-color: #4caf50;\n        background: #e8f5e9;\n      }#quiz-basic .options li.wrong {\n        border-color: #f44336;\n        background: #ffebee;\n      }#quiz-basic .options li.disabled {\n        cursor: default;\n        opacity: 0.7;\n      }#quiz-basic .options li.show-answer {\n        border-color: #4caf50;\n        background: #e8f5e9;\n      }#quiz-basic .feedback {\n        margin-top: 15px;\n        padding: 12px;\n        border-radius: 6px;\n        display: none;\n      }#quiz-basic .feedback.correct {\n        background: #e8f5e9;\n        color: #2e7d32;\n        display: block;\n      }#quiz-basic .feedback.wrong {\n        background: #ffebee;\n        color: #c62828;\n        display: block;\n      }#quiz-basic .score {\n        text-align: center;\n        font-size: 1.5em;\n        padding: 20px;\n        background: white;\n        border-radius: 8px;\n        margin-top: 20px;\n      }#quiz-basic .code-block {\n        background: #1e1e1e;\n        color: #d4d4d4;\n        padding: 14px;\n        border-radius: 6px;\n        font-family: \"Monaco\", \"Consolas\", \"Courier New\", monospace;\n        font-size: 0.9em;\n        overflow-x: auto;\n        margin: 12px 0;\n        white-space: pre-wrap;\n      }\n<\/style>\n<h1>\u6e2c\u9a57\uff1aasyncpg CRUD \u64cd\u4f5c\u8207\u53c3\u6578\u5316\u67e5\u8a62<\/h1>\n    <p>\u5171 5 \u984c\uff0c\u9ede\u9078\u7b54\u6848\u5f8c\u6703\u7acb\u5373\u986f\u793a\u7d50\u679c<\/p>\n\n    <div class=\"question\" id=\"q1\">\n      <h3>1. \u5728 asyncpg \u4e2d\uff0c\u7576\u4f60\u9700\u8981\u67e5\u8a62\u591a\u7b46\u8cc7\u6599\u4e26\u904d\u6b77\u8655\u7406\u6642\uff0c\u61c9\u8a72\u4f7f\u7528\u54ea\u500b\u65b9\u6cd5\uff1f<\/h3>\n      <ul class=\"options\">\n        <li onclick=\"checkAnswer(1, 'A', 'A')\">A. fetch()<\/li>\n        <li onclick=\"checkAnswer(1, 'B', 'A')\">B. fetchrow()<\/li>\n        <li onclick=\"checkAnswer(1, 'C', 'A')\">C. fetchval()<\/li>\n        <li onclick=\"checkAnswer(1, 'D', 'A')\">D. execute()<\/li>\n      <\/ul>\n      <div class=\"feedback\" id=\"fb1\"><\/div>\n    <\/div>\n\n    <div class=\"question\" id=\"q2\">\n      <h3>2. \u95dc\u65bc <code>fetchrow()<\/code> \u65b9\u6cd5\uff0c\u4ee5\u4e0b\u6558\u8ff0\u4f55\u8005\u6b63\u78ba\uff1f<\/h3>\n      <ul class=\"options\">\n        <li onclick=\"checkAnswer(2, 'A', 'C')\">A. \u627e\u4e0d\u5230\u8cc7\u6599\u6642\u6703\u56de\u50b3\u7a7a\u7684 list []<\/li>\n        <li onclick=\"checkAnswer(2, 'B', 'C')\">B. \u56de\u50b3\u578b\u5225\u662f list[Record]<\/li>\n        <li onclick=\"checkAnswer(2, 'C', 'C')\">C. \u627e\u4e0d\u5230\u8cc7\u6599\u6642\u6703\u56de\u50b3 None<\/li>\n        <li onclick=\"checkAnswer(2, 'D', 'C')\">D. \u53ea\u80fd\u7528\u65bc SELECT COUNT(*) \u9019\u985e\u805a\u5408\u67e5\u8a62<\/li>\n      <\/ul>\n      <div class=\"feedback\" id=\"fb2\"><\/div>\n    <\/div>\n\n    <div class=\"question\" id=\"q3\">\n      <h3>3. asyncpg \u4f7f\u7528\u4ec0\u9ebc\u683c\u5f0f\u7684\u53c3\u6578\u4f54\u4f4d\u7b26\u4f86\u9032\u884c\u53c3\u6578\u5316\u67e5\u8a62\uff1f<\/h3>\n      <ul class=\"options\">\n        <li onclick=\"checkAnswer(3, 'A', 'B')\">A. \u4f7f\u7528 %s \u548c %d \u683c\u5f0f\u5316\u7b26\u865f<\/li>\n        <li onclick=\"checkAnswer(3, 'B', 'B')\">B. \u4f7f\u7528 $1, $2, $3 \u4f54\u4f4d\u7b26<\/li>\n        <li onclick=\"checkAnswer(3, 'C', 'B')\">C. \u4f7f\u7528 ? \u4f5c\u70ba\u4f54\u4f4d\u7b26<\/li>\n        <li onclick=\"checkAnswer(3, 'D', 'B')\">D. \u4f7f\u7528 :name \u547d\u540d\u53c3\u6578<\/li>\n      <\/ul>\n      <div class=\"feedback\" id=\"fb3\"><\/div>\n    <\/div>\n\n    <div class=\"question\" id=\"q4\">\n      <h3>4. \u5982\u679c\u9700\u8981\u5728 INSERT \u5f8c\u53d6\u5f97\u65b0\u63d2\u5165\u8cc7\u6599\u7684 ID\uff0c\u61c9\u8a72\u5982\u4f55\u505a\uff1f<\/h3>\n      <ul class=\"options\">\n        <li onclick=\"checkAnswer(4, 'A', 'D')\">A. \u4f7f\u7528 execute() \u4e26\u89e3\u6790\u56de\u50b3\u7684\u72c0\u614b\u5b57\u4e32<\/li>\n        <li onclick=\"checkAnswer(4, 'B', 'D')\">B. \u5148 INSERT \u518d\u7528 SELECT MAX(id) \u67e5\u8a62<\/li>\n        <li onclick=\"checkAnswer(4, 'C', 'D')\">C. \u4f7f\u7528 fetchrow() \u914d\u5408 INSERT \u8a9e\u53e5<\/li>\n        <li onclick=\"checkAnswer(4, 'D', 'D')\">D. \u4f7f\u7528 fetchval() \u914d\u5408 RETURNING \u5b50\u53e5<\/li>\n      <\/ul>\n      <div class=\"feedback\" id=\"fb4\"><\/div>\n    <\/div>\n\n    <div class=\"question\" id=\"q5\">\n      <h3>5. \u95dc\u65bc <code>executemany()<\/code> \u65b9\u6cd5\uff0c\u4ee5\u4e0b\u6558\u8ff0\u4f55\u8005\u6b63\u78ba\uff1f<\/h3>\n      <ul class=\"options\">\n        <li onclick=\"checkAnswer(5, 'A', 'B')\">A. \u57f7\u884c\u901f\u5ea6\u8207\u8ff4\u5708\u57f7\u884c\u591a\u6b21 execute() \u76f8\u540c<\/li>\n        <li onclick=\"checkAnswer(5, 'B', 'B')\">B. \u5c07\u6240\u6709\u8cc7\u6599\u5728\u4e00\u6b21\u8cc7\u6599\u5eab\u5f80\u8fd4\u4e2d\u5b8c\u6210\uff0c\u6548\u80fd\u66f4\u4f73<\/li>\n        <li onclick=\"checkAnswer(5, 'C', 'B')\">C. \u53ea\u80fd\u7528\u65bc SELECT \u67e5\u8a62<\/li>\n        <li onclick=\"checkAnswer(5, 'D', 'B')\">D. \u9700\u8981\u70ba\u6bcf\u7b46\u8cc7\u6599\u5efa\u7acb\u500b\u5225\u7684\u9023\u7dda<\/li>\n      <\/ul>\n      <div class=\"feedback\" id=\"fb5\"><\/div>\n    <\/div>\n\n    <div class=\"score\" id=\"score\" style=\"display:none;\"><\/div>\n<script>\n\n      let answered = 0;\n      let correct = 0;\n      const total = 5;\n      const explanations = {\n        1: \"fetch() \u56de\u50b3\u7684\u662f list[asyncpg.Record]\uff0c\u9069\u5408\u7528\u65bc\u67e5\u8a62\u591a\u7b46\u8cc7\u6599\u4e26\u904d\u6b77\u8655\u7406\u3002\u5373\u4f7f\u67e5\u8a62\u7d50\u679c\u70ba\u7a7a\uff0c\u4e5f\u6703\u56de\u50b3\u7a7a list []\uff0c\u4e0d\u6703\u56de\u50b3 None\u3002\",\n        2: \"fetchrow() \u7528\u65bc\u67e5\u8a62\u55ae\u7b46\u8cc7\u6599\uff0c\u627e\u4e0d\u5230\u8cc7\u6599\u6642\u6703\u56de\u50b3 None\u3002\u9019\u8207 fetch() \u4e0d\u540c\uff0cfetch() \u5728\u6c92\u6709\u7d50\u679c\u6642\u56de\u50b3\u7a7a list\u3002\u4f7f\u7528 fetchrow() \u6642\u52d9\u5fc5\u6aa2\u67e5\u56de\u50b3\u503c\u662f\u5426\u70ba None\u3002\",\n        3: \"asyncpg \u4f7f\u7528 PostgreSQL \u539f\u751f\u7684\u53c3\u6578\u4f54\u4f4d\u7b26\u683c\u5f0f $1, $2, $3...\uff0c\u800c\u975e Python DB-API \u5e38\u898b\u7684 ? \u6216 %s\u3002\u9019\u662f PostgreSQL \u7684\u6a19\u6e96\u53c3\u6578\u5316\u67e5\u8a62\u683c\u5f0f\u3002\",\n        4: \"\u6587\u4ef6\u4e2d\u660e\u78ba\u8aaa\u660e\uff0c\u82e5\u9700\u8981\u53d6\u5f97\u65b0\u63d2\u5165\u8cc7\u6599\u7684 ID\uff0c\u61c9\u4f7f\u7528 RETURNING \u5b50\u53e5\u914d\u5408 fetchval()\u3002\u4f8b\u5982\uff1aawait conn.fetchval(\\\"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id\\\", name, email)\",\n        5: \"executemany() \u6703\u5c07\u6240\u6709\u8cc7\u6599\u5728\u4e00\u6b21\u8cc7\u6599\u5eab\u5f80\u8fd4\u4e2d\u5b8c\u6210\uff0c\u5927\u5e45\u6e1b\u5c11\u7db2\u8def\u5ef6\u9072\u7684\u5f71\u97ff\u3002\u5c0d\u65bc 1000 \u7b46\u8cc7\u6599\uff0cexecutemany() \u901a\u5e38\u6bd4\u8ff4\u5708\u57f7\u884c\u5feb 10 \u500d\u4ee5\u4e0a\u3002\"\n      };\n\n      function checkAnswer(qNum, selected, correctAns) {\n        const question = document.getElementById(\"q\" + qNum);\n        if (question.classList.contains(\"answered\")) return;\n\n        question.classList.add(\"answered\");\n        answered++;\n\n        const options = question.querySelectorAll(\".options li\");\n        options.forEach((opt) => {\n          opt.classList.add(\"disabled\");\n          if (opt.textContent.startsWith(correctAns + \".\")) {\n            opt.classList.add(\"show-answer\");\n          }\n        });\n\n        const feedback = document.getElementById(\"fb\" + qNum);\n        if (selected === correctAns) {\n          correct++;\n          feedback.className = \"feedback correct\";\n          feedback.innerHTML = \"\u2713 \u6b63\u78ba\uff01\" + explanations[qNum];\n        } else {\n          feedback.className = \"feedback wrong\";\n          feedback.innerHTML =\n            \"\u2717 \u932f\u8aa4\u3002\u6b63\u78ba\u7b54\u6848\u662f \" + correctAns + \"\u3002\" + explanations[qNum];\n          options.forEach((opt) => {\n            if (opt.textContent.startsWith(selected + \".\")) {\n              opt.classList.add(\"wrong\");\n            }\n          });\n        }\n\n        if (answered === total) {\n          const scoreDiv = document.getElementById(\"score\");\n          scoreDiv.style.display = \"block\";\n          scoreDiv.innerHTML =\n            \"\u6e2c\u9a57\u5b8c\u6210\uff01\u5f97\u5206\uff1a\" +\n            correct +\n            \" \/ \" +\n            total +\n            (correct === total\n              ? \" \ud83c\udf89 \u5168\u5c0d\uff01\"\n              : correct >= Math.ceil(total * 0.6)\n                ? \" \ud83d\udc4d \u4e0d\u932f\uff01\"\n                : \" \ud83d\udcda \u518d\u8907\u7fd2\u4e00\u4e0b\u5427\");\n        }\n      }\n    \n<\/script>\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\">\u524d\u8a00<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u5728\u524d\u5169\u7bc7\u6587\u7ae0\u4e2d\uff0c\u6211\u5011\u5b78\u6703\u4e86\u5982\u4f55\u5efa\u7acb asyncpg \u9023\u7dda\u4ee5\u53ca\u4f7f\u7528\u9023\u7dda\u6c60\u7ba1\u7406\u8cc7\u6599\u5eab\u9023\u7dda\u3002\u672c\u7bc7\u5c07\u6df1\u5165\u63a2\u8a0e\u5982\u4f55\u4f7f\u7528 asyncpg \u57f7\u884c\u5be6\u969b\u7684\u8cc7\u6599\u5eab\u64cd\u4f5c\uff1a\u65b0\u589e\u3001\u67e5\u8a62\u3001\u66f4\u65b0\u3001\u522a\u9664\uff08CRUD\uff09\uff0c\u4ee5\u53ca\u5982\u4f55\u6b63\u78ba\u4f7f\u7528\u53c3\u6578\u5316\u67e5\u8a62\u4f86\u9632\u6b62 SQL Injection \u653b\u64ca\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u5b78\u7fd2\u76ee\u6a19<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u8b80\u5b8c\u672c\u7bc7\u5f8c\uff0c\u4f60\u5c07\u80fd\u5920\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u4f7f\u7528 asyncpg \u57f7\u884c INSERT\u3001UPDATE\u3001DELETE \u64cd\u4f5c<\/li>\n<li>\u6b63\u78ba\u4f7f\u7528\u53c3\u6578\u5316\u67e5\u8a62\u9632\u6b62 SQL Injection<\/li>\n<li>\u7406\u89e3 fetch\u3001fetchrow\u3001fetchval\u3001execute \u7684\u5dee\u7570<\/li>\n<li>\u8655\u7406\u67e5\u8a62\u7d50\u679c\u4e26\u8f49\u63db\u70ba Python \u7269\u4ef6<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\u67e5\u8a62\u65b9\u6cd5\u7e3d\u89bd<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">asyncpg \u63d0\u4f9b\u4e86\u56db\u7a2e\u4e3b\u8981\u7684\u67e5\u8a62\u65b9\u6cd5\uff0c\u6bcf\u7a2e\u90fd\u6709\u4e0d\u540c\u7684\u7528\u9014\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table>\n<thead>\n<tr>\n<th>\u65b9\u6cd5<\/th>\n<th>\u56de\u50b3\u503c<\/th>\n<th>\u4f7f\u7528\u6642\u6a5f<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>fetch()<\/code><\/td>\n<td><code>list[Record]<\/code><\/td>\n<td>\u67e5\u8a62\u591a\u7b46\u8cc7\u6599<\/td>\n<\/tr>\n<tr>\n<td><code>fetchrow()<\/code><\/td>\n<td><code>Record<\/code> \u6216 <code>None<\/code><\/td>\n<td>\u67e5\u8a62\u55ae\u7b46\u8cc7\u6599<\/td>\n<\/tr>\n<tr>\n<td><code>fetchval()<\/code><\/td>\n<td>\u55ae\u4e00\u503c\u6216 <code>None<\/code><\/td>\n<td>\u53d6\u5f97\u55ae\u4e00\u6b04\u4f4d\u503c<\/td>\n<\/tr>\n<tr>\n<td><code>execute()<\/code><\/td>\n<td>\u72c0\u614b\u5b57\u4e32<\/td>\n<td>INSERT\/UPDATE\/DELETE<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\u8b93\u6211\u5011\u9010\u4e00\u4e86\u89e3\u9019\u4e9b\u65b9\u6cd5\u7684\u4f7f\u7528\u65b9\u5f0f\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">fetch() &#8211; \u67e5\u8a62\u591a\u7b46\u8cc7\u6599<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u7576\u4f60\u9700\u8981\u53d6\u5f97\u591a\u7b46\u67e5\u8a62\u7d50\u679c\u6642\uff0c\u4f7f\u7528 <code>fetch()<\/code>\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\"><span class=\"hljs-keyword\">import<\/span> asyncpg\n\n<span class=\"hljs-keyword\">async<\/span> def get_all_users(conn: asyncpg.Connection):\n    rows = <span class=\"hljs-keyword\">await<\/span> conn.fetch(<span class=\"hljs-string\">\"SELECT id, name, email FROM users\"<\/span>)\n\n    <span class=\"hljs-keyword\">for<\/span> row <span class=\"hljs-keyword\">in<\/span> rows:\n        print(f<span class=\"hljs-string\">\"ID: {row&#91;'id']}, Name: {row&#91;'name']}\"<\/span>)\n\n    <span class=\"hljs-keyword\">return<\/span> rows\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><code>fetch()<\/code> \u56de\u50b3\u7684\u662f <code>list[asyncpg.Record]<\/code>\u3002\u5373\u4f7f\u67e5\u8a62\u7d50\u679c\u70ba\u7a7a\uff0c\u4e5f\u6703\u56de\u50b3\u7a7a list <code>[]<\/code>\uff0c\u4e0d\u6703\u56de\u50b3 <code>None<\/code>\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Record \u7269\u4ef6\u7684\u5b58\u53d6\u65b9\u5f0f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">asyncpg \u7684 <code>Record<\/code> \u7269\u4ef6\u652f\u63f4\u591a\u7a2e\u5b58\u53d6\u65b9\u5f0f\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">row = await conn.fetchrow(<span class=\"hljs-string\">\"SELECT id, name FROM users WHERE id = $1\"<\/span>, <span class=\"hljs-number\">1<\/span>)\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 1\uff1a\u50cf dict \u4e00\u6a23\u7528 key \u5b58\u53d6<\/span>\nuser_id = row&#91;<span class=\"hljs-string\">'id'<\/span>]\nname = row&#91;<span class=\"hljs-string\">'name'<\/span>]\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 2\uff1a\u50cf tuple \u4e00\u6a23\u7528 index \u5b58\u53d6<\/span>\nuser_id = row&#91;<span class=\"hljs-number\">0<\/span>]\nname = row&#91;<span class=\"hljs-number\">1<\/span>]\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 3\uff1a\u53d6\u5f97\u6240\u6709 keys<\/span>\ncolumns = row.keys()  <span class=\"hljs-comment\"># &#91;'id', 'name']<\/span>\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 4\uff1a\u53d6\u5f97\u6240\u6709 values<\/span>\nvalues = row.values()  <span class=\"hljs-comment\"># &#91;1, 'Alice']<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">fetchrow() &#8211; \u67e5\u8a62\u55ae\u7b46\u8cc7\u6599<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u7576\u4f60\u78ba\u5b9a\u53ea\u9700\u8981\u4e00\u7b46\u8cc7\u6599\u6642\uff0c\u4f7f\u7528 <code>fetchrow()<\/code>\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">async def get_user_by_id(conn: asyncpg.Connection, user_id: int):\n    row = await conn.fetchrow(\n        <span class=\"hljs-string\">\"SELECT id, name, email FROM users WHERE id = $1\"<\/span>,\n        user_id\n    )\n\n    <span class=\"hljs-keyword\">if<\/span> row is None:\n        <span class=\"hljs-keyword\">return<\/span> None\n\n    <span class=\"hljs-keyword\">return<\/span> dict(row)  <span class=\"hljs-comment\"># \u8f49\u63db\u70ba dict<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>\u91cd\u8981\u63d0\u9192<\/strong>\uff1a<code>fetchrow()<\/code> \u5728\u627e\u4e0d\u5230\u8cc7\u6599\u6642\u6703\u56de\u50b3 <code>None<\/code>\uff0c\u52d9\u5fc5\u6aa2\u67e5\u56de\u50b3\u503c\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">fetchval() &#8211; \u53d6\u5f97\u55ae\u4e00\u503c<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u7576\u4f60\u53ea\u9700\u8981\u67e5\u8a62\u7d50\u679c\u4e2d\u7684\u55ae\u4e00\u6b04\u4f4d\u503c\u6642\uff0c<code>fetchval()<\/code> \u662f\u6700\u7c21\u6f54\u7684\u9078\u64c7\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">async def count_users(conn: asyncpg.Connection) -&gt; int:\n    count = await conn.fetchval(<span class=\"hljs-string\">\"SELECT COUNT(*) FROM users\"<\/span>)\n    <span class=\"hljs-keyword\">return<\/span> count  <span class=\"hljs-comment\"># \u76f4\u63a5\u56de\u50b3\u6578\u5b57\uff0c\u4f8b\u5982 42<\/span>\n\nasync def get_user_name(conn: asyncpg.Connection, user_id: int) -&gt; str | None:\n    name = await conn.fetchval(\n        <span class=\"hljs-string\">\"SELECT name FROM users WHERE id = $1\"<\/span>,\n        user_id\n    )\n    <span class=\"hljs-keyword\">return<\/span> name  <span class=\"hljs-comment\"># \u56de\u50b3 'Alice' \u6216 None<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><code>fetchval()<\/code> \u9810\u8a2d\u56de\u50b3\u7b2c\u4e00\u500b\u6b04\u4f4d\u7684\u503c\u3002\u5982\u679c\u9700\u8981\u53d6\u5f97\u5176\u4ed6\u6b04\u4f4d\uff0c\u53ef\u4ee5\u6307\u5b9a <code>column<\/code> \u53c3\u6578\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-comment\"># \u53d6\u5f97\u7b2c\u4e8c\u500b\u6b04\u4f4d\uff08index \u5f9e 0 \u958b\u59cb\uff09<\/span>\nemail = await conn.fetchval(\n    <span class=\"hljs-string\">\"SELECT id, name, email FROM users WHERE id = $1\"<\/span>,\n    user_id,\n    column=<span class=\"hljs-number\">2<\/span>  <span class=\"hljs-comment\"># \u53d6\u5f97 email \u6b04\u4f4d<\/span>\n)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">execute() &#8211; \u57f7\u884c\u975e\u67e5\u8a62\u8a9e\u53e5<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u5c0d\u65bc INSERT\u3001UPDATE\u3001DELETE \u7b49\u4e0d\u9700\u8981\u56de\u50b3\u8cc7\u6599\u7684\u64cd\u4f5c\uff0c\u4f7f\u7528 <code>execute()<\/code>\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">async def create_user(conn: asyncpg.Connection, name: str, email: str):\n    result = await conn.execute(\n        <span class=\"hljs-string\">\"INSERT INTO users (name, email) VALUES ($1, $2)\"<\/span>,\n        name, email\n    )\n    <span class=\"hljs-keyword\">print<\/span>(result)  <span class=\"hljs-comment\"># 'INSERT 0 1' - \u8868\u793a\u6210\u529f\u63d2\u5165 1 \u7b46<\/span>\n\nasync def update_user(conn: asyncpg.Connection, user_id: int, name: str):\n    result = await conn.execute(\n        <span class=\"hljs-string\">\"UPDATE users SET name = $1 WHERE id = $2\"<\/span>,\n        name, user_id\n    )\n    <span class=\"hljs-keyword\">print<\/span>(result)  <span class=\"hljs-comment\"># 'UPDATE 1' - \u8868\u793a\u66f4\u65b0\u4e86 1 \u7b46<\/span>\n\nasync def delete_user(conn: asyncpg.Connection, user_id: int):\n    result = await conn.execute(\n        <span class=\"hljs-string\">\"DELETE FROM users WHERE id = $1\"<\/span>,\n        user_id\n    )\n    <span class=\"hljs-keyword\">print<\/span>(result)  <span class=\"hljs-comment\"># 'DELETE 1' - \u8868\u793a\u522a\u9664\u4e86 1 \u7b46<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><code>execute()<\/code> \u56de\u50b3\u7684\u662f\u4e00\u500b\u72c0\u614b\u5b57\u4e32\uff0c\u683c\u5f0f\u70ba <code>\"\u547d\u4ee4 OID \u5f71\u97ff\u7b46\u6578\"<\/code>\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u53d6\u5f97 INSERT \u5f8c\u7684\u65b0 ID<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u5982\u679c\u9700\u8981\u53d6\u5f97\u65b0\u63d2\u5165\u8cc7\u6599\u7684 ID\uff0c\u4f7f\u7528 <code>RETURNING<\/code> \u5b50\u53e5\u914d\u5408 <code>fetchval()<\/code>\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">async def create_user_return_id(conn: asyncpg.Connection, name: str, email: str) -&gt; int:\n    new_id = await conn.fetchval(\n        <span class=\"hljs-string\">\"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id\"<\/span>,\n        name, email\n    )\n    <span class=\"hljs-keyword\">return<\/span> new_id  <span class=\"hljs-comment\"># \u56de\u50b3\u65b0\u5efa\u7acb\u7684 user id<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">\u53c3\u6578\u5316\u67e5\u8a62\uff1a$1, $2 \u4f54\u4f4d\u7b26<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">asyncpg \u4f7f\u7528 PostgreSQL \u539f\u751f\u7684\u53c3\u6578\u4f54\u4f4d\u7b26\u683c\u5f0f <code>$1<\/code>, <code>$2<\/code>, <code>$3<\/code>&#8230;\uff0c\u800c\u975e Python DB-API \u5e38\u898b\u7684 <code>?<\/code> \u6216 <code>%s<\/code>\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u57fa\u672c\u7528\u6cd5<\/h3>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-comment\"># \u55ae\u4e00\u53c3\u6578<\/span>\nrow = await conn.fetchrow(\n    <span class=\"hljs-string\">\"SELECT * FROM users WHERE id = $1\"<\/span>,\n    user_id\n)\n\n<span class=\"hljs-comment\"># \u591a\u500b\u53c3\u6578<\/span>\nrows = await conn.fetch(\n    <span class=\"hljs-string\">\"SELECT * FROM users WHERE age &gt; $1 AND city = $2\"<\/span>,\n    <span class=\"hljs-number\">18<\/span>, <span class=\"hljs-string\">\"Taipei\"<\/span>\n)\n\n<span class=\"hljs-comment\"># \u540c\u4e00\u53c3\u6578\u4f7f\u7528\u591a\u6b21<\/span>\nrows = await conn.fetch(\n    <span class=\"hljs-string\">\"SELECT * FROM users WHERE first_name = $1 OR last_name = $1\"<\/span>,\n    <span class=\"hljs-string\">\"Chen\"<\/span>\n)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u70ba\u4ec0\u9ebc\u8981\u7528\u53c3\u6578\u5316\u67e5\u8a62\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u7d55\u5c0d\u4e0d\u8981\u9019\u6a23\u505a<\/strong>\uff08SQL Injection \u6f0f\u6d1e\uff09\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-comment\"># \u5371\u96aa\uff01\u5343\u842c\u4e0d\u8981\u9019\u6a23\u5beb\uff01<\/span>\nuser_input = <span class=\"hljs-string\">\"'; DROP TABLE users; --\"<\/span>\nquery = f<span class=\"hljs-string\">\"SELECT * FROM users WHERE name = '{user_input}'\"<\/span>\nawait conn.fetch(query)  <span class=\"hljs-comment\"># \u6703\u57f7\u884c\u60e1\u610f SQL<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>\u6b63\u78ba\u505a\u6cd5<\/strong>\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-comment\"># \u5b89\u5168\uff1a\u4f7f\u7528\u53c3\u6578\u5316\u67e5\u8a62<\/span>\nuser_input = <span class=\"hljs-string\">\"'; DROP TABLE users; --\"<\/span>\nawait conn.fetch(\n    <span class=\"hljs-string\">\"SELECT * FROM users WHERE name = $1\"<\/span>,\n    user_input  <span class=\"hljs-comment\"># \u6703\u88ab\u7576\u4f5c\u7d14\u6587\u5b57\u8655\u7406\uff0c\u4e0d\u6703\u57f7\u884c<\/span>\n)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">\u53c3\u6578\u5316\u67e5\u8a62\u6703\u81ea\u52d5\u8655\u7406\u8df3\u812b\u5b57\u5143\uff0c\u78ba\u4fdd\u4f7f\u7528\u8005\u8f38\u5165\u4e0d\u6703\u88ab\u89e3\u8b80\u70ba SQL \u6307\u4ee4\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">executemany() &#8211; \u6279\u6b21\u63d2\u5165\u8cc7\u6599<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u7576\u9700\u8981\u4e00\u6b21\u63d2\u5165\u5927\u91cf\u8cc7\u6599\u6642\uff0c<code>executemany()<\/code> \u6bd4\u8ff4\u5708\u57f7\u884c\u591a\u6b21 <code>execute()<\/code> \u66f4\u6709\u6548\u7387\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">async def bulk_insert_users(conn: asyncpg.Connection, users: <span class=\"hljs-keyword\">list<\/span>&#91;tuple]):\n    await conn.executemany(\n        <span class=\"hljs-string\">\"INSERT INTO users (name, email) VALUES ($1, $2)\"<\/span>,\n        users\n    )\n\n<span class=\"hljs-comment\"># \u4f7f\u7528\u7bc4\u4f8b<\/span>\nusers_data = &#91;\n    (<span class=\"hljs-string\">\"Alice\"<\/span>, <span class=\"hljs-string\">\"alice@example.com\"<\/span>),\n    (<span class=\"hljs-string\">\"Bob\"<\/span>, <span class=\"hljs-string\">\"bob@example.com\"<\/span>),\n    (<span class=\"hljs-string\">\"Charlie\"<\/span>, <span class=\"hljs-string\">\"charlie@example.com\"<\/span>),\n]\n\nawait bulk_insert_users(conn, users_data)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><code>executemany()<\/code> \u6703\u5c07\u6240\u6709\u8cc7\u6599\u5728\u4e00\u6b21\u8cc7\u6599\u5eab\u5f80\u8fd4\u4e2d\u5b8c\u6210\uff0c\u5927\u5e45\u6e1b\u5c11\u7db2\u8def\u5ef6\u9072\u7684\u5f71\u97ff\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u6548\u80fd\u6bd4\u8f03<\/h3>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">import time\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 1\uff1a\u8ff4\u5708\u57f7\u884c\uff08\u6162\uff09<\/span>\nstart = time.time()\n<span class=\"hljs-keyword\">for<\/span> name, email in users_data:\n    await conn.execute(\n        <span class=\"hljs-string\">\"INSERT INTO users (name, email) VALUES ($1, $2)\"<\/span>,\n        name, email\n    )\n<span class=\"hljs-keyword\">print<\/span>(f<span class=\"hljs-string\">\"\u8ff4\u5708\u57f7\u884c\uff1a{time.time() - start:.3f} \u79d2\"<\/span>)\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 2\uff1aexecutemany\uff08\u5feb\uff09<\/span>\nstart = time.time()\nawait conn.executemany(\n    <span class=\"hljs-string\">\"INSERT INTO users (name, email) VALUES ($1, $2)\"<\/span>,\n    users_data\n)\n<span class=\"hljs-keyword\">print<\/span>(f<span class=\"hljs-string\">\"executemany\uff1a{time.time() - start:.3f} \u79d2\"<\/span>)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">\u5c0d\u65bc 1000 \u7b46\u8cc7\u6599\uff0c<code>executemany()<\/code> \u901a\u5e38\u5feb 10 \u500d\u4ee5\u4e0a\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u5c07 Record \u8f49\u63db\u70ba Python \u7269\u4ef6<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u8f49\u63db\u70ba dict<\/h3>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">row = await conn.fetchrow(<span class=\"hljs-string\">\"SELECT id, name, email FROM users WHERE id = $1\"<\/span>, <span class=\"hljs-number\">1<\/span>)\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 1\uff1a\u4f7f\u7528 dict() \u5efa\u69cb\u5b50<\/span>\nuser_dict = dict(row)\n\n<span class=\"hljs-comment\"># \u65b9\u5f0f 2\uff1a\u4f7f\u7528\u89e3\u5305<\/span>\nuser_dict = {**row}\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u8f49\u63db\u70ba Pydantic Model<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u5728\u5be6\u969b\u5c08\u6848\u4e2d\uff0c\u6211\u5011\u901a\u5e38\u6703\u4f7f\u7528 Pydantic \u4f86\u5b9a\u7fa9\u8cc7\u6599\u6a21\u578b\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\"><span><code class=\"hljs\">from pydantic import BaseModel\n\nclass User(BaseModel):\n    id: int\n    name: str\n    email: str\n\nasync def get_user(conn: asyncpg.Connection, user_id: int) -&gt; User | None:\n    row = await conn.fetchrow(\n        \"SELECT id, name, email FROM users WHERE id = $1\",\n        user_id\n    )\n\n    if row is None:\n        return None\n\n    return User(**dict(row))\n\nasync def get_all_users(conn: asyncpg.Connection) -&gt; list&#91;User]:\n    rows = await conn.fetch(\"SELECT id, name, email FROM users\")\n    return &#91;User(**dict(row)) for row in rows]\n<\/code><\/span><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u8655\u7406\u6b04\u4f4d\u540d\u7a31\u4e0d\u4e00\u81f4<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u7576\u8cc7\u6599\u5eab\u6b04\u4f4d\u540d\u7a31\u8207 Python \u5c6c\u6027\u540d\u7a31\u4e0d\u540c\u6642\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\"><span><code class=\"hljs\">class User(BaseModel):\n    id: int\n    name: str\n    email_address: str  # Python \u7fd2\u6163\u7528\u5e95\u7dda\n\nasync def get_user(conn: asyncpg.Connection, user_id: int) -&gt; User | None:\n    row = await conn.fetchrow(\n        \"SELECT id, name, email AS email_address FROM users WHERE id = $1\",\n        user_id\n    )\n\n    if row is None:\n        return None\n\n    return User(**dict(row))\n<\/code><\/span><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">\u4f7f\u7528 SQL \u7684 <code>AS<\/code> \u95dc\u9375\u5b57\u4f86\u91cd\u65b0\u547d\u540d\u6b04\u4f4d\uff0c\u8b93\u67e5\u8a62\u7d50\u679c\u76f4\u63a5\u7b26\u5408 Pydantic Model \u7684\u6b04\u4f4d\u540d\u7a31\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u5b8c\u6574 CRUD \u7bc4\u4f8b<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u4ee5\u4e0b\u662f\u4e00\u500b\u5b8c\u6574\u7684\u4f7f\u7528\u8005\u7ba1\u7406\u6a21\u7d44\u7bc4\u4f8b\uff1a<\/p>\n\n\n<div class=\"cbc-code-wrapper\"><div class=\"cbc-code-bar\"><button class=\"cbc-copy-button\">Copy<\/button><\/div><pre class=\"wp-block-code\"><span><code class=\"hljs\">import asyncpg\nfrom pydantic import BaseModel\n\nclass User(BaseModel):\n    id: int\n    name: str\n    email: str\n\nclass UserCreate(BaseModel):\n    name: str\n    email: str\n\nclass UserRepository:\n    def __init__(self, pool: asyncpg.Pool):\n        self.pool = pool\n\n    async def create(self, user: UserCreate) -&gt; int:\n        \"\"\"\u65b0\u589e\u4f7f\u7528\u8005\uff0c\u56de\u50b3\u65b0 ID\"\"\"\n        async with self.pool.acquire() as conn:\n            new_id = await conn.fetchval(\n                \"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id\",\n                user.name, user.email\n            )\n            return new_id\n\n    async def get_by_id(self, user_id: int) -&gt; User | None:\n        \"\"\"\u6839\u64da ID \u67e5\u8a62\u4f7f\u7528\u8005\"\"\"\n        async with self.pool.acquire() as conn:\n            row = await conn.fetchrow(\n                \"SELECT id, name, email FROM users WHERE id = $1\",\n                user_id\n            )\n            if row is None:\n                return None\n            return User(**dict(row))\n\n    async def get_all(self) -&gt; list&#91;User]:\n        \"\"\"\u67e5\u8a62\u6240\u6709\u4f7f\u7528\u8005\"\"\"\n        async with self.pool.acquire() as conn:\n            rows = await conn.fetch(\"SELECT id, name, email FROM users\")\n            return &#91;User(**dict(row)) for row in rows]\n\n    async def update(self, user_id: int, name: str, email: str) -&gt; bool:\n        \"\"\"\u66f4\u65b0\u4f7f\u7528\u8005\u8cc7\u6599\uff0c\u56de\u50b3\u662f\u5426\u6210\u529f\"\"\"\n        async with self.pool.acquire() as conn:\n            result = await conn.execute(\n                \"UPDATE users SET name = $1, email = $2 WHERE id = $3\",\n                name, email, user_id\n            )\n            # result \u683c\u5f0f\u70ba 'UPDATE N'\uff0cN \u662f\u5f71\u97ff\u7684\u7b46\u6578\n            return result == \"UPDATE 1\"\n\n    async def delete(self, user_id: int) -&gt; bool:\n        \"\"\"\u522a\u9664\u4f7f\u7528\u8005\uff0c\u56de\u50b3\u662f\u5426\u6210\u529f\"\"\"\n        async with self.pool.acquire() as conn:\n            result = await conn.execute(\n                \"DELETE FROM users WHERE id = $1\",\n                user_id\n            )\n            return result == \"DELETE 1\"\n\n    async def bulk_create(self, users: list&#91;UserCreate]) -&gt; None:\n        \"\"\"\u6279\u6b21\u65b0\u589e\u591a\u4f4d\u4f7f\u7528\u8005\"\"\"\n        async with self.pool.acquire() as conn:\n            await conn.executemany(\n                \"INSERT INTO users (name, email) VALUES ($1, $2)\",\n                &#91;(u.name, u.email) for u in users]\n            )\n<\/code><\/span><\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">\u65b9\u6cd5\u9078\u64c7\u901f\u67e5\u8868<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table>\n<thead>\n<tr>\n<th>\u60c5\u5883<\/th>\n<th>\u63a8\u85a6\u65b9\u6cd5<\/th>\n<th>\u539f\u56e0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u67e5\u8a62\u591a\u7b46\u8cc7\u6599<\/td>\n<td><code>fetch()<\/code><\/td>\n<td>\u56de\u50b3 list\uff0c\u65b9\u4fbf\u8fed\u4ee3<\/td>\n<\/tr>\n<tr>\n<td>\u67e5\u8a62\u55ae\u7b46\u8cc7\u6599<\/td>\n<td><code>fetchrow()<\/code><\/td>\n<td>\u76f4\u63a5\u56de\u50b3 Record \u6216 None<\/td>\n<\/tr>\n<tr>\n<td>\u53ea\u8981\u55ae\u4e00\u6b04\u4f4d\u503c<\/td>\n<td><code>fetchval()<\/code><\/td>\n<td>\u6700\u7c21\u6f54\uff0c\u76f4\u63a5\u56de\u50b3\u503c<\/td>\n<\/tr>\n<tr>\n<td>COUNT\u3001SUM \u7b49\u805a\u5408<\/td>\n<td><code>fetchval()<\/code><\/td>\n<td>\u76f4\u63a5\u53d6\u5f97\u8a08\u7b97\u7d50\u679c<\/td>\n<\/tr>\n<tr>\n<td>INSERT \u4e26\u53d6\u56de ID<\/td>\n<td><code>fetchval()<\/code> + RETURNING<\/td>\n<td>\u4e00\u6b21\u5b8c\u6210\u63d2\u5165\u548c\u53d6\u503c<\/td>\n<\/tr>\n<tr>\n<td>\u4e00\u822c INSERT\/UPDATE\/DELETE<\/td>\n<td><code>execute()<\/code><\/td>\n<td>\u4e0d\u9700\u8981\u56de\u50b3\u8cc7\u6599<\/td>\n<\/tr>\n<tr>\n<td>\u6279\u6b21 INSERT<\/td>\n<td><code>executemany()<\/code><\/td>\n<td>\u6548\u80fd\u6700\u4f73<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">\u5c0f\u7d50<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u672c\u7bc7\u4ecb\u7d39\u4e86 asyncpg \u7684\u56db\u7a2e\u67e5\u8a62\u65b9\u6cd5\u548c\u53c3\u6578\u5316\u67e5\u8a62\u7684\u4f7f\u7528\u65b9\u5f0f\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>fetch()<\/strong> \u7528\u65bc\u67e5\u8a62\u591a\u7b46\u8cc7\u6599<\/li>\n<li><strong>fetchrow()<\/strong> \u7528\u65bc\u67e5\u8a62\u55ae\u7b46\u8cc7\u6599<\/li>\n<li><strong>fetchval()<\/strong> \u7528\u65bc\u53d6\u5f97\u55ae\u4e00\u503c<\/li>\n<li><strong>execute()<\/strong> \u7528\u65bc\u57f7\u884c INSERT\/UPDATE\/DELETE<\/li>\n<li><strong>executemany()<\/strong> \u7528\u65bc\u6279\u6b21\u63d2\u5165<\/li>\n<li>\u4f7f\u7528 <code>$1, $2...<\/code> \u53c3\u6578\u4f54\u4f4d\u7b26\u9632\u6b62 SQL Injection<\/li>\n<li>Record \u53ef\u4ee5\u8f49\u63db\u70ba dict \u6216 Pydantic Model<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">\u4e0b\u4e00\u7bc7\u6211\u5011\u5c07\u4ecb\u7d39\u4ea4\u6613\uff08Transaction\uff09\u7ba1\u7406\uff0c\u5b78\u7fd2\u5982\u4f55\u78ba\u4fdd\u591a\u500b\u8cc7\u6599\u5eab\u64cd\u4f5c\u7684\u539f\u5b50\u6027\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u5ef6\u4f38\u95b1\u8b80<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/magicstack.github.io\/asyncpg\/current\/usage.html#prepared-statements\">asyncpg \u5b98\u65b9\u6587\u4ef6 &#8211; Prepared Statements<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-prepare.html\">PostgreSQL \u53c3\u6578\u5316\u67e5\u8a62<\/a><\/li>\n<li><a href=\"https:\/\/docs.pydantic.dev\/\">Pydantic \u5b98\u65b9\u6587\u4ef6<\/a><\/li>\n<\/ul>\n\n\n\n<div id=\"quiz-advanced\">\n<style>\n#quiz-advanced .badge {\n        display: inline-block;\n        padding: 4px 10px;\n        border-radius: 12px;\n        font-size: 0.75em;\n        font-weight: bold;\n        margin-left: 8px;\n      }#quiz-advanced .badge.scenario {\n        background: #ebf8ff;\n        color: #2b6cb0;\n      }#quiz-advanced .badge.debug {\n        background: #fef3c7;\n        color: #92400e;\n      }#quiz-advanced .question-adv {\n        background: white;\n        border-radius: 10px;\n        padding: 24px;\n        margin: 20px 0;\n        box-shadow: 0 2px 8px rgba(0, 0, 0, 0.08);\n        border-left: 4px solid #ed8936;\n      }#quiz-advanced .question-adv h3 {\n        margin-top: 0;\n        color: #2d3748;\n      }#quiz-advanced .code-block {\n        background: #1e1e1e;\n        color: #d4d4d4;\n        padding: 14px;\n        border-radius: 6px;\n        font-family: \"Menlo\", \"Monaco\", \"Courier New\", monospace;\n        font-size: 0.9em;\n        overflow-x: auto;\n        margin: 12px 0;\n        white-space: pre-wrap;\n      }#quiz-advanced .options-adv {\n        list-style: none;\n        padding: 0;\n      }#quiz-advanced .options-adv li {\n        padding: 14px 16px;\n        margin: 10px 0;\n        border: 2px solid #e2e8f0;\n        border-radius: 8px;\n        cursor: pointer;\n        transition: all 0.2s;\n      }#quiz-advanced .options-adv li:hover:not(.disabled) {\n        border-color: #ed8936;\n        background: #fffaf0;\n      }#quiz-advanced .options-adv li.disabled {\n        cursor: default;\n        opacity: 0.7;\n      }#quiz-advanced .options-adv li.show-answer {\n        border-color: #48bb78;\n        background: #f0fff4;\n      }#quiz-advanced .options-adv li.wrong {\n        border-color: #fc8181;\n        background: #fff5f5;\n      }#quiz-advanced .feedback-adv {\n        margin-top: 16px;\n        padding: 14px;\n        border-radius: 8px;\n        display: none;\n        line-height: 1.6;\n      }#quiz-advanced .feedback-adv.correct {\n        background: #f0fff4;\n        color: #276749;\n        display: block;\n      }#quiz-advanced .feedback-adv.wrong {\n        background: #fff5f5;\n        color: #c53030;\n        display: block;\n      }#quiz-advanced .feedback-adv \n      .score-adv {\n        text-align: center;\n        font-size: 1.4em;\n        padding: 24px;\n        background: white;\n        border-radius: 10px;\n        margin-top: 24px;\n        box-shadow: 0 2px 8px rgba(0, 0, 0, 0.08);\n      }#quiz-advanced .intro {\n        background: #ebf8ff;\n        padding: 16px;\n        border-radius: 8px;\n        margin-bottom: 20px;\n        color: #2c5282;\n      }\n<\/style>\n<h1>\u9032\u968e\u6e2c\u9a57\uff1aasyncpg CRUD \u64cd\u4f5c\u8207\u53c3\u6578\u5316\u67e5\u8a62<\/h1>\n    <div class=\"intro\">\n      <strong>\u6e2c\u9a57\u76ee\u6a19\uff1a<\/strong>\u9a57\u8b49\u4f60\u662f\u5426\u80fd\u5728\u5be6\u969b\u60c5\u5883\u4e2d\u61c9\u7528\u6240\u5b78\u3002<br \/>\n      \u5171 5 \u984c\uff0c\u5305\u542b\u60c5\u5883\u984c\u8207\u932f\u8aa4\u8a3a\u65b7\u984c\u3002\n    <\/div>\n\n    <div class=\"question-adv\" id=\"qAdv1\">\n      <h3>1. \u4f60\u6b63\u5728\u958b\u767c\u4e00\u500b\u4f7f\u7528\u8005\u7ba1\u7406 API\uff0c\u9700\u8981\u5be6\u4f5c\u300c\u6839\u64da ID \u67e5\u8a62\u55ae\u4e00\u4f7f\u7528\u8005\u300d\u7684\u529f\u80fd\u3002\u67e5\u8a62\u7d50\u679c\u9700\u8981\u8f49\u63db\u70ba Pydantic Model\u3002\u4ee5\u4e0b\u54ea\u7a2e\u5be6\u4f5c\u65b9\u5f0f\u6700\u6b63\u78ba\uff1f <span class=\"badge scenario\">\u60c5\u5883\u984c<\/span><\/h3>\n      <ul class=\"options-adv\">\n        <li onclick=\"checkAnswerAdv(1, 'A', 'B')\">A. \u4f7f\u7528 <code>fetch()<\/code> \u67e5\u8a62\u5f8c\u53d6\u7b2c\u4e00\u7b46\uff0c\u82e5\u7d50\u679c\u70ba\u7a7a\u5247\u56de\u50b3 None<\/li>\n        <li onclick=\"checkAnswerAdv(1, 'B', 'B')\">B. \u4f7f\u7528 <code>fetchrow()<\/code> \u67e5\u8a62\uff0c\u6aa2\u67e5\u662f\u5426\u70ba None\uff0c\u82e5\u6709\u8cc7\u6599\u5247\u7528 <code>User(**dict(row))<\/code> \u8f49\u63db<\/li>\n        <li onclick=\"checkAnswerAdv(1, 'C', 'B')\">C. \u4f7f\u7528 <code>fetchval()<\/code> \u76f4\u63a5\u53d6\u5f97\u4f7f\u7528\u8005\u7269\u4ef6<\/li>\n        <li onclick=\"checkAnswerAdv(1, 'D', 'B')\">D. \u4f7f\u7528 <code>execute()<\/code> \u57f7\u884c SELECT \u8a9e\u53e5\u4e26\u89e3\u6790\u56de\u50b3\u503c<\/li>\n      <\/ul>\n      <div class=\"feedback-adv\" id=\"fbAdv1\"><\/div>\n    <\/div>\n\n    <div class=\"question-adv\" id=\"qAdv2\">\n      <h3>2. \u5c0f\u660e\u5beb\u4e86\u4ee5\u4e0b\u7a0b\u5f0f\u78bc\u4f86\u641c\u5c0b\u4f7f\u7528\u8005\uff0c\u4f46\u88ab\u8cc7\u5b89\u5718\u968a\u6a19\u8a18\u70ba\u6709\u5b89\u5168\u6f0f\u6d1e\u3002\u8acb\u554f\u554f\u984c\u5728\u54ea\u88e1\uff1f <span class=\"badge debug\">\u932f\u8aa4\u8a3a\u65b7<\/span><\/h3>\n      <div class=\"code-block\">async def search_user(conn, name: str):\n    query = f&#8221;SELECT * FROM users WHERE name = &#8216;{name}'&#8221;\n    return await conn.fetch(query)<\/div>\n      <ul class=\"options-adv\">\n        <li onclick=\"checkAnswerAdv(2, 'A', 'C')\">A. fetch() \u61c9\u8a72\u6539\u6210 fetchrow() \u56e0\u70ba\u53ea\u641c\u5c0b\u4e00\u500b\u540d\u5b57<\/li>\n        <li onclick=\"checkAnswerAdv(2, 'B', 'C')\">B. \u61c9\u8a72\u4f7f\u7528 async with \u4f86\u7ba1\u7406\u9023\u7dda<\/li>\n        <li onclick=\"checkAnswerAdv(2, 'C', 'C')\">C. \u4f7f\u7528 f-string \u7d44\u5408 SQL \u6703\u9020\u6210 SQL Injection \u6f0f\u6d1e\uff0c\u61c9\u6539\u7528\u53c3\u6578\u5316\u67e5\u8a62 <code>$1<\/code><\/li>\n        <li onclick=\"checkAnswerAdv(2, 'D', 'C')\">D. SELECT * \u6548\u80fd\u4e0d\u4f73\uff0c\u61c9\u8a72\u660e\u78ba\u6307\u5b9a\u6b04\u4f4d\u540d\u7a31<\/li>\n      <\/ul>\n      <div class=\"feedback-adv\" id=\"fbAdv2\"><\/div>\n    <\/div>\n\n    <div class=\"question-adv\" id=\"qAdv3\">\n      <h3>3. \u4f60\u9700\u8981\u5f9e\u8cc7\u6599\u5eab\u53d6\u5f97\u4f7f\u7528\u8005\u7e3d\u6578\u4f86\u986f\u793a\u5728\u5100\u8868\u677f\u4e0a\u3002\u4ee5\u4e0b\u54ea\u500b\u5be6\u4f5c\u65b9\u5f0f\u6700\u7c21\u6f54\u6709\u6548\uff1f <span class=\"badge scenario\">\u60c5\u5883\u984c<\/span><\/h3>\n      <ul class=\"options-adv\">\n        <li onclick=\"checkAnswerAdv(3, 'A', 'D')\">A. <code>rows = await conn.fetch(\"SELECT COUNT(*) FROM users\")<\/code> \u7136\u5f8c\u53d6 <code>rows[0][0]<\/code><\/li>\n        <li onclick=\"checkAnswerAdv(3, 'B', 'D')\">B. <code>row = await conn.fetchrow(\"SELECT COUNT(*) FROM users\")<\/code> \u7136\u5f8c\u53d6 <code>row[0]<\/code><\/li>\n        <li onclick=\"checkAnswerAdv(3, 'C', 'D')\">C. <code>await conn.execute(\"SELECT COUNT(*) FROM users\")<\/code> \u4e26\u89e3\u6790\u56de\u50b3\u7684\u72c0\u614b\u5b57\u4e32<\/li>\n        <li onclick=\"checkAnswerAdv(3, 'D', 'D')\">D. <code>count = await conn.fetchval(\"SELECT COUNT(*) FROM users\")<\/code> \u76f4\u63a5\u53d6\u5f97\u6578\u5b57<\/li>\n      <\/ul>\n      <div class=\"feedback-adv\" id=\"fbAdv3\"><\/div>\n    <\/div>\n\n    <div class=\"question-adv\" id=\"qAdv4\">\n      <h3>4. \u4ee5\u4e0b\u7a0b\u5f0f\u78bc\u57f7\u884c\u5f8c\uff0c<code>result<\/code> \u8b8a\u6578\u7684\u503c\u6703\u662f\u4ec0\u9ebc\uff1f <span class=\"badge debug\">\u932f\u8aa4\u8a3a\u65b7<\/span><\/h3>\n      <div class=\"code-block\">result = await conn.execute(\n    &#8220;UPDATE users SET name = $1 WHERE id = $2&#8221;,\n    &#8220;Alice&#8221;, 999  # \u5047\u8a2d id=999 \u7684\u4f7f\u7528\u8005\u4e0d\u5b58\u5728\n)\nprint(result)<\/div>\n      <ul class=\"options-adv\">\n        <li onclick=\"checkAnswerAdv(4, 'A', 'B')\">A. \u6703\u62cb\u51fa Exception\uff0c\u56e0\u70ba\u627e\u4e0d\u5230 id=999 \u7684\u4f7f\u7528\u8005<\/li>\n        <li onclick=\"checkAnswerAdv(4, 'B', 'B')\">B. \u6703\u5370\u51fa <code>\"UPDATE 0\"<\/code>\uff0c\u8868\u793a\u6c92\u6709\u4efb\u4f55\u8cc7\u6599\u88ab\u66f4\u65b0<\/li>\n        <li onclick=\"checkAnswerAdv(4, 'C', 'B')\">C. \u6703\u56de\u50b3 None<\/li>\n        <li onclick=\"checkAnswerAdv(4, 'D', 'B')\">D. \u6703\u56de\u50b3 False \u8868\u793a\u66f4\u65b0\u5931\u6557<\/li>\n      <\/ul>\n      <div class=\"feedback-adv\" id=\"fbAdv4\"><\/div>\n    <\/div>\n\n    <div class=\"question-adv\" id=\"qAdv5\">\n      <h3>5. \u4f60\u9700\u8981\u4e00\u6b21\u6027\u532f\u5165 5000 \u4f4d\u4f7f\u7528\u8005\u7684\u8cc7\u6599\u5230\u8cc7\u6599\u5eab\u3002\u4ee5\u4e0b\u54ea\u7a2e\u505a\u6cd5\u6548\u80fd\u6700\u4f73\uff1f <span class=\"badge scenario\">\u60c5\u5883\u984c<\/span><\/h3>\n      <div class=\"code-block\">users_data = [(&#8220;User1&#8221;, &#8220;u1@example.com&#8221;), (&#8220;User2&#8221;, &#8220;u2@example.com&#8221;), &#8230;]  # 5000 \u7b46<\/div>\n      <ul class=\"options-adv\">\n        <li onclick=\"checkAnswerAdv(5, 'A', 'C')\">A. \u4f7f\u7528 for \u8ff4\u5708\u9010\u7b46\u547c\u53eb <code>conn.execute()<\/code> \u63d2\u5165<\/li>\n        <li onclick=\"checkAnswerAdv(5, 'B', 'C')\">B. \u5c07\u6240\u6709\u8cc7\u6599\u7d44\u6210\u4e00\u689d\u8d85\u9577\u7684 INSERT \u8a9e\u53e5\u57f7\u884c<\/li>\n        <li onclick=\"checkAnswerAdv(5, 'C', 'C')\">C. \u4f7f\u7528 <code>conn.executemany()<\/code> \u6279\u6b21\u63d2\u5165<\/li>\n        <li onclick=\"checkAnswerAdv(5, 'D', 'C')\">D. \u4f7f\u7528 <code>asyncio.gather()<\/code> \u540c\u6642\u57f7\u884c 5000 \u500b execute() \u547c\u53eb<\/li>\n      <\/ul>\n      <div class=\"feedback-adv\" id=\"fbAdv5\"><\/div>\n    <\/div>\n\n    <div class=\"score-adv\" id=\"scoreAdv\" style=\"display:none;\"><\/div>\n<script>\n\n      let answeredAdv = 0;\n      let correctAdv = 0;\n      const totalAdv = 5;\n      const explanationsAdv = {\n        1: \"<code>fetchrow()<\/code> \u662f\u67e5\u8a62\u55ae\u7b46\u8cc7\u6599\u7684\u6700\u4f73\u9078\u64c7\uff0c\u5b83\u76f4\u63a5\u56de\u50b3 Record \u6216 None\u3002\u642d\u914d <code>User(**dict(row))<\/code> \u53ef\u4ee5\u4e7e\u6de8\u5730\u8f49\u63db\u70ba Pydantic Model\u3002\u9078\u9805 A \u96d6\u7136\u53ef\u884c\u4f46\u591a\u9918\uff0c<code>fetchval()<\/code> \u53ea\u80fd\u53d6\u5f97\u55ae\u4e00\u6b04\u4f4d\u503c\u800c\u975e\u6574\u500b Record\uff0c<code>execute()<\/code> \u5247\u4e0d\u9069\u7528\u65bc SELECT \u67e5\u8a62\u3002\",\n        2: \"\u9019\u662f\u5178\u578b\u7684 SQL Injection \u6f0f\u6d1e\uff01\u4f7f\u7528 f-string \u6216\u5b57\u4e32\u4e32\u63a5\u7d44\u5408 SQL \u6703\u8b93\u60e1\u610f\u8f38\u5165\uff08\u5982 <code>'; DROP TABLE users; --<\/code>\uff09\u88ab\u7576\u4f5c SQL \u6307\u4ee4\u57f7\u884c\u3002\u6b63\u78ba\u505a\u6cd5\u662f\u4f7f\u7528\u53c3\u6578\u5316\u67e5\u8a62\uff1a<code>await conn.fetch(\\\"SELECT * FROM users WHERE name = $1\\\", name)<\/code>\uff0c\u8b93 asyncpg \u81ea\u52d5\u8655\u7406\u8df3\u812b\u5b57\u5143\u3002\",\n        3: \"<code>fetchval()<\/code> \u662f\u53d6\u5f97\u55ae\u4e00\u503c\u6700\u7c21\u6f54\u7684\u65b9\u6cd5\uff0c\u5b83\u9810\u8a2d\u56de\u50b3\u67e5\u8a62\u7d50\u679c\u7b2c\u4e00\u7b46\u7b2c\u4e00\u500b\u6b04\u4f4d\u7684\u503c\u3002\u5c0d\u65bc COUNT\u3001SUM \u7b49\u805a\u5408\u51fd\u6578\uff0c\u76f4\u63a5\u4f7f\u7528 fetchval() \u53ef\u4ee5\u4e00\u6b65\u5230\u4f4d\u53d6\u5f97\u7d50\u679c\uff0c\u4e0d\u9700\u8981\u984d\u5916\u7684\u7d22\u5f15\u64cd\u4f5c\u3002\",\n        4: \"<code>execute()<\/code> \u56de\u50b3\u7684\u662f\u72c0\u614b\u5b57\u4e32\uff0c\u683c\u5f0f\u70ba\u300c\u547d\u4ee4 \u5f71\u97ff\u7b46\u6578\u300d\u3002\u7576 UPDATE \u6c92\u6709\u5339\u914d\u5230\u4efb\u4f55\u8cc7\u6599\u6642\uff0c\u4e0d\u6703\u62cb\u51fa\u4f8b\u5916\uff0c\u800c\u662f\u56de\u50b3 <code>\\\"UPDATE 0\\\"<\/code>\u3002\u9019\u662f\u6b63\u5e38\u884c\u70ba\uff0c\u958b\u767c\u8005\u9700\u8981\u81ea\u884c\u6aa2\u67e5\u56de\u50b3\u503c\u4f86\u5224\u65b7\u64cd\u4f5c\u662f\u5426\u7b26\u5408\u9810\u671f\u3002\",\n        5: \"<code>executemany()<\/code> \u6703\u5c07\u6240\u6709\u8cc7\u6599\u5728\u4e00\u6b21\u8cc7\u6599\u5eab\u5f80\u8fd4\u4e2d\u5b8c\u6210\uff0c\u5927\u5e45\u6e1b\u5c11\u7db2\u8def\u5ef6\u9072\u7684\u5f71\u97ff\u3002\u6587\u4ef6\u4e2d\u63d0\u5230\u5c0d\u65bc 1000 \u7b46\u8cc7\u6599\uff0cexecutemany() \u901a\u5e38\u5feb 10 \u500d\u4ee5\u4e0a\u3002\u8ff4\u5708\u57f7\u884c\u6703\u6709 5000 \u6b21\u5f80\u8fd4\uff0c\u7d44\u6210\u8d85\u9577 SQL \u6709\u9577\u5ea6\u9650\u5236\u4e14\u96e3\u4ee5\u7dad\u8b77\uff0casyncio.gather() \u6703\u540c\u6642\u5efa\u7acb\u5927\u91cf\u9023\u7dda\u9020\u6210\u8cc7\u6e90\u58d3\u529b\u3002\"\n      };\n\n      function checkAnswerAdv(qNum, selected, correctAns) {\n        const question = document.getElementById(\"qAdv\" + qNum);\n        if (question.classList.contains(\"answered\")) return;\n\n        question.classList.add(\"answered\");\n        answeredAdv++;\n\n        const options = question.querySelectorAll(\".options-adv li\");\n        options.forEach((opt) => {\n          opt.classList.add(\"disabled\");\n          if (opt.textContent.startsWith(correctAns + \".\")) {\n            opt.classList.add(\"show-answer\");\n          }\n        });\n\n        const feedback = document.getElementById(\"fbAdv\" + qNum);\n        if (selected === correctAns) {\n          correctAdv++;\n          feedback.className = \"feedback-adv correct\";\n          feedback.innerHTML = \"\u2713 \u6b63\u78ba\uff01\" + explanationsAdv[qNum];\n        } else {\n          feedback.className = \"feedback-adv wrong\";\n          feedback.innerHTML =\n            \"\u2717 \u932f\u8aa4\u3002\u6b63\u78ba\u7b54\u6848\u662f \" + correctAns + \"\u3002\" + explanationsAdv[qNum];\n          options.forEach((opt) => {\n            if (opt.textContent.startsWith(selected + \".\")) {\n              opt.classList.add(\"wrong\");\n            }\n          });\n        }\n\n        if (answeredAdv === totalAdv) {\n          const scoreDiv = document.getElementById(\"scoreAdv\");\n          scoreDiv.style.display = \"block\";\n          let msg = \"\";\n          if (correctAdv === totalAdv) msg = \"\ud83c\udfaf \u5b8c\u7f8e\uff01\u4f60\u5df2\u7d93\u6e96\u5099\u597d\u5be6\u6230\u4e86\uff01\";\n          else if (correctAdv >= Math.ceil(totalAdv * 0.8))\n            msg = \"\ud83d\udcaa \u5f88\u68d2\uff01\u518d\u8907\u7fd2\u4e00\u4e0b\u932f\u7684\u984c\u76ee\";\n          else if (correctAdv >= Math.ceil(totalAdv * 0.6))\n            msg = \"\ud83d\udcd6 \u5efa\u8b70\u91cd\u8b80\u76f8\u95dc\u6bb5\u843d\u5f8c\u518d\u8a66\u4e00\u6b21\";\n          else msg = \"\ud83d\udd04 \u5efa\u8b70\u91cd\u65b0\u95b1\u8b80\u6587\u4ef6\uff0c\u7279\u5225\u6ce8\u610f\u64cd\u4f5c\u6d41\u7a0b\";\n          scoreDiv.innerHTML =\n            \"\u6e2c\u9a57\u5b8c\u6210\uff01\u5f97\u5206\uff1a\" + correctAdv + \" \/ \" + totalAdv + \"<br>\" + msg;\n        }\n      }\n    \n<\/script>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u6e2c\u9a57\uff1aasyncpg CRUD \u64cd\u4f5c\u8207\u53c3\u6578\u5316\u67e5\u8a62 \u5171 5 \u984c\uff0c\u9ede\u9078\u7b54\u6848\u5f8c\u6703\u7acb\u5373\u986f\u793a\u7d50\u679c 1. \u5728 asyncp [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rolpb_likes":0,"footnotes":""},"categories":[316],"tags":[317],"class_list":["post-300","post","type-post","status-publish","format-standard","hentry","category-asyncpg","tag-asyncpg"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":7}},"_links":{"self":[{"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/comments?post=300"}],"version-history":[{"count":0,"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/posts\/300\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/media?parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/categories?post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.itsmygo.uk\/wordpress\/wp-json\/wp\/v2\/tags?post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}