pymysql模块操作数据库
一、连接数据库登录验证示例
import pymysql username = input("username:") password = input("password:") conn = pymysql.connect( # 连接数据库 user="root", password="", host="localhost", port=3306, # 端口号是数字类型，不要加引号！！！ database="TestDB", charset="utf8") # charset="utf8",不要写成 utf-8 ！！！ cursor = conn.cursor() # 获取输入sql语句的光标对象 sql = "select * from userinfo where username=%s and password=%s" ret = cursor.execute(sql, (username, password)) # 执行sql语句 # sql = "select * from userinfo where username=%(user)s and password=%(pwd)s" # ret = cursor.execute(sql, {"user": username, "pwd": password}) if ret: print("Connection succeeded") else: print("Connection failed") cursor.close() # 关闭光标对象 conn.close() # 关闭连接
二、基本使用
2.1 创建数据库
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = """create table xx( id int auto_increment primary key, name char(10) not null unique, age int not null )engine=innodb default charset=utf8""" cursor.execute(sql) cursor.close() db_conn.close()
2.2 查找数据时返回字典格式的数据
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor(pymysql.cursors.DictCursor) sql = "select * from userinfo" cursor.execute(sql) ret = cursor.fetchall() print(ret) cursor.close() db_conn.close()
如果只是 cursor = conn.cursor() ，没有加上上面那些代码，返回的是元祖格式的数据：
三、pymysql增删改查操作
3.1 增 insert
①普通版插入数据
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "insert into userinfo(username, password) values(%s,%s)" username = "peter" password = 123456 cursor.execute(sql,(username,password)) db_conn.commit() # 提交事务！！！一定要记得写！！！ cursor.close() db_conn.close()
②插入数据失败回滚
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "insert into userinfo(username, password) values(%s,%s)" username = "peter" password = 123 try: cursor.execute(sql,(username,password)) db_conn.commit() # 提交事务 except Exception as e: print("Error:%s" % e) db_conn.rollback() # 有异常，回滚事务 finally: cursor.close() db_conn.close()
因为设计表的时候，用户名（username）设置唯一，且上面已经插入一条用户名为 peter 的用户，所以再次插入相同的用户名将会失败，所以使用了异常处理以及回滚事务。或者当用户名和密码，即使其中一个为空的时候，插入数据也会不成功。
③获取刚插入数据的ID值（关联操作要用到）
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql_cla = "insert into class (name) values(%s)" sql_stu = "insert into student (name, classid) values(%s,%s)" try: cursor.execute(sql_cla,"跆拳道") pre_id = cursor.lastrowid # 获取刚才插入数据的ID值 cursor.execute(sql_stu,("女汉子",pre_id)) db_conn.commit() # 提交事务 except Exception as e: db_conn.rollback() # 有异常，回滚事务 finally: cursor.close() db_conn.close()
课程表：
学生表：
可以看到课程表有刚插入的跆拳道课程，同时学生表有一个叫做"女汉子"的学生关联到了相应的课程ID。
④批量插入数据
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "insert into student (name, classid) values(%s,%s)" data = (("A","1"),("B","2"),("C","3")) try: ret = cursor.executemany(sql,data) # 插入多条数据；ret为受影响的行数 print(ret) # 3 db_conn.commit() # 提交事务 except Exception as e: db_conn.rollback() # 有异常，回滚事务 finally: cursor.close() db_conn.close()
3.2 删 delete
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "delete from student where name=%s" name = "女汉子" try: cursor.execute(sql,name) db_conn.commit() except Exception as e: db_conn.rollback() finally: cursor.close() db_conn.close()
3.3 改 update
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "update student set name=%s, classid=%s where id=%s" name = "女汉子" classid = 3 id = 2 try: cursor.execute(sql,(name,classid,id)) db_conn.commit() except Exception as e: db_conn.rollback() finally: cursor.close() db_conn.close()
3.4 查 select
①从查到的结果中取单条数据 fetchone()
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "select * from student where id=1" cursor.execute(sql) ret1 = cursor.fetchone() # fetchone只取一条数据 print(ret1) # (1, '学生1', 1) ret2 = cursor.fetchone() # 再取数据，取不到不会报错，返回None print(ret2) # None cursor.close() db_conn.close()
②从查到的结果中取多条数据 fetchmany()
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "select * from student where id<10" cursor.execute(sql) ret1 = cursor.fetchone() # fetchone只取一条数据 print(ret1) # (1, '学生1', 1) ret2 = cursor.fetchmany(3) print(ret2) # ((2, '学生2', 1), (3, '学生3', 2), (4, '学生4', 2)) cursor.close() db_conn.close()
③从查到的结果中取全部数据 fetchall()
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "select * from student where id<=10" cursor.execute(sql) ret1 = cursor.fetchone() # fetchone只取一条数据 print(ret1) # (1, '学生1', 1) ret2 = cursor.fetchmany(3) print(ret2) # ((2, '学生2', 1), (3, '学生3', 2), (4, '学生4', 2)) ret3 = cursor.fetchall() print(ret3) # ((5, '学生5', 3), (6, '学生6', 3), (7, '学生7', 4), (8, '学生8', 4), (9, '学生9', 5), (10, '学生10', 5)) cursor.close() db_conn.close()
④其他操作
cursor.scroll(3,mode="absolute") # 相对绝对位置移动光标，写多少就是移到多少 cursor.scroll(2,mode="relative") # 相对当前光标位置再往后移动多少
绝对位置移动光标取数据示例
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "select * from student" cursor.execute(sql) cursor.scroll(0,mode="absolute") # 从0开始取，即表中的ID=1的位置 ret1 = cursor.fetchmany(2) # 往后取2个数据 print(ret1) # ((1, '学生1', 1), (2, '学生2', 1)) cursor.scroll(2,mode="absolute") # 从2开始取，即表中的ID=3的位置 ret2 = cursor.fetchmany(2) # 往后取2个数据 print(ret2) # ((3, '学生3', 2), (4, '学生4', 2)) cursor.close() db_conn.close()
相对当前位置移动光标取数据示例
import pymysql db_conn = pymysql.connect( user="root", password="", host="localhost", port=3306, database="TestDB", charset="utf8") cursor = db_conn.cursor() sql = "select * from student" cursor.execute(sql) ret1 = cursor.fetchmany(3) print(ret1) # ((1, '学生1', 1), (2, '学生2', 1), (3, '学生3', 2)) cursor.scroll(2,mode="relative") # 本来光标在ID=3位置的后面，然后相对当前位置往后移动了2位，即光标现在处于ID=5位置的后面 ret2 = cursor.fetchmany(2) # 往后取2个数据，即取ID=6,7位置的数据 print(ret2) # ((6, '学生6', 3), (7, '学生7', 4)) cursor.close() db_conn.close()
