pymysql 操作

1, 连接pymysql ,批量处理

# _*_ encoding:utf-8 _*_
import pymysql
#  连接数据库
conn = pymysql.connect(host='10.21.252.61', port=13306, user='root', password='root1234', database='opp_insp', charset='utf8')

#拿到游标
cursor = conn.cursor()
import re
 # = re.compile()
add_field_list = []
i = 0
with open('hp.txt', 'r', encoding='utf-8') as f:
    for line in f.readlines():
        curLine=line.strip().split("\t")
        # print(curLine, type(curLine))
        # ip = curLine[0]
        # storag_name = curLine[1]
        # serial_number = curLine[2]
        add_field_list.append([curLine[0], curLine[1], curLine[2]])
        if len(curLine[0]) > 14:
            print(len(curLine[0]),curLine[2])

        i += 1
print(i,add_field_list)
effect_row = cursor.executemany("UPDATE storage_meta SET storage_category = (%s) ,storage_name = (%s)  WHERE serial_number = (%s)", add_field_list)   #  cursor.executemany 一次性执行多个 ,执行多次

print(i,effect_row,'>>',add_field_list)# conn.commit()    #记住,对于数据库的更改一定要提交,否则数据库不会改动
# # 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
# conn.rollback()
conn.commit()
cursor.close()   # 关闭游标
conn.close()      # 关闭连接

2, 拼接sql 语句,并操作

def isExisted(username,password):
    sql = "select * from user where username = '%s' and password = '%s'" %(username,password)
    print(sql)    # 输出 select * from user where username = 'ty888' and password = '888'
    a = cursor.execute(sql)  # 可以不接返回值,有返回值的话是返回条数
    print('你好啊a',a)  #  你好啊a 6
    result = cursor.fetchall()      # #  获取从游标的位置往后的剩余所有记录 
    print('len(result)', len(result))   #  len(result) 6
    print('result',result)  # result ((38, 'ty888', '888'), (39, 'ty888', '888'), (68, 'ty888', '888'), (69, 'ty888', '888'), (70, 'ty888', '888'), (71, 'ty888', '888'))
    for i in result:
        print('first: ',i[0],i[1],i[2])  
    # cursor.execute(sql)
    cursor.scroll(2, mode='absolute')
    result2 = cursor.fetchall()
    print('result:2', result2)
    for i in result2:
        print('大师傅2',i[0], i[1], i[2])
        # 分别输出first:  38 ty888 888
                first:  39 ty888 888
                first:  68 ty888 888
                first:  69 ty888 888
                first:  70 ty888 888
                first:  71 ty888 888

    cursor.scroll(3, mode='absolute')  # 游标回滚到查询到的所有的第3条记录之后
    data = cursor.fetchone()  #  获取从游标的位置往后的剩余1条记录 
    print('data',data)    # 输出 data (69, 'ty888', '888')

    data3 = cursor.fetchone()   # 在data列表之后又获取一条记录
    print('data3', data3)  #输出  data3 (70, 'ty888', '888')

    cursor.scroll(1, mode='absolute')     # 游标回滚到查询到的所有的第1条记录之后 
    row_2 = cursor.fetchmany(3)      #  获取从游标的位置往后的剩余3条记录 
    print('row2', row_2)    #  输出结果 row2 ((39, 'ty888', '888'), (68, 'ty888', '888'), (69, 'ty888', '888'))

    cursor.scroll(-4, mode='relative')       # 以相对位置回滚到往前4条记录, 注意,如果超出范围,汇报IndexError错
    data2 = cursor.fetchone()  #  data2 (38, 'ty888', '888')
    print('data2', data2)   # 输出 data2 (38, 'ty888', '888')
    effect_row1 = cursor.execute("update user set username = 'ty666' where username =  %s", ('8888',)) # 更新,默认会更新所有符合条件的返回受影响的条数
    print('effect_row1',effect_row1)    #  effect_row1 0      

    effect_row = cursor.executemany("insert into user(username,password)values(%s,%s)",[("u1pass", "11111"), ("u2pass", "22222")])   #  cursor.executemany 一次性执行多个 ,执行多次
    print(effect_row) # 返回执行成功的条数

    # 获取自增id 
    new_id  = cursor.lastrowid

    conn.commit()    #记住,对于数据库的更改一定要提交,否则数据库不会改动
# 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
 conn.rollback()



    cursor.close()   # 关闭游标
    conn.close()      # 关闭连接

isExisted('ty888',888)

输出: 
select * from user where username = 'ty888' and password = '888'
你好啊a 6
len(result) 6
result ((38, 'ty888', '888'), (39, 'ty888', '888'), (68, 'ty888', '888'), (69, 'ty888', '888'), (70, 'ty888', '888'), (71, 'ty888', '888'))
first:  38 ty888 888
first:  39 ty888 888
first:  68 ty888 888
first:  69 ty888 888
first:  70 ty888 888
first:  71 ty888 888
result:2 ((68, 'ty888', '888'), (69, 'ty888', '888'), (70, 'ty888', '888'), (71, 'ty888', '888'))
大师傅2 68 ty888 888
大师傅2 69 ty888 888
大师傅2 70 ty888 888
大师傅2 71 ty888 888
data (69, 'ty888', '888')
data3 (70, 'ty888', '888')
row2 ((39, 'ty888', '888'), (68, 'ty888', '888'), (69, 'ty888', '888'))
data2 (38, 'ty888', '888')
effect_row1 0
2

3、fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

import pymysql  

conn  = pymysql.connect(host = '127.0.0.1' , port = 3306 , user = 'root' , passwd = ' ', db=' tkq1') 

#游标设置为字典类型 
cursor  = conn.cursor(cursor = pymysql.cursors.DictCursor) 
cursor.execute( "select * from tb7" )   

row_1  = cursor.fetchone() 

print row_1　　 #{u'licnese': 213, u'user': '123', u'nid': 10, u'pass': '213'}   

# 提交修改
conn.commit() 

# 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
 conn.rollback()


conn.close() 

3,sql注入,记住sql语句的引号和格式化字符串的引号要相呼应

import pymysql
conn = pymysql.connect(host='10.0.0.105', port=3306, user='root', password='666', db='web_yuan', charset='utf8')
cursor = conn.cursor()
user = "u1' or '1'-- "
passwd = "u1pass"
sql = "select username,password from user where username='%s' and password='%s'" % (user, passwd)
# 拼接语句被构造成下面这样，永真条件，此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。
# select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'
print('你好啊sql: ',sql)
row_count = cursor.execute(sql)
row_1 = cursor.fetchone()
print (row_count, row_1)
conn.commit()
cursor.close()
conn.close()
返回: 
你好啊sql:  select username,password from user where username='u1' or '1'-- ' and password='u1pass'
49 ('root', '123456')  # 49条, 第一条数据

或者          记住sql语句的引号和格式化字符串的引号要相呼应
user2 = 'u1" or "1"-- '
passwd = "u1pass"
sql2 = 'select username,password from user where username="%s" and password="%s"'% (user2, passwd)
输出:
你好啊sql:  select username,password from user where username="u1" or "1"-- " and password="u1pass"
48 ('root', '123456')

记住sql语句的引号和格式化字符串的引号要相呼应

2、避免注入，使用pymysql提供的参数化语句 结论：excute执行SQL语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞。

import pymysql
conn = conn = pymysql.connect(host='10.0.0.105', port=3306, user='root', password='666', db='web_yuan', charset='utf8')
cursor = conn.cursor()
user1 = "root"
passwd1 = "123456"
user = "u1' or '1'-- "
passwd = "u1pass"
# 执行参数化查询
sql01 = "select username,password from user where username='%s' and password='%s'" % (user, passwd)
print('sql语句01:',sql01)

sql02 = "select username,password from user where username=%s and password=%s "% (user1, passwd1)
print('sql语句02:',sql02)




row_count = cursor.execute("select username,password from user where username=%s and password=%s", (user1, passwd1))
# row_count = cursor.execute(sql)
row_1 = cursor.fetchone()
print (row_count, row_1)

sql=cursor.mogrify("select username,password from user where username=%s and password=%s",(user,passwd))
print (sql)
conn.commit()
cursor.close()
conn.close()
输出:sql语句01: select username,password from user where username='u1' or '1'-- ' and password='u1pass'
sql语句02: select username,password from user where username=root and password=123456 
7 ('root', '123456')
select username,password from user where username='u1\' or \'1\'-- ' and password='u1pass'

3,使用with简化连接过程

import pymysql
import contextlib
# 定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def mysql(host='10.0.0.105', port=3306, user='root', password='666', db='web_yuan', charset='utf8'):
    print('你好',host,port,user,password,db,charset)
    conn = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db, charset=charset)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        yield cursor
    finally:
        conn.commit()
        cursor.close()
        conn.close()

    # 执行sql

with mysql() as cursor:
    print (cursor)
    row_count = cursor.execute("select * from user")
    row_1 = cursor.fetchone()
    print (row_count, row_1)

部分参考: https://blog.csdn.net/weixin_30295091/article/details/95799939 https://blog.csdn.net/vivian_wanjin/article/details/82778589