{"id":1586,"date":"2020-05-12T12:07:00","date_gmt":"2020-05-12T04:07:00","guid":{"rendered":"https:\/\/www.yumefx.com\/?p=1586"},"modified":"2021-03-02T21:25:20","modified_gmt":"2021-03-02T13:25:20","slug":"python%e6%93%8d%e4%bd%9cmysql","status":"publish","type":"post","link":"https:\/\/www.yumefx.com\/?p=1586","title":{"rendered":"python\u64cd\u4f5cmysql"},"content":{"rendered":"\n<h1 style=\"text-align: center;\">\u73af\u5883\u51c6\u5907<\/h1>\n<p>PyMySQL \u662f\u5728 Python3.x \u7248\u672c\u4e2d\u7528\u4e8e\u8fde\u63a5 MySQL \u670d\u52a1\u5668\u7684\u4e00\u4e2a\u5e93\uff0cPython2.X\u4e2d\u5219\u4f7f\u7528mysqldb\u3002<\/p>\n<p>\u5b89\u88c5\u7684\u8bdd\u76f4\u63a5\u4f7f\u7528pip\u5b89\u88c5\u5373\u53ef\u3002<\/p>\n\n\n\n<pre>\n<span>pip install PyMySQL<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u6570\u636e\u5e93\u8fde\u63a5<\/h1>\n\n\n\n<pre>\n<span>import pymysql <\/span>\n<span><\/span>\n<span># \u6253\u5f00\u6570\u636e\u5e93\u8fde\u63a5 <\/span>\n<span>db = pymysql.connect(&quot;localhost&quot;,&quot;testuser&quot;,&quot;test123&quot;,&quot;TESTDB&quot; ) <\/span>\n<span><\/span>\n<span># \u4f7f\u7528 cursor() \u65b9\u6cd5\u521b\u5efa\u4e00\u4e2a\u6e38\u6807\u5bf9\u8c61 cursor <\/span>\n<span>cursor = db.cursor() <\/span>\n<span><\/span>\n<span># \u4f7f\u7528 execute() \u65b9\u6cd5\u6267\u884c SQL \u67e5\u8be2  <\/span>\n<span>cursor.execute(&quot;SELECT VERSION()&quot;) <\/span>\n<span><\/span>\n<span># \u4f7f\u7528 fetchone() \u65b9\u6cd5\u83b7\u53d6\u5355\u6761\u6570\u636e <\/span>\n<span>data = cursor.fetchone() <\/span>\n<span>print (&quot;Database version : %s &quot; % data) <\/span>\n<span><\/span>\n<span># \u5173\u95ed\u6570\u636e\u5e93\u8fde\u63a5 <\/span>\n<span>db.close()<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u521b\u5efa\u6570\u636e\u5e93\u8868<\/h1>\n\n\n\n<pre>\n<span># \u4f7f\u7528 execute() \u65b9\u6cd5\u6267\u884c SQL\uff0c\u5982\u679c\u8868\u5b58\u5728\u5219\u5220\u9664<\/span>\n<span>cursor.execute(&quot;DROP TABLE IF EXISTS EMPLOYEE&quot;)<\/span>\n<span> <\/span>\n<span># \u4f7f\u7528\u9884\u5904\u7406\u8bed\u53e5\u521b\u5efa\u8868<\/span>\n<span>sql = &quot;&quot;&quot;CREATE TABLE EMPLOYEE (<\/span>\n<span>         FIRST_NAME  CHAR(20) NOT NULL,<\/span>\n<span>         LAST_NAME  CHAR(20),<\/span>\n<span>         AGE INT,  <\/span>\n<span>         DAY DATE,<\/span>\n<span>         time DATETIME,<\/span>\n<span>         COMMENT,TEXT,<\/span>\n<span>         SEX CHAR(1),<\/span>\n<span>         INCOME FLOAT )&quot;&quot;&quot;<\/span>\n<span> <\/span>\n<span>cursor.execute(sql)<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u6570\u636e\u5e93\u63d2\u5165\u64cd\u4f5c<\/h1>\n\n\n\n<pre>\n<span># SQL \u63d2\u5165\u8bed\u53e5 <\/span>\n<span>sql = &quot;&quot;&quot;INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (&#x27;Mac&#x27;, &#x27;Mohan&#x27;, 20, &#x27;M&#x27;, 2000)&quot;&quot;&quot; <\/span>\n<span><\/span>\n<span>try: <\/span>\n<span>    # \u6267\u884csql\u8bed\u53e5 <\/span>\n<span>    cursor.execute(sql) <\/span>\n<span>    # \u63d0\u4ea4\u5230\u6570\u636e\u5e93\u6267\u884c <\/span>\n<span>    db.commit() <\/span>\n<span>except: <\/span>\n<span>    # \u5982\u679c\u53d1\u751f\u9519\u8bef\u5219\u56de\u6eda <\/span>\n<span>    db.rollback()<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u6570\u636e\u5e93\u67e5\u8be2\u64cd\u4f5c<\/h1>\n<p>Python\u67e5\u8be2Mysql\u4f7f\u7528 fetchone() \u65b9\u6cd5\u83b7\u53d6\u5355\u6761\u6570\u636e, \u4f7f\u7528fetchall() \u65b9\u6cd5\u83b7\u53d6\u591a\u6761\u6570\u636e\u3002<\/p>\n<ul>\n<li><b>fetchone():<\/b> \u8be5\u65b9\u6cd5\u83b7\u53d6\u4e0b\u4e00\u4e2a\u67e5\u8be2\u7ed3\u679c\u96c6\u3002\u7ed3\u679c\u96c6\u662f\u4e00\u4e2a\u5bf9\u8c61<\/li>\n<li><b>fetchall(): <\/b>\u63a5\u6536\u5168\u90e8\u7684\u8fd4\u56de\u7ed3\u679c\u884c.<\/li>\n<li><b>rowcount:<\/b> \u8fd9\u662f\u4e00\u4e2a\u53ea\u8bfb\u5c5e\u6027\uff0c\u5e76\u8fd4\u56de\u6267\u884cexecute()\u65b9\u6cd5\u540e\u5f71\u54cd\u7684\u884c\u6570\u3002<\/li>\n<\/ul>\n\n\n\n<pre>\n<span># SQL \u67e5\u8be2\u8bed\u53e5<\/span>\n<span>sql = &quot;SELECT * FROM EMPLOYEE \\<\/span>\n<span>       WHERE INCOME &gt; %s&quot; % (1000)<\/span>\n<span><\/span>\n<span>try:<\/span>\n<span>   # \u6267\u884cSQL\u8bed\u53e5<\/span>\n<span>   cursor.execute(sql)<\/span>\n<span>   <\/span>\n<span>   # \u83b7\u53d6\u6240\u6709\u8bb0\u5f55\u5217\u8868<\/span>\n<span>   results = cursor.fetchall()<\/span>\n<span>   for row in results:<\/span>\n<span>      fname = row[0]<\/span>\n<span>      lname = row[1]<\/span>\n<span>      age = row[2]<\/span>\n<span>      sex = row[3]<\/span>\n<span>      income = row[4]<\/span>\n<span>      <\/span>\n<span>      # \u6253\u5370\u7ed3\u679c<\/span>\n<span>      print (&quot;fname=%s,lname=%s,age=%s,sex=%s,income=%s&quot; % \\<\/span>\n<span>             (fname, lname, age, sex, income ))<\/span>\n<span>except:<\/span>\n<span>   print (&quot;Error: unable to fetch data&quot;)<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u6570\u636e\u5e93\u66f4\u65b0\u64cd\u4f5c<\/h1>\n<p>\u66f4\u65b0\u64cd\u4f5c\u7528\u4e8e\u66f4\u65b0\u6570\u636e\u8868\u7684\u7684\u6570\u636e\uff0c\u4ee5\u4e0b\u5b9e\u4f8b\u5c06 TESTDB \u8868\u4e2d SEX \u4e3a &#8216;M&#8217; \u7684 AGE \u5b57\u6bb5\u9012\u589e 1\uff1a<\/p>\n\n\n\n<pre>\n<span># SQL \u66f4\u65b0\u8bed\u53e5<\/span>\n<span>sql = &quot;UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = &#x27;%c&#x27;&quot; % (&#x27;M&#x27;)<\/span>\n<span><\/span>\n<span>try:<\/span>\n<span>   # \u6267\u884cSQL\u8bed\u53e5<\/span>\n<span>   cursor.execute(sql)<\/span>\n<span>   # \u63d0\u4ea4\u5230\u6570\u636e\u5e93\u6267\u884c<\/span>\n<span>   db.commit()<\/span>\n<span>except:<\/span>\n<span>   # \u53d1\u751f\u9519\u8bef\u65f6\u56de\u6eda<\/span>\n<span>   db.rollback()<\/span>\n<\/pre>\n\n\n\n<h1>\u00a0<\/h1>\n<h1 style=\"text-align: center;\">\u5220\u9664\u64cd\u4f5c<\/h1>\n<p>\u5220\u9664\u64cd\u4f5c\u7528\u4e8e\u5220\u9664\u6570\u636e\u8868\u4e2d\u7684\u6570\u636e\uff0c\u4ee5\u4e0b\u5b9e\u4f8b\u6f14\u793a\u4e86\u5220\u9664\u6570\u636e\u8868 EMPLOYEE \u4e2d AGE \u5927\u4e8e 20\u5e76\u5c0f\u4e8e100 \u7684\u6240\u6709\u6570\u636e\u3002<\/p>\n\n\n\n<pre>\n<span># SQL \u5220\u9664\u8bed\u53e5 <\/span>\n<span>sql = &quot;DELETE FROM EMPLOYEE WHERE AGE &gt; %s AND AGE &lt; %s&quot; % (20,100) <\/span>\n<span>try: <\/span>\n<span>    # \u6267\u884cSQL\u8bed\u53e5 <\/span>\n<span>    cursor.execute(sql) <\/span>\n<span>    # \u63d0\u4ea4\u4fee\u6539 <\/span>\n<span>    db.commit() <\/span>\n<span>except: <\/span>\n<span>    # \u53d1\u751f\u9519\u8bef\u65f6\u56de\u6eda <\/span>\n<span>    db.rollback()<\/span>\n<\/pre>\n\n\n\n<p>\u00a0<\/p>\n<p>\u672c\u6587\u8f6c\u8f7d\u81ea<a href=\"https:\/\/www.runoob.com\/python3\/python3-mysql.html\">\u83dc\u9e1f\u6559\u7a0b<\/a>\u3002<\/p>\n<p>\u00a0<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p>\u6700\u806a\u660e\u7684\u5904\u4e16\u4e4b\u672f\u662f\uff0c<\/p>\n<p>\u65e2\u5bf9\u4e16\u4fd7\u6295\u4ee5\u767d\u773c\uff0c<\/p>\n<p>\u53c8\u613f\u4e0e\u5176\u540c\u6d41\u5408\u6c61\u3002<\/p>\n<p>\u300a\u4f8f\u5112\u8b66\u8bed\u300b\u2014\u2014\u82a5\u5ddd\u9f99\u4e4b\u4ecb<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u73af\u5883\u51c6\u5907 PyMySQL \u662f\u5728 Python3.x \u7248\u672c\u4e2d\u7528\u4e8e\u8fde\u63a5 MySQL \u670d\u52a1\u5668\u7684\u4e00\u4e2a\u5e93\uff0cPython2 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1612,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[12,4],"tags":[48,37,15],"_links":{"self":[{"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/posts\/1586"}],"collection":[{"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1586"}],"version-history":[{"count":0,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/posts\/1586\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=\/wp\/v2\/media\/1612"}],"wp:attachment":[{"href":"https:\/\/www.yumefx.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yumefx.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}