import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='homework')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#查询返回字典
cur = conn.cursor() # cursor游标,默认返回元组
try:
cur.execute('select * from students')
ret = cur.fetchone() # 获取结果
print(ret)
ret1 = cur.fetchmany(10)
print(ret1)
ret3 = cur.fetchall()
print(ret3)
except pymysql.err.ProgrammingError as e:
print(e)
cur.close()
conn.close()
插入数据和更新数据
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='homework')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#查询返回字典
cur = conn.cursor() # cursor游标,默认返回元组
try:
cur.execute('insert into student values (18,"男",3,"大壮")')
cur.execute('update student set gender ="女" where sid=17')
conn.commit()
except Exception as e:
print(e)
conn.rollback() # 一旦报错,语句就不执行了
cur.close()
conn.close()
删除数据
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='homework')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)#查询返回字典
cur = conn.cursor() # cursor游标,默认返回元组
try:
cur.execute('delete from student where sid=17')
conn.commit()
except Exception as e:
print(e)
conn.rollback() # 一旦报错,语句就不执行了
cur.close()
conn.close()
pymsql
python操作mysql数据库
连接数据库
获取游标
执行sql(增删改查)
如果涉及到修改:提交
关闭游标
关闭库
sql注入
传参数,注意sql注入问题,传参数通过execute方法来传
# 实际操作mysql时候遇到的问题
# 结合数据库和python写一个登录
username password
import pymysql
user = input('username:')
pwd = input('password:')
conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='homework')
sql = 'select *from userinfo where user="%s" and password="%s";' % (user, pwd)
# sql = 'select *from userinfo where user=%s and password=%s'
print(sql)
cur = conn.cursor()
cur.execute(sql, (user, pwd))
print(cur.fetchone())
# sql注入问题
# select *from userinfo where user="1869" or 1=1;--"and password="3714";
# --会把后面的都注释掉
# select *from userinfo where user="alex";--"and password="3714";
sql事务
begin 开启事务,然后for update 加锁
打开另一个窗口继续执行,会需要等上面释放掉才能查询
commit后如果上面的没超时,才会显示出来。
数据库备份和恢复
表和数据的备份
备份数据 在cmd 命令行直接执行
mysqldump **-uroot -p123 -h127.0.0.1 homework > E:\Users\sturbon\python22\mysql\tmp.sql
恢复数据 在sql里执行命令
切换到一个要备份的数据库中 执行
source E:\Users\sturbon\python22\mysql\tmp.sql
不要加;
备份库
mysqldump -uroot -p123 --databases homework >E:\Users\sturbon\python22\mysql\tmp1.sql
不要加;