• pymysql


    pymysql

    介绍

    pymysql是python操作MySQL的第三方数据库

    因为是第三方,所以在使用之前需要进行安装

    pip install pymysql

    使用

    执行sql语句

    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 创建游标
    cursor = conn.cursor()
    
    # 执行sql语句
    cursor.execute('create table tb2(id int auto_increment key,name varchar(20) not null,age int default 18)')
    
    # 确认执行
    conn.commit()
    
    # 关闭
    cursor.close()
    conn.close()
    View Code

    插入数据

    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 创建游标
    cursor = conn.cursor()
    
    sql = 'insert into tb2(name,age) VALUES (%s,%s)'
    # 单条插入
    cursor.execute(sql,('kidd',15))
    
    # 多条插入
    more = [('one',1),('two',2),('three',3)]
    cursor.executemany(sql,more)
    
    # 确认执行
    conn.commit()
    
    # 关闭
    cursor.close()
    conn.close()
    View Code

    删除数据

    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 创建游标
    cursor = conn.cursor()
    
    # 执行语句
    sql = 'delete from tb2 where name = %s'
    cursor.execute(sql,'kidd')
    
    # 确认执行
    conn.commit()
    
    # 关闭
    cursor.close()
    conn.close()
    View Code

    更新数据

    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 创建游标
    cursor = conn.cursor()
    
    # 执行语句
    sql = 'update tb2 set age=%s where name=%s'
    cursor.execute(sql,(111,'one'))
    
    # 确认执行
    conn.commit()
    
    # 关闭
    cursor.close()
    conn.close()
    View Code

    查看数据

    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 创建游标
    cursor = conn.cursor()
    
    # 执行语句
    sql = 'select * from tb2 limit 3'
    cursor.execute(sql)
    
    # 查看内容
    result = cursor.fetchall() # fetchone,fetchmany
    print(result)
    
    # 关闭
    cursor.close()
    conn.close()
    View Code
    import pymysql
    
    # 进行连接
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database')
    
    # 设置游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    
    # 执行语句
    sql = 'select * from tb2 limit 3'
    cursor.execute(sql)
    
    # 查看内容
    result = cursor.fetchone()
    print(result)
    
    # 关闭
    cursor.close()
    conn.close()
    设置游标

    创建视图,view

    import pymysql
    # 连接Mysql
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='school')
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 创建视图,并查看
    sql = 'create view v1 as select sname,caption from student left join class on class_id = cid'
    cursor.execute(sql)
    sql = 'select * from v1'
    cursor.execute(sql)
    #结果
    result = cursor.fetchall()
    # 打印
    print(result)
    # 关闭
    cursor.close()
    conn.close()
    View Code

    获取存储过程

    import pymysql
    # 连接Mysql
    conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='school')
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 调用存储过程
    cursor.callproc('f1',args=(1,2,'kidd'))
    # 取值
    cursor.execute('select @f_1_0,@f_1_1,@f_1_2')
    # 结果
    result = cursor.fetchall()
    # 打印
    print(result)
    # 关闭
    cursor.close()
    conn.close()
    View Code

    注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:

    • cursor.scroll(1,mode='relative')  # 相对当前位置移动
    • cursor.scroll(2,mode='absolute') # 相对绝对位置移动

    获取最新自增ID

    new_id = cursor.lastrowid

     

  • 相关阅读:
    单元測试和白盒測试相关总结
    数据结构:图的实现--邻接矩阵
    Android提示版本号更新操作流程
    《集体智慧编程》代码勘误:第六章
    LINUX设备驱动程序笔记(三)字符设备驱动程序
    数学定理证明机械化的中国学派(II)
    《Java并发编程实战》第三章 对象的共享 读书笔记
    Linux系列-安装经常使用软件
    Kubuntu 初始配置
    虚拟互换(virtual swap)
  • 原文地址:https://www.cnblogs.com/py-peng/p/11278265.html
Copyright © 2020-2023  润新知