Python模块—Pymysql模块

PyMySQL模块

Python—连接mysql数据库代码。

• 原理图
  

from pymysql import connect, Error

class Mysql:
    """初始化Mysql数据库"""
    def __init__(self, hostname, port, username, password):
        self.hostname = hostname
        self.port = port
        self.username = username
        self.password = password

    """创建数据库连接对象"""
    def conn_db(self):
        try:
            conn = connect(host=self.hostname, port=self.port, user=self.username, password=self.password)
            return conn
        except Error as e:
            print(e)

    """执行SQL语句"""
    def exec_sql(self, sql):
        connection = None
        try:
            connection = self.conn_db()
            with connection.cursor() as cursor:
                cursor.execute(create_database)	# 没有数据库，第一次需要创建。
                cursor.execute(choice_database)	# 选择数据库
                cursor.execute(sql)				# 执行SQL语句
                if 'CREATE' in sql or 'INSERT' in sql or 'UPDATE' in sql or 'DELETE' in sql or 'DROP' in sql:
                    connection.commit()
                    print("数据操作成功！")
                elif 'SHOW' in sql or 'SELECT' in sql:
                    result = cursor.fetchall()
                    print(f"result:{result}")
                    print("数据库读取成功!")
        except Error as e:
            print(e)
        finally:
        	# 关闭游标
        	cursor.close()
        	# 关闭连接
            connection.close()


if __name__ == '__main__':
    open_sql = Mysql(hostname='127.0.0.1', port=3306, username='root', password='123456')
    open_sql.exec_sql(update_users)	# 单个SQL语句的执行
    sqls = [create_users_table, insert_users, update_users, delete_user, drop_table, drop_database]	# 多个SQL语句执行
    for sql in sqls:
        open_sql.exec_sql(sql)

标准化数据库连接格式（后续会持续更新）

# 创建数据库
create_database = """
CREATE DATABASE IF NOT EXISTS students
"""

# 选择数据库
choice_database = """
use students
"""

# 建表
create_users_table = """
CREATE TABLE IF NOT EXISTS users(
    id INT AUTO_INCREMENT,
    name TEXT NOT NULL,
    age INT,
    gender TEXT,
    PRIMARY KEY (id)
)
"""

# 插入记录，注意：`users`, `name`, `age`, `gender`，不是引号包裹，是键盘左上角的撇号
insert_users = """
INSERT INTO 
`users` (`name`, `age`, `gender`)
VALUES
('zhangsan',23,'male'),
('lisi',24,'female'),
('wangwu',25,'male');
"""

# 更改
update_users = """
UPDATE `users` set `name`='jack' where `name` = 'zhangsan'
"""

# 查询
select_users = "SELECT * FROM users"

# 删除记录
delete_user = """
DELETE from users where `name`="lisi"
"""

# 删除表
drop_table = "DROP table users"

# 删除数据库
drop_database = "DROP database students"