Python使用MySQL数据库的方法以及一个实例

时间:2023-03-09 01:50:00
Python使用MySQL数据库的方法以及一个实例

使用环境:Windows+python3.4+MySQL5.5+Navicat

一、创建连接

1.准备工作,想要使用Python操作MySQL,首先需要安装MySQL-Python的包,在Python 3.x下,该包已经改名为MySQLClient。可以使用pip方式安装:

pip install MySQLClient

或者下载包文件,进行安装也可以。

2.Python使用MySQL的流程:

Python使用MySQL数据库的方法以及一个实例

3.启动MySQL服务器:以管理员身份启动“cmd”,输入命令:’net start mysql‘

Python中使用MySQL导入方法:import MySQLdb

4.创建Connection

  Connection:创建了Python客户端与数据库之间的网络通路。他的参数如下

参数名 类型 说明
host String MySQL的服务器地址
port int MySQL的端口号
user String 用户名
passwd String 密码
db String 使用的数据库
charset String 连接字符集

  Connection支持的方法:

方法名 说明
cursor() 创建并且返回游标
commit() 提交当前事物
rollback() 回滚当前事物r()
close() 关闭Connection

5.获取Cursor.

Cursor:游标对象,用于执行查询和获取结果,它支持的方法如下:

方法名 说明
execute() 用于执行一个数据库的查询命令
fetchone() 获取结果集中的下一行

fetchmany(size)

获取结果集中的下(size)行
fetchall() 获取结果集中剩下的所有行
rowcount 最近一次execute返回数据/影响的行数
close() 关闭游标

下面我们在Python中创建一个实例:

import MySQLdb

conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')

cursor=conn.cursor()

print(conn)
print(cursor) cursor.close()
conn.close()

运行程序结果如下:Python使用MySQL数据库的方法以及一个实例

从结果中我们可以看见成功创建了一个Connection和Cursor对象。

二、建立数据库,进行一些简单操作

1.简单的创建一个’user‘表,并且插入一些数据。user表中只有两个字段:userid和username。代码如下:

import MySQLdb
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')
cur=conn.cursor() cur.execute("""
create table if not EXISTS user
(
userid int(11) PRIMARY KEY ,
username VARCHAR(20)
)
""")
for i in range(1,10):
cur.execute("insert into user(userid,username) values('%d','%s')" %(int(i),'name'+str(i)))
conn.commit() cur.close()
conn.close()

我们用Navicat打开数据库,查看一下结果,aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMIAAAD+CAIAAADnO02wAAAbKUlEQVR4nO2d2X8aR7bH++/J4x1LH0d/wL2fO04md5Z4HzPBFpGdWZOZLPOSh0yYsZ1xFGfixDte4gXZ1o5jS/IqW5ItJCSQENASQguIfROLJJb7UHTTbE1jDhTurt/nPLSbbvpU19dVreacOtTofGiEDo3QoVV/fOehDwNERJWLGp0Pj9KhUTpkJxgRvaqoUTo4QgdH6OCqP77zIMGI6FVEjVj8zy3+Z2b/qi/2rvzPuP0hei1FPZvzPDN6nhrdK57Ir3/3B9z+EL2Wop7MOh/PrD00rC25w788cAS3P0SvpajHevvD6dUHUytLztA7+9rS6fQAEVGFoh5OrzzQLQ9O2hbXgm/vVaSJiCoX9UC3NDhhuz9uXVwLvLX7kIqIqHJRQ5O2Aa31vta6uOZHGJVFj6bp5VhaBEbTdB3+pza+qr8P2dHIyoxGQq6KnQCCEaAAMBrSLQ9O2Aa0i7a1wNt7WglGEhQERlMrQ7rlocklmzP4i33vE4wkKACMHuntyJbc4f/77RGCkQQFgNGw0TVsdD01ule8kV//7g/YMDJ8/V/Um58bCvZ3Kajmr58SjGopAIxGLH5kdl/s3YN/IRhJUAAYvZgPvVgIv1hYdwQ2div+2nCTGsGo9gLAaNwW1S5FtUsxZ3Brb9vHKpUqFt/ksUQiSdP0kDddJ7upoLZ9fbk2X07TNH9jpWCoQ6vFaHJlQ7e6Mbm66Qon9h3+tDKMxr9+g1L8s2D78j/epDJiPvVqdjC73viHZcibHvJaDm+jdtzU7KAoatvXl7lfxTmYQp/WGqOZr5qpQ10F24bj2xknmE/jPXJmV/Px2Vh8MxafPdZMybt75BRFNX9liPfIqe3Hjh/KPQZ9bUbybvarth/rzuyXd2/GugvOKnK5hsRIZ09M2RM6R8K1ntx35DOVShWNbfBYeYxygGCxePPweJpDT2aDKkKhZgeFDkgPedP/lNUWo0y7DMebqYNdedvcnRnrllPbjxrQ9szRZkreldmgskd2yymKknczX4WOnzkqP65HB3QdZA7ullMU1Xxcn9nJPYs9oPBywAaD0bQjOeVITjmSrvXkfsEYDXpSg57U4IsTb1Ctyvzt/h0URVHbD79IZQ670Url6o0vzIMe8+Ft1I4bqfyvutFKbTtxycM5l/tPUCuDEermbEcync1R87GZgg7O6/vs6fpj7NhWSEmx7eKXa1SMph2JaUfCFU7uO/JpWYySybIYITMf3sbAVByFxscoOwxQbL82M4NK7gFlMDIcb2YhKD7YlMCoyOWADXVotRhN2bd09i3d6qYrvLX38CcqlSoSjfNYIpmkaXrAnRpwpwbGTrxBbW8bSw24U6ovtlNU65fu1MDYibYbqQF3asBtbttG/fxGasDd/3MKbaQG3KkvD7R+mfMp+1VoP/dgc9s2itp2QuVmDgM1mqYz7dIfb6aaj+rjkWh8+mgzRR3sjMYj+uNHu1CrESjxSLRbTqGNeCQa75Qf7Mz5FFm3nPmqSHTmaHPzUX080nWQaj4+Hc39/pwji24XvRywJUAw0q1sTC7HJ5Zja8FN9Ad/BRhl6KEoinrjQCvDQerLA8wofKCfQ0lGP8+HLA+j1EB2Etze9kVrPTDK9C5FUVSz/GBzppvjnezzrbybAxyzLx+y0hhFZ5iv535/WYyKXq4hMdLaIuOL6y+tYYc/vvPgX1Qq1XokxmNoKr3vS4rAaJrmb6wUDObZaIwOjJq9z83eVebHkPB6lMcyGNmTIjCapvkbKwWDwWjY6Hw643isty+7Q+/sbxOI0T1nQgRGMALD6NHU0oOJxUHtgm3N/9bugyqVKhSO8FgikaBp+t5qQgRG0zR/Y6VgqEOrxWjgJX3vhfmn0Tmr3fu/v/mdSqUKhtZ5bCuRoGn6rmNLBEbTNH9jpWBbIBj9NGLUPJ/tGzbMr7j/51e/JRhJzWAw0jyb6R/W9z6Znl9x//cv96tUKppIegLASPPM0DdsWGBGIyHxFT87nRaBBQKBKm+fOFT9faDujZrujc7dHck+GxGMpCYAjIa0C0PahcHxBRuTYEQwkpoAMHpiWH2iX32iX11xh9/Zf5hgJEEBYDRq8oyaPCNmj90X/fV7fyQYSVAAGGmtoXFraNwaWgvEdx76kIsRRVEEIykIACPdSky3EtMtx1yhrT3v/y0Po6IkvXYYPWZaq+rAiJFGkfmpvqXdUsfLChAARnrHpt6xqbdvuplY7DyMCkl6vTBS+dK2ifTPTqd/NphOp9Of48HI0t7C4KNRUJRCU68LCxEARrPOxIwzMeNMuJlYbDZUmRu+mRfCjB0OodaRtnHQUfnSjwdxYGRpb8mik/OPRhAERq7krDM540x6Isn9H5TEiEvS64TRYDq9kP3n5wvMyJSPkUZBtbS3K/JnHUt7C9N8puM1CqqlXZPZr9BkJyvOXMXOX5wRiAOORtFYExsARkZ3yuhOGV0pTyS1/4O/82DEkvQaYbR3QjhGDCnZ+cfSrmB6OzsTaRQsHYiW7FnsASwkmb15449oMZp1ZzFiI97zGOJGwmPnoyajUbbvecYj7pHFtrMjEWdAkuBoVBSjvIQK/HwIx8iX3sv8s/SzUTGMLO0t7KxUfLApgVEhIzk7xfhslMHInfIyGLER7yxDhZHw+PkQbFl0cpEqjxFnn6W9ReholPuXmEaRP+c13FgEgtEcB6PfFsOoaEIFdjgqsI60jWnt57kflZvUshNai0IhdDRKF30w5+5sqJEonYbCCBmLEZs4QVFUqYQK/HBAGHmLjQSAkcmTMnlScx4ORgISKrATQDACFBhGJjQa/f7vKpVKSEIFdgIIRoCCw8iT8kUZjAQkVGAngGAEKACMzJ4UMhYjIZHw2AkgGAEKACOLN4WMYCRZAWBEe1N0LkZCEglqVVCJCJNgMKK9KT+DUdlzqs9HaRBVf/vEIQCM5n2peV9qnmAkYcFh5Ev5YwQjiQoAowVfaoFgJG1BYORNznuT896kP5okGElTIKNRElkgRjCSqAAwsvqSyMSMESe2kat6Y1TCDewCwGjRn7T6k1a/WDFCsWfZOA+u6ogRnxvYBYMRsmAsicLW6lAboO4qHnBY90mt4eIekSAw8iWQBWOZVfoliVGNM0N43cAuCIy8m1bvptW7FYhm0h3rUBug7hKCUQ0zQ/jdwC6Iv9TccasnbvXEA5FM8rVUMaplZgivG9gF8RbbGZl3RuedUX9kc9ehDwXWDAHxvo56JYwAM0N43cAuAIws9hAyXzj+m/f+KLDYA4j3ddQrYQSZGcLnBnYBYGRa9plW/KYVvy8UQ5WvxYURZ1IqSMsoN6kBZobwuYFdABjNLXmQeYPRt/coBNYMAfEeu8hbbCQAjIw2l9HmNtrc3mBkx66DAos9gHiPXQQjJIjRaNFlXHTN2VzeYGTHu3KCkQQFgJF5yWNa8piWPL5gdMdOucCaISDeYxfBCAkiiHbVj8wfjr29+5DAYg8g3mMXwQgJ4hf+taDVEVxwBAPr8Xf2tRGMJCgAjJbcEZt73eZaD0Y2fiX7PckMkaaqxWjFG1vxxVa8sVB06135nwUGivhEoUAggNuFhhAARvbAJrJwbAuVLCYYSU0AGK2FttZCW2vBrfV4Ym/bxwQjCQoAI2c46QwnnOFEZCO578inBCMJCgAjVziJLLKRWdCYYCQ1AWDkXk8ii2wk0TJZBCOpCRojwSH9uBteibTKJuZ3dZk655O6YlTaDeyCwCiSRBbZFCVGalmTUos2tcqm3B6sI0Z8bmAXAEaeSBJZlMFISEg/7oa/mrTKJrYzfT5sk1q+G9gFglEKWXQzJXaM1LKSo5FaRjUplTI06WQ7uchMpJZRTUp1Zr9M7fOpC85id+XsLOkGdgFg5I2mkLEYCQnpx93wV5Falt+puRgxpGiVTZkDtUoZc4JaxnyslrF0IFqyZ7EH8E1ghW5gF8izUQpZRNQYZTHgKH80yvY9z3jEPbLYdnYkKjIgFXUDuwAwcoRSjlDKEU6txwVhhDJDcDe8MpXqvDIYaZVNLATFB5sSGJUYbRqTIR8IRtrVFLK1cAYjISH9uBsuXNkpqlBlMOLs0yqbhI5GubSoZfkzZQMKAKN7dPIenbxPJxf9SRFilD/FlPpLreiklp3QmmQyoaORr9iDOa8b2AWA0U+WJDKrL4ORkMwQ3A2HEXmLjVQTjISE9ONuOIwIRkgEo6pEMEKqCUZCMkNwNxxGBCMkCIzMSWRWX+YXfiEh/bgbDiOCERIARnfNCWRWX2a1NYKR1ASAkca0hczqS6CwNZIZIkFVjdHcJjKrN4EW7St7DslTE5kAMOo3biBb8G4Jj8Wu8qoNIoIREgBGfbNxZAueLbT2Y9lzCEYiEwBGvTMxZPMEI6kKAKMeQxTZvHtz7+FPCEYSFMGoKhGMkCAw0keQzbs397aJEiN2ieH8BRfri1FJN7ALAKNufQQZLU6MLO0tmW7LWSE2nU7Xu2ZISTewqyYYibHYQzqdLrJiNZ5JjW/hbDyCwGh6HRnt3kBLQYgVo8Lu49y++tQMKe4GdgFg1DUVRka7Nva8/7EIiz1YOF2eq1yMalwzpLQb2AWAUacuhMziiou3Zkg6jTq95KRWn5ohxd3ALgCM7kwGkVmc8d2K8hi9njVDkPLnkzIYwdcMKe4GdgFgdHvCj8zijKHV1kRV7CH7FxJnsmJUBiPAmiG8bmAXAEa3tD5k5jUxYlT0KZlRuUkNsGYInxvYBYBRx7gXmXktuqv1I1IzRIKCwOilBxmLESn2IDUBYKR+6UZmWovsav2QYCRBQWD0wo3M5Iig6o6kZojUVJPRiBR7kJpAHrE9yNhnI4KR1ATx3kjrQ2Zh/uAnmSESVLUY3dEFkVlc8d3v/010gSJ8qv72iUMAGHVPh5Gxv/CXPYdgJDIBYMQE0UZIEK1kBYARJzNkcx/BSJICwoiE9EtbEJOafr17er1rKkwz8UZlzyEYiUwQf6lNBu5M+G9rfea16O7Wj0SLETd4iBEGjIq5gV0AGN0cc94YWbv+3D63Gn5X/ieRYmRpb2lpKYjzqTtGxd3ALgCMrj6xXX5kvfRgfsbm/9WB34sypB8FEPGG9ON0A7sAMLpw33TuJ+MZjWF6wY0KqIsNoyLBjBlxbl/tM0NKu4FdABid7pv+vkf3n07tpGXtrd2HRBfSXzyIGikXo5pmhvC5gV0AGH3XNfHtnZffdIxOmFZ37JSLDCNOgo+A0ahmmSH8bmAXAEb/6Rw/eftFu3pEKwyj1yozhMMAlQtDOp2uX2ZIGTewCwCjU90TiKQJs/2tXQfFF9LPquLRCDAzhNcN7ALA6IzG8EPf9Pe9Oh3t/MVeBcGoRpkhPG5gFwBGqiH6wqD5/IBJv+j95YEjJDNEggLA6Nrwyo/Dy1efLs2uBH/z3p9ISL8EBYDRrXFPx0uP+mU2pJ9gJDUBYNQ1lb8UBMkMkZpAwtYi7NqPaCVaEtIvNYEEikR6DNHemdiCJ7O8OsFIagLAqHMq1K1f7zFkMSKZIRJUtRh1jHtvTwa6ptcrWl7dF02LwAKBAHYfGsEAMLo6vHpjzHVrwk+7N4QH0WJvOdTtw+5DIxgARucHLZefLN8Yc1mc8T2CE4ywtxzq9mH3oREMAKNTffrzg/TV4VXTWhQt2kcwkpoBYNR+++UPd2dVj2xzjsguxV8JRhI0AIy+uvn8u97pCw8W5uzruwSH9GNvOdTtw+5DIxgARkd/fHKya/LcgMW4ur7zkAgxUss5UT7b27W5t68R3MBuABj96+rjbzonzg6YjavhnYc+FCVGTccspW5fI7iB3eAwum+eZTASEtKPveUg/UcwYu9DtRihSe3sgMUoXozYGDJ1we1jtjUyqkV5LBNGne1vfXsTc7Ksi3NkV2a/rCvt6yo4K6qRMWexO3ncwG4AGB2/Pvxtj+7cIM0+GwkJ6cfe8lcw7bGW0s9GGhlFUXINg06LUp/2RS1KOXN8l4Lpfo2MfbhBAGXPYg9Ap6d9UYtyO8tfSTewGwBG/1aPnurTX3iwYFxd39kqZoxyO7gQI27fZw/THmvJHUW4Rxbb7srPDCmYy/LdwG4AGH1zZ/yHu7OqR4tz9siu1r8KzAzB3vJ6YKRvb2IhKD7YlMCozGAjRoxO9U2fG7BcebpicmTeYgsJ6cfe8grQQZMOejrhm9QKMOIAoT3WInQ0impk2QeptFrOnFXaDewGgNG5AfOlx7ZrI2vmtRha+1FcGHFmmYLOKzepWZTbmYlJrhA6GkWLPpjzuYHdADC6/GTp2shax7jX4orvaftEYGYI9pZD3T7sPjSCAWB0c8zVofXd0YVoJohWSEg/9pZD3T7sPjSCAWB0ZzLQNRXuMUTnmehHgpHUDACjrun1HkO0dza+4E3sO/KZwMwQ7C2Hun3YfWgEA8CoW7/eY4j2zcYXvIn9Rz4TGNKPveVQtw+7D41gABh1ToW69ZHemRg7GhGMpGYQIf1a3x1dsFsfmSeZIRJWtRj9+Nxx86Xn9mSQdm/uFZwZUuVVG0TV3z5xCACjiw+tV5/Zb770WFwb6L1R2XMIRiITAEY//GS8+NDKvMUmpWekKACMvu3RnR0wX366UlFmSJVXbRARjJAAMGq/M/7DXaPq8dKcI7KLYCRJAWB04taL7zUzFytMMKryqg0ighESwUiYsosM56zFWG+MSriBXQAYfS36Sa30mp11xagBlw5lBIDRya7JM/dMl58us4/Y4ir2kLNWep7qiBGfG9gFgNF3ffpzg/SVZ3bTWkyEGFnaW7K1QPDVDOF1A7sAMDpzb071aPHaiNPsjKP3RmIq9sCtBcIpBpJRLka1rBnC6wZ2QbzFfrTIvMXORD+KDaPcUYLbgfmjUc1qhvC7gV1Av6m9cN+eDLC/qYmoZkj+M0llGIHVDCnjBnYBYNQx7r09GeQu2ieuYg+cKaTMpFbTmiF8bmAXAEadU2Em3mgLxRuJC6M0z+uacpMabM2QRn1rBLOg8Uysdzbeb9ywehP7P/iM1AyRoAAw6jNu9Bs3NXNbVl9y/wd/J8UeJCgIjGbjBCOJC2JSM0R7Z+P9xk2rL4EwIjVDpCa4R+wKM0NAvMcughESAEbqce/tSTakn2AkRQFgdPW5nQ3pR++NSGaIBFUtRhcfWa8+d6jHfZZKij1UedUGUfW3TxwCwOj0fZPqse16hcUeqrxqg4hghASA0Xf9hvND81efO0hmiGQFgNG3PVNnByxXhldNzDJZZc8hGIlMABid7NGdGTBfRhiJMoi2tAhGSNCjEcFIkgLA6JRm5vyDBebZSGwYcX5rLxLAWjeM+N3ALgCMztw3X3q8dH3UZXbG94j7ETsbepYRntGowA3sAsBI9cj243OHetxrcW3sbftEbCH9HBV2HxaMGo8iqCDa3IVpRIpRkchVzu2rcWYIrxvYBYARWoaWu0yWqEL6WRXrvlyMapkZwusGdgFg1DUd7maWEEW/8IsRo+IzSf5oVLvMEF43sAsAo96ZWN/sRv9cNt5IVJkhSCW6rwxGgJkhvG5gFwBGfcaN/rmc6EfRhfSX7NkyGEFmhvC5gV0AGPUbNzRzmxqTeDEqndBTblIDzQxpvLwiViCTWhwNSOykRjJDpCaQR+zsKv3oEZuE9EtNABjdKlgXm2AkNQFgdH3Eoc59/UgyQ6QmAIwuPVr88bldnVlR5GMS0i9BAWB09j5a3yhb3ZFgJDVBBIr0TZ8btFwZzq6LTTJDJKhqMfqmc/z0T0bV48pWovWJQoFAALcLDSEAjE7cGjvVb7jwcGHOvr6r9SOCkQQFgNG/1SPf9U1feLBgJBhJVVAY6S88IKORdAUzqX2vMVx8aJ2zR3a1kmcjKQoAo5NdE6fvzV3KrNJPMJKiADD6HmXNPlutqBAW7oZXIq2yifm9XabO+aSuGJV2A7sAMDo/aLn8ZOn6WGWZIbgbLlxqGdttWmUT1aTUZj+rI0Z8bmAXAEZXni5fH3V2aH0VZYbgbrhwqWXZPuNu+3z1xqikG9gFgNHNF+5bWn/nVHieWd9IXBix//mLDAIcjNQyqkmplKFJJ3tYkZlILaOalOrMfpkaDTS5Z7G7ODt53MAuAIzu6IIo5GiBWW1NSEg/7oZXJJaF/GeSXIyYz7MdrVXKOCNI5mO1jKUD0ZI9iz0gBx7mqiXdwC4AjNDa6n2zGxVlhuBuuHBxOpXv2Siv73nGo1LTE7OdHYm4AxKfG9gFgFHvTAytaWz1VpAZgrvhgpX7HJL3VFIGI62yiZ2Vig82JTAqhITXDewCwKjHEO2difVxVukXEtKPu+GClfNfn/Pnks/nK4tR3ggiEKPcq6hlMnU5N7ALAKPOqRBTMyQhvGYI7oZXIM6sxPPeqOiklj21SSYTOhr5ir8i4nEDuwAwUr/03J4IZCoYHflUYGYI7obDiLzFRgJ5b7RyY8x1ayJbT01ISD/uhsOIYIQEgNHZAfOlJ0s3xtxsLDbBSGoCwOg/vVPnBi1Xn9nZWGwhmSG4Gw4jghESRLxRx9gpzYzqsW3OkflpVkhIP+6Gw4hghASA0dFrw9/2TJ1/sMDGGxGMpCYAjJRXHrV3TpwdpI329Z2tH5HMEGkKDqPVDEZlzyF5aiITAEb/uvb0ZLfu3NA8OxqVPYdgJDIBYPRVx+h3/YYLDxcrCqKt8qoNIoIREgBGJ7snz9w3XXq6PFdJEG2VV20QEYyQADA6fW/uIsrhd5IKRhIVAEaqzIoiXosrvqeNlOWTogAwuj7q7Bj33tGF2CKhZc95zTDirOBYenl1nG5gFwBGtyf8KFaE/YW/7DmvFUacdWELVoKtI0Z8bmAXAEaFQbSiKvaQ02c566an64kRrxvYBYBR32y8P7MSbWZBY1FjlLO8Oef21bhmCK8b2AWAEWKIuy62qIo9cNZF59YFQcrFqJY1Q3jdwC6Q0Wij35izSr+oMEpzhoaW9nZFqUmt9jVDSruBXQAY9aDMEM6kJsKaIRlpFCXL8tWlZkgJN7ALAKPOqXC3PsJdXl1sxR4YFfZvGYyga4aUcgO7ADDqGOcury4+jPhe15Sb1ABrhjTwWyMQjK6NrKnHvXd0ITaHn9QMkZoAMLr8BK0o4qfdG3sPf0KKPUhQABhdeLBwZXj1ZmaVfoKRFAWA0dmCAuqkZojUBIDRD3eNFx+iQJE4ChQhxR6kJgiMNLMXHlh/fI5qhhCMpCio0ciKRiM0qZHMEAmqWozO3DOpHtmujzotzkzydZXfSCRBUecH6StPV26+8AiPfiQiyhMKonV0jPvY90a4XSJ6/fT/bQ1Xn1yZ2xsAAAAASUVORK5CYII=" alt="" />,可以看到成功创建表,并且插入了十个数据。

2.我们操作一下Cursor里面的一些方法。

  execute()方法:执行SQL,将一个结果从数据库获取到客户端

  fetch*()方法:移动rownumber,返回数据。

例如我们有如下代码:

sql='select * from user'
cursor.execute(sql) print(cursor.rowcount) rs=cursor.fetchone()
print(rs) rs=cursor.fetchmany(3)
print(rs)
rs=cursor.fetchall()
print(rs)

结果如下:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAsUAAAB3CAIAAABkNKoYAAAMWklEQVR4nO3dW5KjOhKA4bOdlrcwERMTUWyjtAytovV4NtBsosXm5oGLuQgMypSwXP8X/VCF3SbJMpBIAv3zn//+b/7vAQAAcNE/1BMAAECIegIAAEhRTwAAACnqCQAAIEU9AQAApKgnAACAFPUEAACQop4AAABS1BMAAECKegIAAEhRTwAAACmdesJ6b41ZLTTWd13XdZ2365cAAMAnEdUTxlgfQtd1IazrCdO4NnhrjDGNa1vXUFIAAPCxFNonjLF+U09YH1rXDG+wPnirECwAAHhLWeqJVZvE+BtNFAAAfKZM9cRiSbQBAwAAfAzqCQAAIEU9AQAApBg/AQAApLi/AwAASGnVE+snTMyePxF5FQAAfBKd51n1Vk+14vmYAAD8EMzfAQAApKgnAACAFPUEAACQop4AAABS1BMAAECKegIAAEhRTwAAACnqCQAAIEU9AQAApKgnAACAFPUEAACQop4AAABSmvWEMY3zrjFqs3/1842FVvMzJfbiUd9wAADqolZPGNO4Vn9e8sa1q2lL77UXz7j17xInAAAlqdUTjWtX85KrzFduGtd6m/zfJaLxH8RjrG9dUyo6AADeiE49sT3Lmsa1wVtjktstjGlce1tnxzb+M/FYr99CAwDA+9OpJ7aNE9aH6WLdWB9uamNIlhY/TRQAgJ9JoZ7YtkCsllQ3tiA5/uq2FAAAFSr1hPXLIYqrJds3SAw3WQRvjfUh9EMcnm0JjWvHhf3Qh+n93oeu60LrnA/zURGNa/s3T30ZyfHrbikAALWor554LEoK81iMdYivt3//VFuMP3g7NkQM7x9HgVBPAABwSab+jgL1xNHnWz80UYRlnbH64fvXl2ufjRnb91+Nn/4OAMDPpDMesz/fTr/mHj9xcL7vb/Lsg9krI2b1xHe0UEgfP1HhyFMAAORy3S+a9f6Ow36No+Wx9on4HZ5p8XO/KADgZ1J7ntXqVLoc0zC8pPX87JfjJIblY1vFQWGxGD8xe2x2NP4XUd336C0AAO6l+7zt5ajM2PMl5c/PNuNtHbOxDrNfx5V2XRe8c234++f3v883zO8NGcJ43t+xrBsuPd/T9I+eYOQEAOBH0p0P7PU59SMv4re1FAAAP0q5+crvfX42AADIp1w9AQAAPhX1BAAAkKKeAAAAUtQTAABAinoCAABIUU8AAAAp6gkAACBFPQEAAKSoJwAAgBT1BAAAkNKdv+M5P+eZ5YIV6cxTqmUvHvUNz/Sx5JN8nlkOAAd05xeNzOu9t1xIPk+prr14xq1XPFeRT/J52dV8qucZwMdTqyf6GTZPLrdeeqi9d57SbfwH8fSzrmqteptP68M433p6Vm/MZzT+u/LZX8rXm8+9+BPyqZtnAB9Pp57YO1qtlk8HO9GR+r55SqPxn4mnn8ddIYBNnq0P0+nQWJ+Qlnvnfd3G//Xr6658ruadry6f0fgl+dTKM4CfQKeeuNQ4YUx/Eqn1OJUQv9al3vZi2s0+trrEJsefL59+USnWl8+0+GmiACCnUE9cHTlR3WF6JaWe0OiN3uYzcv6oqs87Of5M+VyZt53U6Hz8jKIAIKdST8TPr1eXCw2D2IO3sy7k6eqqb4TuRt6a6f3eh67rQuucD/1L/X9pXDv0Qy8PqSn1hMYmv/wQ3avJYvlMiD9rPqdeLd1iouz381r8hXdVAB/pc+qJx+KQbR7DMdpbY/bagfv3T8fu8Qdvx0vX4f2RUSBvV08YY732AMAy+UyIv1A+g3L7ROl8no6fegKA3Ef1d7zsP17dSrA8cD9/+P715drnxeJmAOa79HfMXsrX5JM9nwnxl+nvyHBvaqF8Xo2f/g4AcjrjMfd6aqPLb6knjPVTC/DeYXp2vP5+deV6eTxm0Gg8IJ/Df8mZT0lgx8rkMyH+vXxq5RnAT1D0ftFhYfHz38vlseu/3SvXhPjz3S+6ukWwca7A+U8xn2nxZ7tfdLldpdontPKZHD/3iwKQU3ue1flDkjG2v69/uUThlv2X/dDD8vFa8ODAveifXj5+eBv/i6giRUD69q7yOb/CnuKsKJ/R+F9ElS2f8wDMOLpB+PmbODPmcy/+F1FduRgAgD26z9uODul6Lp9Gnm87feXPJ54+fNb3PPvV+ud6vXNt+Pvn97/PN9jZ2PshjOf4+bF6OIj/KKrYeSh5ew/y2XXd1DpdYz7n8R9FlS2fj8fzPouwLBmryOdB/EdRRUdO7CwHgD2684GlH5s+8mJor8Z6yLaXfEZeIp/XnbkGAICT7p+v/N7nPZeXe3vJZ12fDwCf4f56AgAA1I56AgAASFFPAAAAKeoJAAAgRT0BAACkqCcAAIAU9QQAAJCingAAAFLUEwAAQIp6AgAASOnO3xGfH/LkvJFXVqQz36OWvXjUNzzTx5JP8qm6OvKpuzryqbs68vk4szyB7vyikSkN95YLyed71LUXz7j1it9d8kk+LyOfusinLvKp62o+tfKsVk/0MxKeXy7cgNvne1zFfxBPP3ul1nrn+YzM952a0lvyeRD/LfkcPn+c8luylhu/n9H478qn9cu/b+ox96587sV/Vz772emEO/vjvnzuxX/n/t5fzcuqgTv391j8CflUybNOPbEX/e5ywZyN7zDf4zyGM/H082ErrHeZT2OsFxfFN+YzGv+N+ZyHlJzbe7+f2/hv/X4u5j03pmmur+XW72ck/rvz+fzktEuyu/O5jv/r19ed+3vj2qr39038ku+nPM869cTVxonGeef8+3Q4XXU1fq0Se3OxolBP3Ch9H86Tz+iSuqTFn+/76WYf23fSytdSTHL8Zfb36nb/5Piz5XNd33i9hpACkuPP10ShUE9cHTkxDP6wtdYTCfGr9E5t81ndAWUlvZ7Ilc8sfavFJMefKZ/LVxfn5upcir/M99P0h/96dv/k+MscPyvMZ2L8+UZRqNQT8bPC3nLrXNM3zygPtJm6kZ698lO1NXUq97w10/u9D31nnvOhf6n/L41ro/2UCfErdUysP2RYYv20UZLPj60ubz6T48+bz3HTas3n9fgz5XOucU638C22vyfEny+fZhxo93g8rNe8liiTz7T4s+3vm/4XvcuzIvt7YvxXT9nnla4njPV9gnK0T6xGpuz1jS37mMP0txx/8Hb8Qw3vn/W6pcWf8/wXwhhbH5FkFbE1Zsxncvx58zn+WfuXJauIrTF/Pq/Hn7ueMKaxVr9xosD+nhZ/1nw2znmvX+8+SuUzIf58+ew3cXi1bRXriUeRfKbF/+b1xNn+DmOsn52Y89QTR/1z02jtsB7Dsvjh+9fXNAh5/f6k+Au0J2utZbnGvPlMjr/S9s8S30/V9s+LW7f7/TTW5ehFKvb9vBp/vnyasYfbjGcnySo2a8yez7T4s34/jfVd14XQukahalmuscT3MyH+t+7veOxfCa2WTy02c4qX1Ad/vz7pQ9PCzp9t9vf73rkySIy//3j5Bh5fcapU8QcfqJ7P5Pgz5XMznr+yfCbHn/v7qdsyPyn2/bwaf6HvZ8Hrhyzfz/PXD2WOn7XlMzn+vXzK83zP/aKPsu0TL5fH6sEX49ouxV/mftFi+4NWPpPjz3f/2PyIU10+k+PPl8/H5iyiqMz+nhB/of29VL2ba38/HX/W7+dk3qegouT56FL8736/6EEoe8vn52NjrMrzSg//TrPj7FgbHvwhF/1VJvI40vP1xM5DDhK3d55PYxrn7PPnsf2wlnzuxf8iqmz57Ldlakuc7i6rJZ978b+IKmc+t1st/PyDT860v189bWfN57pejI2bSVYgn9H4X0SVdX+fIm9cG4aXKspnNP4XUV2/+D9P93nbr9u4xiWLoa0PjeeV9n+kZV/U7NfxJoKu64J3rg1///z+9/kGOxuL+zwcj51V27u21vEfRRUfLp64ves2w3G7VkHWks+9+I+iypnPeZzzYr+WfO7FfxRV5nxGz8cV5fNSPVEgn3bqdJ+tqKJ8RuM/iqpUPr2t7/i5F/9RVNGREzvLr9KdDyw9VpXi6N0ctJRKtpd8Rl4in9eRT13kUxf51HX+mj/Z/fOVv8Pzs0vKvb3ks67PfzfkUxf51EU+39n99QQAAKgd9QQAAJCingAAAFLUEwAAQIp6AgAASFFPAAAAKeoJAAAgRT0BAACkqCcAAIAU9QQAAJCingAAAFLUEwAAQIp6AgAASFFPAAAAKeoJAAAgRT0BAACkqCcAAIAU9QQAAJCingAAAFL/BwDtiN4YwRSoAAAAAElFTkSuQmCC" alt="" />

我们可以看出执行查询全部数据后,rowcount为10

执行fetchone()方法后返回一个数据,执行fetchmany(3)后返回3条数据,执行fetchall()后返回剩下的所有数据。

再有如下代码:

res=cursor.fetchall()
for row in res:
print('userid=%s,userna=%s' %row)

此时的执行结果为:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAM8AAACsCAIAAABTg2/8AAAImElEQVR4nO2d3Z2tKBDEN52LaVzTGMIwiuVxE9iTxGpy++AXIiJ9lKabW/+nmTMO0jUloEP9/KsDgIu/ancA/EHAbYAPuA3wAbcBPuA2wAfcBviQ6DZjrTWmdi/A+7C6zZh++IzjZ+gvzGSMdeM4ju7Wbca6aZqmaXIWviRQVzdZbusWw924zfTDZ3TWGGP64fMZehgui+q6iZtJc9xm3fgZ+uV460ZnWbqmnuq6CXXbz68fN47TkTF2Xa7f6Rje5qVCtK7yp66v2+42f8EUrJ/mCXBXx+tlP3z8Dzc159+1brz6UdCPeZKdpml0Lj22BYNfeiwMC3m7rt+/ftYGdxvt48ex/cRSyetYVjvb8W7tyeBG/xRB/6m6FWJ321VvjJkH4Egv58tj+bwfPuvIPB+zuCajSNMPn/UUxrr0tU5VrXRdMQdvn1P7SWjHbEodvnB2HcSC/mtx2+KF4De30eg8I2z1n893LjIc4b91D/X4t+q67c88EN7OmNR2jrbbv/j59Tvafx1um7/dR+bjhR7tLulH0fO+uG4rXVei/flxw+zOjLoI7STd9hPtv6x1W6b310kvdRdNdBttbOuI91al60rOdG/N+KnPY2NbvP+C7kmDacK6cb/mvItgW090wfrG9IOLL0h94n8wr/LtvIlOH9c064JvXvecrtfSdd2uq7YaM24UCO0kbHfZ/5hunByegJj1QfM0Tc4ui9bzOsCXbJ+JlpHB+jPFGMoX/5F/6u28N4aLPRPvh0/8hrdYXcG9avitd97RDYkn29R2/vv373/2A/z72f2C8fuf1o0Ncc/bnuDfPwKBNOK2nP+Jgeo04jagArgN8AG3AT7gNsAH3Ab4gNsAH3Ab4ANuA3zAbYAPuA3wAbcBPiS6DenlVpGVJw325qexjnujcxtU1E2W27rstMh5hxxII0E3cTNpZjqjSoijAerqJtRtibSIf5g6twV7gKPbmMt3QIDbtKSX1+Pzh0Ckly8TbszoSy8n2sk58vW6Yg7+OnNVML1M1a0E+tLLpMMY6rp1lZWRXqbqVgJ96eXbU6TP+HpdifaNpPQyVbcSqEwv5x/GUNdtujizw9R2kmPbZVZUlNv2acIKTi+v7RyTkvO6B+nl6/RyVDdO9KWXE+0gvZxIL6f150Hc87YnIL0snEbchvSyChpxG1AB3Ab4gNsAH3Ab4ANuA3zAbYAPuA3wAbcBPuA2wAfcBviA2wAfEt2G9HKryMqTBnvzE2RuvwYBdXWT5bYub3PpfMRy/LI7EIa7p7pu4mbSnB3Vgxddqbv1WRESdBPqNlpaRM/YFmyg5ZzUJOimNb28/6J155Se16wtWpe69HKmboXQml7eW7vbGl66rpiDv85cFU8v5+tWAq3p5fwjS9d166rM20BqO8mE32V6OV+3EiC9jPQyHyrTy2aeHnbHDE/GjId1JWc66kxKaCc5tl31n6BbCVSmlw/PjdbzLusepJev08tR3RI6v4769LI/CSK9nJ9e9nVjQ9zzticgvSycRtyG9LIKGnEbUAHcBviA2wAfcBvgA24DfMBtgA+4DfABtwE+4DbAB9wG+IDbAB8S3Yb0cqvIypMGe/Ov29k3zyC9nE913WS5rcva43rYgLpmWWC4GyToJm4m/XpHNVcHtSJBN6Fuu03BbPi7n+Vz3kBba1KropvW9PLWWlqysJC361KXXs7UrRD608sXwVW2umIO/jpzxZhevtOtBIrTy8vxy29/kWX6Q9PLmbqVQGt6OedcPHWRUsfV08v5upVAX3o57Oe3Y9tbdVFTx9R+3n4eG9si/afqVgJ96eXjiZbebP1Hejlct+3p7rhunOhLL3fePdo4Hi5ipJcT6eWEbmyIe972BKSXhdOI25BeVkEjbgMqgNsAH3Ab4ANuA3zAbYAPuA3wAbcBPuA2wAfcBviA2wAfcBvgQ6LbkF5uFVl50mBv/n2DNbYENkAt3WS5raPsYMa+j++oqJu4mTTfbf3ghgFBeTIVdRPqtvt3+M3bnq0yt1VPL9fVTWt62Q7r/u37eRnpZS8mmKdbIVSml411S/IlQzWkl/f+U3Qrgb70sjH7W6qfue3PSi9TdSuBvvTy1hOfs2nY6tKSXqbqVgJ96eVDaw/Gtrfq0pJePpxLxthWMOXrneX0B7OE9HL4ix//pEgvn9ZtsXcs13dbVzLl251u/p+kl7vT6mruEtLL6fTylW5siHve9gSkl4XTiNvwXywVNOI2oAK4DfABtwE+4DbAB9wG+IDbAB9wG+ADbgN8wG2AD7gN8AG3AT4kug3p5VaRlSfNSS+fY0v4Z3wOEnST5baOvscVZCJBN3EzKdxWCAm6CXVbflpEFxXTyxJ005deXlTbd5bfRmCQXvZCFXm6FUJfenkR2kvNpGNqpeuipo5v6spuZ0vHHL84pWDW/lN1K4G+9HLYIN69HEn44d3LFz+KnhfvXk6380V6mapbCfSll8N+4t3LkbEN717ez3L6g1lCetmYfhjs/jXevXz64vLdyzHdOFGZXt4OxruXae9evtCNDXHP256A9LJwGnEb0ssqaMRtQAVwG+ADbgN8wG2AD7gN8AG3AT7gNsAH3Ab4gNsAH3Ab4ANuA3xIdBvSy60iK08a7M1PNbUGQ/g316umrm6y3Nbl7WC+2tcK0lTXTdxMmiNEP3z49502QHXdhLotmRa5zA3IJ9gDHN3GXOzU9XVTm15eu5S+WMNC3q5LX3o5T7dCqE0vryLOzSUqLF0XNXV8U1d2O8fQ8qFLqfRytm4l0JdejvQT6eUw4Xfx7mWKbiXQl14289X6OKf5Vl2J9o2k9DJVtxLoSy93x1kA6eXY2BbvP0m3EuhLL3fHtd12Vx+sS9jqoqaOL4sitpOw3WX/Y7pxojK97J/XX3ghvZxOL1/pxoa4521PQHpZOI24DellFTTiNqCC/wFxUGBzFsG0mQAAAABJRU5ErkJggg==" alt="" />

3.上面介绍的便是数据库中常说的Select操作,下面我们介绍数据的更新,即:insert、update、delete操作。值得注意的是在这部分操作时需要注意的是是否数据发生异常,如果数据没有发生异常,我们便可以直接使用commit()进行提交(注:如没有使用commit,则数据库不会发生任何变化)。但是如果出现了异常,那么久需要使用rollback()进行回滚。

3.1先来看一个没有异常,正常提交的例子:

sql_insert='insert into user(userid,username) values(10,"name10")'
sql_update='update user set username="name91" where userid=9'
sql_delete='delete from user where userid=3' cursor.execute(sql_insert)
print(cursor.rowcount)
cursor.execute(sql_update)
print(cursor.rowcount)
cursor.execute(sql_delete)
print(cursor.rowcount)
conn.commit()

上面的操作即是:添加一条(10,’name10‘)的数据、将userid=9的username修改为’name91‘,删除userid=3的数据,执行上面代码后我们来用Navicat查看一下数据:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMgAAAD/CAIAAAA7RQ7cAAAgAElEQVR4nO2d+XcTV5r367+Y6e4QMMbYmJ1gH5o/YN7zNt3T00tCAiTqNsExZKZnprsn6fR7zuQ9k466gTRxSCfsIHYQm21sEeMFGxtvWN5kW7ZUlrxK1r6W9pLmh1tVKsla6SuVXHW/5/mhKFVJj+79cO916XnugxVv2uH2kX0zzj7c0Ys7enHHks331uHf2pGQ/g5h6zdtd/tCvbi9V23rUdteqGxLVu+bVf/BtWNIq1vY+rLtbl+oZ9ryYsr8QmnuUpoWzcQvfvVvXDuGtLqFrS/b5vIGu5Wm55OGzonlZ+PL8ybXz947xrVjSKtbWFHpNpc3+HxiuVOheza21D66OG9w/vRgTSQSaUFCelVhRaVbXd5Ah0L3bGyxfWShdXhudtnxkwPVESSkv0PYuo1bXJ5Ax+hi+8h869Dc00Ht7LL9x2+/L0FC+jtEgdU+stA2PNci1z6Va2eXbQCstFTiOL7gjfDAcBzPw3/iwhfEdsDWbdzi9ASejc4zI5aWHrEy8YNzJhBYEAUZLJcn8Gx0oW1koXVorkU+O7ds/8k7RxBYAhR8sNrHFttGF9tGFtqG5+cMjn8++AECS4CCDZY30DGu71DogM2bXP/y7jEElgAFHaxgl9LYrTR2K41dStOihfjFr/6NM7DGv9yF/fDT8RXn66qxyi+7EFi5FEywikq3un2hXpW1V20DprN63zz8nwgsAQoqWGXb3L5Q/4xzYMY5oHENaNx6u//t6t8X3FSIwMq9YIK1ftN2wk/K5zyDcx75vEc+7zU4ggdqPpJIJF5fIIWFQiSO422WSJ7sbjW2+8truXlzHMdTf1khGOhQaGAVl+8g/OHhRf/won9kyT+8FDC6QgeP/iE7sAa/LMeq/7zi+NqffohRol+1yPbRp8r/pG6zRNos6qO7sX13ZfswDNv95TX2W7EuxsCruQZr4otK7P26FcfjJ/fQTtCv+hqq6FOVJye9voDXN3miEquqb6jCMKzyi3FfQxW258TJ92OvAW9Lqaqeeas9J+qp81X1AW/9irsSfFxhg7Vh805PIDyqC47oQqO60Ig+ZHSTB499IpFIPF5/CksPVgwiDCg/PDoYYfFEHWAJuJTtw8AFkTZL5M+i3IJFfa/xk5XY4bq4Y/ZJyuqrsD3Hx8HxxPFKrKqOOsCiV9ZXYRhWVU+/Fbh+4njVSQW4oO4wfXF9FYZhlScV1En2XcwFKz8OskEHa5cnEB7Tk2N6clRPjupJo5s8lDFYreZwqzncOnCqHDsijj9u2odhGLbn6ECYuuzOESxW5Z+pWs2qo7uxfXfC8W915wi2+9RVM+te9j+hWhqwQMdHu5bufpYqT0ys6PI4GqK3K04w499KbhIdJ/64VQBWZGyZHNOTY/rQmD5kdJEHj/0hLVgkmRYsYKqju2m8EsNR+GBFhwqM6elKeuCJvSANWOMnKxksEg9IScBK8HGQDXQoXLDCo7rQqC44oguOLAWMruCBox9LJBLC40thIZLEcbzFFG4xhVv6T5Vje2r6wy2msOSzPRh25HNTuKX/VM2dcIsp3GJS1ezGfnQn3GJq+hEGDsItpvDn7x35POZV5q3AefbFqprdGLb7lMREXwbVcBynvpfiZCVWeVzhIzy+seOVGHb4kcdHKE4erwPfGqDjIzz1VRg48BEe36Oqw49iXgVWX0W/FeGZOF5ZeVzhI+oOY5Unxzyx7x9zZcLjhB8H2UKwwdrpCYRHl/wji/7hBd/QgnfZEQCPG7IAi+IJwzCs/L0jNBnhz9+jx+73mljcUPpRPHZxYIVbolPnnprPjuQDLKq/MQzDKqsOV1Id73vErJyr6lkI0ufisUsOlmeCfnv2+6cFK+HHFTZYxeU7CD85NO+RzxGDs+6XWpfe5nvr8H9KJBI34U1hYEp+aiV5YDiOp/6yQjDIa6z1ZdsJX+ilxtGP2/tUlh6VZYn+Scfl9qQwCiwdyQPDcTz1lxWCQQZrXelWtzfYM2XsVhq6JvSdCt2CyfnTQzUZgtVsCPHAEFjwwVpbstnlCXSOLXaMzrcPzbbKNXPLth+/fVgikThdRAoLhUI4jjcvhXhgOI6n/rJCMNCh0MBaU7zJSfjb5DMtL/HmAdV3fVNaneX//vJXEonE4XSnsGAohOP4E32QB4bjeOovKwQLwgXrtaJSh9vX3D/9Xa9S1jPZ2D0+s2j6Pz9/F4ElNIMO1kaH2/ukZ1L2YqKpW/H4+djMoumffnZIIpHgSMJTTsCSvRhv7B7X0CNW2psh+sGt7HY71y4UhCC2A/ZaUamD8D3tn27um27um3rSG11jpb0ZgcUzwQTr9eJNTo+/fUjTJte0yTWtg5q5bNK/YPnBrRBYQDDBAo8busaXno8vPVcsPVcsLZpc/3LoKAJLgIIJVlHpVrcv2Kcy902b+6bNvSqzzur5xa9/g8ASoGCCtb5sO+EPDWmdcq1zUOsc1DqX7b797/8WgSVAwQSruHyHJ0COLnpHgC14jc7gOx/8Fx/BklVT8QF7a9UxL+QXrKRucC6YYIGwmXF9QAFMFzDRMe9pb15VYKlr99I9KavGsGoZ67U8gpXKDc4FF6xd3mB40hCaNIQmDKEJQ8hEhybnM/Y+51LX7o32Ysw/IvkEK6UbnAsmWCWbd3mDkUkjOWkkJw3khIE0E+ShD//IN7Bk1ew+lFXHzEOsBpVVY3tra6vj5yp17V46zo5+G1k1trdWRp2vlkWnONY7M7Mea5RK7gbnggrWll3eYERpClNmDJuJ8Lv/+scMkylg+ZFrxQ0OKcGi2YnOWuraavrq6Pwlq2Z4AfxE72IuYD6EOpvaDc4Ff8RiwJo08ROs7EasKA0pxiz2lYmOo6MVa9ASzoi1YfMubzAylf2IBTepI+eK6cMUa6xEYKlr9zJzWeIBKQlYK6lJ6QbnyiVYprCFBiufsfe5F6sXV3R5GrBY59S1ezMdsWL/6pNVx8+UBTde5QQsc3iK52CxJ7P4YSLdVBi9c291daYjViThkj+VG5wLPljTpvAUbQxY+Uzq4FboyTtQDsAyh4FNmaNg5TP2nlshsIByCNa0CYElXMEHS8WAZQ5bPRRY+Uzq4FYILCDIYPmCEZUlrDJTxoCVz9h7boXAAoIPltoSZgyBJVjBBwu3hIGxwcokqSNXVcmQOFKuwMItYRsNVtqb0YjFM8EHa8YapgyBJWDBBisU0TBgWcM2LwJLoIIJVgkNFjAElpAFFawtb/iCEY2V1FjIGQs5YyFtHhKBJUxBBssfimitpIY2uxeBJVDlBCzG+AwWKz6UrXyDlcQNzgUfrFkbOWsjtTZSa+MrWCBaLxr1wlYewUrlBueCC9YuBixgDi8fkykoJQ7azPtUWHCxo0CQHzf4Q+FZa4gxh5eP6V+U0oKV4yydlG5wLrhg7fQHw7OWwKwloLUEtJag3UMlrPIqmYJSJmDlMEsntRucCyZYxeU7/MGw1uTTmHxas09r9tkJKsVeqGDlMksnpRucCyZY68u2+4LkjIGYMRAzBs+MwWMjAmBTEF5l6VB6JbAgZumkdINzwQRr3catvkBIrXMyZnX5fvnr3/AwmSISeUWwYGbppHKDc8EEa23JZm8gNL1om16wTi/aphdtVqcXVLHnF1isqWxFiky6qRBilk4qNzgXTLBeL97k9Qen5s2MWRyen7xTjbJ0BCiYYK1ZX+b1B5VzRuWcUTlnUs6ZLA5i3/7DKJlCgIIP1tSsaWrWqJw1Ts0ZLQ5i35tVCCwBCipYxWU+f1C1YFbNm6fnzdPzZqvDs++tKpSlI0BBXWNtKPcFQjNLNpw2m8v7k7ffR8kUAhTkvwr9gZB22alddmj1Do3eYXf7fnqwBoElQMEEq6h0qz9IzpuIeRMxZ3LPGd0Owv9z0b+iLB1hChpY68u2B0LhRYt30eJdtHoXLV6nJ/hm1X9kGDZj5YXsdjvXLhSEYIJVXL4jEArr7AHGXN4gKDaOwBKaYIK1YfPOQCi87AxS5gi6faEDNR8hsAQouGDtCpIRg4s0uEiDK2RwhQg/efBYpvu8c90UcITAAoIJVsnmXUEyYnSRjBF+8tCHnyCwBCioYG15I0hGTG6SMcJPvvthpjHvXDcFHCGwgHIPVsbJFFw3RTaSiyvomAKRNOaVvIKV3A3OBR8sM0GaaCMCvARLKqoQy8GhXFwR26d5BCuVG5wrJ2Ax5qHByiSZguumeDXJxRVM91qtnE2F8W5wrlyAFWbMEwjzHSypKOmIJRVhFWKxCExV0W5PMH9JRViFWEqdF0mtVumKu5hTMSeTusG54INl8YQZY8DKJJmC66Z4FUlF8d0cCxbNjlxcQV0oF4voG6Qi+mWpiOEF8BO9i7kg1bS30g3OlYPFOxFmjOA1WFEwWIofsaI0pBiz2FcmOo6OVgkGrYRucC7IYAVCEb0rrHeG9c6w3hV2+7KopcN1U2SnZN2ZBiy5uILBIvGAlASsJCNSYVJlhQ4WEYjIl8KMLbuyKHnCdVNkrujEtlJpwGKdk4srMh2xYvmRiuLn1wIUZLAcvkgzTgJ7ipOzNpKHYMVPTMn+Kkw4FUanwQqRKNMRy5poyZ/SDc4FH6zv1CRjWiuZeS0drpsCjtCTd6A8gZVJMgXXTQFHCCwgBBZkIbCA8gRWJlk6XDcFHCGwgHIAlopkTGulohsySabguingCIEFBBesXQ5f+IkqxJjWSu3oh8ASmiBHkNq9Ydl0kDGtNQQC/VCWjgAFG6ypAGNaSwhsFZn2ZpRXyDPBB6tJ6WdMYwlmHvMOyw9uhcACgg9W46SPMY05CPYgTXszAotnggvWTruXfDzhZWwGgSVUQQWrfKfdQzaMexibMQUOHP0YgSVAIbAgC4EFBBOsYgCWgmBsxhQ4UMNfsNhbINPiAKxEbnAuyGDZPGS9gmAM5zNY6tq9e/fu5RysxG5wrjyBxb+SJ2Ab5JUbsOcZrGRucK4cgDXmZgw3+cGmIHwDK8G27ZRYDZr7WjrJ3eBccMHaYfOQdaMuxnCj/50PPuJdyZPEJSWAYsHKaS2dVG5wLuhghR6NOBlTG338q6XDLgWRfsTKWS2d1G5wLqhgbdphI0IPhx2MqQ2+t6vTg7WqaunE14NgT2mR/NXSSeMG54IP1oMhG2Nqgxfs6MevkidRZT1iwa+lk9gNzgUTrPWbtluJ0H25lTHVMgILHOeilk4qNzgXdLCC9wYtjKmWPfuP/A7V0hGgcgDWSzNjDFio5InQBBWssu1Wd1D60sTY9DKx/8hvEVgCVA7AGjAxNq0nQIVVVEtHaIINFpF4xEIlT4SmXCzezYwxaywEltAEF6wdViL0QG5lTE0/bkBZOgIUNLCoJ+8jDsbURt/bH/yXRCJZ8EZSG47jb5yL8MAgNuiqFlSwynfYPGT9mIsx3OR/p+YjBJYABResuNBkgglNRmAJTfDBis3SCRxEYAlSOQOLSaaoQWAJUZDBslHJFO76MXfdqAun47EYgDAMQ2AJQbD/KvSEHo04Hg7bHw7ZHsitzHMsNlgJ2VpdYH2qYX1va+QAR2DFxP8VWHgDTLCoB6QDprv9hju9y7d7dFNLrjer/n0lWCvZWnVgzQ1xP2IVYLQMI6hglW2zuAI3uxZuPJ+71qG92j4zMWf9+XsfSiSSNksEGDvgkTnZZkFgvYqEAlZR6Vaz0ydpxS8/nb74nfK8bHxMY/rpwQ+SgcVma9WBxejTpGDlPEuHNRUWUlRyJBKBC9a6jVvMDu8F2cS5xrEzDSN/eyQfVut//Pb7KcBi2FpdYDF2YCjFGivXWTpRRd++YAQVrJLNJrv3bMPIN3VDXz98+dW9vqHppX1vVUkkklZzGFgcVcz5VQrWG+cinZGI5F7yEStnWTqxjV9wsyJ0sDzf1g//7dHg6QcDtdJe+dTSj5KDxZwUEFjQsnTixHOwtpgc3rOPR76tHwJsDal0P95/WCKRtJjCwBiqmDPAVhdYnRrq4FNN6qkwp1k60WwdWTWvHzdQa6wnE+dl42cbx848HhnBDf98oHolWHFUrTqw3milv3QsVenBgpulw0yRBUZVBPZfhdssLv/V9hlJG365VXWpZVoxa/nZu8ckEslTKwkMwzDmmG2rDKzkhp68A8F9jrXd6g7cfrF0q3vxZvfCja75yUXHL3/9G4lE8lRHpjYEFs8EP9Dv/qDl/qD53kuz9GU0maLZEEptCCyeCf6P0HWjzrrR+E1BmpdCqQ2BxTPBBGsDs1XkOMHsQQp2TX6iD6Y2BBbPBH/Eqh9zNyiIhnHP4wmvxkwVEEBgCU2QoxtsROjBsP3RqLNe4W4Yj4KFsnQEKGhgFZVus7gDd/oM9wYtD4btdWPurAoIWD0RHpjdbufch0IwmGCtLdlsdvokHdob3Ut3+o33h2y4yZ/5Pu+ctwWsBuXch0IwmGCtKd5ktBNnmsYvtaqvPV+4029UG3xgD1IEltAMJlg/KNq4bHF9eb//20bFpVb8RvfS9LIHbBWJwBKawQTr+2s36MyOv9zoqH3w8uyTSUnH3JSe2F/9ewSWAA0mWP+4pnjJaP/s8tMv7vZ883jscrtmSucGyRQILKEZVLBeK1o02j4933T85vPTdcMXW9TKJfdb7/MUrDom+K5aGtugheAG5wYTrH94rWjRYP3vc41/udH51aOhCy0q5ZLrrfd/y0Ow6qqxPbXyJA1aCG5wbtDBskXBeqqapMHKpOQJ522RsanFe/aKFUkbtBDc4NzgrrHWLxlt//+8DEyFF1rUSl6Cpait2FMrrqJmoIoT6rgGpY9lImyv+ER1/GWK2gp69hLVsa6so86L6qITHOvNZSIs9hNTusG5wV+8/8+l707e7v66YeRiK86ssTIpecJ5W2RqddVRJhS1FVjMsBELFoZVyWIvU4ur6MmrrppeGMlEGEZNagCp6F3MBcynqMV7ovAlc4NzgwnW917foDM5Pr/a9ldp37eNisvtGuWS+60jfASLtbKRVsWMFvEjVpSGaMfLT0TDjFdwk+i4Lj5Lp+KEOrUbnBvc51glerPz+M3Orx4Onn0yKemYndIR+4/8PsNaOpy3RaamqK14ZbAUtRXsuSxzsFYu0lO6wbnBBOu1otJlK3jyPnaxRX29a3FaTz15z6TkCedtkbGpxXvYcKSYCleAxUJEfmJvpiOWRyaKLsgi0ipwVyo3ODfYvxXaiG8bxy62qK52zt3qXVYte8EepPwCK8JeSjP9nRFYHrV4Dz2dVVVnOmJ5Ei75U7nBuUGObjA5fJfbZq49n7/Vu3xv0KI2+t6p+TjDWjqctwWsBuXch0IwuPFYWy2uwK0e3d1+4z259eGIE6dDkzMpecJ5W8BqUM59KASDCRbYH+u+3Ppw2F436moY98zQEaQILKEZTLBA+tejEWfdmLth3PN40qexhA4e+yTDWjqctwWsBuXch0Iw2GB5QnTAu6dx0qexhA4d+yTDkiectwWsBuXch0Iw6FMhk0xBPJ7wMiMWAktoloNkigGwcnfUK4gZlKUjYEEDa+3GLWan7+rz+Zs9+rsvzQ+GHbgpcCDjLB1YfnAriA26qgUTrDXFm4x2z/nmqSvPtDde6O6+NKuNfvAcK+3NCCyeCSZYrxWVLlvdp+uGzn6nvPJMSz95zzRLB5Yf3AqBBQQTrO+vK9FbnF/c7fm6YeRCi+pa12JWWTqw/OBWCCwgmGB9b+0Gndnx5xudtQ8Hzz5RSjrnp/TEfgSWIAUVrNeLdSaH+NqzU/cHzsgmrmSZ/gXLD26FwAKCD9bnfAeLtRtogh1A8wZWajc4F1ywwFTY8aVwpsIVu/lzM2IlKCrAsaAu3teW6M3OE7e7T9cNn2+evta1wCzeM0mmgOVHPrWyQzkBq/C4grt3w7qNeovr1P3+bxoVF1vx6y9008teXoOVYNt+VoPmvJZOCjc4F9TnWOvLDDbi64aR881Tko7ZW70GlcEHnmNlkkwBy4/8KVGHxoKVl1o6hcgVVLBe31ButHvOP52+0jFLP3mnIkj5CFbi+Sd+xMp5LZ0CnAYjEbhgxfxWOGB6MGxnfivMJEsHlh95UpIOTQMW9Fo6BcpVLqIb+o33Bi0Phh3srSIzSaaA5Ud+lKyv04AFs5ZOKjc4F0ywWPFYLjoeKwjisfgGVvICgemmQqi1dAqwTiEtmGBR23EriIYJ7+NJX5PSr7WEDn34SYZZOrD84FboyTsQTLA2bN5p95KPJ32NSn+TMiCbCmqt5KEP/5hhMgUsP7gVAgsIKljlO+0e8vGEt3HSh8ASuHIzFY57Hk/6mpQBrTUEwMokSweWH9wKgQWUy8V7llk6sPzgVggsIPiPG273G6SDlgfDTDIFAkuIgv+AVNI5d6NHxyRTgOdYKEtHgIIGFvhJ51zz1JUO7Y0evXTQqs6m5AksP7gVxAZd1YIJFvMj9Lmn05LOudug5EkNSqYQomCC9YOi0mWr69SDgW+axi+1zdzo0aMsHcEKKljrNuotrpPS3q8bRi+0qK93L03TG6+lvRmBxTPBB+vE3Z7TDSPnW1TXAFj8Dk1eIQQWENypMNGIhcASpOAu3kuXre4vH778VjZxqV1Dr7H4CFbCWINIJJJnsJK7wblggrWmeJPBRvytcez8U9XVzvnbfUZVNoUwYfmRe7Gio1YEruQRrFRucC6oz7FKNpsc3gstKknH3M0evXTQojb6D9R8zLtkimThU5FIvsFK6gbnggnWuo1bzU5/wm2M+AUWM0AkGChYDZrzLJ0UbnAumGAVlW2zuIN3Bkxgy2T2xmu8S6Zg6Ihf28SClessnaRucC6YYIHNbR+OOOrGXPX05rYguoFfYMVFDCebCnOdpZPKDc4FFazynTYP2TDueTzhbZz0N01F47F4laUTu56JW92kAQtilk5KNzgXTLBiQpOnYiJIeZVMETM8xOTPRPKZpZPSDc4FF6xddm+4cdLfpPTLpgKyaZ6CFbvTS/LnWDnP0knhBueCClb5Tjs1FVKDFjMVoiwdoQn+4j2uMgVYvKNkCqEJJljry7ZZ3UHpgOn+in3eEVhCE+wHpC7/9a6F2716aewDUpSlIzTBBGttSbnJ4b3YMn21Y/Zmj05K7TbzEUqmEKDg/ghdZrAR3zwevfAU7I8VrbCKwBKaoIbNsGtCt6qvd0f3eUdZOgIUNLC+v65Eb3aeuPX8q0eD575TSjqz2zXZygvZ7XauXSgIQQVr7Qad2SG+1n7qfv+3TeOXn2mmdO79R36HwBKgYIL1vdc36Ez2P0la/yrt/aZx7HK7RonAEqqggrWWDZbicjsasYSrXE2FZ2TjV55pp3TE/iNojSVEwQTrB+s26i3OE7e7TtcNnWueukpVpkBgCVEwwQJZOrUPX54BmdAvlrIqK8d1U2QjubiCiiqoEMtjXskrWMnckIowDBNJ8+fISsEEC2wKckY2fqlVfe35/O3+7LJ0uGyG7CQVMd0mFcX1aR7BSuiGXFyBYRUiUQWPwALbGF1p11zvWrjdZ7gnt2aVpcNlM2SlGJjk4ooYsvIHVio35GI+gVVUts3iDtzu1d8dMN2X2x6Numbo/bF4DVZMF7LAkoqwCrFYFD9XRecv5kapCKsQS6nzIik1l8XOcPQp5mQqN/gF1vpNO6xE6L7c9nDEAUKyNPSOfpkkU3DZDFlJLq5gz0FYCrDo1+TiCooCuVhE0xCdyqQihhfAT/Qu5gIGIvpsKjf4BVZx+Q6bh6wbo6oHNE76s8rS4bIZshUzfFSIxaJkU2EcDSnGLPaViY6jo9WKQSuxG3wDC2TpEMyO3FpLFlk6XDbDq0sqiu3BNGCBtXV0nZ0xWPF/faZ2g29ggRHLTWWAsSpTZJJMwWUzvKpW9ngasFjnWFNZOrDYfwBarVJRPDMr3OAXWNR23EP2R6NOupZOKPNaOlw2Q3ZK/hQr/VTIujf6TCAtWAmX/AndYF0Xc3G+BfWvwtKtFlfgVu+y9KX5wZCdqv517A8ZZulw0wCwhZ68A0F9jlWy2eTwXW7XXO9avNNvvD8UrVeYSTIF100BRwgsIJhgrVlfZrAR3zSOXWhRXX0+f6ffxMS8I7CEJrg/QpfoLa4vpL1/ezx6sVV944WOiXnPJEuH66aAIwQWENxAv+Ilk/1PV9v+eq//W9mEpHNuSk/9CJ1JMgXXTQFHCCwgmGD945r1i0bbpxdkx291f90weqldw8RjIbCEJphg/cNrRYsG6/870yC+3lH7aOhCK67Uud868juUpSNM5RKsJQqstDejvEKeCSZYYCr87/Oyv9zqOl0/crFthhmx0t6MwOKZYIIFFu+fSVq/uNf3TdP45WezWYUmw/KDWyGwgGCC9f11JXqL88SdF6frh88/nb7atTCVTWgyLD+4FQILCCZYVFm5+uFzzVNXwN4NBlT9S6CCCdbrG8rpAgJgtxmL2uh7pwYVwhSiYIIF9se60b14u89wb9DycMTJlO5Ne3NBg8XasD3mFIZhK7YqzjdY2fiWT8EEi9rR76X5wZANRM4w0Q1pby5UsMD22dFtaJmTVKet2K04j2Bl7Vs+BROs4k07bJ5Q3WiC0ORVXvKEXQwi7l+xL3EwFWbhWz4FFazynXYPScclg12Tqe24eQWWrJrdXWkKCOSylk62vuVTMMECBQQYqtj7vK/ykiepxoGUYOW6lk52vuVTUMGKjlj+JmVMZQo+gZXdiJXDWjpZ+5ZPQZ4KbR6yXkE0gNmQNRWu8lo6KzovFpIsSp5ArKWTvW/5FNS/CqPJFK56BcEuILDKS54kn31WdHkasCDW0snet3wKJlhFpdss7sCdfuO9QXYBgVUNFrtcDWu5HT2dul5hTmvpZO1bPgX3AekWs9N/vWvhVu+ydNDycMTJ7N2AaukITVB/0inZbHJ4L7XNXHsOdpux4Sb/gaMfo5InAhRMsNYUbzLaPee+U15u11zvXrpLVaZAYAlRUMEC6V+Pxy48VV3tnNJq6hcAAADrSURBVL/dF914DdXSEZrghs2ULlvdp+uGzz5RXnkGwmZ8IGwGlTwRmqCCVVS6bHV/9VB+VjZ5uV17swfU0kFgCVE5AOvR0NknyivPtGDEAlMhytIRoOCBRUWQjpxvnpZ0zN3uM6gNVIo9rM9AEqCwNcWbjDbiTNP4pVb8etfi3QFz5hGkSEjJBLbj9l5onpZ0zN7s0d8btDLPsbj2DWkVC1tbstns9EmeaW90L90dMIHSvRmGJiMhJRO2rnSrxeW/+UJ3t990f8jG3niNa9+QVrGw9WXb6Zh3e92oq2Hcq7FQP0Jz7RvSKtb/Am21v0AFhd5VAAAAAElFTkSuQmCC" alt="" />从结果可以看到代码执行正常。

3.2.再来看一个有异常的数据

sql_insert='insert into user(userid,username) values(10,"name10")'
sql_update='update user set username="name91" where userid=9'
# sql_delete='delete from user where userid=3'
# ##error
sql_delete='delete from user where useri=3'
try:
cursor.execute(sql_insert)
print(cursor.rowcount)
cursor.execute(sql_update)
print(cursor.rowcount)
cursor.execute(sql_delete)
print(cursor.rowcount)
except Exception as e:
print(e)
conn.rollback()

这里的insert和update操作一样,只不过把delete里面的userid字段错误的写成了useri,执行代码:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAakAAACACAIAAAA+mgFGAAAJ9ElEQVR4nO3d34GrKBvH8W3nmDZe2xjKoIqlgt07mljS3HuBICIg/kni5Pl+Ls7JOEYByU/EJPPXAwDk+evTBQCADyD7AEhE9gGQiOwDIBHZB0CiQvYpY9QwvL8oAPA2c/YNgzLOPZ9P58g+AF8uH/cNgzJkH4BvR/YBkIjsAyAR2QdAIrIPgERkHwCJyD4AEhWzz+qR7APwzQrvbfZ4hzOAL8bneQFIRPYBkIjsAyAR2QdAIrIPgERkHwCJyD4AEpF9ACQi+wBIRPYBkIjsAyAR2QdAIrIPgERkHwCJ+NvkACTib5MDkIjvrAcgEdkHQCKyD4BEZB8Aicg+ABKRfQAkIvsASMTfJgcgEX+bHIBEfJ4XgERkHwCJyD4AEpF9ACQi+wBIRPYBkIjsAyAR2QdAIrIPgERkHwCJyD4AEpF9ACSqZt8wjNro8a5fZxC/a4sv3Xoz/50Xzt6lb3yqPHdrh9+i3W7r1/XrgqicfcMwanvrb7Ly37SVPjhg1JrQPGDU9lZf8/Op8uzdL/3Na7Rb8XUd0ujipitn36itUfmein+zfFDGf+dVXD/7Lqzn87nO+JOVGUZtjVo8WP5x4c2/NTydfO70At7rg39POTb7mw3DqG1hyLC3PLXt7C5P935v2N9u2H/Wr+v5V8ocHuLUFLIv23GjjYZR2zguDePEzYvQkz0vjoGzwXC2345ifMPF8ptrcVVqXLX3Y+U5X4sDW7hnf7tP/6m9riNlLr4SLWRfcdBXbKPFuFQZNw/BWq05aqO1ufz1Q/bhzu55pO5ZqqLLh37r76wvz/St2yhbM17GtltzynRF9l3jO2ohwT2P1D1LVXT5rF/v3yoqZV85a6YHq3lAT2k9DsPbsu/nz0+YZ5lnIf3ZYy7wqG24qG+sP5XfuHQSM5nHUSb893w+//vvn3+b26nWIrRb1nTZfpu1nueV4o+xXsY4vxFt3PP5/Ptno757Gr+w3+nH0MLrKozartszTrCoUNr1r/aWMCtqtp3sOF7RDov+EA6BUUlTpNvvb4fi+tsFq/Sr4us9O15GDVcd373tuWrbK2P6RdnnXJgxzK6Lfbu/c9xXOk7ZTfSpmyZPLKz/WM4G+JOQr6BRPkDtItfr26lJ73+l1Snut1jrzXaI/Tg8SOO+t5z72z+5PljWa3EvL/QZv44xcyH7h/PHSvtoHvddNvpD6PZZv9rVDrX1a2r9qt4O9THNFcf3mDdkX/81b1enXFwLx4P67uxrLJ86acd2fAQly60ew1J/frN6rF37990CKrZ8eb+1zW62Q/ZgGo+cDpdmuxXeh+SXL94PsBxWrCedzxRvcztXheze/rC3HRrrV2raer9ao5rzpcbG62j38T3m5de8j3DiynfcPd9Xe2Ic/aYunLw8dr6y1mqlTVf2lZeP2lqt4j+jKt/zOTz/2N7O/bPP/zhf+ywHNcUdHfvVmdJulr/f3v6wt7J7C9Zev7wLZeKl8Wb/j7XuP77HDKElr7L9HpdpYakmquM+bzETbzPui2+kLA/al+tXx33WGBtO8f5hYzv18n/nuC9bbb4WrI9Hfn327ekPe9th7+cO9o779r6O1tXfPL7HvOM9LsXdZFf108LF3Fl8f9+odcz+aY1s+2n2ZfMgh2X7irvoOZbpKaVxjNN5tyHccU9Pkv7sl50wi5stV2FRjPldTsX91jabXSgp47JrlldmX2W/aVjX5rmS+v7q7DvQH/a2Q239RpGK/areDovXUXq745Lje8Ar3k7f+EzbYtKhdtE+lO7nxoVuHZfLWYDYRS55c3m68eL9svijvw/7zO6jhYn/9frhx2T76dylS9tqntiubadVhVLTlfdbPy7D4qaeMs4t66vivci/l+3QX8524eN+nTM/f/6XzfukXWW+Vlq2W6FSzX64o5Cl7Rw+XsVGONAf+tuh2G49pcrW7+w/zuj4buTzx3dXS86FX8brVRrfZfCS/ZX39aHPSAG4uWzO50If/g6rz35GCoBYfH8fAInIPgASkX0AJCL7AEhE9gGQiOwDIBHZB0Aisg+ARGQfAInIPgASkX0AJCL7AEhE9gGQiOwDIBHZB0Aisg+ARGQfAInIPgASkX0AJCL7AEhE9gGQiOwDIBHZB0Aisg+ARGQfAInIPgASkX0AJCL7AEhE9gGQiOwDIBHZB0Aisg+ARGQfAInIPgASkX0AJCL7AEhE9l1g1FoNw+GnD0qdeXq/k+X8Ld7WnvjV5uwzzj0TzqgPFuuMYRi1nepi9RiXOKvHvpdE//rDoIxzzpljL7bTT39TOS8vz8sKcLaagzK+5xhFen65OfuGQZmk0/ifPlSqU5RxIfKmGr0u+x6rdtvrzNPfWc7Ly/OyMpxoz1HbucNYPRJ/36yefa9/qbzC+4v9wezbtdnPHtC37f3MjuJZ8/Gbz/3oRPZ9eI8vKvCorbtT9q3L8yKHq5mN9cJPv6z/o181+8LwX/l5QBd+eD6f6UWNX5ItfCTzJtnUyajten1/qTRPNYZf1ZYXxaLO60/XL4sqpCs7Z1TyrORKOV+/Uc6p3ZJfxbFDrb6PZFLSGdN4rWbl9P/5sv38+SmWM2uH+Nt2OWvW5Y/bN2rRULV2q5WnrdZ/qv2t0p619q809a8/96Nfln1JH00G/L4fTH0q6ROjtrFf+tNkfJy/5ML68zXFqK1RYYXkfDtP0pWXt+tTW61yDTjPi8e5nub6tXK6ZV636hvWmTYVMq1ar2k34SmdzVKtb6mcNdXyD8mZMc+g3vK091vrP8X+VmvPWvmLyD5pquO+1BA61HKh74pJX5nGQeV54vT263JcNvXc4vo9Y5NVUfuzoNrXS+vXyll+YrO+OzLdt7Ifv1g9ZtdiV9W31JLl8k+/HbV11tp1rzibfc3+s93fNtu/vl+yT5b+7FtdAFZf80d6/3xtsjw515bXvC77auU52Q492We1iv+Myzn4V2bfxgr+fNj5rD3Zt6/d9rZ/fb/M98lyJvsOjPu23zcQr+06l/cUtbj8WPaty9Mc9223Q1f2GWPDkM8/vLy+a+3j9UvGffvep8J9XlGOZ99jOf/iu2J87NK5ITNl1mL+JSyPnXhaPr/Hqry8XZ9XXvPWy1l5YrG+Wfsos/FeXD/r79vZjzrTuKnXN4xbwoccDlzTVcsfK786KP3laWj0n83+lrZnrfzV/S6OKe/v+3L55zp23aRLp1Syk2S8T+eWfWi+ZoyT06t5Gd+/a8trmvc38+XZTcn0x3j/dLWdQnka24kvv2I7DPPnBxa3XAr1Sibv09fk5v1Tv2s331Davie7VjpeyXNHbUPhd5VnU7H/9PS3rD1r7b+533Znwxfg87wAJCL7AEhE9gGQiOwDIBHZB0Aisg+ARGQfAInIPgASkX0AJCL7AEhE9gGQ6P87x2OdwLn7hgAAAABJRU5ErkJggg==" alt="" />

可以看到显示出异常,这时我们来看一下数据库数据:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAALoAAADuCAIAAAABc4pHAAAOc0lEQVR4nO2dT2/byBmH5/P42FqCoo8Qf4cQkNdC8il6yIYL24tE197SnLK6RA4QFVs7h0VPu1hdVs4eIt6KtiicCOpuLKuJLfYw/DP8NzP0kHxp8vdgDgxJSe/wfTRDOTNDtgJAG0YdALhLsFMAtGEuANqw5wBoo2hdHMf5x5XbgOI4TjXfv5pjeB2gS7uALtAlB9AFuuQAukCXHNxBXc6f/YHd+9N5Yv9kyLrP/g5dygS6QJcc3EFdsgp0KR9TXa42n7PK9fWN4zhvP7oVle+GbPfZi3Le3HEcSU1bUnhCK9Hl52c7bPhNYvvF1/eYh3/043TP37Xz9eLtR/ftx8WjXbb33XSPMbb77IX4VsLJjB8tW5d333bZV5PE9vlxzw/CP7p5PfB3dY9/vdp8vtr8etRlg5PXA8ZY99vzzesB6x0dfxU9h7+tx+AkeKve0Ym3f3Dy+eok8aqUj6ufLuur/2UVtS6RxAfpv/foZ1ewxNtgKbZN9xg/wX370f3GKlcXr17nx122P4ltizu9cjJgvcNzvv3usMsGE2+DhWeeDBhjgxP/rfj57w4Hx3N+wmTfP/lkwBjrHs+9neKrghOSH1dwqUKXsw/bsw/bs5+e7rADO779Zo8xxnqPftp6p708YFF2Hr8/+/D+0S7be7mNv9XLA7b79C8fhNeK/yy0KHTh6QwT5idVoHv0LpHIWI7Dl8+PgrYqaUPadvrH3Sldbm6UuvDy/tGuL016yuuvS/i1ZkH+un4jET1Bocv5cTdIdnrjkaFLyscVXHhCjXS5XG+yyvXNjeM4pxfb04vt6Y9Pd1jv4Y/b04vt88c9xg6eXGxPf3z68OX29GJ7evH+4S67/3J7evHmPuMb29OL7ZMHB08iR4O34vvFk98/3GVs9+nzC/+0QovjOF695sdd1j2cby7Xm18Ou4ztv1pvLufHhxNeay7E5nJ9MmB8Y3O53rwa7L+KHOXlZOC/1eX63WG3ezjfXE72Wff4l3X0/SNnpm6nflzB5bo6XTxLGGNs58GBn+/tkwd+6/ngjWCDx/24TDFdtqdh59V7+PigCl28LDLGWHew3/XSuXkV3GcOTgSx/H1xmbJ1Wb/z3158f6UuqR9XP10+XV5lFd7V/W1504DiOI6kpi0pBdy7/P5pnVU8Xf5104DiOI6kpi0pVejy/X+uG1CgSzG6/Pb7ZVa5vr52HOf7f143oDiOI6lpSwpPqJEu//3tU1b5cn3tOM5f//2lAcVxHElNW1K+QBfoUqkuDmgZRroo/9//j392G1BWq5XJZWoMhtcBurQL6AJdcgBdoEsOoAt0yUExujCW7g10aRiF6ZJqzN3T5cx1XfeHM2pdpkPG2HBa6WfqUKQuSWPuli7Pl667dH9Y0uqyGPUZ6w+H/SbqwoeyisP+YkNcySW4hTT0rYu7GLVEF9GYJuoyHbL+aDTkNe2PFt7uxajvV99P83TI+qOpt3849bqYyKuCXZGdbrt0CYxpqC6+EYtR38vyYjT0sz0d+oenw8ACbkX4quCEQJKYHw3VhY98jrkijogmT38prUuYY0n7Ip6Zth22LMkGpjW6xAbQk6e/Cl347Snfm954ZOgS7YEEGqoLH/kcuJIcEU2e/ip0EfYtRn3d1kXot1zXnQ5FPVqgS+oAevL05xJFRJRG1RmFHZHwE1ipS+oNsrArsr8WmOrCB8ozxrIG0JNLUEjBX3U5xrqoBtCTZxq6FIipLsoB9OSZhi4FYqyLagA9eaahS4GY6qIcEU2eaehSINAFuuTAVBflwPGynnUCiDDSRX7YcJ5BfTC8TI0BumgBXTjQRQvowoEuWkAXDnTRArpwoIsW0IUDXQRS/n/Yo1JdssMgB7oETIdZIyEr1UUWBjmmupS6JjgdkVGVLllnFA+DHOiSynSY2bpUMxMgPQxyTHUpdU1wKpKDaKO6VDATID0McqBLnMgAWp9461L6TID0MMgpURfzJeSrJytJCl2KnglQT1dcc11KXRO8WsKuJYlClyJnAsjCIAe6+MS7hqxfRiXPBJCGQY6pLqUuIV8f8Fddjqkupa4JXh+gCwe6aAFdOKa6lLqEfH2ALhxTXUpdE7w+QBcOdNECunBMdcFMgLZhpIv8sOM4y0awWq2oQ6gF0EUL6MKBLlpAFw500QK6cKCLFtCFA120gC4c6CIytrz/B7bG0QPV6pIZBjnQJWBmd7z0jK14pirURRYGOaa6KId2U1fwVowt1rFnwg6azigRBjnQJYVkmgRdxhbr2LbXXYSnzewOi3UhY4t17LG33xqHnYzw5kG/kyJG/Wwx1kU5tJu6gnmYCamNEtXFP2Nmd7yEzmzLT2zYhYytwAJuRfiq4ITAh3CvJAxyoEsKYyv+ZY+3LmGOJe2LeGbadtiypDcwyTDIKVEXPhOAuoK3I94PKHSZ2Z0gs+mNR4YuChtq1x2Z6qIc2k1dQW3CXyRCJ+Oj0EXYN7M7uq1L9KfP2LLGqjDIgS4hKXerPqrOKHxpx7J0W5eMj5SEQY6pLsqZANQVLAb8VZdjqotyaDd1BYsBunCgixbQhWOqi3ImAHUFiwG6cEx1UQ7tpq5gMUAXDnTRArpwTHXBTIC2YaSL/DDmGTUM6KIFdOFAFy2gCwe6aAFdONBFC+jCgS5aQBcOdEkgLmPpQ6BLWhjkQJcYi1G/30+sPVm5LulhkGOqS8MWeedLWUpX7aYMgxzoIpCyTK6HcJnKfyZAdhjkmOrSoEXe05fb5kR1KfWZALIwyIEuHuIjHNStS2nPBJCHQU6JutypZwIIuWbRpLuuW90zARRhkGOqS3MWeRfI3boU+UwAWRjkQJcU8ndGxT0TQBoGOaa64JkArcJUFyzy3iqgixbQhWOqC54J0CpMdcEi760CumgBXTimumAmQNsw0kV+GK1Lw4AuWkAXDnTRArpwoIsW0IUDXbSALhzoogV04UCXkMhwpej/BVepiyQMcqBLiGTAQMW61EySEFNdmjS0G7oogS4hQi8QH6skXKbSZwJIwiDHVJfGDO0WCUf4+0R1KXUmgCwMcqBLKvEOId66lDYTQB4GOSXqcqdmAsTIqUthMwEUYZBjqkuDhnaHQ/GnQ8kP6bJnAsjCIAe6CAS9QyJJqs6o0JkA2WGQY6oLZgK0ClNdMLS7VUAXLaALx1QXzARoFaa6YGh3q4AuWkAXjqkumAnQNox0kR92HGe5dhtQVqsVeQx1KNBF9zKRx1CHAl10LxN5DHUo0EX3MpHHUIcCXXQvE3kMdSjQRfcykcdQhwJdomUSDFkajqOXqQ5hkBfoEk1SbzTLuEx1CIO8mOqiHNpNXkPtsrB7fXueeZnqEAZ5gS5+mY86vZE98PqAztEidpn87anF+vbRMH7afNTx+w9rIpw58fZbk7CLEd58arHoJ0rDIC+muiiHdpPXULdMhmGm56MOi3zFo7owNphGT1vYA7/7mAz9G46pxZjXrXBRwlcFJwSfsrB7oVJZYZAX6CLoItwxjAeRb3a8dQlzHKZzdhQOpEzYkLY9ic8E6Bwt5GGQlxJ14TMByGuoW+ajzq11mY86Ym+ir0vyllYaBnkx1UU5tJu8htplYffElEs6o4QuQuJnR33d1mU9tcIbHXc84K+ShUFeoItYwhvPIItauqwXds/vUAZD3dZlnXqDLAuDvJjqopwJQF7Doi4TeQx1KKa6KId2k9ewqMtEHkMdCnTRvUzkMdShmOqinAlAXsOiLhN5DHUoprooh3aT17Coy0QeQx0KdNG9TOQx1KGY6oKZAG3DSBf5YcwzahjQRQvowoEuWkAXDnTRArpwoIsW0IUDXbSALhzo4iGsEpeyMlxlusjDIAe6pJFYE5mmdUlZmpkYU12atMh7QDJNJLrUzxbokkLK4sfCZSr9mQCSMMgx1aWBi7ynpSmqSyXPBKijLdAlTnoPEG9dSn8mQA07ItfFMwHiZKRJoUvhzwSoqS1Y5D1KVgYVuhT5TABZGORAF4HsBwipOqNCnwlQw+cY+ZjqgmcCtApTXbDIe6uALlpAF46pLngmQKsw1QWLvLcK6KIFdOGY6oKZAG3DSBf5YbQuDQO6aAFdONBFC+jCgS5aQBcOdNECunCgixbQhQNdBFL/f9h13Yp1yQ6DHOgSIIw+SQwhqFAXWRjkmOrSoKHdWcNTXLdqXTLDIAe6hPhf5pQvtXCZSp8JIAmDHFNdmjW0O8h5/J4hqkvZMwEywyAHugTExkRmdUZlzwSQhUFOibrcsZkA0fuE2F2DQpcCZwJIwyDHVJfmDO2OfJUjY/TdKmcCSMMgB7qEiKsfZP/dpfSZAJIwyDHVBTMBWoWpLhja3SqgixbQhWOqC2YCtApTXTC0u1VAFy2gC8dUF8wEaBtGusgPO46zbASr1Yo6hFoAXbSALhzoogV04UAXLaALB7poAV040EUL6MKBLgIz239mfMeeRY5Uqkt2GORAl4CxxZg19jejmapQF1kY5JjqohzaTV1BbSK5mdmdSKKq00UaBjnQxSeeJ/8rvlwuI7qMLdaxbSveW4Q9SPDCscU69tjbb415wxHrY/xdwU5pGOSY6qIc2k1dQW1mdkfsBZhEF//YzO54uZ3Zlp/jsDMZW4EF3IrwVcEJgRr+XmkY5EAXgeCr3rFtK6sziuVY0r6IZ6Zthy1LooHJCIOcEnXhMwGoK3g7xlb0W63QZWZ3gmSnNx4ZuihkiIdBjqkuyqHd1BW8Dck8KnQR9gmdiUoX8UfQcjm24mbU74cRdAmR/blD1RkJr7Us3dZlmXqDXOO/upjropwJQF3BYsBfdTmmuiiHdlNXsBigCwe6aAFdOKa6KGcCUFewGKALx1QX5dBu6goWA3ThQBctoAvHVBfMBGgbRrqYvBi0DegCcgBdQA6gC8gBdAE5gC4gB9AF5AC6gBxAF5AD6AJyAF1ADqALyAF0ATn4P1qUrP92RY28AAAAAElFTkSuQmCC" alt="" />数据没有任何改变。这就是rollback()的作用

因此,我们以后再写增删改查操作时,最好把操作放入一个try控制块中,来避免一些不必要的错误。

下面是一个银行转账的实例:

#-*-encoding:utf-8 -*-

import MySQLdb
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='199331',db='test',charset='utf8')
cur=conn.cursor()
##创建数据表
cur.execute("""
create table if not EXISTS account(
accid int(10) PRIMARY KEY ,
money int(10)
)
""")
###插入两行数据
cur.execute('insert into account(accid,money) VALUES (1,110)')
cur.execute('insert into account(accid,money) VALUES (2,10)')
conn.commit() cur.close()
conn.close()
#-*- encoding:utf-8 -*-
import sys
import MySQLdb class TransferMoney(object):
def __init__(self,conn):
self.conn=conn
def check_acct_available(self,accid):
cursor=self.conn.cursor()
try:
sql='select * from account where accid=%s' %accid
cursor.execute(sql)
print('check_acct_available'+sql)
rs=cursor.fetchall()
if len(rs)!=1:
raise Exception('账号%s 不存在' %accid)
finally:
cursor.close()
def has_enough_money(self,accid,money):
cursor=self.conn.cursor()
try:
sql='select * from account where accid=%s and money>%s' %(accid,money)
cursor.execute(sql)
print('check_money_available'+sql)
rs=cursor.fetchall()
if len(rs)!=1:
raise Exception('账号%s 没有足够钱' %accid)
finally:
cursor.close()
def reduce_money(self,accid,money):
cursor=self.conn.cursor()
try:
sql='update account set money=money-%s where accid=%s' %(money,accid)
cursor.execute(sql)
print('reduce money'+sql)
rs=cursor.fetchall()
if cursor.rowcount!=1:
raise Exception('账号%s 减款失败' %accid)
finally:
cursor.close()
def add_money(self,accid,money):
cursor=self.conn.cursor()
try:
sql='update account set money=money+%s where accid=%s' %(money,accid)
cursor.execute(sql)
print('reduce money'+sql)
rs=cursor.fetchall()
if cursor.rowcount!=1:
raise Exception('账号%s 加款失败' %accid)
finally:
cursor.close()
def transfer(self,source_accid,target_accid,money):
###检测两个账号是否可用
try:
self.check_acct_available(source_accid)
self.check_acct_available(target_accid)
####检测付款人是否有足够的钱
self.has_enough_money(source_accid,money)
self.reduce_money(source_accid,money)
self.add_money(target_accid,money)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e if __name__=='__main__':
source_accid=sys.argv[1]
target_accid=sys.argv[2]
money=sys.argv[3] conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='199331',db='test',charset='utf8')
tr_money=TransferMoney(conn) try:
tr_money.transfer(source_accid,target_accid,money)
except Exception as e:
print('出现问题'+str(e))
finally:
conn.close()