How to install and configure MySQL database

1. What is pymysql?

PyMySQL is a library used to connect to the MySQL server in the Python3.x version,#mysqldb is used in ##Python2. PyMySql follows the specifications of Python Database API v2.0 and includes the pure-Python MySQL client library.

2. Install PyMySQL

 $ pip install pymysql

3. MySQL database installation and configuration

Before using

PyMySQL to connect to the MySQL database, Please ensure that the MySQL database installation and configuration is complete. For details on how to install and configure the MySQL database, please refer to MySQL installation and MySQL management.

4.1. Connect to database operation

import pymysql


# 数据库服务器名
HOSTNAME = 'node05'
# 数据库用户名
USER = 'root'
# 数据库名
DATABASE = 'cayman'
# 数据库密码
PASSWORD = 'Love88me'


# 打开数据库连接
conn = pymysql.connect(HOSTNAME, USER, PASSWORD, DATABASE)


# 使用cursor()方法创建一个游标对象
cursor = conn.cursor()


# 使用execute()方法执行SQL查询语句
cursor.execute("select VERSION()")


# 使用fetchone()查询单条数据
data = cursor.fetchone()
print(f"Database Version: {data}")


# 关闭数据库连接
conn.close()

4.2. Create table operation

import pymysql


# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor对象创建一个流标对象
cursor = db.cursor()


# 使用execute()方法执行SQL, 如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS employee")


# 使用预处理语句创建表
sql = """ CREATE TABLE employee(
    id bigint primary key auto_increment,
    user_name varchar(50) not null,
    age int,
    sex char(1),
    income float
)
"""


# 执行sql语句
cursor.execute(sql)


# 关闭数据库连接
db.close()

4.3.1. Insert a single statement into the database

import pymysql


# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor对象创建一个流标对象
cursor = db.cursor()


# SQL语句
sql = """
 insert into employee(user_name, age, sex, income) values ('风清扬', 64, '男', 22000);
"""


try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交
    db.commit()
except:
    # 如果发生错误就回滚
    db.rollback()


# 关闭数据库连接
db.close()

4.3.2. Database Insert multiple statements

import pymysql


# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor对象创建一个流标对象
cursor = db.cursor()


# SQL语句
sql = " insert into employee(user_name, age, sex, income) values (%s, %s, %s, %s)"


data = (
    ('风清扬', 64, '男', 22000),
    ('令狐冲', 22, '男', 14000),
    ('任盈盈', 20, '男', 10000),
    ('东方不败', 32, '男', 18000),
    ('任我行', 56, '男', 17000),
    ('段誉', 33, '男', 19000),
    ('王语嫣', 26, '女', 9000),
    ('木婉清', 23, '女', 6000),
    ('乔峰', 38, '男', 23000),
    ('阿朱', 24, '女', 5000),
    ('阿紫', 22, '女', 5500),
    ('虚竹', 35, '男', 11000),
    ('梦姑', 25, '女', 6500),
    ('梅超风', 41, '女', 15000),
    ('陈玄风', 44, '男', 12000),
    ('杨过', 28, '男', 24000),
    ('小龙女', 38, '女', 15000),
    ('鸠摩智', 44, '男', 16000)
)


try:
    # 执行sql语句
    cursor.executemany(sql, data)
    # 提交
    db.commit()
except:
    # 如果发生错误就回滚
    db.rollback()


# 关闭数据库连接
db.close()

4.4. Database query

PythonQueryMySQLUse fetchone() to obtain a single piece of data, Use the fetchall() method to obtain multiple pieces of data.

• fetchone(): This method gets the next query result set. The result set is an object

• fetchall(): Receive all returned result rows;

• rowcount(): This is a read-only property and returns the number of rows affected after executing the execute() method.

4.4.1. Query example

Query

employeeAll data in the tableincome(salary) is greater than 20000

# 1.查询employee表中工资大于20000的员工信息
import pymysql


# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor对象创建一个流标对象
cursor = db.cursor()


# 查询语句
sql = "select * from employee where income >'%d' "%(20000)


try:
    # 执行sql语句
    cursor.execute(sql)
    # 获取所有满足条件的列表
    ret = cursor.fetchall()
    # 遍历打印结果
    for row in ret:
        user_name = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print(f"员工: {user_name}，年龄: {age}, 性别: {sex}, 工资: {income}")
except:
    print("无满足条件的数据或查询出错!!")


# 关闭数据库连接
db.close()

4.5. Database update operation

import pymysql


# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor对象创建一个流标对象
cursor = db.cursor()


# 更新语句
sql = "update employee set income=income+income*0.1 where sex='%c'"%('女')


try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交
    db.commit()


except:
    # 发生错误时回滚
    db.rollback()


# 关闭数据库
db.close()

4.6. Delete operation

import pymysql


# 设置数据库连接信息
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'


# 打开数据库连接
db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)


# 使用cursor()方法获取游标
cursor = db.cursor()


# 构建删除数据SQL语句
sql = "delete from employee where user_name = '%s'"%('鸠摩智')


try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交
    db.commit()


except:
    # 发生异常时回滚
    db.rollback()


# 关闭数据库连接
db.close()

4.7 Perform transaction operation

In database operations, the transaction mechanism can ensure the security of data consistency. The most basic transactions should have four attributes: atomicity, consistency, isolation, and durability. These four properties are called

ACID properties.

• Atomicity: A transaction is an indivisible unit of work. All operations included in the transaction are either done or none.

• #In a database, a transaction must transform the database from one state to another consistent state to achieve consistency. Consistency and atomicity are closely related.

• Isolation refers to the ability of a transaction to execute without interference from other transactions.. Within a transaction, the data operated and used are closed to other concurrent transactions, so concurrently executed transactions cannot interfere with each other.

• Durability: Durability, also known as permanence, means that once a transaction is submitted, its changes to the data in the database should be Permanent. Subsequent operations or failures should not have any impact on it.

Python DB API 2.0 transactions provide two methods commit and rollback. For database programming that supports transactions, when the stream mark is established, an invisible database transaction is automatically started.

The above is the detailed content of How to install and configure MySQL database. For more information, please follow other related articles on the PHP Chinese website!