在python中 使用Mysql

在数据库中插入数据

# !/usr/bin/env/python
# .*. encoding:utf-8 -*-
import pymysql

def connect():
    #num = int(input('部门编号:'))
    #name = (input('部门名称:'))
    #loc = (input('部门所在地:'))


    #1.创建连接(主机,端口,用户名,口令,数据库名)
    con = pymysql.connect(host='localhost',
                          port=3306,
                          user='root',
                          passwd='limenglei520',
                          database='hrs',
                          autocommit=True,
                          charset='utf8')
    #autocommit=True 自动提交 charset要写不然编码有可能错
    try:
        with  con.cursor() as cursour:
            #插入一条
            '''   
            result = cursour.execute(
                'insert into TbDept values( %s, %s, %s)',
                (num,name,loc)

            )
            '''
            #插入多条
            result = cursour.executemany(
                'insert into TbDept values( %s, %s, %s)',
                ((51,'xx','xx'), (52,'nn','nn'), (53,'pp','pp'))

            )



            if result:
                print('添加成功')
                con.commit()
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()

    finally:
        con.close()


if __name__ == '__main__':
    connect()

在数据库中更新数据

# !/usr/bin/env/python
# .*. encoding:utf-8 -*-

import pymysql

def connect():
    id = int(input('更新的部门编号:'))
    line = input('更新的列名:')
    content = input('更新的内容:')


    #1.创建连接(主机,端口,用户名,口令,数据库名)
    con = pymysql.connect(host='localhost',
                          port=3306,
                          user='root',
                          passwd='limenglei520',
                          database='hrs',
                          autocommit=True,
                          charset='utf8')

    try:
        with  con.cursor() as cursour:


            #更新
            update = cursour.execute(
                f'update TbDept set {line}=(%s) where dno=(%s)',
                (content,id)
            )

            if update:
                print('更新成功')
                con.commit()
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()

    finally:
        con.close()

if __name__ == '__main__':
    connect()

从数据库中抓取数据

# !/usr/bin/env/python
# .*. encoding:utf-8 -*-
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 connect():
    con = pymysql.connect(host='localhost',
                        port=3306,
                        user='root',
                        passwd='limenglei520',
                        database='hrs',
                        autocommit=True,
                        charset='utf8',
                        cursorclass=pymysql.cursors.DictCursor)
    #cursorclass 改游标类型
    #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 in depts:
                print(dept)
                #   方法一 print(dept['no'],dept['name'],dept['loc'])
                #   方法二 dept = Dept(**dept) #解包
                print(dept)
    except pymysql.MySQLError as e:
        pass