Jun 01, 2021 Article blog
3. 3. MySQL database installation and configuration
4. 4.1. Connecting database operations
5. 4.2. Create a table operation
6. 4.3.1. The database inserts a single statement
7. 4.3.2. The database inserts multiple statements
10.. 4.5. Database update operation
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.
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.
$ pip install pymysql
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.
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()
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()
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()
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()
Python
MySQL
to get a single piece of data using
fetchone()
and
fetchall()
to get multiple pieces of data.
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()
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()
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()
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.
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.
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