Python程序接入MySQL数据库

Python程序接入MySQL数据库

删除数据

"""
example02 - 输入部门编号，删除对应的部门
        - 删除数据

Author: Hao
Date: 2021/8/31
"""
import pymysql

no = int(input('要删除的部门编号: '))
conn = pymysql.connect(host='47.104.31.138', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute('delete from tb_dept where dno=%s', (no, ))
        if affected_rows == 1:
            print('删除部门成功!')
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()

更改数据

import pymysql

no = int(input('部门编号: '))
name = input('部门名称: ')
loc = input('部门所在地: ')
conn = pymysql.connect(host='47.104.31.138', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'update tb_dept set dname=%s, dloc=%s where dno=%s',
            (name, loc, no)
        )
        if affected_rows == 1:
            print('更新部门成功!')
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()

查询数据库

多种查询方法都可以 输出字典 迭代器

import pymysql

conn = pymysql.connect(host='47.104.31.138', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 第二步:获得游标对象
    with conn.cursor() as cursor:
        # 第三步:通过游标执行SQL
        cursor.execute('select dno, dname, dloc from tb_dept')
        # 第四步:通过游标抓取数据(查询结果)
        # fetchone / fetchmany / fetchall
        # 提示:如果查询结果数据体量很大,那么最好不使用fetchall,
        # 否则程序可能因为内存不足而崩溃

        # print(cursor.fetchall())

        # row = cursor.fetchone()
        # while row:
        #     print(row)
        #     row = cursor.fetchone()

        # for row in iter(lambda:coursor.fetchone(), None):
        #     print(row)

except pymysql.MySQLError as err:
    print(err)
    # conn.rollback()
finally:
    conn.close()

查询部门信息

import pymysql


class Dept:

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

    def __str__(self):
        return f'编号：{self.no}\n名称: {self.name}\n所在地：{self.location}'


conn = pymysql.connect(host='47.104.31.138', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor(pymysql.cursors.DictCursor) as cursor:
        cursor.execute(
            'select dno as no, dname as name, dloc as location from tb_dept'
        )
        dept_row = cursor.fetchone()
        while dept_row:
            # 通过获取到的部门的数据创建一个部门对象
            dept = Dept(**dept_row)
            print(dept.no, dept.name, dept.location, sep='\t')
            dept_row = cursor.fetchone()
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()

批量插入数据

import time

import pymysql

conn = pymysql.connect(host='localhost', port=3306,
                       user='root', password='root',
                       database='hrs', charset='utf8mb4')
start = time.time()
try:
    with conn.cursor() as cursor:
        params = []
        for i in range(1, 50001):
            params.append((f'dno{i}', ))
            if i % 1000 == 0:
                cursor.executemany(
                    'insert into tb_dept (dno) values (%s)',
                    params
                )
                params.clear()
    conn.commit()
    end = time.time()
    print(f'耗时: {end - start:.3f}秒')
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()