2018-11-22pyMySQL增加删改查

在表格中增加

import pymysql

# 固定格式
def main():
    no = int(input('部门编号: '))
    name = input('部门名称: ')
    loc = input('部门所在地: ')
    # 1. 创建连接（主机、端口、用户名、口令、数据库名、字符集、……）
    con = pymysql.connect(host='localhost', port=3306,
                          user='root', password='123456',
                          database='HRS', charset='utf8',
                          autocommit=True)
    try:
        # 2. 通过连接对象的cursor方法获取游标
        with con.cursor() as cursor:
            # 3. 通过游标对象的execute方法向数据库服务器发出SQL
            result = cursor.execute(
                'insert into TbDept values (%s, %s, %s)',
                (no, name, loc)
            )
            # 4. 处理服务器返回的信息
            if result == 1:
                print('添加成功')
    except pymysql.MySQLError as e:
        print('恭喜你又卵了:', e)
    finally:
        # 5. 关闭连接释放资源
        con.close()


if __name__ == '__main__':
    main()

在表格中删除

import pymysql


def main():
    no = int(input('请输入要删除的部门编号: '))
    # 1. 创建连接（主机、端口、用户名、口令、数据库名、字符集、……）
    con = pymysql.connect(host='localhost', port=3306,
                          user='root', password='123456',
                          database='HRS', charset='utf8',
                          autocommit=True)
    try:
        # 2. 通过连接对象的cursor方法获取游标
        with con.cursor() as cursor:
            # 3. 通过游标对象的execute方法向数据库服务器发出SQL
            result = cursor.execute(
                'delete from TbDept where dno=%s',
                (no, )
            )
            # 4. 处理服务器返回的信息
            if result == 1:
                print('删除成功')
    except pymysql.MySQLError as e:
        print('恭喜你又卵了:', e)
    finally:
        # 5. 关闭连接释放资源
        con.close()


if __name__ == '__main__':
    main()

更新表格

import pymysql


def main():
    no = int(input('部门编号: '))
    name = input('新的名称: ')
    loc = input('新的所在地: ')
    con = pymysql.connect(host='localhost', port=3306,
                          user='root', password='123456',
                          database='HRS', charset='utf8',
                          autocommit=True)
    try:
        with con.cursor() as cursor:
            result = cursor.execute(
                'update TbDept set dname=%s, dloc=%s where dno=%s',
                (name, loc, no)
            )
            if result == 1:
                print('更新成功')
    except pymysql.MySQLError as e:
        print('恭喜你又卵了:', e)
    finally:
        con.close()


if __name__ == '__main__':
    main()

查找所以部门

import pymysql


class Dept(object):

    def __init__(self, no, name, loc):
        self.no = no
        self.name = name
        self.loc = loc

    def __str__(self):
        return f'{self.no}\t{self.name}\t{self.loc}'


def main():
    con = pymysql.connect(host='localhost',
                          port=3306,
                          user='root',
                          password='123456',
                          database='HRS',
                          charset='utf8',
                          cursorclass=pymysql.cursors.DictCursor)
    try:
        with con.cursor() as cursor:
            cursor.execute('select dno no, dname name, dloc loc from TbDept')
            depts = cursor.fetchall()
            for dept_dict in depts:
                dept = Dept(**dept_dict)
                print(dept)
    except pymysql.MySQLError:
        pass
    finally:
        con.close()


if __name__ == '__main__':
    main()

一次添加多条

import pymysql


def main():
    con = pymysql.connect(host='localhost', port=3306,
                          user='root', password='123456',
                          database='HRS', charset='utf8',
                          autocommit=True)
    try:
        with con.cursor() as cursor:
            # 游标对象的executemany方法可以一次性执行多个SQL操作
            # 相当于是以批处理的方式执行SQL（比一个个执行单条SQL效率更高）
            result = cursor.executemany(
                'insert into TbDept values (%s, %s, %s)',
                ((80, '研发2部', '上海'),
                 (90, '销售3部', '深圳'),
                 (100, '运维2部', '重庆'))
            )
            print(result)
    except pymysql.MySQLError as e:
        print('恭喜你又卵了:', e)
    finally:
        con.close()


if __name__ == '__main__':
    main()