• 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

     

  • 相关阅读:
    HDFS Namenode 高可用
    旁路模式,numa、mmu、调整内存分页大小
    k8s 调度 GPU
    破解 CSDN 登录后才能复制
    微信小程序开发video遮罩功能(禁止拖动进度条)
    微信小程序之下拉刷新
    微信小程序JS中文排序
    vue2 和 vue3 路由使用对比
    Golang 操作mongo
    win10安装CUDA和cuDNN的正确姿势
  • 原文地址:https://www.cnblogs.com/py-peng/p/11278265.html
Copyright © 2020-2023  润新知