pymysql是python提供的一个mysql客户端模块,用于与mysql服务器建立连接,发送查询,并获取结果等;
基本使用:
import pymysql
# 1.建立连接
try:
conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
print("连接服务器成功!")
#2.获取游标对象
cursor = conn.cursor()
#3.执行sql语句
count = cursor.execute("select *from user")
print("结果数量: %s" % count)
# 提取结果
# print(cursor.fetchall())
# print(cursor.fetchone())
# print(cursor.fetchmany(1))
# 移动游标位置 相对当前位置
cursor.scroll(1,"relative")
cursor.scroll(-1, "relative")
print(cursor.fetchone())
# 移动游标位置 使用绝对位置
cursor.scroll(0, "absolute")
print(cursor.fetchone())
print(cursor.fetchall())
# 注意 游标移动到末尾后无法在读取到数据 若需重复读取数据,需要使用scroll来移动游标
except Exception as e:
print("连接服务器失败.....")
print(type(e),e)
finally:
if cursor:
cursor.close()
print("关闭游标")
if conn:
conn.close()
print("关闭链接")
为了防止SQL注入攻击,我们使用pymsql封装好的功能,下面是改良版
try:
conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
print("连接服务器成功!")
cursor = conn.cursor(pymysql.cursors.DictCursor)
user = input("username:")
password = input("password:")
sql = "select *from user where name = %s and password = %s"
print(sql)
count = cursor.execute(sql,(user,password)) # 参数交给模块
if count:
print("登录成功!")
else:
print("登录失败!")
except Exception as e:
print(type(e),e)
finally:
if cursor:cursor.close()
if conn: conn.close()