Python如何操作MySQL？

# 不指定版本默认安装最新版本
pip install pymysql

import pymysql


# 连接数据库
conn = pymysql.connect(
    user='root',            # 登录MySQL时的用户名
    password='1026',        # 密码
    host='127.0.0.1',       # MySQL的ip地址，本地就是127.0.0.1
    port=3306,              # MySQL的端口，没有修改过的话就是3306
    database='book_manage',   # 需要操作的数据库的库名
    charset='utf8mb4',        # 数据库的字符集
    autocommit=True           # 数据修改之后是否自动提交，True表示自动提交
)

# 获取游标对象，该对象用来执行SQL语句
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 增--第一种方式execute
sql = 'insert into author(name, age) values ("python", 10), ("java", 5);'
rows = cursor.execute(sql)  # rows是影响数据库的行数
# 增--第二种方式execute+字符串格式化
sql = 'insert into author(name, age) values (%s, %s);'
rows = cursor.execute(sql, ('go', 5))
# 增--第三种方式增加多条，executemany
sql = 'insert into author(name, age) values (%s, %s);'
rows = cursor.executemany(sql, [('php', 10), ('ruby', 15), ('js', 20)])

# 如果在建立连接时没有指定autocommit=True，这里就需要手动提交
conn.commit()  

# 修改数据
sql = 'update author set name = %s where id = %s;'
# 改一条
rows = cursor.execute(sql, ('haha', 2))
# 改多条
rows = cursor.executemany(sql, [('haha', 5), ('xxx', 3)])

# 删除数据
sql = 'delete from author where name = %s and id = %s;'
# 执行一条删除SQL
rows = cursor.execute(sql, ('xxx', 3))
# 执行多条删除SQL
rows = cursor.executemany(sql, [('haha', 2), ('haha', 5)])
# 查询数据
sql = 'select * from author;'
rows = cursor.execute(sql)
# fetchall获取查询的所有结果
result = cursor.fetchall()
print(result)
# fetchone获取查询结果中的第一条
result1 = cursor.fetchone()
print(result1)
# fetchmany获取查询结果中的前n条，括号中写几就获取前几条数据
result2 = cursor.fetchmany(3)

import pymysql


class MyDBHandle:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='123456',
            db='userinfo',
            charset='utf8',
            autocommit=True
        )
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)

    def _db_handle_select(self, *args):
        sql = 'select * from user where username=%s'
        res = self.cursor.execute(sql, args)
        if not res:
            return {}
        else:
            return self.cursor.fetchone()

    def _db_handle_insert(self, *args):
        sql = 'insert into user(username, password) values(%s, %s)'
        self.cursor.execute(sql, args)

    def login_interface(self, name, pwd):
        user_dict = self._db_handle_select(name)
        if not user_dict:
            return False, '用户名不存在'
        else:
            if pwd != user_dict.get('password'):
                return False, '用户名或密码错误'
        return True, '登录成功'

    def register_interface(self, name, pwd):
        user_dict = self._db_handle_select(name)
        if user_dict:
            return False, '用户名已经存在'
        self._db_handle_insert(name, pwd)
        return True, '注册成功'


class MyTest:

    def __init__(self, db_handle_obj):
        self.db_handle = db_handle_obj

    def run(self):
        while 1:
            cmd_list = [('登录', 'login'), ('注册', 'register')]
            for index, item in enumerate(cmd_list):
                print(index, item[0])
            cmd = input('请输入功能编号：').strip()
            if not cmd.isdigit() or int(cmd) not in range(len(cmd_list)):
                continue
            cmd_func = cmd_list[int(cmd)][1]
            if hasattr(self, cmd_func):
                func = getattr(self, cmd_func)
                func()

    def login(self):
        while 1:
            username = input('username>>:').strip()
            password = input('password>>:').strip()
            flag, msg = self.db_handle.login_interface(username, password)
            print(msg)
            if flag: break

    def register(self):
        while 1:
            username = input('username>>:').strip()
            password = input('password>>:').strip()
            re_pwd = input('re_pwd>>:').strip()
            if re_pwd != password:
                print('两次密码输入不一致')
                continue
            flag, msg = self.db_handle.register_interface(username, password)
            print(msg)
            if flag: break


if __name__ == '__main__':

    test = MyTest(MyDBHandle())
    test.run()