前言

Python连接数据库进行数据的增删查改操作是我们用Python开发系统时最基本的操作。但是很多刚学Python的同学对于如何用Python连接数据库，如何对数据库表的数据进行插入，删除等操作还不是很了解。今天我们就以现在最流行的mysql数据库为例，讲解一下Python是如何连接mysql的，以及一些常见的sql插入，修改等操作。

Python连接mysql代码如下：

# -*- coding:utf-8 -*-
'''
堆代码 duidaima.com 2023-03-19
定义对mysql数据库基本操作的封装
1.包括基本的单条语句操作，删除、修改、更新
2.独立查询单条、查询多条数据
3.独立添加多条数据
'''
 
import pymysql
import logging
import osclass OperationDbInterface(object):
 
    def __init__(self):
        self.conn = pymysql.connect(host='localhost',
                                    user='root',
                                    password='root',
                                    db='test',
                                    port=3306,
                                    charset='utf8',
                                    cursorclass=pymysql.cursors.DictCursor)  # 创建数据库连接
        self.cur = self.conn.cursor()  # 创建游标
 
    # 定义单条数据操作，增删改
    def op_sql(self, param):
        try:
            self.cur.execute(param)  # 执行sql语句
            self.conn.commit()
            return True
        except pymysql.Error as e:
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levekname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
            return False
 
    # 查询表中单条数据
    def select_one(self, condition):
        try:
            self.cur.execute(condition)
            results = self.cur.fetchone()  # 获取一条结果
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
        finally:
            return results
 
    # 查询表中所有数据
    def select_all(self, condition):
        try:
            self.cur.execute(condition)
            self.cur.scroll(0,  mode='absolute')  # 光标回到初始位置
            results = self.cur.fetchall()  # 返回游标中所有结果
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
        finally:
            return results
 
    # 定义更多数据操作（插入数据，更新数据，删除数据）
    def operate_more(self, condition, param):
        try:
            self.cur.execute(condition, param)
            self.conn.commit()
            return True
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levekname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
            return results
 
    # 数据库关闭
    def __del__(self):
        if self.cur is not None:
            self.cur.close()
        if self.conn is not None:
            self.conn.close()if __name__ == "__main__":
    test = OperationDbInterface()  # 实例化类
    result_1 = test.select_one('select*from people')  # 查询一条数据
    print(result_1)
    result_2 = test.select_all('select*from people')  # 查询所有数据
    print(result_2)
    result_3 = test.operate_more('insert into people  values (%s, %s, %s)', ('4', '付千', '兰州'))  # 插入一条数据
    print(result_3)
    # result_4 = test.operate_more("insert into people  values (%s, %s, %s)", [('4', '付千', '兰州'),
                                                                         #  ('5', '韩以', '温州')])  插入两条数据
    result_5 = test.operate_more('delete from people where NAME = %s', ('付千'))  # 删除一条数据
    print(result_5)
    result_6 = test.operate_more('update people set NAME = %s where NAME = %s', ('陈浩', '陈昊'))  # 修改一条数据
    print(result_6)

以上就是Python连接mysql数据库进行增删查改操作的简单例子。我们在使用 pymysql.connect进行数据库连接时，注意一定要把连接字符串拼写正确，因为很多初学者在用Python连接mysql时报错基本都是连接字符串没有拼写正确造成的。