使用Python代码操作MySQL5.x数据库

要使用Python代码操作数据库需要安装一个第三方库PyMySQL。（注：Python版本为3.x）

可以使用pip管理工具安装：

1
pip install PyMySQL

连接MySQL数据库

编写代码，连接数据库

1
2
3
4
5
6
7
8
9
# Connection 创建连接
conn = pymysql.connect(host='localhost',
					   user='root',
					   password='root',
					   database='test',
					   port=3306,
					   charset='utf8',
					   autocommit=False,
					   cursorclass: pymysql.cursors.DictCursor)

host ：主机名/主机IP

port ：端口

user ：MySQL的登录用户名

password ：MySQL的登录密码

database ：要使用的数据库，可以简写成 db

charset ：连接数据库采用的字符编码

autocommit ：默认值是False，表示DML(数据操纵语言)不会自动提交，如果为True则会自动提交

cursorclass ：cursorclass设置cursor游标的类型，这里设置的是dict类型，表示查询返回的结果是以字典的方式，这一项是不必须的。

常用方法

connection对象常用的方法：

1
2
3
4
cursor()        使用该连接创建并返回游标对象
commit()        提交当前事务
rollback()      回滚当前事务
close()         关闭连接

cursor对象常用的方法和属性

1
2
3
4
5
6
execute(sql)    执行一个SQL命令
fetchone()      取得结果集的下一行
fetchmany(size) 获取结果集的下几行
fetchall()      获取结果集中的所有行
rowcount      	返回数据条数或影响行数
close()         关闭游标对象

使用Python代码实现增删改查

在MySQL中建库建表

1
2
3
4
5
6
7
8
9
10
-- 建库 test
create database test default charset utf8;
-- 切换/使用test库
use test;
-- 建表
create table dept(
	dno int primary key,			-- 部门编号
	dname varchar(20) not null,		-- 部门名称
	dlocation varchar(20) not null	-- 部门位置
);

得到数据库连接

1
2
3
4
5
6
7
8
9
10
11
12
13
def get_conn():
    config = {
        'host': 'localhost',
        'port': 3306,
        'user': 'root',
        'password': 'root',
        'db': 'test',
        'charset': 'utf8',
        'autocommit': False,  # 默认不自动提交
        'cursorclass': pymysql.cursors.DictCursor  # 设置游标的类型,查询返回的结果是以字典的方式
    }
    conn = pymysql.connect(**config)  # **config 语法是将config字典拆分成键值对的形式
    return conn

插入操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
def insert():
    """
    插入
    """
    # 得到Connection(连接) / Cursor(游标)
    conn = get_conn()
    try:
        # 创建Cursor对象,支持上下文语法,可以放在with中
        with conn.cursor() as cursor:
            # 向数据库发出sql语句
            dno = input('部门编号:')
            dname = input('部门名称:')
            dloc = input('部门地址:')
            # 如果使用字符串格式化的方式来组装SQL语句，最大的风险是用被SQL注射攻击
            # sql = "insert into dept values (%d, '%s', '%s')" % (dno, dname, dloc)
            # result = cursor.execute(sql)
            # result = cursor.execute('insert into dept values (%s, %s, %s)', (dno, dname, dloc))
            # 这个方式传参是以字典的方式,但是要注意的是在占位的时候用%(name)s
            result = cursor.execute(
                'insert into dept values (%(dno)s, %(dname)s, %(dloc)s)',
                {'dno': dno, 'dname': dname, 'dloc': dloc}
            )
            # print('成功插入', cursor.rowcount, '条数据') # 这里cursor.rowcount是获取到受影响的行
            print('成功插入', result, '条数据')
            
            # 如果事务中的所有操作全部成功了最后手动提交
            conn.commit()
    except BaseException as e:
      	print(e)
		# 如果事务操作有任何一个操作发生异常,那么就会回滚事务
        conn.rollback()
    finally:
        conn.close()

修改操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
def update():
    """
    修改
    """
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            dno = input('部门编号:')
            dname = input('部门名称:')
            # 这个方式传参是以字典的方式,但是要注意的是在占位的时候用%(name)s
            result = cursor.execute(
                'update dept set dname=%(dname)s where dno=%(dno)s',
                {'dno': dno, 'dname': dname}
            )
            # print('成功插入', cursor.rowcount, '条数据') # 这里cursor.rowcount是获取到受影响的行
            print('成功修改', result, '条数据')
            conn.commit()
    except BaseException as e:
      	print(e)
      	conn.rollback()
    finally:
        conn.close()

删除操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
def delete(dno):
    """
    根据编号删除
    :param dno: 编号
    """
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
          	dno = input('部门编号:')
            # 向数据库发出sql语句
            # execute方法中占位后传参除了元组和字典外,还可以是列表
            result = cursor.execute('delete from dept where dno=%s', [dno])
            conn.commit()
            print('删除成功' if result == 1 else '删除失败')
    except Exception as e:
        print(e)
        conn.rollback()
    finally:
        conn.close()

查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
def select():
    """
    查询
    """
    conn = get_conn()
    try:
        # 创建Cursor对象
        with conn.cursor() as cursor:
            # 向数据库发出sql语句
            cursor.execute('select dno, dname, dlocation from dept')
            result = cursor.fetchone()
            # 程序中最好不要使用fetchall(),如果库中数据量很大,查询的性能就很低
            while result:
                print(result)
                # 取出部门名称
                # 在这里我上面连接数据时,使用了cursorclass参数,查询时返回的结果是以字典的方式
                print(result['dname'])
                result = cursor.fetchone()
    except Exception as e:
        print(e)
    finally:
        conn.close()

上面就是对MySQL数据库简单的增删查改操作。

合并

编写一个类，将上面的操作数据库的方法都包含在里面：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
class MySqlHelper(object):
    """MySQLHelper"""
    def __init__(self, connect_config):
        """
        构造器
        :param connect_config: 连接配置，传入一个dict
        """
        self.connect_config = connect_config
        self.conn = None
        self.cursor = None

    def connect_db(self):
        """创建数据库连接"""
        self.conn = pymysql.connect(**self.connect_config)
        # self.cursor = self.conn.cursor()

    def close_db(self):
        """关闭数据库连接"""
        # if self.cursor:
        #     self.cursor.close()
        if self.conn:
            self.conn.close()

    def execute_dql(self, sql, *, param=None):
        """
        执行dql操作，即 select 语句
        :param sql: sql语句，string
        :param param: 参数列表，dict
        :return: 查询结果，tuple
        """
        # res = ''
        try:
            self.connect_db()
            with self.conn.cursor() as self.cursor:
                self.cursor.execute(sql, param)
                res = self.cursor.fetchall()
        except BaseException as e:
            print(e)
        finally:
            self.close_db()
        return res

    def execute_dml(self, sql, *, param=None):
        """
        执行dql操作，即 update、delete、insert 语句
        :param sql: sql语句，string
        :param param: 参数列表，dict
        :return: 查询结果，int [1：成功，0：正常失败，-1：错误失败]
        """
        try:
            self.connect_db()
            with self.conn.cursor() as self.cursor:
                count = self.cursor.execute(sql, param)
                self.conn.commit()
                if count:
                    res = 1
                else:
                    res = 0
        except BaseException as e:
            print(e)
            self.conn.rollback()
            res = -1
        finally:
            self.close_db()
        return res

使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
def main():
    config = {
        'host': 'localhost',
        'user': 'root',
        'password': 'root',
        'database': 'test',
        'port': 3306,
        'charset': 'utf8',
        'autocommit': False,
        'cursorclass': pymysql.cursors.DictCursor  # cursorclass设置cursor游标的类型，这里设置的是dict类型
    }
    sqlhelper = MySqlHelper(config)
    sql = 'select dno, dname, dlocation from dept where dno=%(no)s'
    param = {
        'no': 10
    }
    res = sqlhelper.execute_dql(sql, param=param)
    print(res)

    sql = 'insert into dept values (%(no)s, %(name)s, %(location)s)'
    param = {
        'no': 88,
        'name': 'sda88',
        'location': 'DSfcz88'
    }
    res = sqlhelper.execute_dml(sql, param=param)
    print(res)