mysql操作之pymsql

登入数据库：

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)
# select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

if result:
    print('登录成功')
else:
    print('登录失败')

防SQL注入（execute）：

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
    print('登录成功')
else:
    print('登录失败')

增删改查：

import pymysql

增加，删，该
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('root','123123')"
受影响的行数
r = cursor.execute(sql)
#  ******
conn.commit()
cursor.close()
conn.close()

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
# sql = "insert into userinfo(username,password) values(%s,%s)"
# cursor.execute(sql,(user,pwd,))

sql = "insert into userinfo(username,password) values(%s,%s)"
# 受影响的行数
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
#  ******
conn.commit()
cursor.close()
conn.close()




查
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo"
cursor.execute(sql)

cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchall()
print(result)


result = cursor.fetchmany(4)
print(result)
cursor.close()
conn.close()




新插入数据的自增ID: cursor.lastrowid
import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)
cursor.close()
conn.close()

查看新增数据ID:

import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)
cursor.close()
conn.close()