python操作mysql

我们知道在java中操作数据库最原始的方式是使用jdbc，那么在python中呢？

操作mysql数据库的基本步骤

  • 引入pymysql模块
  • 创建连接
  • 执行sql语句
  • 关闭连接
import pymysql

 # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8')
    print(conn)  # <pymysql.connections.Connection object at 0x0000000002517370>

新增数据

执行insert语句

"""
    操作mysql,插入数据
"""

import pymysql


def main():
    # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8')
    print(conn)  # <pymysql.connections.Connection object at 0x0000000002517370>

    # 2、拿到游标对象，发送sql语句
    try:
        with conn.cursor() as cursor:
            result = cursor.execute('insert into dept values (6,"测试部","cloudDB01")')
            if result == 1:
                print('添加数据成功')  # 实际没有添加成功，原因没有自动提交
            conn.commit()
    except Exception as e:
        print(f'失败,原因: {e}')
        conn.rollback()  # 失败就要回滚
    finally:
        conn.close()


if __name__ == '__main__':
    main()

删除数据

执行delete语句

"""
    操作mysql，删除数据
"""
import pymysql


def main():
    # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8')
    no = input('请输入需要删除的部门编号： ')

    # 2、拿到游标对象，发送sql语句
    try:
        with conn.cursor() as cursor:
            result = cursor.execute('delete from dept where deptno = %s', (no,))
            if result == 1:
                print('删除数据成功')  # 实际没有删除成功，原因没有自动提交
            conn.commit()
    except Exception as e:
        print(f'失败,原因: {e}')
        conn.rollback()  # 失败就要回滚
    finally:
        conn.close()


if __name__ == '__main__':
    main()

更新数据

执行update语句

"""
操作mysql,更新数据库
"""
import pymysql


def main():
    # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8')
    deptno = input('请输入需要更新的部门编号: ')
    dname = input('请求新的部门名字: ')
    # 2、拿到游标对象，发送sql语句
    try:
        with conn.cursor() as cursor:
            result = cursor.execute('update dept set dname=%s where deptno=%s', (dname, deptno))
            if result == 1:
                print('更新数据成功')  # 实际没有更新成功，原因没有自动提交
            conn.commit()
    except Exception as e:
        print(f'失败,原因: {e}')
        conn.rollback()  # 失败就要回滚
    finally:
        conn.close()


if __name__ == '__main__':
    main()

查询数据

执行select语句

"""
操作mysql,查询数据库
"""
import pymysql


def main():
    # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8')

    # 2、拿到游标对象，发送sql语句
    try:
        with conn.cursor() as cursor:
            cursor.execute('select deptno,dname,db_source from dept')
            # print(cursor.fetchall()) # 返回的是一个元组
            for row in cursor.fetchall():
                print(row)
    except Exception as e:
        print(f'失败,原因: {e}')
        conn.rollback()  # 失败就要回滚
    finally:
        conn.close()


if __name__ == '__main__':
    main()

以字典的方式接受数据并创建对象

"""
操作mysql,查询数据库
"""
import pymysql


# 定义对象类来接受参数
class Dept:
    def __init__(self, deptno, dname, db_source):
        self.deptno = deptno
        self.dname = dname
        self.db_source = db_source

    def __str__(self):
        return f'{self.deptno}\t{self.dname}\t{self.db_source}'


def main():
    # 1、建立连接这里的参数有点多 host:主机ip,port:端口,user:用户,password:密码,db:要连接的数据库,charset:字符集
    conn = pymysql.connect(host='39.108.7.88', port=3306,
                           user='root', password='123456',
                           db='cloudDB01', charset='utf8',
                           cursorclass=pymysql.cursors.DictCursor)  # 以字典的方式将元素取出

    # 2、拿到游标对象，发送sql语句
    try:
        with conn.cursor() as cursor:
            cursor.execute('select deptno,dname,db_source from dept')

            for row in cursor.fetchall():
                dept = Dept(**row)  # 解包语法
                print(dept)

    except Exception as e:
        print(f'失败,原因: {e}')
        conn.rollback()  # 失败就要回滚
    finally:
        conn.close()


if __name__ == '__main__':
    main()