连接mysql数据库实现增删改查(一)

在python中我们通过pymysql来连接数据库，具体实现如下

'''
连接mysql数据库
此类进行封装了一些基础的操作数据库方法
'''
import pymysql
from Homework.Log import Log


class my_db:

    def __init__(self,host,port,user,password,database,charset):
        self.logger = Log().setLog()

        try:
            self.con = pymysql.connect(host=host,port=port,user=user,password=password,database=database,charset=charset)
        except Exception as e:
            self.logger.info('数据库连接失败:{}'.format(e))
        self.cur = self.con.cursor()

    def select(self,sql):
        try:

            # 执行sql
            self.cur.execute(sql)
            # 返回结果
            res = self.cur.fetchall()
            self.logger.info('正在执行{0},查询结果为：{1}'.format(sql,res))
        except Exception as e:
            self.logger.info('查询失败:%s'%e)

    def editor(self,sql):
        try:
            self.logger.info('正在执行{}'.format(sql))
            # 执行sql
            self.cur.execute(sql)
            # 提交事物
            self.cur.execute('commit')
            self.logger.info('增删改成功')
        except Exception as e:
            self.logger.info('增删改失败:{}'.format(e))

下面是个demo文件，对封装的my_db类进行调用，去进行相应的增删改操作

'''
sql语句进行查询
'''
from Study.mysqlConnect import my_db

mysql = my_db(host='127.0.0.1',port=3306,user='root',password='root123456',database='collect',charset='utf8')
mysql.select('select * from C;')
mysql.select('select * from S;')
mysql.editor('insert into S (sno,sname,sdd,sa) values(2019009,"马云","互联网",78')
mysql.select('select * from sc;')

下面是查询结果日志；

2021-04-29 22:13:45,298 - mysqlConnect.py:32  - 正在执行select * from C;,查询结果为：(('C1', '税收基础'), ('C2', '金融工程'), ('C3', '会计'), ('C4', '统计学习方法'), ('C5', '大数据'), ('C6', '机器学习算法'))

2021-04-29 22:13:45,299 - mysqlConnect.py:32  - 正在执行select * from S;,查询结果为：((2019001, '托马斯李', '运营', 26), (2019002, '米高扬', '管理', 30), (2019003, '蝙蝠侠', '安防', 22), (2019004, '李嘉诚', '投资', 45), (2019005, '雷军', '开发', 34), (2019006, '周小川', '管理', 56), (2019007, '陆奇', '运营', 36), (2019008, '普京', '安防', 67))

2021-04-29 22:13:45,299 - mysqlConnect.py:38  - 正在执行insert into S (sno,sname,sdd,sa) values(2019009,"马云","互联网",78

2021-04-29 22:13:45,299 - mysqlConnect.py:45  - 增删改失败:(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")

2021-04-29 22:13:45,300 - mysqlConnect.py:32  - 正在执行select * from sc;,查询结果为：((2019001, 'C2', 80), (2019002, 'C2', 78), (2019003, 'C1', 89), (2019003, 'C5', 60), (2019004, 'C4', 90), (2019005, 'C1', 87), (2019005, 'C2', 75), (2019005, 'C3', 80), (2019005, 'C4', 90), (2019005, 'C5', 86), (2019005, 'C6', 88), (2019006, 'C1', 99), (2019006, 'C2', 61), (2019007, 'C1', 62), (2019007, 'C2', 78), (2019007, 'C3', 77), (2019007, 'C4', 69), (2019007, 'C5', 98), (2019007, 'C6', 88), (2019008, 'C1', 78))