Show you what python operates mySQL database

This article is written primarily to introduce Python to the MySQL database and to give you a deeper understanding in conjunction with the appropriate examples. The code examples in this paper are very detailed and have some reference learning value for everyone.

1. What is pymysql?

PyMySQL is a library used in Python3.x to connect MySQL mysqldb is used in Python2 PyMySql the specifications of Python数据库API v2.0 and contains pure-Python MySQL client library.

2. Install PyMySQL

 $ pip install pymysql

3. MySQL database installation and configuration

Before connecting MySQL database with PyMySQL make sure that MySQL database installation configuration is complete and how to install and configure MySQL database, refer to MySQL installation and MySQL management.

4.1. Connecting database operations

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 a 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. The database inserts a single statement

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. The database inserts 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 queries

Python MySQL to get a single piece of data using fetchone() and fetchall() to get multiple pieces of data.

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

• fetchall(): Receive all return result lines;

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

4.4.1. Example of a query

Query all data in the employee table income is greater than 20,000

# 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 action

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 a transaction operation

In database operations, transaction mechanisms ensure data consistency. T he most basic transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four properties are called ACID attributes.

• Atomicity: A transaction is an indivisible unit of work that includes operations that are either done or not done.

• Consistency: A transaction must be to change a database from one consistency state to another. Consistency is closely related to atomicity.

• Isolation: The execution of one transaction cannot be interfered with by other transactions. That is, the operation within one transaction and the data used are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other.

• Durability: Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent actions or failures should not have any effect on them.

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

4.8 Error handling

Some errors and exceptions to database operations are defined in the DB API and the following table lists them:

The above is about Python operation MySQL database explanation, I hope to help you, perhaps students can also go to the following tutorial to learn

python tutorial: https://www.w3cschool.cn/python/

python3 Basic Microsyscope: https://www.w3cschool.cn/minicourse/play/python3course