08、python操作mysql
一、python连接数据库
import pymysql
db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接
cursor.execute("SELECT VERSION()") # 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone() # 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close() # 关闭数据库连接
import pymysql
conn = pymysql.connect(
host='localhost',
user='root',
password="root",
database='db',
port=3306, charset='utf-8',
)
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
二、创建表操作
import pymysql
# 打开数据库连接
db = pymysql.connect(
"localhost",
"testuser",
"test123",
"TESTDB"
)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
三、操作数据
1、插入操作
import pymysql
# 打开数据库连接
db = pymysql.connect(
"localhost",
"testuser",
"test123",
"TESTDB"
)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cursor.execute(sql) # 执行sql语句
db.commit() # 提交到数据库执行
except:
db.rollback() # 如果发生错误则回滚
# 关闭数据库连接
db.close()
import pymysql
# 打开数据库连接
db = pymysql.connect(
"localhost",
"testuser",
"test123",
"TESTDB"
)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES (%s, %s, %s, %s, %s )" % \
('Mac', 'Mohan', 20, 'M', 2000)
try:
cursor.execute(sql) # 执行sql语句
db.commit() # 执行sql语句
except:
db.rollback() # 发生错误时回滚
# 关闭数据库连接
db.close()
2、查询操作
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
-
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
-
fetchall(): 接收全部的返回结果行.
-
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
cursor.execute(sql)# 执行SQL语句
results = cursor.fetchall()# 获取所有记录列表
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
# 关闭数据库连接
db.close()
3、更新操作
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
cursor.execute(sql) # 执行SQL语句
db.commit() # 提交到数据库执行
except
db.rollback() # 发生错误时回滚
# 关闭数据库连接
db.close()
4、删除操作
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try
cursor.execute(sql) # 执行SQL语句
db.commit() # 提交修改
except
db.rollback() # 发生错误时回滚# 关闭连接
db.close()
四、数据备份
1、数据库的逻辑备份
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例： #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
2、数据恢复
#方法一： [root@jason backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql
五、事务和锁
begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值，for update添加行锁； update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务
###
六、python操作MySQL
python 胶水语言、调包侠(贬义词>>>褒义词)
"""
python这门语言本身并不牛逼 牛逼的是支持该语言的各种功能强大的模块、软件
"""
# 后期在使用python编程的时候 很多看似比较复杂功能可能都已经有相应的模块
模块名字 pymysql
下载模块
1.命令行
pip3 install pymysql
pip3 install pymysql -i 源地址
2.借助于pycharm
3.python解释器配置文件
1、模块基本使用
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db_5',
charset='utf8'
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
cursor.execute(sql)
# 获取返回结果
res = cursor.fetchall()
print(res)
2、SQL注入问题
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db_5',
charset='utf8'
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 构造SQL语句
# sql = "select * from userinfo where name='%s' and password='%s'"%(username,password)
# 针对核心数据 不要自己拼接 交由execute方法帮你筛选再拼接
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
# 执行SQL语句
cursor.execute(sql,(username,password))
res = cursor.fetchall()
if res:
print(res)
print('登录成功')
else:
print('用户名或密码错误')
"""
登录功能
1.获取用户名和密码
2.基于用户名和密码直接精准查找
"""
"""
问题1
用户名正确 不需要密码也能登录
问题2
用户名和密码都不需要也能登录
SQL注入问题的产生
就是通过一些特殊符号的组合 达到某些特定的效果从而避免常规的逻辑
SQL注入问题如何解决
execute方法自动帮你解决
"""
3、功能补充
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db_5',
charset='utf8',
autocommit=True # 涉及到增删改 自动二次确认
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
sql1 = 'select * from userinfo'
sql2 = 'insert into userinfo(name,password) values(%s,%s)'
sql3 = 'update userinfo set name="jasonNB" where id=1'
sql4 = 'delete from userinfo where id=2'
# 1.查询语句可以正常执行并获取结果
# cursor.execute(sql1)
# 2.插入语句能够执行 但是并没有影响表数据
# cursor.execute(sql2,('jackson',666))
# 3.更新语句能够执行 但是并没有影响表数据
# res = cursor.execute(sql3)
# print(res)
# 4.删除语句能够执行 但是并没有影响表数据
# res = cursor.execute(sql4)
# print(res)
'''针对增删改操作 需要二次确认才可生效'''
# cursor.execute(sql2,('jackson',666))
# conn.commit()
# cursor.execute(sql3)
# conn.commit()
# cursor.execute(sql4)
# conn.commit()
cursor.executemany(sql2,[('jason111',123),('jason222',321),('jason333',222)])
# 主动关闭链接 释放资源
# conn.close()
4、注册登录功能
def register(cursor):
# 获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 验证用户名是否已存在
sql = 'select * from userinfo where name=%s'
cursor.execute(sql, (username,))
res = cursor.fetchall()
if not res:
sql1 = 'insert into userinfo(name,password) values(%s,%s)'
cursor.execute(sql1, (username, password))
print('用户:%s注册成功' % username)
else:
print('用户名已存在')
def login(cursor):
# 获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 先获取是否存在用户名相关的数据
sql = 'select * from userinfo where name=%s'
cursor.execute(sql, (username,))
res = cursor.fetchall() # 得到一个列表套字典
if res:
# 校验密码
real_dict = res[0]
if password == str(real_dict.get('password')): # 密码的类型为整形
print('登录成功')
else:
print('密码错误')
else:
print('用户名不存在')
def get_conn():
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db_5',
charset='utf8',
autocommit=True # 涉及到增删改 自动二次确认
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
return cursor
func_dic = {'1': register, '2': login}
while True:
print("""
1.注册功能
2.登录功能
""")
cursor = get_conn()
choice = input('请输入功能编号>>>:').strip()
if choice in func_dic:
func_name = func_dic.get(choice)
func_name(cursor)
else:
print('暂时没有当前功能编号')
作业
1.整理今日内容并完成博客编写 2.熟练完成多表查询练习题(课上五道题必须搞会) 3.注册登录功能必须脱稿完成
浙公网安备 33010602011771号