用Python操作MySQL

偏执的太偏执、 2021-10-29 07:14 199阅读 0赞

# pymysql #

在Python编译器中执行SQL

1 import pymysql    #导入模块
     2 
     3 user = input("username:")
     4 pwd = input("password:")
     5 
     6 #连接数据库
     7 conn = pymysql.connect(host='localhost',port = 3306,user = 'root',password = '',database = 'db1')       
     8 
     9 cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)  #打印返回字典模式
    10 sql = 'select * from userinfo where username = %s and password = %s'
    11 cursor.execute(sql,user,pwd)
    12 #cursor.execute(sql,[user,pwd])
    13 #cursor.execute(sql,{'u':user,'p':pwd})
    14 result = cursor.fetchaone()     #获取第一行数据
    15 cursor.close()     #关闭游标
    16 conn.close()       #关闭连接
    17 if result:
    18     print('登录成功')
    19 else：
    20     print('登录失败')

获取新创建数据自增ID

1 import pymysql    #导入模块
     2  
     3  
     4  #连接数据库
     5 conn = pymysql.connect(host='localhost',port = 3306,user = 'root',password = '',database = 'db1')       
     6 
     7 cursor = conn.cursor()
     8 cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
     9 conn.commit()
    10 cursor.close()
    11 conn.close()
    12   
    13 # 获取最新自增ID
    14 new_id = cursor.lastrowid

获取查询数据

1 import pymysql
     2 conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',password = '',database = 'db1')
     3 cursor = conn.cursor()
     4 cursor.execute('select * from score')
     5 
     6 #获取第一行数据
     7 ret = cursor.fetchone()
     8 
     9 #获取钱n行数据
    10 ret = cursor.fetchmany(3)
    11 
    12 #获取所有数据
    13 ret = cursor.fetchall() 
    14 
    15 conn.commit()
    16 cursor.close()
    17 conn.close()

几个含有one，many的用法和commit

1 #execute
     2 row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
     3 #executemany     (只与insert into    搭配)
     4 row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
     5 
     6 #fetchone    获取第一行数据
     7 
     8 #fetchmany（）  获取前n个数据
     9 
    10 #fetchall        获取所有数据    
    
       修改数据库是必须要用到commit才能提交成功

存储过程

存储带参数

import pymysql
    
    conn = pymysql.connet(host = 'localhost',username = 'root',password ='',database = 'db1')
    cursor = conn.cursor(cursor = pymysql.cursor.DicCursor)
    cursor.callproc('p4',(12,2,3,4))
    r1 = cursor.fetchall()
    print(r1)
    
    
    cursor.execute('select @_p4_0,@_p4_1,@_p4_2,@_p4_3')
    r2 = cursor.fetchall()
    print(r2)
    
    cursor.close()
    conn.close()

查找最新插入数据的ID（lastrowid）

# 导入pymysql模块
    import pymysql
    # 连接database
    conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
    username = "Alex"
    age = 18
    try:
        # 执行SQL语句
        cursor.execute(sql, [username, age])
        # 提交事务
        conn.commit()
        # 提交之后，获取刚插入的数据的ID
        last_id = cursor.lastrowid
    except Exception as e:
        # 有异常，回滚事务
        conn.rollback()
    cursor.close()
    conn.close()

转载于:https://www.cnblogs.com/yandw/p/10871476.html