Navicat的使用、PDManer、PyMySQL(连接数据库、执行SQL语句、结果获取、插入数据、删除数据、更新数据)

【一】Navicat的使用

【1】连接数据库

打开 Navicat ，找到连接选项

输入连接参数

测试参数

【2】创建数据库

新建数据库

输入数据库参数

新建表并设置主键

修改表字段

【3】导出SQL文件

【4】备份数据库

【5】视图展示

会展示当前数据库下的所有表模型

【二】PDManer(元数建模)

【1】软件官网

https://gitee.com/robergroup/pdmaner/releases

【三】PyMySQL

【1】介绍

纯Python实现的模块，可以与Python代码兼容衔接，并也几乎兼容MySQL-python。
遵循 Python 数据库 API v2.0 规范。
官网：https://zetcode.com/python/pymysql/

【2】安装

# [1] 安装pymysql
pip install pymysql

【3】连接数据库

使用connect函数创建连接对象，此连接对象提供关闭数据库、事务提交、事务回滚等操作。
传入参数有很多，具体参考文档，一般参数基本连接信息 host, user, password, port(默认为3306), database。

（1）连接语法

import pymysql

# [1] 连接数据库所需要知道的参数
"""
user=用户名
password=密码,
host=IP 本地 127.0.0.1 / localhost,
database= 需要连接到哪个数据库,
port=端口,
charset=编码集,
# sql_mode=严格模式,
cursorclass=Cursor 获取查询集的显示结果样式,
connect_timeout=10, 连接超时 默认10秒
autocommit=False 自动执行提交,
passwd=输入密码,  
db=需要连接到哪个数据库,
"""

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4'
)
print(conn)

# 输出结果
<pymysql.connections.Connection object at 0x0000018B69FAFA00>
# 连接成功

（2）主要方法介绍

方法	功能
cursor()	获取游标对象，操作数据库，如执行DML操作，调用存储过程等
commit()	提交事务
rollback()	回滚事务
close()	关闭数据库连接

【4】执行SQL-查询语句

（1）创建游标对象

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

（2）执行SQL语句

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 创建SQL语句
sql = 'select * from emp;'
# 执行
res = cursor.execute(sql)

【5】查询结果获取

（1）获取所有结果（fetchall）

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 创建SQL语句
sql = 'select * from emp;'
# 执行
res = cursor.execute(sql)
# 获取执行SQL语句的结果
# 获取结果的时候类似于管道里面存了结果
# fetchall 一下子将所有数据全部拿出来
result = cursor.fetchall()
print(result)

# 输出结果
[{'id': 1, 'name': 'chosen', 'gender': 'male', 'age': 20, 'hire_date': datetime.date(2024, 5, 29), 'post': 'professor', 'post_comment': None, 'salary': 1000.1, 'office': 501, 'depart_id': 1}, {'id': 2, 'name': 'mengmeng', 'gender': 'female', 'age': 25, 'hire_date': datetime.date(2022, 1, 2), 'post': 'teacher', 'post_comment': None, 'salary': 12000.5, 'office': 401, 'depart_id': 1}, ...]

（2）获取一行数据（fetchone）

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 创建SQL语句
sql = 'select * from emp;'
# 执行
res = cursor.execute(sql)
# 获取执行SQL语句的结果
# 获取结果的时候类似于管道里面存了结
result = cursor.fetchone()  # 返回一条数据
# 获取到一条结果，如果再 fetchone 就会拿到下一条结果
# result = cursor.fetchone()
print(result)

# 输出结果
{'id': 1, 'name': 'chosen', 'gender': 'male', 'age': 20, 'hire_date': datetime.date(2024, 5, 29), 'post': 'professor', 'post_comment': None, 'salary': 1000.1, 'office': 501, 'depart_id': 1}

（3）获取查询指定结果数据量（fetchmany）

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 创建SQL语句
sql = 'select * from emp;'
# 执行
res = cursor.execute(sql)
# 获取执行SQL语句的结果
# 获取结果的时候类似于管道里面存了结
result = cursor.fetchmany(3)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
print(result)

# 输出结果
[{'id': 1, 'name': 'chosen', 'gender': 'male', 'age': 20, 'hire_date': datetime.date(2024, 5, 29), 'post': 'professor', 'post_comment': None, 'salary': 1000.1, 'office': 501, 'depart_id': 1}, {'id': 2, 'name': 'mengmeng', 'gender': 'female', 'age': 25, 'hire_date': datetime.date(2022, 1, 2), 'post': 'teacher', 'post_comment': None, 'salary': 12000.5, 'office': 401, 'depart_id': 1}, {'id': 3, 'name': 'xiaomeng', 'gender': 'male', 'age': 35, 'hire_date': datetime.date(2019, 6, 7), 'post': 'teacher', 'post_comment': None, 'salary': 15000.99, 'office': 401, 'depart_id': 1}]

【注意】

当上一条查询指令是 fetchall 它会把所有数据都拿出来
所以当再用 fetchone 的时候就拿不到数据了，会返回 None

（4）移动光标

cursor.scroll(1, 'relative')  # 相对于当前位置往后移动一个单位
cursor.scroll(1, 'absolute')  # 相对于起始位置往后移动一个单位

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 创建SQL语句
sql = 'select * from emp;'
# 执行
res = cursor.execute(sql)
# 获取执行SQL语句的结果
# 获取结果的时候类似于管道里面存了结
result = cursor.fetchmany(2)
cursor.scroll(1, 'relative')
result = cursor.fetchone()
print(result)

# 输出结果
{'id': 4, 'name': 'xiaona', 'gender': 'female', 'age': 29, 'hire_date': datetime.date(2018, 9, 6), 'post': 'teacher', 'post_comment': None, 'salary': 11000.8, 'office': 401, 'depart_id': 1}


result = cursor.fetchmany(2)
cursor.scroll(1, 'absolute')
result = cursor.fetchone()
print(result)

# 输出结果
{'id': 2, 'name': 'mengmeng', 'gender': 'female', 'age': 25, 'hire_date': datetime.date(2022, 1, 2), 'post': 'teacher', 'post_comment': None, 'salary': 12000.5, 'office': 401, 'depart_id': 1}

【6】插入数据

插入操作中参数可以以元组、列表和字典形式传入
需要使用到占位符 “%s”，注意这只是个占位符，不同于Python 中字符串格式化中的转换说明符。

转换说明符	解释
%d、%i	转换为带符号的十进制数
%o	转换为带符号的八进制数
%x、%X	转换为带符号的十六进制数
%e	转化为科学计数法表示的浮点数（e 小写）
%E	转化为科学计数法表示的浮点数（E 小写）
%f、%F	转化为十进制浮点数
%g	智能选择使用 %f 或 %e 格式
%G	智能选择使用 %F 或 %E 格式
%c	格式化字符及其ASCII码
%r	使用 repr() 函数将表达式转换为字符串
%s	使用 str() 函数将表达式转换为字符串

（1）方式一：直接写原生的SQL语句

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 插入数据
sql = 'insert into user(username,password) values("chosen","123456");'
# 指定sql语句
cursor.execute(sql)
# 提交事务才可以生效
conn.commit()

（2）方式二：格式化传入参数

# %s 位置站位
import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 插入数据
sql = 'insert into user(username,password) values(%s,%s);'
# 指定sql语句
cursor.execute(sql, ['max', '123789'])
cursor.execute(sql, ('opp', '456789'))
# 提交事务才可以生效
conn.commit()

（3）方式三：关键字传入参数

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 插入数据
sql = 'insert into user(username,password) values(%(username)s,%(password)s);'
# 指定sql语句
cursor.execute(sql, {'username':'james','password':'456789'})
# 提交事务才可以生效
conn.commit()

（4）批量插入数据

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 插入数据
# （1）方案一：遍历每一个数据然后插入数据
name_list = [i for i in "chosen"]
password_list = [str(i) for i in range(6)]
data_all = list(zip(name_list, password_list))
# [('c', '0'), ('h', '1'), ('o', '2'), ('s', '3'), ('e', '4'), ('n', '5')]
# 插入数据
sql = 'insert into user(username,password) values(%s,%s);'
for data in data_all:
    cursor.execute(sql, data)
# 指定sql语句
# 提交事务才可以生效
conn.commit()


# （2）方案2 ： 一次性批量插入数据
name_list = [i for i in "Ptrjkl"]
password_list = [str(i) for i in range(6)]
data_all = list(zip(name_list, password_list))
# [('c', '0'), ('h', '1'), ('o', '2'), ('s', '3'), ('e', '4'), ('n', '5')]
# 插入数据
sql = 'insert into user(username,password) values(%s,%s);'
# 指定sql语句
cursor.executemany(sql, data_all)
# 提交事务才可以生效
conn.commit()

【7】删除数据

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 删除数据
# [1] 直接写SQL语句
sql = 'delete from user where id=2;'
cursor.execute(sql)
# 提交事务
conn.commit()

# [2] 占位
sql = 'delete from user where id=%s;'
cursor.execute(sql, [5])
# 提交事务
conn.commit()

# [3] 关键字占位
sql = 'delete from user where id=%(id)s;'
cursor.execute(sql, {'id': 8})
conn.commit()

【8】更新数据

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)


# 更新数据
# [1]直接写
sql = 'update user set password="666" where id=6;'
cursor.execute(sql)
conn.commit()

# [2]占位
sql = 'update user set password=%s where id=%s;'
cursor.execute(sql, ['963', 1])
conn.commit()

# [3]关键字占位
sql = 'update user set password=%(pwd)s where id=%(id)s'
cursor.execute(sql, {'pwd': '000', 'id': 9})
conn.commit()

【9】SQL注入问题

import pymysql

conn = pymysql.connect(
    user='root',
    password='123456',
    host='localhost',
    database='emp_data',
    port=3306,
    charset='utf8mb4',
    # 自动提交事务
    # autocommit=True
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)


def get_username_password():
    username = input("username:>>>>>").strip()
    password = input("password:>>>>>").strip()
    return username, password


def login():
    username, password = get_username_password()
    sql = 'select * from user where username=%s and password=%s;'
    cursor.execute(sql, [f'{username}', f'{password}'])
    result = cursor.fetchone()
    if result:
        print(f'登录成功！')
    else:
        print(f'登录失败')
    print(result)

if __name__ == '__main__':
    login()

posted @ 2024-06-12 21:24 光头大炮阅读(384) 评论(0) 编辑收藏举报

chosen-yn

Navicat的使用、PDManer、PyMySQL(连接数据库、执行SQL语句、结果获取、插入数据、删除数据、更新数据)

【一】Navicat的使用

【1】连接数据库

【2】创建数据库

【3】导出SQL文件

【4】备份数据库

【5】视图展示

【二】PDManer(元数建模)

【1】软件官网

【三】PyMySQL

【1】介绍

【2】安装

【3】连接数据库

（1）连接语法

（2）主要方法介绍

【4】执行SQL-查询语句

（1）创建游标对象

（2）执行SQL语句

【5】查询结果获取

（1）获取所有结果（fetchall）

（2）获取一行数据（fetchone）

（3）获取查询指定结果数据量（fetchmany）

【注意】

（4）移动光标

【6】插入数据

（1）方式一：直接写原生的SQL语句

（2）方式二：格式化传入参数

（3）方式三：关键字传入参数

（4）批量插入数据

【7】删除数据

【8】更新数据

【9】SQL注入问题

公告