08、python操作mysql

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.注册登录功能必须脱稿完成