import pymysql
1.登录mysql
conn = pymysql.connect(host= ‘127.0.0.1‘,user=‘root‘,password=‘123‘,database=‘homework‘)
2.获取游标
cur = conn.cursor()
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#查询结果返回字典
3.查select
# cur.execute(‘select * from student‘)
# ret = cur.fetchone()#获取游标后第一条值
# print(ret)
# ret = cur.fetchmany(10)#返回元组,获取游标后多条
# print(ret)
# ret = cur.fetchall()#返回元组,获取游标后全部
# print(ret)
4.增,删,改
# try:
# cur.execute(‘delete from student where sid = 17‘)
# cur.execute(‘insert into student values(17,"男",3,"大壮")‘)
# cur.execute(‘update student set gender="女" where sid = 17‘)
# conn.commit()
# except Exception as e:
# print("Error:",e)
# conn.rollback()
# print(‘操作完成!‘)
5.rowcount
这是一个只读属性，并返回执行execute()方法后影响的行数。
# cur.execute(‘select * from student‘)
# print(cur.rowcount)
# for i in range(cur.rowcount):
# print(cur.fetchone())
6. 写入用户信息
# sql = ‘create table userinfo(id int unsigned primary key auto_increment,name char(12),password char(20)) ‘
# cur.execute(sql)
# sql = ‘insert into userinfo(name,password) values("hanshiqu","sss123")‘
# cur.execute(sql)
# conn.commit()
7.用户登录系统
# name = input("用户名:")
# pwd = input("密码:")
# l = ‘select * from userinfo where name = %s and password = %s‘
# cur.execute(l,(name,pwd))
# ret = cur.fetchone()
# print(ret[1])
8.事务锁
# begin; # 开启事务
# select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务
# name = input("用户名:")
# worklock_list = [‘begin‘,‘select * from userinfo where name = "%s" for update‘%name,‘update userinfo set password = "Iloveu" where id = 1‘]
# for work in worklock_list:
# cur.execute(work)
# # conn.commit()
# # print(‘修改完成!‘)
# sql = ‘select * from userinfo where id=1‘
# cur.execute(sql)
# print(cur.fetchone()[2])
# conn.commit()
9.关闭调用
调用完数据库,最后需要关闭调用的游标/链接
cur.close()
conn.close()