这是 Python3 下的 MySQL 基本操作。其他类型的数据库用法基本一样。就是库的名字不同。因为 python 官方很早之前就规定了数据库第三方库的借口,来避免 API 混乱的情况。
这是 python3 的库,所以 windows 下安装不会像 python2 那样各种奇葩 VC 错误。是比较方便的傻瓜安装。
- py -3 -m pip install PyMySQL
- python3 pip install PyMySQL
当然,引入的时候:
- import pymysql
Function | 描述 |
---|---|
connection | 创建 connection 对象 |
cursor() | 使用该链接创建 + 返回游标 |
commit() | 提交当前事务 |
rollback() | 回滚当前十五 |
close() | 关闭连接 |
介绍一下 connection 的参数
上一段代码示例:
- conn = pymysql.Connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'dyx240030', db = 'imooc', charset = 'utf8') cursor = conn.cursor() print(conn) print(cursor) cursor.close() conn.close()
- OUT: <pymysql.connections.Connection object at 0x00000051C15BFDA0 > <pymysql.cursors.Cursor object at 0x00000051C15BFD68 >
Function | 描述 |
---|---|
execute(op[,args]) | 执行一个数据库查询和命令 |
fetchone() | 取得结果集下一行 |
fetchmany(size) | 取得结果集 size 行 |
fetchall() | 取得结果集剩下所有行 |
rowcount | 最近一次 execute 返回数据的行数或影响行数 |
close() | 关闭 cursor |
代码实现:
- conn = pymysql.Connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'dyx240030', db = 'imooc', charset = 'utf8') cursor = conn.cursor() sql = "select * from user"cursor.execute(sql) print("cursor.excute:", cursor.rowcount)
- rs = cursor.fetchone() print("rs:", rs)
- for each in cursor.fetchmany(2) : print(each) print() for each in cursor.fetchall() : print(each)
- OUT: cursor.excute: 4 rs: ('1', 'name1')('2', 'name2')('3', 'name3')
- ('4', 'name4')
不同于 select 操作,这三个操作修改了数据库内容,所以需要 commit(),否则数据库没有做相应的更改,但是也不会报错。
按照一般的思路,一般是以下套路:
下面这段脚本,实现 insert/update/delete 操作。其实这种检错模式不对,这里只做简单 raise,后面有更好的方法。
- conn = pymysql.Connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'dyx240030', db = 'imooc', charset = 'utf8') conn.autocommit(False) cursor = conn.cursor()
- sqlInsert = "insert into user(userid,username) values('6','name6')"sqlUpdate = "update user set username='name41' where userd='4'"sqlDelete = "delete from user where userid='1'"
- try: cursor.execute(sqlInsert) print(cursor.rowcount) cursor.execute(sqlUpdate) print(cursor.rowcount) cursor.execute(sqlDelete) print(cursor.rowcount)
- conn.commit() except Exception as e: print("Reason:", e) conn.rollback()
- cursor.close() cursor.close()
- [OUT] : 1 Reason: (1054, "Unknown column 'userd' in 'where clause'")
可以看一下类思想的 SQL 操作,其中之前提到过的高级报错模式用到了之前看似无用的
函数,通过查看操作对于数据库的影响来检错。
- rowcount
- import os import sys import pymysql
- class transferMoney(object) : def__init__(self, conn) : self.conn = conn def transfer(self, sourceID, targetID, money) : #其他函数中若是有错会抛出异常而被检测到。
- try: self.checkIdAvailable(sourceID) self.checkIdAvailable(targetID) self.ifEnoughMoney(sourceID, money) self.reduceMoney(sourceID, money) self.addMoney(targetID, money) self.conn.commit() except Exception as e: self.conn.rollback() raise e def checkIdAvailable(self, ID) : cursor = self.conn.cursor() try: sql = "select * from account where id = %d" % ID#select语句判断可以用len(rs) cursor.execute(sql) rs = cursor.fetchall() if len(rs) != 1 : #数据库类思想的报错模式,检查操作对数据库的影响条目。没有达到目标,抛出异常raise Exception("账号 %d 不存在" % ID)
- finally: cursor.close()
- def ifEnoughMoney(self, ID, money) : cursor = self.conn.cursor() try: sql = "select * from account where id = %d and money>=%d" % (ID, money) cursor.execute(sql) rs = cursor.fetchall() if len(rs) != 1 : raise Exception("账号 %d 不存在 %d Yuan" % (ID, money))
- finally: cursor.close()
- def reduceMoney(self, ID, money) : cursor = self.conn.cursor() try: sql = "update account set money = money-%d where id=%d" % (money, ID) cursor.execute(sql) if cursor.rowcount != 1 : raise Exception("失败减钱")
- finally: cursor.close()
- def addMoney(self, ID, money) : cursor = self.conn.cursor() try: sql = "update account set money = money+%d where id=%d" % (money, ID) cursor.execute(sql) if cursor.rowcount != 1 : raise Exception("失败加款")
- finally: cursor.close()
- if __name__ == "__main__": if len(sys.argv) >= 2 : sourceID = int(sys.argv[1]) targetID = int(sys.argv[2]) money = int(sys.argv[3])
- conn = pymysql.Connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'dyx240030', db = 'imooc', charset = 'utf8') trMoney = transferMoney(conn)
- try: trMoney.transfer(sourceID, targetID, money) except Exception as e: print("出现问题" + str(e))
- finally: conn.close()
来源: http://www.linuxidc.com/Linux/2017-06/145155.htm