Python__模块(DATA-管理)__MySQL / DBM

MySQL（安装）

pip install PyMySQL

---

MySQL（参考代码）

创建表

import pymysql.cursors
# 连接MySQL数据库
connection = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="新数据库",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)
# 通过cursor创建游标
cursor = connection.cursor()
# 创建sql 语句，并执行
sql = "CREATE TABLE `users` (\
       \
        `id` INT(11) NOT NULL AUTO_INCREMENT,\
        `email` VARCHAR(255) COLLATE utf8_bin NOT NULL,\
        `password` VARCHAR(255) COLLATE utf8_bin NOT NULL,\
         PRIMARY KEY (`id`)\
      \
       ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin\
         AUTO_INCREMENT=1 ;"
cursor.execute(sql)
# 提交SQL
connection.commit()

连接数据库

import pymysql.cursors
# 连接MySQL数据库
connection = pymysql.connect(host='127.0.0.1',
                             port=3306,
                             user='root',
                             password='123456',
                             db='新数据库',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 通过cursor创建游标
cursor = connection.cursor()
# 创建sql 语句，并执行
sql = "INSERT INTO users (email, password) VALUES ('123@.info', '000000')"
cursor.execute(sql)
# 提交SQL
connection.commit()

数据查询

import pymysql.cursors
# 连接MySQL数据库
connection = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="新数据库",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)
# 通过cursor创建游标
cursor = connection.cursor()
# 执行数据查询
sql = "SELECT `id`, `password` FROM `users` WHERE `email`='huzhiheng@itest.info'"
cursor.execute(sql)
# 查询数据库单条数据
result = cursor.fetchone()
print(result)
print("-----------------------")
# 执行数据查询
sql = "SELECT `id`, `password` FROM `users`"
cursor.execute(sql)
# 查询数据库多条数据
result = cursor.fetchall()
for data in result:
    print(data)
# 关闭数据连接
connection.close()

数据获取

import pymysql
# 连接数据库
con = pymysql.connect(
    host="localhost", 
    user="root", 
    password="123456", 
    database="新数据库", 
    charset="utf8"
)
# 创建游标
cur = con.cursor()
# 生成数据库
sql = "select * from users"
# 获取结果
cur.execute(sql)

# 获取所有记录  fetchall--获取所有记录
# fetchmany--获取多条记录，需传参  fetchone--获取一条记录

all = cur.fetchall()
# 输出查询结果
print(all)
# 关闭游标
cur.close()
# 关闭数据库连接，目的为了释放内存
cur.close()

数据插入

import pymysql
# 连接数据库
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="新数据库",
    charset="utf8mb4",
)
# 创建游标
cur = con.cursor()
# SQL插入语句
sql = "INSERT INTO users (email, password) VALUES ('test.info', '000000')"
# 获取结果
cur.execute(sql)
# 提交SQL
con.commit()
# 关闭游标
cur.close()

数据删除

import pymysql
# 连接数据库
con = pymysql.connect(
    host="localhost", 
    user="root", 
    password="123456", 
    database="新数据库", 
    charset="utf8"
)
# 创建游标
cur = con.cursor()
# SQL 删除语句
sql = "DELETE from users WHERE id = 5 "
cur.execute(sql)
con.commit()
# 关闭游标
cur.close()
# 关闭数据库连接，目的为了释放内存
cur.close()

---

DBM（参考代码）

综合例子

# @参数列表 /
# r     只读
# w     写
# n     总是创建一个数据
# c     存在不创建,不存在则创建
# ---------------------------- / 

import dbm
# 创建数据库
with dbm.open("./demo.db", "c") as db:
    db["x"] = "{" + "{0}".format(1) + "}"
    db["key"] = "k"
    db["today"] = "t"
    db["author"] = "a"

# 读取->输出:键值/键名
"""
with dbm.open("demo.db", "r") as db:
    print("获取所有键:", db.keys())
    print(db["author"])
    for k in db.keys():
        print(k, db[k].decode("utf-8"))
"""
# 写入->修改键值
"""
with dbm.open("demo.db", "w") as db:
    try:
        with dbm.open("demo.db", "r") as db:
            print("获取所有键:", db.keys())
            print(db["author"])

            for k in db.keys():
                print(k, db[k].decode("utf-8"))
                db["autho"] = "new"
    except TypeError as err:
        print(err)
"""

---