二阶段day19-PyMySQL

test1-对数据库表格 内容

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()

test2-对数据库表格 内容

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()

test3-对数据库表格 内容

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()

test4-对数据库表格 内容

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()

test5-对数据库表格 批量增 内容

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)',
                ((50, '研发2部', '上海'),
                 (60, '销售3部', '深圳'),
                 (70, '运维2部', '重庆'))
            )
            print(result)
    except pymysql.MySQLError as e:
        print('恭喜你又卵了:', e)
    finally:
        con.close()


if __name__ == '__main__':
    main()

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容