构造类实现mysql增改查以及常用的方法

几个知识点

1. cur.execute()返回值是影响条数
2. fetchone()是取结果集中的第一条,所以多条里取一条记得让目标数据在第一条才能用这个方法
3. fetchmany() 读取多条
4. fetchall()获取全部数据
5. cursor.rownumber 获取当前游标位置
6. cursor.scroll(2,mode='relative')将游标前移2个位置
7. conn.rollback()回滚事务 -->事务回滚需要在关闭游标之前执行

__author__ = 'kangpc'
__date__ = '2021-12-31 9:10'

import pymysql,random,time


class MakeDb:
	def __init__(self):
		# 连接参数
		self.host = "10.11.22.33"
		self.port = 3306
		self.user = "root"
		self.passwd = "123456"
		self.database = "finance"

	def open_conn(self):
		self.conn = pymysql.connect(
			host = self.host,
			port = self.port,
			user = self.user,
			password = self.passwd,
			database = self.database,
			charset = 'utf8'
		)

	def insert(self,sql, purchase_order,account_period):
		try:
			cur = self.conn.cursor()
			result = cur.execute(sql,(purchase_order,account_period))
		except Exception as e:
			print("----执行sql报错,错误信息----")
			raise e
		finally:
			cur.close()
			self.conn.commit()
			self.conn.close()
		return result

	def update(self,sql):
		try:
			cur = self.conn.cursor()
			result = cur.execute(sql)
		except Exception as e:
			print("----执行sql报错,错误信息----")
			raise e
		finally:
			cur.close()
			self.conn.commit()
			self.conn.close()
		return result

	def select(self,sql):
		try:
			cur = self.conn.cursor()
			cur.execute(sql)
		except Exception as e:
			print("----执行sql报错,错误信息----")
			raise e
		finally:
			cur.close()
			self.conn.commit()
			self.conn.close()
		return cur.fetchone()


if __name__ == '__main__':
	# 在fin_order插入收货单
	sql1 = "INSERT INTO finance.fin_order...."

	# 查询fin_order未对账的最小id
	sql2 = "select min(id) from fin_order....."

	# 批量更新汇总单为未对账的状态
	sql3 = "update fin_order .........."


	db = MakeDb()
	conn = db.open_conn()
	db.select(sql2)