python数据库mysql数据库操作类_Python之pymysql数据库操作类

from typing import Any, Union, Tuple

import pymysql

class Database(object):

"""

pymysql 数据库操作类。

"""

def __init__(self, host, user, password, db, port=3306, charset="utf8"):

"""

初始化数据库类

:param host: 链接地扯

:param user: 用户名

:param password: 密码

:param db: 数据库名

:param port: 端口

:param charset: 字符集

"""

try:

self.connect = pymysql.connect(host=host, port=port, user=user,

passwd=password,

db=db, charset=charset)

self.cursor = self.connect.cursor()

print("初始化")

except pymysql.err as res:

print("链接出错了", str(res))

def insert_all(self, insert_sql: str, data: dict) -> int:

"""

功能 : 添加多条数据

:param insert_sql: sql语句.如: insert into test1(name,age) values(%s,%s)

:param data: {('小陈', 20), ('小东', 19)}

:return: 返回影响行数.如添加了两条数据就返回 2 .

"""

try:

self.cursor.executemany(insert_sql, data)

count = self.cursor.rowcount

self.connect.commit()

return count

except Exception as e:

self.connect.rollback()

print("添加多条数据出错:{}".format(e))

def insert(self, insert_sql: str, data: tuple) -> int:

"""

功能 : 添加单条数据

:type data: object ('小陈', 20)

:param insert_sql: sql语句. 如 : insert into test1(name,age) values(%s,%s)

:return: 返回添加成功的 ID .

"""

try:

self.cursor.execute(insert_sql, data)

insert_id = self.connect.insert_id()

self.connect.commit()

return insert_id

except Exception as e:

self.connect.rollback()

print("添加单条数据出错:{}".format(e))

def select(self, select_sql: str, data=None) -> tuple:

"""

功能：查询数据

:param select_sql: 查询的 sql 语句。 如： select * from test1

:param data: 默认为 None ,当 sql = 'select * from test1 where id=%s'，那么 data 就类似于 data = 2

:return: 当 data 为 None 时：((1, '小东', 19), (2, '小陈', 20)) . 当 data 为不 None 时： ((1, '小东', 19),)

"""

try:

self.cursor.execute(select_sql, data)

result_all: Union[Tuple, Any] = self.cursor.fetchall()

return result_all

except Exception as e:

self.connect.rollback()

print("查询数据出错:{}".format(e))

def delete(self, delete_sql: str, data) -> int:

"""

功能： 删除数据

:param delete_sql: DELETE FROM test1 WHERE age = %s

:param data: 如： 11

:return: 返回删除的影响行数

"""

try:

self.cursor.execute(delete_sql, data)

# 删除的影响行数

row_count = self.cursor.rowcount

# 提交事务

self.connect.connect()

return row_count

except Exception as e:

self.connect.rollback()

print("删除数据出错:{}".format(e))

def update(self, update_sql, data):

try:

self.cursor.execute(update_sql, data)

# 更新的影响行数

row_count = self.cursor.rowcount

# 提交事务

self.connect.connect()

return row_count

except Exception as e:

self.connect.rollback()

print("更新数据出错:{}".format(e))

def __del__(self):

"""

关闭数据库链接

"""

self.connect.close()

self.cursor.close()

print("关闭数据库")

复制代码