连接数据库
conn = pymysql.connect(host='127.0.0.1', user='root', password='root', database='demo')
cur = conn.cursor() # 游标
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # 获取字典形式数据
#cur.rowcount() # 获取查出多少行,便于fetchone()取所有数据
cur.execute("select * from student") # sql语句
cur.close()
conn.close()
增删改查
# 查询
cur.rowcount() # 获取查出多少行,便于fetchone()取所有数据
for i in range(cur.rowcount):
ret = cur.fetchone()
print(ret)
# 获取数据会有游标记录,如下面会获取第1条数据、获取2到第11条、获取12到最后数据
ret = cur.fetchone() # 获取一条数据
print(ret)
ret1 = cur.fetchmany(10) # 获取指定数量数据
print(ret1)
ret2 = cur.fetchall() # 获取所有数据
print(ret2)
# 增加、删、该
try:
cur.execute('insert into student values(21,"路飞",18)') # 添加数据
cur.execute('update student set name = "布鲁克" where id = 17') # 修改数据
cur.execute('delete from student where id = 16') # 删除数据
conn.commit() # 提交数据库
except Exception as e:
print(e)
conn.rollback() # 错误回滚
sql注入
# 数据注入
usr = input("user")
pwd = input("pwd")
cur = conn.cursor()
sql = "select * from login where user = %s and password = %s" # 不要在这里进行数据拼接
cur.execute(sql, (usr, pwd)) # 防止sql注入
print(cur.fetchone())