python访问mysql的简单实例

前言：我用的是第三方库pymysql，访问MySQL数据库。

首先我们先创建一个test用的database以及相关的test表：

DROP DATABASE IF EXISTS db_python;
CREATE DATABASE db_python DEFAULT charset utf8;

USE db_python;
# 部门表
CREATE TABLE tb_dept ( 
	deptno INT NOT NULL COMMENT '编号', 
	deptname VARCHAR ( 10 ) NOT NULL COMMENT '名称', 
	deptaddr VARCHAR ( 20 ) NOT NULL COMMENT '地址' 
);
INSERT INTO tb_dept VALUES
	( 101, '研发部门', '武汉' ),
	( 102, '销售部门', '广州' ),
	( 114, '会计部门', '北京' ),
	( 127, '统筹部门', '上海' );
ALTER TABLE tb_dept ADD PRIMARY KEY(deptno);
# 员工表
DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp (
	empno INT NOT NULL COMMENT '编号',
	empname VARCHAR ( 10 ) NOT NULL COMMENT '姓名',
	empjob VARCHAR ( 10 ) NOT NULL COMMENT '职位',
	empsal INT NOT NULL DEFAULT 3000 COMMENT '薪酬',
	deptno INT NOT NULL COMMENT '所属部门',
	PRIMARY KEY ( empno ),
	FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
);
INSERT INTO tb_emp VALUES
	(10001,'杨过','销售主管',2000000,102),
	(20001,'黄蓉','高级会计师',1200000,114),
	(30001,'欧阳锋','架构师',2400000,101),
	(30051,'阿紫','程序员',200000,101),
	(40001,'张三丰','总裁',90000000,127);

操作一条数据，用的语法是：

result = concur.execute(
    'insert into tb_dept values(%s,%s,%s)',
    (992, 'jack', 'xx1')
)

操作多条数据，用的语法是：

result = concur.executemany(
    'insert into tb_dept values(%s,%s,%s)',
    ((993, 'jack', 'xx1'), (994, 'jack', 'xx1'))
)

test实例，添加一个部门：

import pymysql

def mysql_test():
    # 1，创建数据库连接对象
    con = pymysql.connect(
        host='localhost',
        port=3306,
        database='db_python',
        charset='utf8',
        user='root',
        password='python3',
        autocommit=False  # 自动提交
    )
    try:
        d_no = int(input('部门编号：'))
        d_name = input('部门名称：')
        d_addr = input('部门地址：')
        # 2，通过连接对象获取游标
        with con.cursor() as concur:
            # 3，执行sql语句，返回执行结果
            result = concur.execute(
                'insert into tb_dept values(%s,%s,%s)',
                (d_no, d_name, d_addr)
            )
        if result:
            print('添加成功！')
            # 4，提交事物
            con.commit()
        pass
    finally:
        # 5，关闭连接池，释放资源
        con.close()

if __name__ == '__main__':
    mysql_test()

执行后的结果：

deptno  deptname  deptaddr
   101	研发部门	  武汉
   102	销售部门	  广州
   114	会计部门	  北京
   127	统筹部门	  上海
   207	后勤部门	  黑龙江

更新，删除一个部门的主代码：

# 更新一个部门
result = concur.execute(
    'update tb_dept set deptname=%s, deptaddr=%s where deptno=%s',
    (d_name, d_addr, d_no)
)
# 删除一个部门
result = concur.execute(
    'delete from tb_dept where deptno=%s',
    (d_no)
)

查询部门的全部信息：

import pymysql
from pymysql.cursors import DictCursor

def mysql_test():
    con = pymysql.connect(
        host='localhost',
        port=3306,
        database='db_python',
        charset='utf8',
        user='root',
        password='python3',
        autocommit=True  # 自动提交
    )
    try:
        with con.cursor(cursor=DictCursor) as concur:
            # 查询所有部门
            concur.execute(
                'select deptno as no, deptname as name, deptaddr as addr from tb_dept'
            )
            results = concur.fetchall()
            print(results)
            print('编号\t名称\t\t地址')
            for dept in results:
                print(dept['no'], end='\t\t')
                print(dept['name'], end='\t')
                print(dept['addr'])
    finally:
        # 5，关闭连接池，释放资源
        con.close()

if __name__ == '__main__':
    mysql_test()

翻页效果：

def mysql_test2():
    # 员工类
    class Emp(object):
        def __init__(self, no, name, job, sal, deptno):
            self.no = no
            self.name = name
            self.job = job
            self.sal = sal
            self.deptno = deptno
        def __str__(self):
            return f'{self.no}\t{self.name}\t{self.job}\t{self.deptno}'

    # 1，创建数据库连接对象
    con = pymysql.connect(
        host='localhost', port=3306,
        database='db_python', charset='utf8',
        user='root', password='python3', autocommit=True
    )
    try:
        page = int(input('page:'))
        size = int(input('size:'))
        with con.cursor() as cur:
            cur.execute(
                'select empno as no, empname as name, empjob as job, '
                'empsal as sal, deptno from tb_emp limit %s, %s',
                ((page-1)*size, size)
            )
            print('编号\t姓名\t职位\t部门')
            for emp in cur.fetchall():
                print(Emp(*emp))
    finally:
        con.close()

if __name__ == '__main__':
    mysql_test2()

emp的数据：

10001    杨过    销售主管    2000000    102
20001    黄蓉    高级会计师    1200000    114
20002    郭襄    出纳    300000    114
30001    欧阳锋    架构师    2400000    101
30051    阿紫    程序员    200000    101
30052    张三    程序员    200000    101
30053    李斯    程序员    300000    101
30054    王五    高级程序员    600000    101
30055    宙斯    PM    900000    101
30056    雅典娜    PM    900000    101
40001    张三丰    总裁    90000000    127

运行结果：

page:2
size:5
编号    姓名    职位    部门
30052    张三    程序员    101
30053    李斯    程序员    101
30054    王五    高级程序员    101
30055    宙斯    PM    101
30056    雅典娜    PM    101