用Python操作MySQL

pymysql

 

在Python编译器中执行SQL

import pymysql    #导入模块

user = input("username:")
pwd = input("password:")

#连接数据库
conn = pymysql.connect(host='localhost',port = 3306,user = 'root',password = '',database = 'db1')       

cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)  #打印返回字典模式
sql = 'select * from userinfo where username = %s and password = %s'
cursor.execute(sql,user,pwd)
#cursor.execute(sql,[user,pwd])
#cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchaone()     #获取第一行数据
cursor.close()     #关闭游标
conn.close()       #关闭连接
if result:
    print('登录成功')
else：
    print('登录失败')

获取新创建数据自增ID

import pymysql    #导入模块
 
 
 #连接数据库
conn = pymysql.connect(host='localhost',port = 3306,user = 'root',password = '',database = 'db1')       

cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
  
# 获取最新自增ID
new_id = cursor.lastrowid

获取查询数据

import pymysql
conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',password = '',database = 'db1')
cursor = conn.cursor()
cursor.execute('select * from score')

#获取第一行数据
ret = cursor.fetchone()

#获取钱n行数据
ret = cursor.fetchmany(3)

#获取所有数据
ret = cursor.fetchall() 

conn.commit()
cursor.close()
conn.close()

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

#execute
row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
#executemany     (只与insert into    搭配)
row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

#fetchone    获取第一行数据

#fetchmany（）  获取前n个数据

#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()