0129 pymysql库练习

import pymysql
#创建链接（桥）
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='py2101',charset='utf8')
#创建游标（干活的人）
cursor = conn.cursor()

sql = "select * from salary_emp"

results = cursor.execute(sql)

print(results)

cursor.close()

conn.close()

sql注入
import pymysql

username= input("请输入用户名：").strip()
password = input("请输入密码：").strip()
#创建链接（桥）
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='py2101',charset='utf8')
#创建游标（干活的人）
cursor = conn.cursor()
# sql = "select * from user where username='%s' and password='%s'"%(username,password)
sql = "select * from user where username=%s and password=%s"
print(sql)
results = cursor.execute(sql,[username,password])
print(results)
cursor.close()
conn.close()
if results:
    print("登录成功")
else:
    print("登录失败")



import pymysql

#创建链接（桥）
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='py2101',charset='utf8')
#创建游标（干活的人）
cursor = conn.cursor()
# sql = "select * from user where username='%s' and password='%s'"%(username,password)
# sql = "insert into user values('lym','123')"
sql = "update user set password='250' where username='lym'"
print(sql)
results = cursor.execute(sql)  #影响数据的条数
print(results)

conn.commit()
# conn.rollback()
# sql1 = "select * from user"
#
# results1 = cursor.execute(sql1)  #影响数据的条数
# print(results1)

cursor.close()
conn.close()

import pymysql
def add_del_update(sql):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='py2101', charset='utf8')
    cursor = conn.cursor()
    try:
        results = cursor.execute(sql)
    except:
        conn.rollback()
        print("操作失败")
    print(cursor.lastrowid)#获取自增id
    conn.commit()
    cursor.close()
    conn.close()
    return results

sql = "insert into user(username,password) values('fq','123')"
add_del_update(sql)




import pymysql

#创建链接（桥）
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='py2101',charset='utf8')
#创建游标（干活的人）
# cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "select * from salary_emp "

cursor.execute(sql)  #影响数据的条数

# results = cursor.fetchall() #二维元组

# results = cursor.fetchone() #元组

results = cursor.fetchmany(2)
results1 = cursor.fetchmany(2)
print(list(results))
print(list(results1))

cursor.close()
conn.close()


import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',charset='utf8')

cursor = conn.cursor()

sql = "create database py2101nb character set utf8"

cursor.execute(sql)  #影响数据的条数

cursor.execute("use py2101nb") #使用该数据库

cursor.execute("create table user(id int PRIMARY KEY ,uname VARCHAR(20))")

cursor.close()
conn.close()