pymysql

1. pymysql连接数据库

import pymysql

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

# 创建数据库连接对象
conn = pymysql.connect(host="127.0.0.1",user='root',password='',database="db03")
# 创建游标，执行sql语句需要创建游标
cursor = conn.cursor()
sql = "select * from t01 where name='%s' and chengji=%s"
# 防止sql注入，不应该在编写sql字符串命令时传入用户名，密码
# 应该在执行excute时传入用户名，密码
# select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
# 执行sql语句
cursor.execute(sql,(user,pwd))
# 获取查询数据
result = cursor.fetchone()
print(result)
# 执行完之后，需要关闭游标
cursor.close()
# 关闭连接
conn.close()

2.执行SQL语句

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"
# 通过excute函数执行sql语句时，可以在excute函数内传入占位符数据，
# 可以通过填写的方式，或者通过列表的方式，也可以通过字典的方式
cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username=%(u)s and password=%(p)s"
# cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
cursor.close()
conn.close()

3. 数据库增，删，改，查

import pymysql

# 增加，删，改
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('root','123123')"
# 如果需要返回值，返回受影响行数
cursor.execute(sql)
# r = cursor.execute(sql)
# 执行完excute函数之后，需要执行commit函数提交
conn.commit()
cursor.close()
conn.close()

# 可以执行excutemany函数插入多行
# 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') # 相对绝对位置移动
# 获取一行数据，每次执行fetchone函数时，都会按顺序向下获取
# 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()
# 获取最后一行的id
# print(cursor.lastrowid)
# cursor.close()
# conn.close()