pymysql模块 sql注入

连接数据库:

 1 import pymysql
 2 
 3 conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
 4                        database='homework')
 5 cur = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 查询返回字典  # 游标
 6 try:
 7     cur.execute('select * from students')
 8     ret1 = cur.fetchone()  #  获取所有
 9     print(ret1)
10     ret = cur.fetchmany(5)  # 获取多条结果
11     print(ret)
12 except Exception as f:
13     print(f)
14 cur.close()
15 conn.close()

数据的插入:

 1 import pymysql
 2 
 3 conn = pymysql.connect(host='127.0.0.1',user='root',password='123',database='homework')
 4 cur = conn.cursor()
 5 try:
 6     # cur.execute('insert into student values (30,"男",3,"大壮")')
 7     # cur.execute('insert into student values (31,"男",3,"大壮")')
 8     cur.execute('update student set sid=33 where sid=16')
 9     conn.commit()   #提交,数据的提交,否则只在内存中执行了。
10 except Exception as f:
11     print(f)
12     conn.rollback()  # 之前那一条不执行。
13 cur.close()
14 conn.close()

数据的删除:

 1 import pymysql
 2 
 3 conn = pymysql.connect(host='127.0.0.1',user='root',password='123',database='homework')
 4 cur = conn.cursor()
 5 try:
 6     # cur.execute('insert into student values (30,"男",3,"大壮")')
 7     # cur.execute('insert into student values (31,"男",3,"大壮")')
 8     # cur.execute('update student set sid=33 where sid=16')
 9     cur.execute('delete from student where sid=33')
10     conn.commit()   #提交,数据的提交,否则只在内存中执行了。
11 except Exception as f:
12     print(f)
13     conn.rollback()  # 之前那一条不执行。
14 cur.close()
15 conn.close()

rowcount函数:

1     cur.execute('select * from student')
2     print(cur.rowcount) # 能查出来多少行,便于使用fetchone取所有结果
3     for i in range(cur.rowcount):
4         print(cur.fetchone())
5     conn.commit()   #提交,数据的提交,否则只在内存中执行了。
1 select * from userinfo where user="alex";-- --" and password = "123";

防止sql注入:

 1 import pymysql
 2 
 3 
 4 username = input('请输入用户名:')
 5 password = input('请输入密码:')
 6 conn = pymysql.connect(host='127.0.0.1',user='root',password='123',database='day42')
 7 cur = conn.cursor()
 8 sql = 'select * from userinfo where user=%s and password = %s'
 9 cur.execute(sql,(username,password))# 防止sql注入
10 print(cur.fetchone())

总结
pymysql

    pymysql操作mysql数据库

        连接数据库

        获取游标

        执行sql

        如果涉及到修改:提交

        关闭游标

        关闭库

    sql注入

        传参数,注意sql注入的问题,传参数通过execute方法来传

        execute(select    *   from    表   where   name =%s',(alex))

事务和锁

begin;开启事务

select   *   from    emp     where   id=1    for    update;    查询id值,for    update;   添加行锁

update    emp     set    salary = 10000    where    id  =1 ;      完成更新

commit;      提交事务

 

上一篇:MySQL数据库与python交互


下一篇:使用Python循环插入10万数据